ALTER TABLE ... ALTER CONSTRAINT for FKs

Allow constraint attributes to be altered,
so the default setting of NOT DEFERRABLE
can be altered to DEFERRABLE and back.

Review by Abhijit Menon-Sen

1 files changed, 19 insertions, 0 deletions

diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out
index 04668a8886..0299bfe873 100644
--- a/src/test/regress/expected/foreign_key.out
+++ b/src/test/regress/expected/foreign_key.out
@@ -1132,6 +1132,15 @@ CREATE TEMP TABLE fktable (
     id int primary key,
     fk int references pktable deferrable initially deferred
 );
+-- check ALTER CONSTRAINT
+ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey NOT DEFERRABLE;
+-- illegal option
+ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey NOT DEFERRABLE INITIALLY DEFERRED;
+ERROR:  constraint declared INITIALLY DEFERRED must be DEFERRABLE
+LINE 1: ...e ALTER CONSTRAINT fktable_fk_fkey NOT DEFERRABLE INITIALLY ...
+                                                             ^
+-- reset
+ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey DEFERRABLE INITIALLY DEFERRED;
 INSERT INTO pktable VALUES (5, 10);
 BEGIN;
 -- doesn't match PK, but no error yet
@@ -1142,6 +1151,16 @@ UPDATE fktable SET id = id + 1;
 COMMIT;
 ERROR:  insert or update on table "fktable" violates foreign key constraint "fktable_fk_fkey"
 DETAIL:  Key (fk)=(20) is not present in table "pktable".
+-- change the constraint definition and retest
+ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey DEFERRABLE INITIALLY IMMEDIATE;
+BEGIN;
+-- doesn't match PK, should throw error now
+INSERT INTO fktable VALUES (0, 20);
+ERROR:  insert or update on table "fktable" violates foreign key constraint "fktable_fk_fkey"
+DETAIL:  Key (fk)=(20) is not present in table "pktable".
+COMMIT;
+-- reset
+ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey DEFERRABLE INITIALLY DEFERRED;
 -- check same case when insert is in a different subtransaction than update
 BEGIN;
 -- doesn't match PK, but no error yet