Constraints In Microsoft SQL Server 2000 - Cascading Updates And Deletes

The processing of making automatic deletion and updates is known as Cascading. We cannot insert a row with a Foreign key value (except NULL) if there is no Candidate key with that value. The ON DELETE clause controls what actions are taken if we attempt to delete a row to which existing Foreign keys point. The ON DELETE clause has the following two options:

1. NO ACTION specifies that the deletion fails by producing an error.
2. CASCADE specifies that all the rows with Foreign keys pointing to the deleted row are also deleted.

The ON UPDATE clause defines the actions that are taken if you attempt to update a candidate key value to which existing Foreign keys point. It also supports the NO ACTION and CASCADE options.

The following code (Example 8) illustrates theses concepts. The Order_Parts table establishes a Foreign key referencing the Car_Parts table defined earlier. Usually, Order_Parts would also have a Foreign key against an order table, but this is a simple example.


CREATE TABLE Order_Parts
(Order_NO int,   
Part_NO   int; FOREIGN KEY REFERENCES Car_Parts(Part_NO)
ON DELETE 
NO ACTION, qty_ordered int)
GO


To sum it all up: when a value other than NULL is entered into the column of a Foreign key constraint, the value must exist in the referenced column; otherwise, a Foreign key violation error message is returned. Foreign key constraints can reference only tables within the same database on the same server. Cross-database referential integrity must be implemented through triggers. Foreign key constraints can reference another column in the same table (a self-reference). The References clause of a column-level Foreign key constraint can list only one reference column, which must have the same data type as the column on which the constraint is defined.

The references clause of a table-level Foreign key constraint must have the same number of reference columns as the number of columns in the constraint column list. The data type of each reference column must also be the same as the corresponding column in the column list. Cascade may not be specified if a column of type timestamp is part of either the Foreign key or the referenced key. It is possible to combine Cascade and No Action on tables that have referential relationships with each other. If SQL Server encounters No Action, it terminates and rolls back related Cascade actions. When a Delete statement causes a combination of Cascade and No Action actions, all the Cascade actions are applied before SQL Server checks for any No Action. Foreign key constraints are not enforced on temporary tables. Foreign key constraints can reference only columns in Primary key or Unique constraints in the referenced table or in a Unique index on the referenced table.

Next: Unique Constraints >>
 

More Database Code Articles
More By Gayathri Gokul