THE IMPORTANCE OF FOREIGN KEY CONSTRAINT COLUMNS ORDER

Due to the order of the multiple columns definition, creating a foreign
key constraint may generate an error. Tables that have multiple column
primary key definitions have an ordering property, which users must 
adhere
to when creating a foreign key constraint that references a primary key
with multiple columns.

A work around to this problem is to create a unique constraint in a
different order definition. The following is a sample script that 
illustrates
this scenario. An error is generated when a foreign key is defined with
an order different than the referencing primary key constraint columns.

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 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_pkey1, T1_pkey2)
      REFERENCES Table_1 (pkey1, pkey2))
GO
CREATE TABLE Table_3       -- ERROR WILL BE GENERATED
(pkey1 INT NOT NULL,
 T1_pkey1 INT NOT NULL,
 T1_pkey2 INT NOT NULL,
 col1 INT NULL,
CONSTRAINT pk_Table_3 PRIMARY KEY (pkey1),
CONSTRAINT fk_Table_1_Table_3 FOREIGN KEY (T1_pkey2, T1_pkey1)
      REFERENCES Table_1 (pkey2, pkey1))
GO
            -- UNIQUE CONSTRAINT TO ALLEVIATE ERROR
ALTER TABLE Table_1 ADD CONSTRAINT ak_Table_1 unique (pkey2, pkey1)
GO
CREATE TABLE Table_3      -- ERROR RESOLVED
(pkey1 INT NOT NULL,
 T1_pkey1 INT NOT NULL,
 T1_pkey2 INT NOT NULL,
 col1 INT NULL,
CONSTRAINT pk_Table_3 PRIMARY KEY (pkey1),
CONSTRAINT fk_Table_1_Table_3 FOREIGN KEY (T1_pkey2, T1_pkey1)
      REFERENCES Table_1 (pkey2, pkey1))
GO
DROP TABLE Table_3
GO
DROP TABLE Table_2
GO
DROP TABLE Table_1
GO

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