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 Student(rollnumber) 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.
Contact Us