在 Postgresql 中按名称删除约束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5273717/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Drop constraint by name in Postgresql
提问by danidacar
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.
如何仅通过知道名称就可以在 Postgresql 中删除约束名称？我有一个由 3rd 方脚本自动生成的约束列表。我需要在不知道表名和约束名的情况下删除它们。
回答by a_horse_with_no_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'
Alternatively you can use pg_constraintto retrieve this information
或者，您可以使用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';
Then you can run the required ALTER TABLE statement:
然后您可以运行所需的 ALTER TABLE 语句：
ALTER TABLE your_table DROP CONSTRAINT constraint_name;
Of course you can make the query return the complete alter statement:
当然你可以让查询返回完整的alter语句：
SELECT 'ALTER TABLE '||table_name||' DROP CONSTRAINT '||constraint_name||';'
FROM information_schema.constraint_table_usage
WHERE table_name in ('your_table', 'other_table')
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.
如果有多个模式具有相同的表，请不要忘记在 WHERE 子句（和 ALTER 语句）中包含 table_schema。
回答by Kuberchaun
If your on 9.x of PG you could make use of the DO statement to run this. Just do what a_horse_with_no_name did, but apply it to a DO statement.
如果您使用 PG 9.x，您可以使用 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$$;
回答by Praveen Kumar C
-- Drop the right foreign key constraint
-- 删除正确的外键约束
ALTER TABLE affiliations
DROP CONSTRAINT affiliations_organization_id_fkey;
NOTE:
笔记：
affiliations -> Table Name
隶属关系 -> 表名
affiliations_organization_id_fkey ->Contraint name
affiliations_organization_id_fkey -> 约束名称
