Hi All,
Hope you all are doing good.
In this article, we will know about various referential actions of Foreign Key.
The foreign key constraint ensures the referential integrity.
It means that you can only insert a row into the child table if there is a corresponding row in the parent table.
Besides, the foreign key constraint allows you to define the referential actions when the row in the parent table is updated or deleted.
syntax: FOREIGN KEY (foreign_key_columns)
REFERENCES parent_table(parent_key_columns)
ON UPDATE action
ON DELETE action;
The ON UPDATE and ON DELETE specify which action will execute when a row in the parent table is updated and deleted. The following are permitted actions:
Delete actions of rows in the parent table
If you delete one or more rows in the parent table, you can set one of the following actions:
ON DELETE NO ACTION: SQL Server raises an error and rolls back the delete action on the row in the parent table.
ON DELETE CASCADE: SQL Server deletes the rows in the child table that is corresponding to the row deleted from the parent table.
ON DELETE SET NULL: SQL Server sets the rows in the child table to NULL if the corresponding rows in the parent table are deleted. To execute this action, the foreign key columns must be nullable.
ON DELETE SET DEFAULT: SQL Server sets the rows in the child table to their default values if the corresponding rows in the parent table are deleted. To execute this action, the foreign key columns must have default definitions. Note that a nullable column has a default value of NULL if no default value specified.
By default, SQL Server applies ON DELETE NO ACTION if you don’t explicitly specify any action.
Update action of rows in the parent table
If you update one or more rows in the parent table, you can set one of the following actions:
ON DELETE NO ACTION: SQL Server raises an error and rolls back the update action on the row in the parent table.
ON DELETE CASCADE: SQL Server updates the corresponding rows in the child table when the rows in the parent table are updated.
ON DELETE SET NULL: SQL Server sets the rows in the child table to NULL when the corresponding row in the parent table is updated. Note that the foreign key columns must be nullable for this action to execute.
ON DELETE SET DEFAULT: SQL Server sets the default values for the rows in the child table that have the corresponding rows in the parent table updated.
Thanks for reading my article.
Hope you all are doing good.
In this article, we will know about various referential actions of Foreign Key.
The foreign key constraint ensures the referential integrity.
It means that you can only insert a row into the child table if there is a corresponding row in the parent table.
Besides, the foreign key constraint allows you to define the referential actions when the row in the parent table is updated or deleted.
syntax: FOREIGN KEY (foreign_key_columns)
REFERENCES parent_table(parent_key_columns)
ON UPDATE action
ON DELETE action;
The ON UPDATE and ON DELETE specify which action will execute when a row in the parent table is updated and deleted. The following are permitted actions:
- NO ACTION
- CASCADE
- SET NULL
- SET DEFAULT
Delete actions of rows in the parent table
If you delete one or more rows in the parent table, you can set one of the following actions:
ON DELETE NO ACTION: SQL Server raises an error and rolls back the delete action on the row in the parent table.
ON DELETE CASCADE: SQL Server deletes the rows in the child table that is corresponding to the row deleted from the parent table.
ON DELETE SET NULL: SQL Server sets the rows in the child table to NULL if the corresponding rows in the parent table are deleted. To execute this action, the foreign key columns must be nullable.
ON DELETE SET DEFAULT: SQL Server sets the rows in the child table to their default values if the corresponding rows in the parent table are deleted. To execute this action, the foreign key columns must have default definitions. Note that a nullable column has a default value of NULL if no default value specified.
By default, SQL Server applies ON DELETE NO ACTION if you don’t explicitly specify any action.
Update action of rows in the parent table
If you update one or more rows in the parent table, you can set one of the following actions:
ON DELETE NO ACTION: SQL Server raises an error and rolls back the update action on the row in the parent table.
ON DELETE CASCADE: SQL Server updates the corresponding rows in the child table when the rows in the parent table are updated.
ON DELETE SET NULL: SQL Server sets the rows in the child table to NULL when the corresponding row in the parent table is updated. Note that the foreign key columns must be nullable for this action to execute.
ON DELETE SET DEFAULT: SQL Server sets the default values for the rows in the child table that have the corresponding rows in the parent table updated.
Thanks for reading my article.
No comments:
Post a Comment