New Declarative Referential Integrity Actions

The two declarative referential integrity (DRI) actions available in SQL Server 2000 are NO ACTION and CASCADE. When you create or alter a table, you can define the DRI actions on foreign key columns to tell SQL Server what to do when the referenced row is deleted from the parent table. For example, when defining the ContactDetails table, you can specify CASCADE to delete the rows when the referred row is deleted from the Customer table, or you can specify NO ACTION, in which case SQL Server raises an error indicating that you cannot delete the rows from Customer table because they are being referenced in the ContactDetails table.

SQL Server 2005 introduces two new DRI actions: SET NULL and SET DEFAULT. When SET NULL is specified, first you have to make sure that the foreign key column is nullable, and then, when rows from the parent table are deleted, SQL Server puts NULL in the referenced foreign key column. When SET DEFAULT is specified, first you have to make sure that the column is either nullable or has a default definition, and then when rows from the parent table are deleted, SQL Server puts the default value in the referenced foreign key column. If a default constraint is not defined on the column and the column is nullable, SQL Server puts NULL in that column.

In addition to using the delete operation, you can also specify SET NULL and SET DEFAULT actions for the instances when the referenced row is updated in the parent table.

Here is an example of the new SET NULL and SET DEFAULT actions:

CREATE TABLE dbo.tblMaster    (CustomerID INT PRIMARY KEY NOT NULL,     Name VARCHAR(100)); GO CREATE TABLE dbo.tblChild1    (AddressID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,     CustomerID INT NULL FOREIGN KEY REFERENCES dbo.tblMaster (CustomerID)     ON DELETE SET NULL,     AddressLine1 VARCHAR(100)); GO CREATE TABLE dbo.tblChild2    (AddressID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,     CustomerID INT NOT NULL DEFAULT -1     FOREIGN KEY REFERENCES dbo.tblMaster (CustomerID)     ON DELETE SET DEFAULT,     AddressLine1 VARCHAR(100)); GO INSERT INTO dbo.tblMaster SELECT 1, 'Customer1'; INSERT INTO dbo.tblMaster SELECT 2, 'Customer2'; INSERT INTO dbo.tblMaster SELECT 3, 'Customer3'; INSERT INTO dbo.tblMaster SELECT -1, 'Invalid Customer'; INSERT INTO dbo.tblChild1 SELECT 1, 'Customer1Address'; INSERT INTO dbo.tblChild1 SELECT 2, 'Customer2Address'; INSERT INTO dbo.tblChild1 SELECT 3, 'Customer3Address'; INSERT INTO dbo.tblChild2 SELECT 1, 'Customer1Address'; INSERT INTO dbo.tblChild2 SELECT 2, 'Customer2Address'; INSERT INTO dbo.tblChild2 SELECT 3, 'Customer3Address'; DELETE dbo.tblMaster WHERE CustomerID = 2; GO SELECT * FROM dbo.tblChild1; SELECT * FROM dbo.tblChild2; GO

This script creates a table that is referenced by two other tables. For the first referenced table, tblChild1, you specify the DRI action to set the foreign key column to NULL if the row is deleted from the parent table. For the second referenced table, tblChild2, you specify the DRI action to set the foreign key column to a default value of -1 if the row is deleted from the parent table. In the case of the default value, the value must be present in the parent table (Customer, with -1 as the CustomerID in this case). When the customer with CustomerID as 2 is deleted, the corresponding CustomerID foreign key column in tblChild1 is set to NULL, and the corresponding CustomerID foreign key in the tblChidl2 table is set to the default value of -1.