Creating a composite foreign key in SQL Server 2008

Question

I have two tables that I'd like to create a foreign key for.

Primary Table

PK - Key1 - varchar(20) PK - Key2 - date 

Secondary Table

PK - AutoID FK - Key1 - varchar(20) FK - Key2 - date 

When I try to create the relationship between the primary and secondary table, I keep getting the message

The columns in the Primary Table do not match a primary key or unique constraint.

There can be many records in the secondary table with the same Key1 and Key2 so we made the primary key an auto created number.

Any thoughts on how I can set up the foreign key relationship between these two tables?

Answer 1

A foreign key MUST refer to columns that compose a unique index (PK or UK) with the same number of column, their types and order. E.g.:

CREATE TABLE PrimaryTable (   Key1 varchar(20),   Key2 date) GO  ALTER TABLE PrimaryTable ADD CONSTRAINT PK   PRIMARY KEY (Key1, Key2) GO  CREATE TABLE SecondaryTable (   AutoID int IDENTITY,   Key1 varchar(20),   Key2 date) GO  ALTER TABLE SecondaryTable ADD CONSTRAINT FK   FOREIGN KEY (Key1, Key2) REFERENCES PrimaryTable (Key1, Key2) GO 

Answer 2

Some of this is focused, some of this is context for others having any sort of problem like this (like anyone actually searches first?)

The first thing to check when you have a problem creating a key is make sure you did not mismatch the data types in the two tables. If you have an bigint in one and an int in the other, it will blow. This is true on all keys, but more likely to crop up if you use multiple fields. Simple math shows the reason why the chance increases.

The next issue is data. If you cannot create the key due to data, you have to find out what exists in the child table that does not exist in the parent table. LEFT JOIN the tables (secondary on the second/left side of the join) and only include rows where the primary table is null. You will either have to create these records in the parent table or get rid of them.

One way "around" this is set up a new primary key on the parent table. You then create a foreign key on this new primary key and match as many records as you can in the child table. You then have the join set up and you can go about cleaning as a secondary operation.

Which is better? New primary key or working with the composite key? This really depends on the nature of the data, but I am more fond of using a derived key over a natural key or a composite key. But, there are times where the work necessary to get a single field derived key is a lot of work.