duplicated foreign key constraints - reasons for or against

Question

I've just come across a table in production which has 4 foreign key constraints. Two of the constraints are exact duplicates of the other two.

ie

fk1(a_id) references a(id)
fk2(a_id) references a(id)
fk3(b_id) references b(id)
fk4(b_id) references b(id)

I have never seen this before ... it strikes me as being quite wrong and my gut feeling is there must be a performance hit here (esp on inserting in to this table). In this instance the database is PostGres but I'm interested in what people think the general behaviour would be.

And if anyone's experienced a time where you'd want foreign keys like this I'd also be interested - especially because I'm going to suggest getting rid of the duplicates!

Answer 1

This adds no benefit at all and is redundant. Indeed, it's double the number of FKs that need to be checked for an insert or update of a_id.

I say drop the duplicates.

If one has cascade and the other does not then the non-cascade one is the duplicate (may not apply to PostGres)

Answer 2

Do you have a create script for the database? If you do, that script might reveal why the same constraint is declared more than once.

Weed out the redundant declarations. I can't think of a reason not to. And, if you have a create script, eliminate the redundant declarations there, too. See what happens.

If you don't have a create script, you might consider generating and maintaining one. It's an important piece of documentation for a well managed database.