postgresql 通常在 POSTGRES 中删除外键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40755239/
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 foreign keys generally in POSTGRES
提问by msagala25
How can I drop Foreign keysin general. I mean, if I have many foreign key constraints in a table. like
Foreign keys一般怎么掉。我的意思是，如果我在一个表中有很多外键约束。喜欢
MonthlyEvaluatedBudgetTable Contraints:
MonthlyEvaluatedBudgetTable 约束：
- budgetid_pk (Primary Key)
- branchid_fk (Foreign Key)
- accountid_fk (Foreign Key)
- dept_fk (Foreign Key)
- budgetid_pk（主键）
- branchid_fk（外键）
- accountid_fk（外键）
- dept_fk（外键）
Is there a way in postgres to drop all foreign keys in general and not specifically in an existing table? Im using this line of code to drop a foreign key in an existing table.
postgres 有没有办法删除所有外键，而不是专门删除现有表中的外键？我使用这行代码删除现有表中的外键。
ALTER TABLE "public"."monthlyevaluatedbudgettable"
DROP CONSTRAINT "accountid_fk";
But I want to drop It without specifically inputing accountid_fk,branchid_fk,dept_fk. Is there a way on it? thanks in advance.
但我想删除它而不特别输入accountid_fk, branchid_fk, dept_fk。有办法吗？提前致谢。
回答by Vao Tsun
Loop it in DOstatement, like:
在DO语句中循环它，例如：
b=# create table a (a int primary key, b int unique);
CREATE TABLE
b=# create table b (a int references a(a), b int references a(b));
CREATE TABLE
b=# do
$$
declare r record;
begin
for r in (select constraint_name from information_schema.table_constraints where table_schema = 'public' and table_name='b') loop
raise info '%','dropping '||r.constraint_name;
execute CONCAT('ALTER TABLE "public"."b" DROP CONSTRAINT '||r.constraint_name);
end loop;
end;
$$
;
INFO: dropping b_a_fkey
INFO: dropping b_b_fkey
DO
回答by Alexander
Thank you Vao Tsunfor the solution. It helped me.
感谢Vao Tsun的解决方案。它帮助了我。
In my case (Posgresql 9.6) I just had to add a minor "improvement"
在我的情况下（Posgresql 9.6）我只需要添加一个小的“改进”
and constraint_name like 'fk_%'additional constraint to prevent errors like:
and constraint_name like 'fk_%'防止错误的附加约束，例如：
PG::SyntaxError:?ERROR:??syntax?error?at?or?near?"2200" LINE?1:?ALTER?TABLE?"relationships"?DROP?CONSTRAINT?2200_856906_1_no...
PG::SyntaxError:?ERROR:??syntax?error?at?or?near?"2200" LINE?1:?ALTER?TABLE?"relationships"?DROP?CONSTRAINT?2200_856906_1_no...
execute <<-SQL.squish
DO $$
declare r record;
begin
for r in (
select constraint_name
from information_schema.table_constraints
where table_name='relationships'
and constraint_name like 'fk_%'
) loop
raise info '%','dropping '||r.constraint_name;
execute CONCAT('ALTER TABLE "relationships" DROP CONSTRAINT '||r.constraint_name);
end loop;
end;
$$
SQL
