Delete Action in MS SQL Server

Prerequisite – Foreign key in MS SQL Server

A column can be inserted in a child table only if it is a part of the parent table in case of a foreign key. The foreign key allows us to perform other actions called Referential Actions. Referential actions allow to update or delete a row in case of the parent table. If a user has updated/deleted a column in the parent table, the column is being automatically updated/deleted in the child table also.

Syntax :

foreign key(foreign-key_constraint) 
references parent_table(parentkey_column) 
ON Delete Action

Consider two tables – Student (Parent Table) and Marks (Child Table) from the University database. If a user wants to delete a column, then it has to be done as shown below :

Table – Student

Name	Rollnumber	Course
Maya	111	CSE
Riya	112	Mech

Table – Marks

Name	Rollnumber	Marks
Maya	111	7.8
Riya	112	7.6

SQL query to perform the Delete Action on the table is –

foreign key references Student(Name) 
ON Delete Action

Output –

Table – Student

Name	Rollnumber	Course
–	111	CSE
–	112	Mech

Table – Marks

Name	Rollnumber	Marks
–	111	7.8
–	112	7.6

This deletes the rows from the entire parent and child table as well. There are other permitted actions – no action, cascade, set null, set default.

• On Delete No Action –
  It raises an error and rolls back the delete action on parent table.
• On Delete Cascade –
  The cascade action deletes all the rows from the parent and child table.
• On Delete Set Null –
  The rows from the parent and child table are set null only if the foreign key is nullable.
• On Delete Set Default –
  The child table rows are set to default if the corresponding parent table rows are deleted only if the foreign key has default definitions.