How can I add ON DELETE constraint on the table?

Question

score 78 · Answer 1 · answered Jan 14 '11 at 18:19

78

Use ALTER TABLE+ADD CONSTRAINT. E.g. if you want to link tables members and profiles by member_id and cascade delete profiles each time the member is deleted, you can write something like this:

ALTER TABLE profiles
   ADD CONSTRAINT `fk_test`
   FOREIGN KEY (`member_id` )
   REFERENCES `members` (`member_id` )
   ON DELETE CASCADE

If you will need to update that constraint - you'll have to remove it at then create again, there's no direct way to alter it.

ALTER TABLE profiles DROP FOREIGN KEY `fk_test`

answered Jan 14 '11 at 18:19

Pavel Dubinin

2,410
1
24
28

2

Was not aware that you could not alter a foreign key but you had to recreate it. Saved me a lot of time thank you! – Dylan Pierce Jul 29 '15 at 12:00
Used this for "on UPDATE cascade" too and worked fine (quite obviously). Used for a massive update on many constrained tables. – Diego1974 Jan 29 '18 at 14:08
Do you know if PostgreSQL let to perform this alter without remove foreign before? – Stéphane Klein Sep 27 '22 at 14:12

score -5 · Answer 2 · answered Oct 26 '16 at 15:12

-5

If the foreign key is already created, there is a trick that worked for me. You can modified the dump of your database and import it again with the modifications.

If you are using Mysql and Linux shell, it'd be like this:

First, export your database:

$ mysql -u <user> -p <namedatabase> > database.sql

Then, open the database.sql file and look for the table you want to alter.

Add ON DELETE CASCADE at the end of the foreign key sentence and save it.

Second, import your database with the modifications:

$ mysql -u <user> -p <namedatabase> < database.sql

And you will have your ON DELETE CASCADE working.

answered Oct 26 '16 at 15:12

Caampa

1,547
1
8
3

6

This is a really dangerous solution – CodeMonkey Nov 10 '16 at 21:45
Or you can disable foreign key checks, alter your constraint and turn them back on, without the overhead of reimporting the entire database. – mopsyd Apr 26 '17 at 05:56

How can I add ON DELETE constraint on the table?

2 Answers2

Linked