Builder
http://builder.com.com

Presents your 
SQL SERVER E-NEWSLETTER for August 15, 2002

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

CHECK FOR VALID DATA IN NULLABLE FOREIGN KEYS

When you have two columns that comprise the primary key, and a child
table inherits the primary keys as nullable foreign keys, you may have 
bad
data. You can insert a valid value into one of the foreign key columns 
and
null into the other foreign key column. Then, you can add a table check
constraint that checks for valid data in the nullable foreign keys.

This anomaly may occur for any multi-column foreign key. If the foreign
key consists of two or more columns, it is a candidate for this 
anomaly.
You will need to add a check constraint to test for the anomaly.
Initially, the check constraint will check for nullable values in all 
columns,
which comprise the foreign key. The check constraint will also check 
for
nonnullable values within these columns. If both checks pass, then the
anomaly should be circumvented.

The following is a sample script that illustrates this anomaly and the
check constraint that fixes it.

SET NOCOUNT ON
GO
CREATE TABLE parent (pkey1 INT IDENTITY NOT NULL, pkey2 INT NOT NULL,
col1 INT NULL,
CONSTRAINT pk_parent PRIMARY KEY NONCLUSTERED ( pkey1, pkey2))
GO
INSERT parent (pkey2) VALUES ( 2 )
INSERT parent (pkey2) VALUES ( 85 )
INSERT parent (pkey2) VALUES ( 41 )
INSERT parent (pkey2) VALUES ( 11 )
GO
SELECT * FROM parent
GO
CREATE TABLE child (cpkey1 INT IDENTITY NOT NULL
   CONSTRAINT pk_child PRIMARY KEY NONCLUSTERED (cpkey1),
pkey1 INT NULL, pkey2 INT NULL, col1 INT NULL,
   CONSTRAINT fk_parent_child FOREIGN KEY (pkey1, pkey2)
   REFERENCES parent (pkey1, pkey2))
GO
INSERT child (pkey1, pkey2) VALUES ( null, 85 )
GO
SELECT * FROM child
GO
DELETE child
GO
ALTER TABLE child WITH NOCHECK
   ADD CONSTRAINT ck_fk_parent_child CHECK
      ( (pkey1 IS NOT NULL AND pkey2 IS NOT NULL) OR
        (pkey1 IS NULL AND pkey2 IS NULL) )
GO
INSERT child (pkey1, pkey2) VALUES ( null, 11 )
GO
DROP TABLE child, parent
GO

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