53

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?

gotqn
  • 36,464
  • 39
  • 145
  • 218
Black Eagle
  • 999
  • 2
  • 10
  • 18
  • composite keys are foreign keys. That being said, you need to include all columns from your composite key in order to make the relationship with table2 to work. – Arthur Zennig May 24 '21 at 11:10

2 Answers2

94

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_s
  • 675,133
  • 158
  • 1,253
  • 1,388
  • 1
    Is this standard SQL or only for MS SQL server? – Jus12 Nov 09 '14 at 05:46
  • 2
    @Jus12: that's standard SQL – marc_s Nov 09 '14 at 08:12
  • But Table1 does not have a column "FileType", do you mean he should add a column to Table1 solely for the purpose of creating the constraint? – BornToCode Nov 11 '14 at 05:05
  • 2
    @BornToCode: if he wants to be able to reference that compound primary key using a foreign key, then he **must** add the `FileType` column to the `Table1` so he can create the FK relationship. – marc_s Nov 11 '14 at 05:44
  • I understand. Doesn't this imply that the table design could have been probably done better by making a separate table where FileID is the primary key? – BornToCode Nov 11 '14 at 09:55
  • @BornToCode: well, the question really is whether or not it is *necessary* to have *both columns* in the primary key for `Table1`. If `FileID` is really an `ID` (unique, probably `IDENTITY(1,1)`), then there's usually no need to add a second column to your primary key... – marc_s Nov 11 '14 at 10:31
4

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.

Damien_The_Unbeliever
  • 220,246
  • 21
  • 302
  • 402