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.
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
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.
Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.
- Update Action in MS SQL Server
- Delete statement in MS SQL Server
- Delete Database in MS SQL Server
- Delete Duplicates in MS SQL Server
- Difference between Structured Query Language (SQL) and Transact-SQL (T-SQL)
- SQL | DELETE Statement
- Performing Database Operations in Java | SQL CREATE, INSERT, UPDATE, DELETE and SELECT
- SQL Server Mathematical functions (SQRT, PI, SQUARE, ROUND, CEILING & FLOOR)
- SQL Server Identity
- SQL SERVER | Conditional Statements
- SQL Server | STUFF() Function
- SQL Server | SERVERPROPERTY()
- Mean and Mode in SQL Server
- Allow only alphabets in column in SQL Server
- SQL SERVER | IN Condition
- Reverse Statement Word by Word in SQL server
- Copy tables between databases in SQL Server using Import-and-Export Wizard
- Difference between DELETE and DROP in SQL
- Difference between MySQL and MS SQL Server
- Comparisons between Oracle vs SQL Server
If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to firstname.lastname@example.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.
Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.