在Postgresql中按名称删除约束 [英] Drop constraint by name in Postgresql

问题描述

如何仅通过知道约束名称就可以在Postgresql中删除约束名称？
我有一个由第三方脚本自动生成的约束列表。我需要删除它们而不知道表名仅仅是约束名称。

How can I drop a constraint name in Postgresql just by knowing the name? I have a list of constraints that are autogenerated by a 3rd party script. I need to delete them without knowing the table name just the constraint name.

推荐答案

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

You need to retrieve the table names by running the following query:

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

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

Alternatively you can use pg_constraint to retrieve this information

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语句：

Then you can run the required ALTER TABLE statement:

ALTER TABLE your_table DROP CONSTRAINT constraint_name;

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

Of course you can make the query return the complete alter statement:

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

别忘了将table_schema包含在其中如果有多个具有相同表的模式，则使用WHERE子句（和ALTER语句）。

Don't forget to include the table_schema in the WHERE clause (and the ALTER statement) if there are multiple schemas with the same tables.

这篇关于在Postgresql中按名称删除约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！