Composite FK

Question

Post reply

Composite FK

szahran

Valued Member

Points: 71
More actions
July 14, 2008 at 12:26 am

#92269

I have a table with a composite PK , and there is another table must has a reference to this table . the question is how to reference a composite PK,
is there is anything called composite FK if so how to create it in SQL Server 2005..
i need a solution other than inserting an auto number field to reference.
Thanks.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 1

If a PK looks like this:

Col1

,Col2

,Col3

Then in order to reference that as a foreign key, the code would look something like this:

ALTER TABLE [schema].[tablename]

CONSTRAINT [MyFKName] FOREIGN KEY

(Col1

,Col2

,Col3)

REFERENCES [schema].[parenttable]

(Col1

,Col2

,Col3)

The names of the columns can be different, but the data types should be the same. Other than that, have fun.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Kayal-717623 Ten Centuries Points: 1390 More actions · Answer 2

Hi Grant,

Can a foreign key be a composite key even if the primary key of the table it references is not a composite key. For eg. If Part# is a primary key in a table. Can the foreign key be Part# + PartName + PartSerial.

Thanks in advance.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

In order to use a foreign key constraint you have to have either a primary key or a unique index/constraint on the table to which you are referencing. Otherwise, you'd have to use a trigger to enforce integrity, with all the headaches and maintenance issues that it brings.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Seggerman-675349 Hall of Fame Points: 3563 More actions · Answer 4

I've seen this in Oracle, where call records (telcom application) are keyed on phone number, customer number and effective date (and possibly rate plan)

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 5

mailsar (7/14/2008)
Can a foreign key be a composite key even if the primary key of the table it references is not a composite key. For eg. If Part# is a primary key in a table. Can the foreign key be Part# + PartName + PartSerial.

No. If the primary key (or unique constraint) consists of a single column, the foreign key can only be a single column if the same datatype. If the pk/constraint consists of multiple columns, the fk can only be the same number of columns of the same datatype.

In your example, if pk is Part# but you want to refer to Part#, PartName, PartSerial from another table, define the three columns in a unique constraint or index. You can have multiple unique constraints defined for a table (the primary key is, of course, always one of them) and you can have a fk refer to any of them.

Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms

Kayal-717623 Ten Centuries Points: 1390 More actions · Answer 6

Thanks Tomm and Grant :). That makes sense. I appreciate your replies.