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 –

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

foreign key references 
Student(rollnumber) 
ON Update Action

Output –

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.