SQL Server Performance Forum – Threads Archive

foreign key

Is it possible to have a FK that references part of a composite PK in another table? If not, what are the ways to work around this?

Yes, it is possible.
Nathan H.O.
Moderator
SQL-Server-Performance.com

i am getting an error. "Parent cannot be part of a primary key."

You can use triggers in this case. Oryan

At what point are you getting this error? I have done this sort of thing several times so I guess we are missing something here.

are you sure you can have a FK in one table that references a parent in another table that is part of a composite PK?
Visio does not like this when I "Generate".

bfarr23, Just would like to clarify your question. Let’s say you have field1, field2 as the composite PK and you only want to reference field1 to be your FK…is this your goal? I am using SQL Server 2000 and I don’t think it is possible to have a FK reference a part of a composite PK. But this is interesting If it is really possible I would like to know how. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] As a workaround you might want to consider using a many-to-many relationships by defining a junction table. Please refer to BOL for details. Jon M

]]>