In this video, we will learn about Cascading referential integrity constraint

Cascading referential integrity constraint allows to define the actions Microsoft SQL Server should take when a user attempts to delete or update a key to which an existing foreign keys points.

For example, consider the 2 tables shown below. If you delete row with ID = 1 from tblGender table, then row with ID = 3 from tblPerson table becomes an orphan record. You will not be able to tell the Gender for this row. So, Cascading referential integrity constraint can be used to define actions Microsoft SQL Server should take when this happens. By default, we get an error and the DELETE or UPDATE statement is rolled back.

However, you have the following options when setting up Cascading referential integrity constraint1. No Action: This is the default behaviour. No Action specifies that if an attempt is made to delete or update a row with a key referenced by foreign keys in existing rows in other tables, an error is raised and the DELETE or UPDATE is rolled back.

2. Cascade: Specifies that if an attempt is made to delete or update a row with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are also deleted or updated.

3. Set NULL: Specifies that if an attempt is made to delete or update a row with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are set to NULL.

4. Set Default: Specifies that if an attempt is made to delete or update a row with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are set to default values.

Yes, You can write a query for cascading, but you can not alter the constraint,so if your table already have a foreign key and you want to set cascade, then you have to first drop that constraint and then add foreign key with cascade, below is the code.

Primary Key having default value doesn't make sense. What if you you don't provide value to PK for two records, Default constraint will kick in and try to give them default and two PK records will have same value contradicting PK constraint itself. So PK cant have default constraint. In order for the cascade default action to take, the FK column should have Default constraint.