Wednesday, 6 February 2013

cascade delete on existing table

Cascade delete is a technique used to delete foreign key records (that points to the deleted primary key ) automatically if you are deleting primary key records.

We can set cascade delete while creating table :

CREATE TABLE hard_candy 
   (candy_num INT, 
    candy_flavor CHAR(20),
    FOREIGN KEY (candy_num) REFERENCES all_candy

However to perform cascade delete on existing table:

you can not add ON DELETE CASCADE to an already existing constraint. You will have to drop foreign key constraint and then recreate the constraint
1. First I need to view my foreign key constraint name for the table:

mysql to find foreign key detais of table

select * from information_schema.table_constraints where table_name = 'subheadingtb'

2. Now drop the foreign key constraint using

ALTER TABLE tablename
DROP FOREIGN KEY constraintname

3. Now create the constraint with same name using DELETE CASCADE

ALTER TABLE childtablename
ADD CONSTRAINT constraintname
FOREIGN KEY (coloumnname_of_childtable_which_has_foreign_key)
REFERENCES parenttablename(coloumnname_of_parenttable_which_has_primary_key) ON DELETE CASCADE

For more detail on cascade delete: