Herza I. M. Herza I. M. - 27 days ago 6
MySQL Question

Delete relative row from three different tables in mySQL

I have the following tables.

corp_id (PK) | corp_name

----------------------------------------

1                   | freshfruit

2                   | realsteel

3                   | firmwall


corp_id (FK)| empl_id (PK) | empl_name

--------------------------------------------------------

1                   | 1                   | andy

1                   | 2                   | maria

2                   | 3                   | john

2                   | 4                   | neil

3                   | 5                   | stephan

3                   | 6                   | darwing


empl_id (FK)| client_id (PK)| client_name

---------------------------------------------------------------

1                   | 1                    | moris

1                   | 2                    | bean

1                   | 3                    | bay

3                   | 4                    | phill

4                   | 5                    | hank

5                   | 6                    | suzy


If I delete row in table 1, how do I get to delete rows in table 2 which are the related FK to the deleted row in table 1, and also I get to delete rows in table 3 which are the related FK to the deleted rows in table 2? Thanks in advance.

Answer

Drop the currently exisitng foreign key constraints and then add them again with ON DELETE CASCADE:

ALTER TABLE table2 DROP FOREIGN KEY corp_id

ALTER TABLE table2
ADD CONSTRAINT fk_2
FOREIGN KEY (corp_id) REFERENCES table1 ON DELETE CASCADE;

Make similar changes for the third table.