尝试修改 PostgreSQL 中的约束

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

对 PostgreSQL 使用 EMS SQL 管理器。

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;
115246 次浏览
小开
最佳答案

According to the correct manual (which is supplied by PostgreSQL, not by Oracle), there is no modify constraint available in the ALTER TABLE statement:

Here is the link to the correct manual:

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

小开

There is no ALTER command for constraints in Postgres. The easiest way to accomplish this is to drop the constraint and re-add it with the desired parameters. Of course any change of the constraint will be run against the current table data.

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

As of version 9.4, PostgreSQL supports ALTER TABLE ... ALTER CONSTRAINT for foreign keys.

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.

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/

小开

ALTER CONSTRAINT would require knowing of foreign key name, which is not always convenient.

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 copy attributes of initial foreign key. It only takes foreign table name / column name, drops current key and replaces with new one.


提交答案