人大金仓（pgsql）删除外键

原创已于 2024-02-07 16:06:06 修改 · 916 阅读

8 ·

CC 4.0 BY-SA版权

文章标签：

#人大金仓 #删除外键 #数据库 #pgsql

于 2024-02-06 14:29:35 首次发布

人大金仓同时被 2 个专栏收录

3 篇文章

订阅专栏

PostgreSQL

2 篇文章

订阅专栏

文章详细介绍了如何在PostgreSQL中查询特定模式的外键信息，包括外键名称、关联表和列，以及提供删除单个外键和获取删除所有公共模式外键脚本的SQL命令。

--查询指定模式外键
SELECT
    conname AS "外键名称",
    conrelid::regclass AS "表名",
    a.attname AS "列名",
    confrelid::regclass AS "参考表",
    af.attname AS "参考列"
FROM
    pg_constraint
    JOIN pg_attribute AS a ON conrelid = a.attrelid AND a.attnum = ANY(conkey)
    JOIN pg_attribute AS af ON confrelid = af.attrelid AND af.attnum = ANY(confkey)
WHERE
    connamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public');

--删除单个外键
ALTER TABLE PL_UrgeTaskAttached DROP CONSTRAINT RefPL_UrgeTaskRecord230_EB5EB509;


--获取删除指定模式所有外键的脚本
SELECT
	concat('ALTER TABLE ',conrelid::regclass,' DROP CONSTRAINT ',conname,';') AS drop_statement
FROM
    pg_constraint
    JOIN pg_attribute AS a ON conrelid = a.attrelid AND a.attnum = ANY(conkey)
    JOIN pg_attribute AS af ON confrelid = af.attrelid AND af.attnum = ANY(confkey)
WHERE
    connamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public');