March 2, 2005 at 12:46 am
Can we have two foreign keys defined on a column of a table. The two keys are seperate for each table
March 2, 2005 at 1:45 am
I think it is not possible and it will lead to poor design. The fact is, you shouldn't try to give two different meanings for a single column.
If this is what you wanted to achieve, better create one more columns and create a foreign key on it.
Thanks,
Ganesh
P.S: Please add script, when posting message and this would help everyone to give you a solution.
March 2, 2005 at 1:49 am
In addition to that, when you do a cascade delete on the parent table will lead to ambiguity.
thanks,
Ganesh
March 3, 2005 at 1:42 am
I have to say that I don't understand the question... what does it mean, 2 foreign keys on one column? Is it like you want to have a FK column that will sometimes refer to the table Users, but in some other record to the table Employees? Well, if "separate keys" means that the same value can appear in both these tables, how would then the program know which of these tables to use? If this is what you asked, the answer is emphatic NO.
Maybe if you explain why did you want to do it, we could think about how to achieve the result some other way...
HTH, Vladan
March 3, 2005 at 6:48 am
SQL itself cannot define a single column as FK to 2 seperate tables. You have heard all the arguements as to why.
However that said, you can create triggers to enforce the condition which gives you the relative flexibility to create a FK to 2 seperate tbales.
Don't have time to build an example right now but basically it requires more work and tought on your part for design.
March 3, 2005 at 7:04 am
hi Antares686
Thanks for the help. I have used a trigger for the same
March 3, 2005 at 7:13 am
Can you tell us why you need to have a column point to 2 different foreign keys? I cannot imagine a situation where I would have to do this and I'm a little puzzled by this.
March 3, 2005 at 7:32 am
I have
Table 1 having Main Items1 AND THE Manufacturers
(I1, I2, I3)
Table 2 having multiple Substitute Items for the above Main Items
I1 - S1
I1 - S2
I1 - S3
Also with above requirement, I have to store Multiple manufacturer's for the Main Items and the Substitute Items. These Manufactures I was thinking of storing in one table
MainItem/SubstitueItem ManufacturerID
I1 - M1
I1 - M2
I1 - M3
S1 - M1
S1 - M2
S2 - M3
Wanted a key on column "MainItem/SubstitueItem" that would act as a foreign key on table 1 and table 2
This is just a short description of my requirement
March 3, 2005 at 8:23 am
I think triggers will be your best bet. I've seen similar things, and I would think that in such a situation that you're not going to do cascading updates/deletes, so you shouldn't have any ambiguities since it's more of a reference thing.
But if it's just for use as a reference in a query, you reference the table twice with two different aliases. I see the possibility of union queries in your future...
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
March 3, 2005 at 8:56 am
Wouldn't it be better to store all items (main and substitute) in one table and define the substitution using a substitution table with columns MainItem, SubstituteItem? If any item can only be either main or substitute (not both), just add ItemType column that will hold this info.
Why did you decide to put substitute items into a different table? What advantage you will have if you make it that way? I can't see any at the moment, only complications...
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply