diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 158c594..8a73860 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -6738,6 +6738,23 @@ transformFkeyCheckAttrs(Relation pkrel, bool found_deferrable = false; List *indexoidlist; ListCell *indexoidscan; + int i, + j; + + /* + * We'll disallow foreign keys from being defined which reference indexes + * that have duplicated columns. + */ + for (i = 0; i < numattrs; i++) + { + for (j = i + 1; j < numattrs; j++) + { + if (attnums[i] == attnums[j]) + ereport(ERROR, + (errcode(ERRCODE_INVALID_FOREIGN_KEY), + errmsg("a foreign key cannot contain duplicate column references"))); + } + } /* * Get the list of index OIDs for the table from the relcache, and look up @@ -6750,8 +6767,6 @@ transformFkeyCheckAttrs(Relation pkrel, { HeapTuple indexTuple; Form_pg_index indexStruct; - int i, - j; indexoid = lfirst_oid(indexoidscan); indexTuple = SearchSysCache1(INDEXRELID, ObjectIdGetDatum(indexoid)); @@ -6782,7 +6797,10 @@ transformFkeyCheckAttrs(Relation pkrel, /* * The given attnum list may match the index columns in any order. - * Check that each list is a subset of the other. + * + * We already know that attnums is free of duplicate values per the + * test at the start of this function, so we can skip on testing if + * the attnums is a subset of the index key values. */ for (i = 0; i < numattrs; i++) { @@ -6791,6 +6809,7 @@ transformFkeyCheckAttrs(Relation pkrel, { if (attnums[i] == indexStruct->indkey.values[j]) { + opclasses[i] = indclass->values[j]; found = true; break; } @@ -6798,24 +6817,6 @@ transformFkeyCheckAttrs(Relation pkrel, if (!found) break; } - if (found) - { - for (i = 0; i < numattrs; i++) - { - found = false; - for (j = 0; j < numattrs; j++) - { - if (attnums[j] == indexStruct->indkey.values[i]) - { - opclasses[j] = indclass->values[i]; - found = true; - break; - } - } - if (!found) - break; - } - } /* * Refuse to use a deferrable unique/primary key. This is per SQL diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out index 0299bfe..d23e49b 100644 --- a/src/test/regress/expected/foreign_key.out +++ b/src/test/regress/expected/foreign_key.out @@ -732,6 +732,15 @@ ERROR: there is no unique constraint matching given keys for referenced table " DROP TABLE FKTABLE_FAIL1; ERROR: table "fktable_fail1" does not exist DROP TABLE PKTABLE; +-- Test to ensure foreign keys that reference unique constraints with duplicate +-- column references are disallowed. +CREATE TABLE PKTABLE (ptest1 int); +CREATE UNIQUE INDEX ON PKTABLE (ptest1, ptest1); +CREATE TABLE FKTABLE_FAIL1 (ftest1 int, ftest2 int); +ALTER TABLE FKTABLE_FAIL1 ADD CONSTRAINT fk FOREIGN KEY (ftest1, ftest2) REFERENCES PKTABLE(ptest1,ptest1); +ERROR: a foreign key cannot contain duplicate column references +DROP TABLE FKTABLE_FAIL1; +DROP TABLE PKTABLE; -- -- Tests for mismatched types -- diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql index 531c881..976f402 100644 --- a/src/test/regress/sql/foreign_key.sql +++ b/src/test/regress/sql/foreign_key.sql @@ -439,6 +439,16 @@ CREATE TABLE FKTABLE_FAIL1 (ftest1 int REFERENCES pktable(ptest1)); DROP TABLE FKTABLE_FAIL1; DROP TABLE PKTABLE; +-- Test to ensure foreign keys that reference unique constraints with duplicate +-- column references are disallowed. +CREATE TABLE PKTABLE (ptest1 int); +CREATE UNIQUE INDEX ON PKTABLE (ptest1, ptest1); +CREATE TABLE FKTABLE_FAIL1 (ftest1 int, ftest2 int); +ALTER TABLE FKTABLE_FAIL1 ADD CONSTRAINT fk FOREIGN KEY (ftest1, ftest2) REFERENCES PKTABLE(ptest1,ptest1); + +DROP TABLE FKTABLE_FAIL1; +DROP TABLE PKTABLE; + -- -- Tests for mismatched types --