CODEWITHSUNDEEP
sqldatabase
Peculiar Peculiar
Peculiar Peculiar

Reputation: 1

When is the right instance or scenario to have multiple foreign keys in a table

It is definitely possible to have multiple foreign keys on a table. But when is the right instance to do that?

Is it right to have an instance whereby you have 2 foreign keys where the second foreign key references a foreign key that the second table that is being referenced already has as a foreign key?

Context

Id Int
SecondTableId Int(FK)
ThirdTableId Int(FK)
....

Third Table
ThirdTableId Int
SecondTableId Int(FK)
....

Upvotes: 0

Views: 490

Answers (1)

Chrimle
Chrimle

Reputation: 612

Is it right to have an instance whereby you have 2 foreign keys where the second foreign key references a foreign key that the second table that is being referenced already has as a foreign key?

In that case, no.

Just to clarify how I interpreted your question:

TableA(a,b,c)
b -> TableB.b
c -> TableC.c

TableB(b,c)
c -> TableC.c

TableC(c,d)

So, Table A references both B and C, and B also references C.

This is redundant, and should not be used. However, there are situations where this is a valid thing to do.

For example, if an entity A has a reference to another entity B and have the option to reference a weak entity C (that is a weak entity of B), you could have such a double reference to make sure that the weak entity C belongs to the same B entity that A referenced. (If that is required by your domain).

But in general, no. There's no need to have such redundant references unless it is to maintain some sort of constraint.

Cheers! ^_^

Upvotes: 1

Related Questions