Update Action in MS SQL Server

Referential Actions allow a user to either update or delete a column from the parent table. If a column is removed from the parent table, it is removed from the child table immediately.

Syntax :

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

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

Table – Student

Name Rollnumber Course
Maya 111 CSE
Riya 112 Mech

Table – Marks

Name Rollnumber Marks
Maya 111 8.9
Riya 112 7.9

SQL query to perform the Update Action on the table is :

foreign key references 
ON Update Action

Output –

Table – Student

Name Rollnumber Course
Maya 110 CSE
Riya 111 Mech

Table – Marks

Name Rollnumber Marks
Maya 110 8.9
Riya 111 7.9

This updates the rollnumber column from the entire parent and child table as well. In the syntax, the constraint is not a mandatory term to include. One can use it whenever necessary. There are other permitted actions – no action, cascade, set null, set default.

  • On Update No Action –
    It raises an error and rolls back the update action on the parent table.

  • On Update Cascade –
    The cascade action updates all the rows from the parent and child table.

  • On Update Set Null –
    The rows from the parent and child table are set to null only if the foreign key is nullable.

  • On Update Set Default –
    The child table rows are set to default if the corresponding parent table rows are updated only if the foreign key has default definitions.

My Personal Notes arrow_drop_up

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.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.

Article Tags :
Practice Tags :

Be the First to upvote.

Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.