sozai sozai - 3 years ago 163
MySQL Question

Delete row from child table when a row from parent table gets deleted

CREATE TABLE LECTURER
(LectID VARCHAR(12) NOT NULL,
Name VARCHAR(30) NOT NULL,
Email VARCHAR(30) NOT NULL,
Faculty VARCHAR(20) NOT NULL,
CONSTRAINT LECTURER_pkey PRIMARY KEY (LectID));


CREATE TABLE NOK
(LectID VARCHAR(12) NOT NULL,
Name VARCHAR(30) NOT NULL,
DOB DATE NOT NULL,
CONSTRAINT NOK_pkey PRIMARY KEY (LectID, Name, DOB),
CONSTRAINT NOK_fkey FOREIGN KEY (LectID) references LECTURER (LectID));


I have created 2 table in mysql , How can I make sure, when row from
LECTURER
table gets deleted associated row from
NOK
should also get deleted ?

Answer Source

You need to use ON DELETE CASCADE in NOK table.

change

CONSTRAINT NOK_fkey FOREIGN KEY (LectID) references LECTURER (LectID)

to

CONSTRAINT NOK_fkey FOREIGN KEY (LectID) references LECTURER (LectID)
ON DELETE CASCADE

Edited ==

Before executing above script, you need to execute below, so that you won't get any error.

ALTER TABLE NOK DROP FOREIGN KEY NOK_fkey;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download