Presents your 
SQL SERVER E-NEWSLETTER for July 18, 2002

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

REFERENCE UNIQUE INDEXES OF PARENT TABLES WITH A FOREIGN KEY

You may want to create a foreign key that references a unique index of
the parent table. First, however, exhaust the possibility of using the
parent table's primary key referenced by the foreign key. Additionally, 
if
the parent table's primary key is insufficient, use a unique constraint 
on
the parent table referenced by the foreign key as the next best
potential candidate. If all else fails, you can create a foreign key 
that
references a unique index.

The following is a sample script that demonstrates the use of unique
indexes on parent tables referenced by a foreign key.

USE pubs
GO
CREATE TABLE Table_1
(pkey1 INT NOT NULL,
 pkey2 INT NOT NULL,
 col1 INT NULL,
CONSTRAINT pk_Table_1 primary key (pkey1, pkey2))
GO
CREATE unique nonclustered INDEX ak_Table_1 on Table_1 (pkey2, pkey1)
GO
CREATE TABLE Table_2
(pkey1 INT NOT NULL,
 T1_pkey1 INT NOT NULL,
 T1_pkey2 INT NOT NULL,
 col1 INT NULL,
CONSTRAINT pk_Table_2 PRIMARY KEY (pkey1),
CONSTRAINT fk_Table_1_Table_2 FOREIGN KEY (T1_pkey2, T1_pkey1)
          REFERENCES Table_1 (pkey2, pkey1))
GO
DROP TABLE Table_2
GO
DROP TABLE Table_1
GO

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