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.