Tuning the Schema - Professional SQL Server 2005 Performance Tuning - page 211

In this example the OrderDetail table contains a column called OrderID. It is linked to the the OrderID
column in the Order table. Adding this constraint has several effects. First, whenever a new row is
inserted into the OrderDetail table that row must contain a value for OrderDetailID. Also, that value
must exist in the Order table. For example, suppose the Order table contained OrderID values between
1 and 10. If you attempt to insert a row in the OrderDetail table where the OrderID value is 25, an error
will occur and the insert will fail.

Another aspect of the constraint affects the Order table. If you attempt to delete a row from this table,
and that row's OrderID value is used in the OrderDetail table, the delete will fail. That's because
allowing the delete to succeed would mean that an OrderDetail row would be ''orphaned'' from its
parent Order row.

One way around this is by defining cascading rules for the foreign key constraints. This gives you the
ability to define the actions that SQL Server 2005 takes when deleting or updating a foreign key.

The REFERENCE clause in the example above support ON DELETE and ON UPDATE options. The valid
options for these statements are: NO ACTION, CASCADE, SET NULL, SET DEFAULT.

NO ACTION: This is the default. This should be self-explanatory, this option means that the nat-
ural behavior of the foreign key will apply.

❑

CASCADE: This means that the change will be cascaded through the foreign key relationship.

❑

SET NULL: This means that the foreign key column will be reset to NULL if possible.

❑

SET DEFAULT: This means that the foreign key column will be reset to the default value defined
for the column.

❑

Given the previous action, the following options are available:

On Delete Cascade: Adding the option ON DELETE CASCADE to the OrderDetail foreign key
would mean that if a row in the Order table was deleted, then ALL the rows in the OrderDetail
where the OrderID matched the deleted row's OrderID would also be deleted.

❑

❑ ON DELETE SET NULL: All the values in the foreign key column are reset to NULL. In the

OrderDetail example, when a row is deleted from Order all the rows in OrderDetail where
the OrderID equals the OrderID from the deleted rowwould get a NULL.

❑ ON DELETE SET DEFAULT: All the values in the foreign key column are reset to default value.

In the OrderDetail example, when a row is deleted from Order all the rows in OrderDetail where
the OrderID equals the OrderID from the deleted rowwould get the default value defined for the
column,inthiscase-1.

ON UPDATE CASCADE: Any change made to the key column is percolated down to all for-
eign keys. Again, using the OrderDetail constraint. If an ON UPDATE CASCADE was added
to the constraint definition then any change to the OrderID value in the Order table would be
performed on any OrderDetail rows where the OrderID values matched. For example, sup-
pose you wanted to change the OrderID value in Order from 5 to 15. Updating the value in
Order would also cause the value of OrderID in OrderDetail to be updated where the OrderID
was 5.

❑

ON UPDATE SET NULL: All the values in the foreign key column are reset to NULL. In the
OrderDetail example, when a row is updated in the Order table all the rows in OrderDetail
where the OrderID equals the OrderID from the updated row would get a NULL.

❑