尝试修改PostgreSQL中的约束条件

Question

我查阅了Oracle提供的文档并找到了一种在不删除表的情况下修改约束的方法。问题是，在修改时出现错误，因为它无法识别关键字。

使用EMS SQL Manager for PostgreSQL。

Alter table public.public_insurer_credit MODIFY CONSTRAINT public_insurer_credit_fk1
    deferrable, initially deferred;

我能够通过使用 : 删除约束来解决它。

ALTER TABLE "public"."public_insurer_credit"
  DROP CONSTRAINT "public_insurer_credit_fk1" RESTRICT;

ALTER TABLE "public"."public_insurer_credit"
  ADD CONSTRAINT "public_insurer_credit_fk1" FOREIGN KEY ("branch_id", "order_id", "public_insurer_id")
    REFERENCES "public"."order_public_insurer"("branch_id", "order_id", "public_insurer_id")
    ON UPDATE CASCADE
    ON DELETE NO ACTION
    DEFERRABLE 
    INITIALLY DEFERRED;

- MISMajorDeveloperAnyways

---

为什么在使用PostgreSQL时你要查看Oracle文档（并使用“plsql”标签提问）？具体出现了什么错误（哪个关键字无法识别）？ - Bruno

错误：在“MODIFY”附近有语法错误 第1行：Alter table public.public_insurer_credit MODIFY CONSTRAINT p... ^ (0.359秒) - MISMajorDeveloperAnyways

我当时是一个新手，正在查阅所提供给我的文档。从未想过数据库管理员会给我错误的文档。对方的评价是应该的。 - MISMajorDeveloperAnyways

无论如何，每个人都曾经是新手，唯一的羞耻是忘记了这一点。至少你提到了它们是Oracle文档，所以很容易就能理解了。那条评论并不应该出现。 - hraban

Answer 1

在Postgres中，没有针对约束的ALTER命令。最简单的方法是使用所需的参数删除约束，然后重新添加它。当然，约束的任何更改都将应用于当前表数据。

BEGIN;
ALTER TABLE t1 DROP CONSTRAINT ...
ALTER TABLE t1 ADD CONSTRAINT ...
COMMIT;

- Chris Cashwell

---

注意：如果我理解正确，DDL语句会对表进行AccessExclusive锁定，因此如果这些命令执行时间很长，你的网站将会停滞，直到命令完成。文档页面有更多细节，包括如何明确指定索引而不是自动生成一个索引。 - Joey Adams

一次交易很好，但一次命令更好。例如：https://dev59.com/8l0Z5IYBdhLWcg3wdQPT#31419767 - Erwin Brandstetter

从9.4版本开始，您确实可以在Postgres中修改外键约束：https://www.postgresql.org/docs/current/sql-altertable.html@mkurz的回答应该被接受为新的正确答案。 - undefined

Answer 2

截至9.4版本，PostgreSQL支持用于外键的“ALTER TABLE ... ALTER CONSTRAINT”。此功能将 “允许修改约束属性，因此默认设置未可延迟的可以更改为可延迟并返回原状态。” 根据您的问题，我认为这（有点）是您一直在寻找的内容。更详细的信息和示例可以在此处找到：
http://www.depesz.com/2013/06/30/waiting-for-9-4-alter-table-alter-constraint-for-fks/。

- mkurz

---

请注意，9.4文档中说：“目前仅可以修改外键约束。”！ - Michael Herrmann

我们处于12+版本，仍然无法修改非外键约束条件。需要在某些约束上添加“DEFERRABLE”，但没有办法在不删除/重新创建它们的情况下进行操作。 - Pawel Veselov

Answer 3

根据正确的手册（由PostgreSQL提供，而不是Oracle），ALTER TABLE语句中没有可用的修改约束。以下是正确手册的链接：

http://www.postgresql.org/docs/current/static/sql-altertable.html

- user330315

---

谢谢你提供正确文档的链接。我们的数据库管理员给了我Oracle PL/SQL文档。真是太好了。 - MISMajorDeveloperAnyways

Oracle和Oracle的PL/SQL是完全不同的东西。他们这样做的唯一可能原因是因为您正在使用EnterpriseDB的Advanced Server，该服务器支持Oracle的PL/SQL（以及其他兼容Oracle的特性）。但是，他们应该给您提供EnterpriseDB的手册，而不是Oracle的手册。 - user330315

最好的猜测是，这个回答只是简单地链接到一个外部网站，没有为未来的读者提供如何正确更改约束条件的解释。 - Brad Koch

Answer 4

使用 ALTER CONSTRAINT 需要知道外键名称，这并不总是方便。

这里有一个函数，你只需要知道表名和列名即可使用。

select replace_foreign_key('user_rates_posts', 'post_id', 'ON DELETE CASCADE');

功能：

CREATE OR REPLACE FUNCTION 
    replace_foreign_key(f_table VARCHAR, f_column VARCHAR, new_options VARCHAR) 
RETURNS VARCHAR
AS $$
DECLARE constraint_name varchar;
DECLARE reftable varchar;
DECLARE refcolumn varchar;
BEGIN

SELECT tc.constraint_name, ccu.table_name AS foreign_table_name, ccu.column_name AS foreign_column_name 
FROM 
    information_schema.table_constraints AS tc 
    JOIN information_schema.key_column_usage AS kcu
      ON tc.constraint_name = kcu.constraint_name
    JOIN information_schema.constraint_column_usage AS ccu
      ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY' 
   AND tc.table_name= f_table AND kcu.column_name= f_column
INTO constraint_name, reftable, refcolumn;

EXECUTE 'alter table ' || f_table || ' drop constraint ' || constraint_name || 
', ADD CONSTRAINT ' || constraint_name || ' FOREIGN KEY (' || f_column || ') ' ||
' REFERENCES ' || reftable || '(' || refcolumn || ') ' || new_options || ';';

RETURN 'Constraint replaced: ' || constraint_name || ' (' || f_table || '.' || f_column ||
 ' -> ' || reftable || '.' || refcolumn || '); New options: ' || new_options;

END;
$$ LANGUAGE plpgsql;

注意：该函数不会复制初始外键的属性。它只接受外键表名/列名，删除当前的键，并替换为新键。

- Daniel Garmoshka

---

非常有趣的解决方案。 - tom10271