How to Add 'On Delete Cascade' in Alter Table Statement

How to add 'ON DELETE CASCADE' in ALTER TABLE statement

You can not add ON DELETE CASCADE to an already existing constraint. You will have to drop and re-create the constraint. The documentation shows that the MODIFY CONSTRAINT clause can only modify the state of a constraint (i-e: ENABLED/DISABLED...).

How to add on delete cascade constraints?

I'm pretty sure you can't simply add on delete cascade to an existing foreign key constraint. You have to drop the constraint first, then add the correct version. In standard SQL, I believe the easiest way to do this is to

• start a transaction,
• drop the foreign key,
• add a foreign key with on delete cascade, and finally
• commit the transaction

Repeat for each foreign key you want to change.

But PostgreSQL has a non-standard extension that lets you use multiple constraint clauses in a single SQL statement. For example

alter table public.scores
drop constraint scores_gid_fkey,
add constraint scores_gid_fkey
   foreign key (gid)
   references games(gid)
   on delete cascade;

If you don't know the name of the foreign key constraint you want to drop, you can either look it up in pgAdminIII (just click the table name and look at the DDL, or expand the hierarchy until you see "Constraints"), or you can query the information schema.

select *
from information_schema.key_column_usage
where position_in_unique_constraint is not null

ALTER TABLE to add ON DELETE CASCADE statement

I found the answer:

    //in Fruit object
    @OneToMany(mappedBy = "fruit", orphanRemoval=true)
    private List<Grape> grapes;

   //in Grape object
   @OneToOne
   private Fruit fruit;

How to add on delete cascade option in mysql?

You have a typo in the Order table. You have PRIMAY where it should be PRIMARY.

After correcting this, I tried creating the tables and all statements worked fine, including the last one.

Trying to 'Delete Cascade' from an already created table with 'alter'

Your best option is to drop the account column from task. When you need to task with account information just get it through project. This also makes maintenance must easier. With account in both task and project how do you ensure both accounts are the same.

Since Postgres does not have alter constraint... you need to drop and recreate the FK in project and task. Following are the alter statements you need.

alter table task drop column account; 
alter table task drop constraint task_project_fkey;        -- verify constraint name 
alter table task add constraint  task_project_fkey
                     foreign key (project) 
                     references project(id)  
                     on delete cascade; 
alter table project drop constraint project_account_fkey;    -- verify constraint name 
alter table project add constraint  project_account_fkey
                        foreign key (account) 
                        references account(id)  
                        on delete cascade;               

As you took the lazy way of defining FK, you will have to verify the constraint names.


NOTE: It is poor practice to name a column the same a the table it is in. So perhaps project.name (or project.description) instead of project.project. Especially since any FK referring to project table do not refer to the
project column in that table. Of course the same applies task and any other table in your system.

Add ON DELETE CASCADE to existing column in Laravel

Drop foreign key first. Thanks to Razor for this tip

$table->dropForeign('answers_user_id_foreign');
$table->foreign('user_id')
->references('id')->on('users')
->onDelete('cascade');

---

Related Topics