Foreign key relationship with composite primary keys in SQL Server 2005
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?
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.
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.