如何在 Postgresql 中自动检测并删除重复的外键约束

Question

我的 Postgresql 数据库在表上有大量重复的外键约束，例如

"fkb43bb0b712b68565" FOREIGN KEY (owner_id) REFERENCES usr(id)
"fkb43bb0b71b63ed43" FOREIGN KEY (owner_id) REFERENCES usr(id)

它们正在做相同的事情但具有唯一的名称。

如何自动检测并删除此类重复项？

谢谢杰

​

Answer 1

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

上面的查询将返回所有外键约束，您只需删除重复的条目即可。

注意：如果您从上面的查询中删除注释，您可以查看 relID 和 ColID，并且该查询应该适用于数据库中的所有模式

Answer 2

SELECT
    array_agg(pc.conname) as duplicated_constraints, 
    pclsc.relname as child_table,
    pac.attname as child_column,
    pclsp.relname as parent_table,
    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
    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
GROUP BY child_table, child_column, parent_table, parent_column, schema_name HAVING COUNT(*)>1
ORDER BY child_table, child_column

将仅列出 Postgresql 中重复的外键约束

Answer 3

约束存储在 pg_constraint 中，只需查询此视图即可找到双重约束。