Skip to content
Related Articles

Related Articles

Delete Action in MS SQL Server

View Discussion
Improve Article
Save Article
  • Last Updated : 28 Aug, 2020

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


Table – Marks


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

foreign key references Student(Name) 
ON Delete Action

Output –

Table – Student


Table – Marks


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.
My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!