duplicated foreign key constraints - reasons for or against

I've just come across a table in production whic开发者_运维知识库h 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!

---

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)

---

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.

---

SELECT
    pc.conname as constraint_name, 
    --conrelid as child_table_id,   
    pclsc.relname as child_table,
    --pc.conkey as child_column_id,
    pac.attname as child_column,
    --confrelid as parent_table_id,
    pclsp.relname as parent_table,
    --pc.confkey as parent_column_id,
    pap.attname as parent_column,   
    nspname as schema_name
FROM 
    (
    SELECT
         connamespace,conname, unnest(conkey) as "conkey", unnest(confkey)
          as "confkey" , conrelid, confrelid, contype
     FROM
        pg_constraint
    ) pc
    JOIN pg_namespace pn ON pc.connamespace = pn.oid
    -- and pn.nspname = 'panmydesk4400'
    JOIN pg_class pclsc ON pc.conrelid = pclsc.oid
    JOIN pg_class pclsp ON      pc.confrelid = pclsp.oid
    JOIN pg_attribute pac ON pc.conkey = pac.attnum    and pac.attrelid =       pclsc.oid
    JOIN pg_attribute pap ON pc.confkey = pap.attnum and pap.attrelid = pclsp.oid

ORDER BY pclsc.relname

it list out all the ( including duplicate )FK constraint including duplicate ,