Now update CountryID in the Countries for a row which also updates the referencing rows in the child table States. To know the update and delete actions in the foreign key, query sys. Replace the constraint name in the script. The below example demonstrates this scenario.
We will create a foreign key now with cascade as delete rule on States table which references to CountryID in parent table Countries. But on Cities table, we have a foreign key constraint with no action for delete and the referenced value still exists in the table. In order to tell the SQL Server what actions to perform whenever a user trying to delete or update a primary key value to which existing foreign key points, we are provided with the following options while working with Cascading Referential Integrity Constraints.
If a user tries to delete or update statement s that will affect rows in the foreign key table, then those values will be set to NULL when the primary key record is deleted or updated in the Primary key table. The important thing that we need to keep in mind that the foreign key columns affected must allow NULL values.
If a user tries to delete the statement s which will affect the rows in the foreign key table, then those rows will be deleted when the primary key record is deleted. Similarly, if an update statement affects rows in the foreign key table, then those rows will be updated with the value from the primary key record after it has been updated.
If a delete or update statement affects rows in a foreign key table, then all rows containing those foreign keys are set to the default value. All foreign key columns in the related table must have default constraints defined on them. This is the default action that SQL Server performs. This specifies that if an update or deletes statement affects rows in foreign key tables, then the action will be denied and rolled back. An error message will be raised.
Insert some sample data using below T-SQL script. Now, Check Records. Now update CountryID in the Countries for a row which also updates the referencing rows in the child table States. To know the update and delete actions in the foreign key, query sys. Replace the constraint name in the script. The below example demonstrates this scenario. We will create a foreign key now with cascade as delete rule on States table which references to CountryID in parent table Countries.
But on Cities table, we have a foreign key constraint with no action for delete and the referenced value still exists in the table.
0コメント