FOREIGN KEY Constraints: NO ACTION, CASCADE, RESTRICT, SET NULL

FOREIGN KEY Constraints in MySQL (NO ACTION, CASCADE, RESTRICT, SET NULL).
Here I test what happens when delete one to one,one to many and many to many relations with foreign keys with options NO ACTION, CASCADE, RSTRICT, SET NULL.

Department Employee  1:N relation.

department  
id name
5dep1

employee
id name department_id
11sam5

ON DELETE NO ACTION
1) Delete a row in department  -> Fail, (foreign key constraint fails)
2) Delete a row in employee -> Success

ON DELETE RESTRICT
1) Delete a row in department  -> Fail, (foreign key constraint fails)
2) Delete a row in employee -> Success

ON DELETE SET NULL
1) Delete a row in department  -> Success, (department_id in employee table set to NULL)
2) Delete a row in employee -> Success

ON DELETE CASCADE
1) Delete a row in department  -> Success, (Related row in employee table also deleted)
2) Delete a row in employee -> Success



 Employee Project M:N relation (emp_project middle table)

employee
id name department_id
11sam5

project
id name
2project1
emp_project
emp_id project_id
112

ON DELETE NO ACTION
1) Delete a row in employee -> Fail, (foreign key constraint fails)
2) Delete a row in project-> Fail, (foreign key constraint fails)
3) Delete a row in emp_project -> Success

ON DELETE RESTRICT
1) Delete a row in employee -> Fail, (foreign key constraint fails)
2) Delete a row in project-> Fail, (foreign key constraint fails)
3) Delete a row in emp_project -> Success 

ON DELETE SET NULL
Can not  SET NULL since middle table foreign keys are composite primary key.

ON DELETE CASCADE
1) Delete a row in employee -> Success(Related row in emp_project table also deleted)
2) Delete a row in department  -> Success, (Related row in emp_project table also deleted)
3) Delete a row in emp_project -> Success 



Employee Profile 1:1 relation.

employee
id name department_id
11sam5
profile
id emp_id interests
611swimming

ON DELETE NO ACTION
1) Delete a row in profile->Success
2) Delete a row in employee -> Fail, (foreign key constraint fails)

ON DELETE RESTRICT
1) Delete a row in profile-> Success
2) Delete a row in employee -> Fail, (foreign key constraint fails)

ON DELETE SET NULL
1) Delete a row in profile-> Success
2) Delete a row in employee -> Success (emp_id in profile table set to NULL)

ON DELETE CASCADE
1) Delete a row in profile-> Success,
2) Delete a row in employee -> Success (Related row in profile table also deleted)




No comments:

Post a Comment