人大金仓（pgsql）删除外键

曹振华

已于 2024-02-07 16:06:06 修改

阅读量525

点赞数 11

分类专栏： PostgreSQL 人大金仓文章标签：人大金仓删除外键数据库 pgsql

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

本文链接：https://blog.csdn.net/chadcao/article/details/136056387

版权

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

3 篇文章 0 订阅

订阅专栏

PostgreSQL

2 篇文章 0 订阅

订阅专栏

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

摘要由CSDN通过智能技术生成

--查询指定模式外键
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');