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

Question

   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 1

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;