Creating a composite foreign key in SQL Server 2008
I have two tables that I'd like to create a foreign key for.
PK - Key1 - varchar(20) PK - Key2 - date
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?
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.
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
This will work:
CREATE TABLE PTable ( Key1 varchar(20) not null, Key2 date not null, constraint PK_PTable PRIMARY KEY (Key1,Key2) ) CREATE TABLE STable ( AutoID int IDENTITY(1,1) not null primary key, Key1 varchar(20) not null, Key2 date not null, constraint FK_STable_PTable FOREIGN KEY (Key1,Key2) references PTable (Key1,Key2) )
What you need to do is get Management Studio to script your tables and compare them to the above.
There are some good answers here, but I would like to take this a step further - for the sake of posterity.
A foreign key does have to reference either a Primary key (Unique, Clustered Index) or a Unique constrained column in another table. Basically, the necessary component is the Unique constraint. I would add that you can have nullable columns in your foreign key, BUT if you do allow nulls in a "composite" key SQL skips the verification of the data in the foreign key relationship. This is an important point to remember as the main reason most of us use foreign keys is to ensure data integrity across our databases.
On a final note, I like to explicitly declare all my key names. Why, you might ask? If you need to use "Full-Text Indexing" in the future for better search capabilities, not doing so forces you to reference all the "auto-generated" names of the keys. This may not be a big deal for small projects that do not require data transformation or scheduled Full-Text index updates, but if you are scripting this functionality you could make your job more difficult (e.g. having to look up the actual name of your Primary Key's default name: pk_someTable_1248594832828495904).
Here is what I would do in writing the SQL to avoid any future pitfalls:
- Do not allow NULLs on Composite foreign keys if possible.
- Name keys explicitly using an agreed upon naming convention (e.g. PK_Schema/56_TalbeName_Col1_Col2). Not only does this give you a standard name for the key, but you can easily see from the index what columns are referenced and in which order.
CREATE TABLE MySchema.PrimaryTable ( Key1 varchar(20) NOT NULL, Key2 date NOT NULL, CONSTRAINT PK_MySchema_PrimaryTable_Key1_Key2 PRIMARY KEY (Key1, Key2) ) GO CREATE TABLE MySchema.SecondaryTable ( AutoID int IDENTITY, Key1 varchar(20) NOT NULL, Key2 date NOT NULL, CONSTRAINT FK_MySchema_SecondaryTable_Key1_Key2 FOREIGN KEY (Key1, Key2) REFERENCES PrimaryTable (Key1, Key2) ) GO
OptillectTeam is basically dead on with his answer. I just wanted to clarify a few important things that had not been mentioned before. There is a good reference on the MSDN sight discussing this and more on foreign keys: Foreign Key Constraint.
FOR ADDING UNIQUE KEY USE BELOW QUERY
ALTER TABLE [TableName] ADD UNIQUE ([Column1], [Column2]);