Two Foreigh Keys on the same columns

Question

Two Foreigh Keys on the same columns

Sanjay Masawan

Ten Centuries

Points: 1031
More actions
March 2, 2005 at 12:46 am

#87735

Can we have two foreign keys defined on a column of a table. The two keys are seperate for each table

Viewing 10 posts - 1 through 9 (of 9 total)

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

babuganesh2000 Hall of Fame Points: 3123 More actions · Answer 1

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.

babuganesh2000 Hall of Fame Points: 3123 More actions · Answer 2

In addition to that, when you do a cascade delete on the parent table will lead to ambiguity.

thanks,

Ganesh

Vladan SSC-Insane Points: 22072 More actions · Answer 3

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

Antares686 SSC Guru Points: 125444 More actions · Answer 4

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.

Sanjay Masawan Ten Centuries Points: 1031 More actions · Answer 5

hi Antares686

Thanks for the help. I have used a trigger for the same

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

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.

Sanjay Masawan Ten Centuries Points: 1031 More actions · Answer 7

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

Wayne West SSC-Insane Points: 22586 More actions · Answer 8

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]

Vladan SSC-Insane Points: 22072 More actions · Answer 9

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...