How to Edit a Table in Order to Enable Cascade Delete

How do I edit a table in order to enable CASCADE DELETE?

Read this Microsoft article first. Read Me. I use the GUI during design so here is a picture of how it is selected in SSMS.

The syntax added to the foreign key is " ON DELETE CASCADE "

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...).

Cascading delete in Hibernate: What order to delete from tables?

What you do is so-called bulk delete. It is performant, but has some drawbacks:

• does not respect cascade
• cannot use joins (what is somewhat logical)

You can remove children entities before, what would be most performant solution.

There is also another solution, to make use of Cascade option (actually it's anti-pattern if many rows should be deleted) - you can iterate over entities you want to delete and call Session.delete() for each.

update

Providing that you have @OneToMany association between Parent and Children entity, you can simply ask for all Children which would be affected and remove them beforehand:

session
  .createQuery("DELETE FROM Children c WHERE EXISTS (SELECT p FROM Parent p WHERE p.price='PRICED' AND c IN p.children)")
  .executeUpdate();

Then you can safely remove your Parent entities:

session
  .createQuery("DELETE FROM Parent p WHERE p.price='PRICED'")
  .executeUpdate();

More concise, but way less performant would be to query for all Parent entities you want to delete and then remove them manually:

List<Parent> parents = session
  .createQuery("SELECT p FROM Parent p WHERE p.price='PRICED'");
for (Parent parent : parents) {
    session.delete(parent);
}

Laravel adding cascade on delete to an existing table

Drop foreign key first then add it

$table->dropIndex('researach_id');
$table->foreign('research_id')
->references('id')->on('researches')
->onDelete('cascade');

Reference:
Laravel -> Database: Migrations -> Foreign Key Constraints

How do I edit a table in order to enable CASCADE DELETE?

Read this Microsoft article first. Read Me. I use the GUI during design so here is a picture of how it is selected in SSMS.

The syntax added to the foreign key is " ON DELETE CASCADE "

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

Cascading deletes like ON DELETE CASCADE for a one time operation in MySQL

No, the simple answer is, no, there is no shortcut.

You either write down DELETE statements to delete all the related rows in the related tables or you have defined foreign key constraints with ON DELETE CASCADE.

Note that - as long as there are no circular paths in the foreign key relationships - it is possible to use a single DELETE statement that deletes from multiple tables:

DELETE a, b, c, d
FROM a
  LEFT JOIN b  ON  b.a_id = a.a_id
  LEFT JOIN c  ON  c.a_id = a.a_id
  LEFT JOIN d  ON  d.b_id = b.b_id 
WHERE
    a.a_id = 1 ;

How to set up cascading deletes in MySQL workbench?

Right Click On Table -> Alter Table -> Foreign Keys Tab

Look at the upper-right corner "Foreign Key Options", there are two drop down boxes, one is to specify the delete action: "On Delete"

---

Related Topics