63

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
  • 42,737
  • 46
  • 157
  • 243
Black Eagle
  • 1,107
  • 2
  • 11
  • 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

3 Answers3

104

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
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 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
  • 3
    @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
  • 1
    @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
  • 234,701
  • 27
  • 340
  • 448
0

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.

Albos Hajdari
  • 113
  • 1
  • 12