Foreign key to 2 tables
-
Hi,
I'd like to know if there is a way to create 2 foreign keys from a single column to 2 different tables and have them be "OR" between them. That is, to check if a value exists in either one of two tables. For example:
Table A:
Column 1: Dept ID
references
Table B:
Column 1: Dept 1 ID
OR
Table C
Column 1: Dept 2 ID
Thanks
-
This was removed by the editor as SPAM
-
You can write a trigger on base table for this type of referential integrity (FK).
-
To do this in schema, you'd need two columns (FKtoTableB and FKtoTableC). If each column was NULLable, you could then write a view that uses the COALESCE function to snag the appropriate column.
Hope this helps!
Take care..- Ward Pond
blogs.technet.com/wardpond -
What you going to do if the key values overlap? You wont know which table its really pointing at if there is a matching record in both.
umm i reckon you would be better off with two foreign keys columns.
Put a check constraint on the table which checks that at least one of them is null so that the record cant be associated with both tables.
something like
sign(isnull(FK1,0)) +sign(isnull(FK2,0)) = 1
-
for my suggestion the key values wouldn't overlap because they would be EXCLUSIVE OR [XOR]
which is similar in concept to what the other guys are suggesting with the only difference being that instead of having two columns as suggested in the other solution you would be having one column in my proposed solution
-
>why would you want to break the referential integrity of the db introduce triggers that have to be maintained and
-in no way does using triggers break the referential integrity. in actual effect you can use triggers to enhance referential integrity more so in a case like this one, where you have two separate tables
>ensure that the domain of you foreign keys are exclusive when you can simply use an extra col and keep the db based on proper foriegn key constraints.
- you would need to have exclusive keys because when you do a union of the two referenced tables you should supposedly have complicity to normal forms design.
-
yes triggers can maintain denormailised figure and ensure fk constraint accross dbs but this is not the place to use them .
" use triggers to enhance referential integrity more so in a case like this one, where you have two separate tables" err yes two seperate table both with child records. that what you use foreign keys for to ensure the chiild records dont get auphoned.
What is the point of trying to cram two keys into one column?? What advantage does it have? I can only see disaster in the future, this solution is difficult to implement, intensive to maintain and very inflexible.
"you would need to have exclusive keys because when you do a union of the two referenced tables you should supposedly have complicity to normal forms design."
if it is meaingful to union the two tables and they both have a primary keys which refence the same child tables why are they seperate tables???
This is just bad design. I think you need to sort out the schema first then if you need to need to implement a many to many join use a junction table.
-
the solutions we have all given are with in the context of the little info that we know of the problem.as to the use of either method it probably boils down to personal preference.
if i knew more about the problem probably i would be in a better position to advice on a good and bad design.
-
Jules is correct. This strikes me as a violation of first normal form.
Remember, the ability to do a particular thing doesn't necessarily make it a good idea..
Take care..- Ward Pond
blogs.technet.com/wardpond
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply