Foreign key relationship with composite primary keys in SQL Server 2005

I have two tables

Table1(
  FileID,
  BundledFileID,
  Domain)

and

Table2(
  FileID,开发者_如何学C
  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.

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.