SQL Server Foreign Key Update and Delete Rules

• Foreign key constraints are used to maintain integrity among related data in different tables. 
• It may be possible that foreign key value is NULL which indicates a particular record has no parent record. But if a value exists, then it is bound to have an associated value in a parent table.
• When applying update or delete operations on parent tables there may be different requirements about the effect on associated values in child tables. There are four available options in SQL Server 2005 and 2008 as follows:


• No Action
• Cascade
• SET NULL
• SET Default

	Update operation on parent table	Delete operation on parent table
No Action	Not allowed. Error message would be generated.	Not allowed. Error message would be generated.
Cascade	Associated values in child table would also be updated.	Associated records in child table would also be deleted.
Set NULL	Associated values in child table would be set to NULL. Foreign key column should allow NULL values to specify this rule.	Associated values in child table would be set to NULL. Foreign key column should allow NULL values to specify this rule.
Set Default	Associated values in child table would be set to default value specified in column definition. Also default value should be present in primary key column. Otherwise basic requirement of FK relation would fail and update operation would not be successful. If no default value is provided in foreign key column this rule could not be implemented.	Associated values in child table would be set to default value specified in column definition. Also default value should be present in primary key column. Otherwise basic requirement of FK relation would fail and delete operation would not be successful. If no default value is provided in foreign key column this rule could not be implemented.

Steps for applying the rule

1. In Object Explorer, right-click the table with the constraint, and click Design.
   The table opens in Table Designer.
2. From the Table Designer menu, click Relationships.
3. In the Foreign Key Relationships dialog box, select the relationship in the Selected Relationship list.
4. In the grid, click Delete Rule or Update Rule and choose an action from the drop-down list box to the left of the property.

Hope its helpful.


For any queries you can put comments.