From d7a2b5bd8718a6207fb364318d7f7cccdf6219c3 Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Thu, 7 Nov 2024 11:13:06 +0100 Subject: [PATCH] Clarify a foreign key error message Clarify the message about type mismatch in foreign key definition to indicate which column the referencing and which is the referenced one. Reported-by: Jian He Discussion: https://www.postgresql.org/message-id/CACJufxEL82ao-aXOa=d_-Xip0bix-qdSyNc9fcWxOdkEZFko8w@mail.gmail.com --- src/backend/commands/tablecmds.c | 2 +- src/test/regress/expected/alter_table.out | 16 +++--- src/test/regress/expected/enum.out | 2 +- src/test/regress/expected/foreign_key.out | 52 +++++++++---------- .../regress/expected/without_overlaps.out | 6 +-- 5 files changed, 39 insertions(+), 39 deletions(-) diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 4345b96de5e..eaa81424270 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -9961,7 +9961,7 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, (errcode(ERRCODE_DATATYPE_MISMATCH), errmsg("foreign key constraint \"%s\" cannot be implemented", fkconstraint->conname), - errdetail("Key columns \"%s\" and \"%s\" " + errdetail("Key columns \"%s\" of the referencing table and \"%s\" of the referenced table " "are of incompatible types: %s and %s.", strVal(list_nth(fkconstraint->fk_attrs, i)), strVal(list_nth(fkconstraint->pk_attrs, i)), diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out index 143ae7c09c0..332cb169172 100644 --- a/src/test/regress/expected/alter_table.out +++ b/src/test/regress/expected/alter_table.out @@ -659,12 +659,12 @@ CREATE TEMP TABLE FKTABLE (ftest1 inet); -- This next should fail, because int=inet does not exist ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable; ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented -DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: inet and integer. +DETAIL: Key columns "ftest1" of the referencing table and "ptest1" of the referenced table are of incompatible types: inet and integer. -- This should also fail for the same reason, but here we -- give the column name ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable(ptest1); ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented -DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: inet and integer. +DETAIL: Key columns "ftest1" of the referencing table and "ptest1" of the referenced table are of incompatible types: inet and integer. DROP TABLE FKTABLE; -- This should succeed, even though they are different types, -- because int=int8 exists and is a member of the integer opfamily @@ -682,7 +682,7 @@ DROP TABLE FKTABLE; CREATE TEMP TABLE FKTABLE (ftest1 numeric); ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable; ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented -DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: numeric and integer. +DETAIL: Key columns "ftest1" of the referencing table and "ptest1" of the referenced table are of incompatible types: numeric and integer. DROP TABLE FKTABLE; DROP TABLE PKTABLE; -- On the other hand, this should work because int implicitly promotes to @@ -704,26 +704,26 @@ CREATE TEMP TABLE PKTABLE (ptest1 int, ptest2 inet, CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp); ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) references pktable; ERROR: foreign key constraint "fktable_ftest1_ftest2_fkey" cannot be implemented -DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: cidr and integer. +DETAIL: Key columns "ftest1" of the referencing table and "ptest1" of the referenced table are of incompatible types: cidr and integer. DROP TABLE FKTABLE; -- Again, so should this... CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp); ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) references pktable(ptest1, ptest2); ERROR: foreign key constraint "fktable_ftest1_ftest2_fkey" cannot be implemented -DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: cidr and integer. +DETAIL: Key columns "ftest1" of the referencing table and "ptest1" of the referenced table are of incompatible types: cidr and integer. DROP TABLE FKTABLE; -- This fails because we mixed up the column ordering CREATE TEMP TABLE FKTABLE (ftest1 int, ftest2 inet); ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) references pktable(ptest2, ptest1); ERROR: foreign key constraint "fktable_ftest1_ftest2_fkey" cannot be implemented -DETAIL: Key columns "ftest1" and "ptest2" are of incompatible types: integer and inet. +DETAIL: Key columns "ftest1" of the referencing table and "ptest2" of the referenced table are of incompatible types: integer and inet. -- As does this... ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest2, ftest1) references pktable(ptest1, ptest2); ERROR: foreign key constraint "fktable_ftest2_ftest1_fkey" cannot be implemented -DETAIL: Key columns "ftest2" and "ptest1" are of incompatible types: inet and integer. +DETAIL: Key columns "ftest2" of the referencing table and "ptest1" of the referenced table are of incompatible types: inet and integer. DROP TABLE FKTABLE; DROP TABLE PKTABLE; -- Test that ALTER CONSTRAINT updates trigger deferrability properly @@ -3458,7 +3458,7 @@ COMMENT ON CONSTRAINT comment_test_child_fk ON comment_test_child IS 'FOREIGN KE ALTER TABLE comment_test ALTER COLUMN id SET DATA TYPE text; ALTER TABLE comment_test ALTER COLUMN id SET DATA TYPE int USING id::integer; ERROR: foreign key constraint "comment_test_child_fk" cannot be implemented -DETAIL: Key columns "id" and "id" are of incompatible types: text and integer. +DETAIL: Key columns "id" of the referencing table and "id" of the referenced table are of incompatible types: text and integer. -- Comments should be intact SELECT col_description('comment_test_child'::regclass, 1) as comment; comment diff --git a/src/test/regress/expected/enum.out b/src/test/regress/expected/enum.out index 1d09c208bc9..4d9f36d0d36 100644 --- a/src/test/regress/expected/enum.out +++ b/src/test/regress/expected/enum.out @@ -581,7 +581,7 @@ DETAIL: Key (id)=(red) is still referenced from table "enumtest_child". CREATE TYPE bogus AS ENUM('good', 'bad', 'ugly'); CREATE TABLE enumtest_bogus_child(parent bogus REFERENCES enumtest_parent); ERROR: foreign key constraint "enumtest_bogus_child_parent_fkey" cannot be implemented -DETAIL: Key columns "parent" and "id" are of incompatible types: bogus and rainbow. +DETAIL: Key columns "parent" of the referencing table and "id" of the referenced table are of incompatible types: bogus and rainbow. DROP TYPE bogus; -- check renaming a value ALTER TYPE rainbow RENAME VALUE 'red' TO 'crimson'; diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out index 69994c98e32..9bcc8495731 100644 --- a/src/test/regress/expected/foreign_key.out +++ b/src/test/regress/expected/foreign_key.out @@ -822,12 +822,12 @@ INSERT INTO PKTABLE VALUES(42); -- This next should fail, because int=inet does not exist CREATE TABLE FKTABLE (ftest1 inet REFERENCES pktable); ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented -DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: inet and integer. +DETAIL: Key columns "ftest1" of the referencing table and "ptest1" of the referenced table are of incompatible types: inet and integer. -- This should also fail for the same reason, but here we -- give the column name CREATE TABLE FKTABLE (ftest1 inet REFERENCES pktable(ptest1)); ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented -DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: inet and integer. +DETAIL: Key columns "ftest1" of the referencing table and "ptest1" of the referenced table are of incompatible types: inet and integer. -- This should succeed, even though they are different types, -- because int=int8 exists and is a member of the integer opfamily CREATE TABLE FKTABLE (ftest1 int8 REFERENCES pktable); @@ -846,7 +846,7 @@ DROP TABLE FKTABLE; -- of the integer opfamily) CREATE TABLE FKTABLE (ftest1 numeric REFERENCES pktable); ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented -DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: numeric and integer. +DETAIL: Key columns "ftest1" of the referencing table and "ptest1" of the referenced table are of incompatible types: numeric and integer. DROP TABLE PKTABLE; -- On the other hand, this should work because int implicitly promotes to -- numeric, and we allow promotion on the FK side @@ -869,23 +869,23 @@ CREATE TABLE PKTABLE (ptest1 int, ptest2 inet, PRIMARY KEY(ptest1, ptest2)); -- This should fail, because we just chose really odd types CREATE TABLE FKTABLE (ftest1 cidr, ftest2 timestamp, FOREIGN KEY(ftest1, ftest2) REFERENCES pktable); ERROR: foreign key constraint "fktable_ftest1_ftest2_fkey" cannot be implemented -DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: cidr and integer. +DETAIL: Key columns "ftest1" of the referencing table and "ptest1" of the referenced table are of incompatible types: cidr and integer. -- Again, so should this... CREATE TABLE FKTABLE (ftest1 cidr, ftest2 timestamp, FOREIGN KEY(ftest1, ftest2) REFERENCES pktable(ptest1, ptest2)); ERROR: foreign key constraint "fktable_ftest1_ftest2_fkey" cannot be implemented -DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: cidr and integer. +DETAIL: Key columns "ftest1" of the referencing table and "ptest1" of the referenced table are of incompatible types: cidr and integer. -- This fails because we mixed up the column ordering CREATE TABLE FKTABLE (ftest1 int, ftest2 inet, FOREIGN KEY(ftest2, ftest1) REFERENCES pktable); ERROR: foreign key constraint "fktable_ftest2_ftest1_fkey" cannot be implemented -DETAIL: Key columns "ftest2" and "ptest1" are of incompatible types: inet and integer. +DETAIL: Key columns "ftest2" of the referencing table and "ptest1" of the referenced table are of incompatible types: inet and integer. -- As does this... CREATE TABLE FKTABLE (ftest1 int, ftest2 inet, FOREIGN KEY(ftest2, ftest1) REFERENCES pktable(ptest1, ptest2)); ERROR: foreign key constraint "fktable_ftest2_ftest1_fkey" cannot be implemented -DETAIL: Key columns "ftest2" and "ptest1" are of incompatible types: inet and integer. +DETAIL: Key columns "ftest2" of the referencing table and "ptest1" of the referenced table are of incompatible types: inet and integer. -- And again.. CREATE TABLE FKTABLE (ftest1 int, ftest2 inet, FOREIGN KEY(ftest1, ftest2) REFERENCES pktable(ptest2, ptest1)); ERROR: foreign key constraint "fktable_ftest1_ftest2_fkey" cannot be implemented -DETAIL: Key columns "ftest1" and "ptest2" are of incompatible types: integer and inet. +DETAIL: Key columns "ftest1" of the referencing table and "ptest2" of the referenced table are of incompatible types: integer and inet. -- This works... CREATE TABLE FKTABLE (ftest1 int, ftest2 inet, FOREIGN KEY(ftest2, ftest1) REFERENCES pktable(ptest2, ptest1)); DROP TABLE FKTABLE; @@ -906,17 +906,17 @@ DROP TABLE PKTABLE; CREATE TABLE PKTABLE (ptest1 int, ptest2 inet, ptest3 int, ptest4 inet, PRIMARY KEY(ptest1, ptest2), FOREIGN KEY(ptest3, ptest4) REFERENCES pktable(ptest2, ptest1)); ERROR: foreign key constraint "pktable_ptest3_ptest4_fkey" cannot be implemented -DETAIL: Key columns "ptest3" and "ptest2" are of incompatible types: integer and inet. +DETAIL: Key columns "ptest3" of the referencing table and "ptest2" of the referenced table are of incompatible types: integer and inet. -- Nor should this... (same reason, we have 4,3 referencing 1,2 which mismatches types CREATE TABLE PKTABLE (ptest1 int, ptest2 inet, ptest3 int, ptest4 inet, PRIMARY KEY(ptest1, ptest2), FOREIGN KEY(ptest4, ptest3) REFERENCES pktable(ptest1, ptest2)); ERROR: foreign key constraint "pktable_ptest4_ptest3_fkey" cannot be implemented -DETAIL: Key columns "ptest4" and "ptest1" are of incompatible types: inet and integer. +DETAIL: Key columns "ptest4" of the referencing table and "ptest1" of the referenced table are of incompatible types: inet and integer. -- Not this one either... Same as the last one except we didn't defined the columns being referenced. CREATE TABLE PKTABLE (ptest1 int, ptest2 inet, ptest3 int, ptest4 inet, PRIMARY KEY(ptest1, ptest2), FOREIGN KEY(ptest4, ptest3) REFERENCES pktable); ERROR: foreign key constraint "pktable_ptest4_ptest3_fkey" cannot be implemented -DETAIL: Key columns "ptest4" and "ptest1" are of incompatible types: inet and integer. +DETAIL: Key columns "ptest4" of the referencing table and "ptest1" of the referenced table are of incompatible types: inet and integer. -- -- Now some cases with inheritance -- Basic 2 table case: 1 column of matching types. @@ -1009,20 +1009,20 @@ create table pktable(ptest1 inet, primary key(base1, ptest1)) inherits (pktable_ -- just generally bad types (with and without column references on the referenced table) create table fktable(ftest1 cidr, ftest2 int[], foreign key (ftest1, ftest2) references pktable); ERROR: foreign key constraint "fktable_ftest1_ftest2_fkey" cannot be implemented -DETAIL: Key columns "ftest1" and "base1" are of incompatible types: cidr and integer. +DETAIL: Key columns "ftest1" of the referencing table and "base1" of the referenced table are of incompatible types: cidr and integer. create table fktable(ftest1 cidr, ftest2 int[], foreign key (ftest1, ftest2) references pktable(base1, ptest1)); ERROR: foreign key constraint "fktable_ftest1_ftest2_fkey" cannot be implemented -DETAIL: Key columns "ftest1" and "base1" are of incompatible types: cidr and integer. +DETAIL: Key columns "ftest1" of the referencing table and "base1" of the referenced table are of incompatible types: cidr and integer. -- let's mix up which columns reference which create table fktable(ftest1 int, ftest2 inet, foreign key(ftest2, ftest1) references pktable); ERROR: foreign key constraint "fktable_ftest2_ftest1_fkey" cannot be implemented -DETAIL: Key columns "ftest2" and "base1" are of incompatible types: inet and integer. +DETAIL: Key columns "ftest2" of the referencing table and "base1" of the referenced table are of incompatible types: inet and integer. create table fktable(ftest1 int, ftest2 inet, foreign key(ftest2, ftest1) references pktable(base1, ptest1)); ERROR: foreign key constraint "fktable_ftest2_ftest1_fkey" cannot be implemented -DETAIL: Key columns "ftest2" and "base1" are of incompatible types: inet and integer. +DETAIL: Key columns "ftest2" of the referencing table and "base1" of the referenced table are of incompatible types: inet and integer. create table fktable(ftest1 int, ftest2 inet, foreign key(ftest1, ftest2) references pktable(ptest1, base1)); ERROR: foreign key constraint "fktable_ftest1_ftest2_fkey" cannot be implemented -DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: integer and inet. +DETAIL: Key columns "ftest1" of the referencing table and "ptest1" of the referenced table are of incompatible types: integer and inet. drop table pktable; drop table pktable_base; -- 2 columns (1 table), mismatched types @@ -1030,19 +1030,19 @@ create table pktable_base(base1 int not null, base2 int); create table pktable(ptest1 inet, ptest2 inet[], primary key(base1, ptest1), foreign key(base2, ptest2) references pktable(base1, ptest1)) inherits (pktable_base); ERROR: foreign key constraint "pktable_base2_ptest2_fkey" cannot be implemented -DETAIL: Key columns "ptest2" and "ptest1" are of incompatible types: inet[] and inet. +DETAIL: Key columns "ptest2" of the referencing table and "ptest1" of the referenced table are of incompatible types: inet[] and inet. create table pktable(ptest1 inet, ptest2 inet, primary key(base1, ptest1), foreign key(base2, ptest2) references pktable(ptest1, base1)) inherits (pktable_base); ERROR: foreign key constraint "pktable_base2_ptest2_fkey" cannot be implemented -DETAIL: Key columns "base2" and "ptest1" are of incompatible types: integer and inet. +DETAIL: Key columns "base2" of the referencing table and "ptest1" of the referenced table are of incompatible types: integer and inet. create table pktable(ptest1 inet, ptest2 inet, primary key(base1, ptest1), foreign key(ptest2, base2) references pktable(base1, ptest1)) inherits (pktable_base); ERROR: foreign key constraint "pktable_ptest2_base2_fkey" cannot be implemented -DETAIL: Key columns "ptest2" and "base1" are of incompatible types: inet and integer. +DETAIL: Key columns "ptest2" of the referencing table and "base1" of the referenced table are of incompatible types: inet and integer. create table pktable(ptest1 inet, ptest2 inet, primary key(base1, ptest1), foreign key(ptest2, base2) references pktable(base1, ptest1)) inherits (pktable_base); ERROR: foreign key constraint "pktable_ptest2_base2_fkey" cannot be implemented -DETAIL: Key columns "ptest2" and "base1" are of incompatible types: inet and integer. +DETAIL: Key columns "ptest2" of the referencing table and "base1" of the referenced table are of incompatible types: inet and integer. drop table pktable; ERROR: table "pktable" does not exist drop table pktable_base; @@ -1154,22 +1154,22 @@ CREATE TEMP TABLE fktable ( ALTER TABLE fktable ADD CONSTRAINT fk_2_3 FOREIGN KEY (x2) REFERENCES pktable(id3); ERROR: foreign key constraint "fk_2_3" cannot be implemented -DETAIL: Key columns "x2" and "id3" are of incompatible types: character varying and real. +DETAIL: Key columns "x2" of the referencing table and "id3" of the referenced table are of incompatible types: character varying and real. -- nor to int4 ALTER TABLE fktable ADD CONSTRAINT fk_2_1 FOREIGN KEY (x2) REFERENCES pktable(id1); ERROR: foreign key constraint "fk_2_1" cannot be implemented -DETAIL: Key columns "x2" and "id1" are of incompatible types: character varying and integer. +DETAIL: Key columns "x2" of the referencing table and "id1" of the referenced table are of incompatible types: character varying and integer. -- real does not promote to int4 ALTER TABLE fktable ADD CONSTRAINT fk_3_1 FOREIGN KEY (x3) REFERENCES pktable(id1); ERROR: foreign key constraint "fk_3_1" cannot be implemented -DETAIL: Key columns "x3" and "id1" are of incompatible types: real and integer. +DETAIL: Key columns "x3" of the referencing table and "id1" of the referenced table are of incompatible types: real and integer. -- int4 does not promote to text ALTER TABLE fktable ADD CONSTRAINT fk_1_2 FOREIGN KEY (x1) REFERENCES pktable(id2); ERROR: foreign key constraint "fk_1_2" cannot be implemented -DETAIL: Key columns "x1" and "id2" are of incompatible types: integer and character varying. +DETAIL: Key columns "x1" of the referencing table and "id2" of the referenced table are of incompatible types: integer and character varying. -- should succeed -- int4 promotes to real ALTER TABLE fktable ADD CONSTRAINT fk_1_3 @@ -1192,11 +1192,11 @@ FOREIGN KEY (x2,x5,x3) REFERENCES pktable(id2,id1,id3); ALTER TABLE fktable ADD CONSTRAINT fk_123_231 FOREIGN KEY (x1,x2,x3) REFERENCES pktable(id2,id3,id1); ERROR: foreign key constraint "fk_123_231" cannot be implemented -DETAIL: Key columns "x1" and "id2" are of incompatible types: integer and character varying. +DETAIL: Key columns "x1" of the referencing table and "id2" of the referenced table are of incompatible types: integer and character varying. ALTER TABLE fktable ADD CONSTRAINT fk_241_132 FOREIGN KEY (x2,x4,x1) REFERENCES pktable(id1,id3,id2); ERROR: foreign key constraint "fk_241_132" cannot be implemented -DETAIL: Key columns "x2" and "id1" are of incompatible types: character varying and integer. +DETAIL: Key columns "x2" of the referencing table and "id1" of the referenced table are of incompatible types: character varying and integer. DROP TABLE pktable, fktable; -- test a tricky case: we can elide firing the FK check trigger during -- an UPDATE if the UPDATE did not change the foreign key diff --git a/src/test/regress/expected/without_overlaps.out b/src/test/regress/expected/without_overlaps.out index 86171c994c9..d6cb65e9a63 100644 --- a/src/test/regress/expected/without_overlaps.out +++ b/src/test/regress/expected/without_overlaps.out @@ -1377,7 +1377,7 @@ CREATE TABLE temporal_fk_rng2rng ( REFERENCES temporal_rng (id, PERIOD valid_at) ); ERROR: foreign key constraint "temporal_fk_rng2rng_fk2" cannot be implemented -DETAIL: Key columns "valid_at" and "valid_at" are of incompatible types: int4range and daterange. +DETAIL: Key columns "valid_at" of the referencing table and "valid_at" of the referenced table are of incompatible types: int4range and daterange. -- works: PERIOD for both referenced and referencing CREATE TABLE temporal_fk_rng2rng ( id int4range, @@ -1562,7 +1562,7 @@ ALTER TABLE temporal_fk_rng2rng FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng; ERROR: foreign key constraint "temporal_fk_rng2rng_fk" cannot be implemented -DETAIL: Key columns "valid_at" and "valid_at" are of incompatible types: tsrange and daterange. +DETAIL: Key columns "valid_at" of the referencing table and "valid_at" of the referenced table are of incompatible types: tsrange and daterange. ALTER TABLE temporal_fk_rng2rng ALTER COLUMN valid_at TYPE daterange USING daterange(lower(valid_at)::date, upper(valid_at)::date); -- with inferred PK on the referenced table: @@ -1884,7 +1884,7 @@ CREATE TABLE temporal_fk_mltrng2mltrng ( REFERENCES temporal_mltrng (id, PERIOD valid_at) ); ERROR: foreign key constraint "temporal_fk_mltrng2mltrng_fk2" cannot be implemented -DETAIL: Key columns "valid_at" and "valid_at" are of incompatible types: int4multirange and datemultirange. +DETAIL: Key columns "valid_at" of the referencing table and "valid_at" of the referenced table are of incompatible types: int4multirange and datemultirange. CREATE TABLE temporal_fk_mltrng2mltrng ( id int4range, valid_at datemultirange, -- 2.30.2