Allow ALTER TABLE ... ALTER CONSTRAINT ... RENAME

Joachim Wieland

2 files changed, 39 insertions, 0 deletions

diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index 02723d62ec..ae05d4a686 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -159,6 +159,10 @@ CREATE TABLE tmp3 (a int, b int);
 CREATE TABLE tmp4 (a int, b int, unique(a,b));
 NOTICE:  CREATE TABLE / UNIQUE will create implicit index "tmp4_a_key" for table "tmp4"
 CREATE TABLE tmp5 (a int, b int);
+-- creates implicit index tmp6_a_key
+CREATE TABLE tmp6 (a int, b int, unique(a));
+NOTICE:  CREATE TABLE / UNIQUE will create implicit index "tmp6_a_key" for table "tmp6"
+CREATE INDEX tmp6_b_key ON tmp6(b);
 -- Insert rows into tmp2 (pktable)
 INSERT INTO tmp2 values (1);
 INSERT INTO tmp2 values (2);
@@ -186,6 +190,22 @@ ALTER TABLE tmp3 add constraint tmpconstr foreign key (a) references tmp2 match
 -- tmp4 is a,b
 ALTER TABLE tmp5 add constraint tmpconstr foreign key(a) references tmp4(a) match full;
 ERROR:  there is no unique constraint matching given keys for referenced table "tmp4"
+-- check if constraint and index name stay in sync if we rename one or the other
+-- fail here
+ALTER TABLE tmp6 ALTER CONSTRAINT tmp6_a_key RENAME TO tmp6_b_key;
+NOTICE:  ALTER TABLE / CONSTRAINT will implicitly rename index "tmp6_a_key" to "tmp6_b_key" on table "public.tmp6"
+ERROR:  relation "tmp6_b_key" already exists
+-- succeed
+ALTER TABLE tmp6 ALTER CONSTRAINT tmp6_a_key RENAME TO tmp6_c_key;
+NOTICE:  ALTER TABLE / CONSTRAINT will implicitly rename index "tmp6_a_key" to "tmp6_c_key" on table "public.tmp6"
+-- Now rename the index (this fails)
+ALTER INDEX tmp6_c_key RENAME TO tmp6_b_key;
+NOTICE:  ALTER INDEX will implicitly rename constraint "tmp6_c_key" to "tmp6_b_key" on table "public.tmp6"
+ERROR:  relation "tmp6_b_key" already exists
+-- this succeeds and uses ALTER TABLE syntax to rename an INDEX
+ALTER TABLE tmp6_c_key RENAME TO tmp6_a_key;
+NOTICE:  ALTER INDEX will implicitly rename constraint "tmp6_c_key" to "tmp6_a_key" on table "public.tmp6"
+DROP TABLE tmp6;
 DROP TABLE tmp5;
 DROP TABLE tmp4;
 DROP TABLE tmp3;
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
index 8690f61dbe..b0400051c0 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -196,6 +196,10 @@ CREATE TABLE tmp4 (a int, b int, unique(a,b));
 
 CREATE TABLE tmp5 (a int, b int);
 
+-- creates implicit index tmp6_a_key
+CREATE TABLE tmp6 (a int, b int, unique(a));
+CREATE INDEX tmp6_b_key ON tmp6(b);
+
 -- Insert rows into tmp2 (pktable)
 INSERT INTO tmp2 values (1);
 INSERT INTO tmp2 values (2);
@@ -227,6 +231,21 @@ ALTER TABLE tmp3 add constraint tmpconstr foreign key (a) references tmp2 match
 
 ALTER TABLE tmp5 add constraint tmpconstr foreign key(a) references tmp4(a) match full;
 
+-- check if constraint and index name stay in sync if we rename one or the other
+-- fail here
+ALTER TABLE tmp6 ALTER CONSTRAINT tmp6_a_key RENAME TO tmp6_b_key;
+
+-- succeed
+ALTER TABLE tmp6 ALTER CONSTRAINT tmp6_a_key RENAME TO tmp6_c_key;
+
+-- Now rename the index (this fails)
+ALTER INDEX tmp6_c_key RENAME TO tmp6_b_key;
+
+-- this succeeds and uses ALTER TABLE syntax to rename an INDEX
+ALTER TABLE tmp6_c_key RENAME TO tmp6_a_key;
+
+DROP TABLE tmp6;
+
 DROP TABLE tmp5;
 
 DROP TABLE tmp4;