SQL Server Performance Forum – Threads Archive
Foreign Key Relation
Hi, I have a doubt in creating the foreign key constraints. Can I create a relation like, many-parents-one-child ? I mean there are 3 PK tables, related to one FK table. My expectation is while entering data in child table, PK should be existing in any one of the parent table. Is it possible to do like this. Or what is the work-around for this. Thanks in advance. RegardsBabu
You cannot do this using sql servers’s built-in constraints.
If the parent structures are all similar, you could consider adding them all to the same parent table, and add a ‘parenttype’ field, the FK constraints could then be used. You may also be able to add some custom constraints to do this instead. Failing that, do your checking logic in a stored procedure and prohibit access to inserts into table , other than through the stored proc.
You’re describing a table with Foreign Key relationships. Just make sure that the FK columns are NOT NULL, this way you cannot have rows without matching values on the remote tables.
Hi Chappy,Adriaan, Thanks for your feed-back. I’m planning to go ahead with implementing triggers on child tables. Since the wirte operation is less and the read will be very high on these tables, I think that could be the best solution here. With Regards
Babu
The only point for using triggers is to avoid ugly messages in the client app. Another way to avoid those ugly messages is to build the same checks you want to do in the triggerm, into the client app immediately before it attempts to save data.
]]>