SQL 尝试修改 PostgreSQL 中的约束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8330357/
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
Trying to modify a constraint in PostgreSQL
提问by MISMajorDeveloperAnyways
I have checked the documentation provided by Oracle and found a way to modify a constraint without dropping the table. Problem is, it errors out at modify as it does not recognize the keyword.
我检查了 Oracle 提供的文档，找到了一种在不删除表的情况下修改约束的方法。问题是，它在修改时出错，因为它无法识别关键字。
Using EMS SQL Manager for PostgreSQL.
使用 EMS SQL Manager for PostgreSQL。
Alter table public.public_insurer_credit MODIFY CONSTRAINT public_insurer_credit_fk1
deferrable, initially deferred;
I was able to work around it by dropping the constraint using :
我能够通过使用以下命令删除约束来解决它：
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;
采纳答案by a_horse_with_no_name
According to the correct manual (which is supplied by PostgreSQL, notby Oracle), there is no modify constraint available in the ALTER TABLE statement:
根据正确的手册（由 PostgreSQL 提供，而不是由 Oracle 提供），ALTER TABLE 语句中没有可用的修改约束：
Here is the link to the correct manual:
这是正确手册的链接：
http://www.postgresql.org/docs/current/static/sql-altertable.html
http://www.postgresql.org/docs/current/static/sql-altertable.html
回答by Chris Cashwell
There is no ALTERcommand for constraints in Postgres. The easiest way to accomplish this is to dropthe constraint and re-add it with the desired parameters. Of course any change of the constraint will be run against the current table data.
ALTERPostgres 中没有约束命令。完成此操作的最简单方法是删除约束并使用所需参数重新添加它。当然，约束的任何更改都将针对当前表数据运行。
BEGIN;
ALTER TABLE t1 DROP CONSTRAINT ...
ALTER TABLE t1 ADD CONSTRAINT ...
COMMIT;
回答by mkurz
As of version 9.4, PostgreSQL supports ALTER TABLE ... ALTER CONSTRAINTfor foreign keys.
从 9.4 版本开始，PostgreSQL 支持ALTER TABLE ... ALTER CONSTRAINT外键。
This features will "Allow constraint attributes to be altered,
so the default setting of NOT DEFERRABLE can be altered to DEFERRABLE and back."Looking at your question I think that is (kind of) what you have been looking for.
此功能将"Allow constraint attributes to be altered,
so the default setting of NOT DEFERRABLE can be altered to DEFERRABLE and back."查看您的问题，我认为这是（某种）您一直在寻找的问题。
More detailed information and an example can be found here:
http://www.depesz.com/2013/06/30/waiting-for-9-4-alter-table-alter-constraint-for-fks/
可以在此处找到更详细的信息和示例：http:
//www.depesz.com/2013/06/30/waiting-for-9-4-alter-table-alter-constraint-for-fks/
回答by Daniel Garmoshka
ALTER CONSTRAINT would require knowing of foreign key name, which is not always convenient.
ALTER CONSTRAINT 需要知道外键名称，这并不总是很方便。
Here is function, where you need to know only table and column names. Usage:
这是函数，您只需要知道表名和列名。用法：
select replace_foreign_key('user_rates_posts', 'post_id', 'ON DELETE CASCADE');
Function:
功能：
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;
Be aware: this function won't copyattributes of initial foreign key. It only takes foreign table name / column name, drops current key and replaceswith new one.
请注意：此函数不会复制初始外键的属性。它只需要外部表名/列名，删除当前键并替换为新键。
