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

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

LEARN AN APPROPRIATE USE OF NULL TABLE VALUES

There's a situation in SQL Server where Declarative Referential
Integrity (DRI) is not enforced because nulls are allowed in the table
referencing the parent table. Even though the parent table doesn't contain
null values, the child table may contain null values in the column that's
referencing the parent table's primary or unique constraint.

This may be appropriate when the value from the parent table is
currently unknown. For example, the parent table may be an address 
table and the child table may contain contact information. The contact 
address, which is to be passed to the parent table, may be temporarily 
unknown for any number or reasons. This is a time-dependent issue where null
values may be appropriate.

Let's look at an example. In the following code, the parent table is
created and two values are inserted.

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

In the code below, the child table is created, and a null value is
inserted into the column referencing the parent table.

CREATE TABLE Child
(pkey1 INT IDENTITY
    CONSTRAINT pkChild PRIMARY KEY,
Parentpkey1 INT NULL
    CONSTRAINT fkChildParent FOREIGN KEY
    REFERENCES Parent(pkey1),
col1 INT NULL)
GO
INSERT Child (Parentpkey1, col1) VALUES (null,2)
GO

In the following code, the values are selected from both the parent and
child tables. Even though the parent table doesn't contain null values,
the child table will allow a null value for the column referencing the
parent table.

Finally, the tables are dropped to clean up the database objects for
this demonstration.

SELECT * FROM Child
GO
SELECT * FROM Parent
GO
DROP TABLE Child, Parent
GO

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