--查询指定模式外键
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');
人大金仓(pgsql) 删除外键
于 2024-02-06 14:29:35 首次发布
文章详细介绍了如何在PostgreSQL中查询特定模式的外键信息,包括外键名称、关联表和列,以及提供删除单个外键和获取删除所有公共模式外键脚本的SQL命令。
摘要由CSDN通过智能技术生成