如何在 PostgreSQL 中按名称删除约束？

Question

如何仅通过名称就可以删除 PostgreSQL 中的约束？

我有一个由第三方脚本自动生成的约束列表。我需要删除它们而不知道表名只是约束名称。

Answer 1

您需要通过运行以下查询来检索表名：

SELECT *
FROM information_schema.constraint_table_usage
WHERE table_name = 'your_table'

或者您可以使用 pg_constraint 来检索此信息

select n.nspname as schema_name,
       t.relname as table_name,
       c.conname as constraint_name
from pg_constraint c
  join pg_class t on c.conrelid = t.oid
  join pg_namespace n on t.relnamespace = n.oid
where t.relname = 'your_table_name';

然后您可以运行所需的 ALTER TABLE 语句：

ALTER TABLE your_table DROP CONSTRAINT constraint_name;

当然您可以使查询返回完整的更改语句：

SELECT 'ALTER TABLE '||table_name||' DROP CONSTRAINT '||constraint_name||';'
FROM information_schema.constraint_table_usage
WHERE table_name in ('your_table', 'other_table')

如果有多个模式具有相同的表，请不要忘记在 WHERE 子句（和 ALTER 语句）中包含 table_schema。

Answer 2

如果您使用 9.x 的 PG，您可以使用 DO 语句来运行它。只需执行 a_horse_with_no_name 所做的操作，但将其应用于 DO 语句。

DO $DECLARE r record;
    BEGIN
        FOR r IN SELECT table_name,constraint_name
                 FROM information_schema.constraint_table_usage
                 WHERE table_name IN ('your_table', 'other_table')
        LOOP
            EXECUTE 'ALTER TABLE ' || quote_ident(r.table_name)|| ' DROP CONSTRAINT '|| quote_ident(r.constraint_name) || ';';
        END LOOP;
    END$;

Answer 3

删除右外键约束

ALTER TABLE affiliations
DROP CONSTRAINT affiliations_organization_id_fkey;

注意：

affiliations -> Table Name

affiliations_organization_id_fkey ->Constraint name

Answer 4

列出约束：

SELECT con.*
FROM pg_catalog.pg_constraint con
INNER JOIN pg_catalog.pg_class     rel ON rel.oid = con.conrelid
INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace
WHERE nsp.nspname = 'your_schema' AND
      rel.relname = 'your_table';

删除约束：

ALTER TABLE your_schema.your_table DROP CONSTRAINT the_constraint_name;

Answer 5

要查找所需表的约束和删除约束：-
在版本13中，表名是schemaname.tablename

1. 在删除约束之前获取DDL整个架构，casacade删除所有引用约束，这就是为什么需要获取所有架构DDL

   pg_dump -h $pg_host -U $pg_usr -d dbname --schema=schemaname -s -f dbname_schename_ddl
   

2. 在删除之前获取约束详细信息

   select conrelid::regclass::text 表名, conname 约束名,contype  
   来自 pg_constraint 其中 connamespace::regnamespace::text = 'schemaname';
   

3. 生成删除约束脚本

   -- 脚本名称 mk_drp_tbl_cons.sql
   选择“更改表”||rtrim(conrelid::regclass::text)||'删除约束 '||rtrim(conname) ||'级联；'来自 pg_constraint 其中 connamespace::regnamespace::text = 'tax' 和 conrelid::regclass::text in ('schemaname.table1','schemaname.tables2) ；
   
   psql -h $pg_host -U $pg_usr -d dbname -tAf -f mk_drp_tbl_cons.sql -o drp_tbl_cons.sql
   
   -- 删除表约束 
   psql -h $pg_host -U $pg_usr -d dbname -tAf -f drp_tbl_cons.sql -o drp_tbl_cons.log
   

4. 添加删除约束，通过在步骤1中运行extract DDL（在匹配之前运行多次makeconstraint

   psql -h $pg_host -U $pg_usr -d dbname -dbname_schename_ddl 
   

5. 通过运行步骤 2 验证约束

如果不匹配再次运行步骤 4，