Presents your 
SQL SERVER E-NEWSLETTER for October 8, 2002

<------------------------------------------->

Automatically DELETE child rows when a parent is deleted

This tip will help you gain a better understanding of how cascading
referential integrity works in SQL Server 2000. Let's take a look 
through a sample script explaining the concept.

First, let's create a parent table that will be referenced by a child
table's foreign key. Two values will be inserted into this parent 
table.

SET NOCOUNT ON
GO
CREATE TABLE Parent
(pkey1 INT NOT NULL
    CONSTRAINT pkParent PRIMARY KEY,
col1 INT NULL)
GO
INSERT Parent (pkey1, col1) VALUES (1, 284)
GO
INSERT Parent (pkey1, col1) VALUES (2, 326)
GO

Second, we'll create a child table with a column that has a foreign key
to the parent key. Notice the two clauses ON DELETE CASCADE and ON 
UPDATE CASCADE within the foreign key constraint declaration. The ON DELETE
CASCADE will cause all the children of the child table to be deleted 
when the referencing values are deleted from the parent table. The ON UPDATE
CASCADE will cause the values of the child table to be updated when the
referencing value in the parent table is updated.

CREATE TABLE Child
(pkey1 INT IDENTITY
    CONSTRAINT pkChild PRIMARY KEY,
Parentpkey1 INT NULL
    CONSTRAINT fkChildParent FOREIGN KEY
    REFERENCES Parent(pkey1)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
col1 INT NULL)
GO

Next, values will be inserted into the child table to reference the two
values inserted in the parent table. We'll select from the tables to
verify the values in both tables.

INSERT Child (Parentpkey1, col1) VALUES (1,12)
GO
INSERT Child (Parentpkey1, col1) VALUES (1,180)
GO
INSERT Child (Parentpkey1, col1) VALUES (1,1693)
GO
INSERT Child (Parentpkey1, col1) VALUES (null,83)
GO
INSERT Child (Parentpkey1, col1) VALUES (2,286)
GO
INSERT Child (Parentpkey1, col1) VALUES (2,29)
GO
SELECT * FROM Child
GO
SELECT * FROM Parent
GO

Now we'll initiate a DELETE on the parent table and then SELECT to
verify the rows in the child table are deleted as a result.

DELETE Parent WHERE pkey1 = 1
GO
SELECT * FROM Child
GO
SELECT * FROM Parent
GO

Next, we'll initiate an UPDATE on the parent table and SELECT from the
two tables to validate the UPDATE took place in the child table. The
following step also deletes the tables to clean up the database objects 
used
for this demonstration.

UPDATE Parent SET pkey1 = 4
WHERE pkey1 = 2
GO
SELECT * FROM Child
GO
SELECT * FROM Parent
GO
DROP TABLE Child, Parent
GO

----------------------------------------