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
5	dep1

employee

id	name	department_id
11	sam	5

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
11	sam	5

project

id	name
2	project1

emp_project

emp_id	project_id
11	2

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
11	sam	5

profile

id	emp_id	interests
6	11	swimming

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)