Foreign key relationship with composite primary keys in SQL Server 2005
Sql Server-2005Foreign Key-RelationshipComposite Primary-KeySql Server-2005 Problem Overview
I have two tables
Table1(
FileID,
BundledFileID,
Domain)
and
Table2(
FileID,
FileType,
FileName)
In Table2 FileID
and FileType
are the composite primary key. I want to create a foreign key relationship from Table1.FileID
to Table2
.
Is it possible to do this?
Sql Server-2005 Solutions
Solution 1 - Sql Server-2005
Since Table2 has a composite primary key (FileID, FileType)
, then any reference to it must also include both columns.
ALTER TABLE dbo.Table1
ADD CONSTRAINT FK_Table1_Table2
FOREIGN KEY(FileID, FileType) REFERENCES Table2(FileID, FileType)
Unless you have a unique constraint/index on the Table2.FileID
field (but if so: why isn't this the PK??), you cannot create a FK relationship to only parts of the PK on the target table - just can't do it.
Solution 2 - Sql Server-2005
marc has already given a pretty good answer. If the rows in Table1 only ever relate to one type of File (e.g. FileType 'ABC'), then you can add FileType to Table1 as a computed column:
ALTER TABLE Table1 ADD FileType as 'ABC'
Which you can then use in the Foreign Key.
Solution 3 - Sql Server-2005
I did this using SQL Server Management Studio:
First reference foreign keys, then make composite primary key.
When you create Table2, don't make a primary key at all in the beginning. First create the foreign key of Table2.FileID with Table1.FileID. And then set the composite key for Table2 (Table2.FileID, Table2.FileType).
Same concept goes if FileType needs to be a foreign key as well. First reference both of the foreign keys and then create the composite key.