Foreign key relationship with composite primary keys in SQL Server 2005

Question

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?

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
– Arthur Zennig, Commented May 24, 2021 at 11:10

marc_s · Accepted Answer · 2010-10-22 12:45:05Z

106

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.

answered Oct 22, 2010 at 12:45

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Jus12 Over a year ago

Is this standard SQL or only for MS SQL server?

marc_s Over a year ago

@Jus12: that's standard SQL

BornToCode Over a year ago

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?

marc_s Over a year ago

@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 Over a year ago

@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...

|

Damien_The_Unbeliever · Accepted Answer · 2010-10-22 12:54:40Z

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.

Albos Hajdari · Accepted Answer · 2022-02-11 12:40:21Z

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.

Collectives™ on Stack Overflow

Foreign key relationship with composite primary keys in SQL Server 2005

3 Answers 3

6 Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

Comments

Linked

Related