Add ON DELETE CASCADE To Foreign Key Constraint

Home » Add Constraint On Delete Cascade Mysql » Add ON DELETE CASCADE To Foreign Key Constraint

Maybe your like

  • Search Search
  • About TIL Today I Learned is an open-source project by Hashrocket that exists to catalogue the sharing & accumulation of knowledge as it happens day-to-day. Posts have a 200-word limit, and posting is open to any Rocketeer as well as selected friends of Hashrocket. We hope you enjoy learning along with us.
    • twitter /hashrockettil
    • github /hashrocket/tilex
  • Statistics
  • Newsletter
  • Surprise Me
Add ON DELETE CASCADE To Foreign Key Constraint

The alter table command lets you do quite a bit. But when it comes to altering existing constraints, there is not much you can do. If you want to add an on delete cascade to an existing foreign key constraint, you are going to need two statements.

The first statement will drop the constraint and the second statement will recreate it with the addition of the on delete clause. Furthermore, you'll want to do this in a transaction to ensure the integrity of your data during the transition between indexes.

Here is an example:

begin; alter table orders drop constraint orders_customer_id_fkey; alter table orders add constraint orders_customer_id_fkey foreign key (customer_id) references customers (id) on delete cascade; commit;

source

Tweet
  • #sql
  • permalink
  • raw
  • likes 25
See More #sql TILs Looking for help? Hashrocket developers believe that data quality is as important as code quality. We enjoy all the challenges of relational databases, from finding the fastest index, to structuring data to fit the needs of an application. We're eager to share our experiences; check out PG Casts, our series of free PostgreSQL screencasts.

Tag » Add Constraint On Delete Cascade Mysql