diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index f99919093ca0da8c59ee4f4df0643837dfbdb38b..44f40118cca88c099e511bebcd17c80330b4adac 100644 *** a/doc/src/sgml/catalogs.sgml --- b/doc/src/sgml/catalogs.sgml *************** *** 2148,2153 **** --- 2148,2163 ---- + confreftype + char[] + + If a foreign key, the reference semantics for each column: + p = plain (simple equality), + e = each element of referencing array must have a match + + + + conpfeqop oid[] pg_operator.oid *************** *** 2193,2198 **** --- 2203,2214 ---- + When confreftype indicates array-vs-scalar + foreign key reference semantics, the equality operators listed in + conpfeqop etc are for the array's element type. + + + In the case of an exclusion constraint, conkey is only useful for constraint elements that are simple column references. For other cases, a zero appears in conkey diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index d6e5d646ee462cb7320df9ef3b17d06de93d55f8..ccdd6f02e8a466682a67ae34eb645726e59b294c 100644 *** a/doc/src/sgml/ddl.sgml --- b/doc/src/sgml/ddl.sgml *************** CREATE TABLE order_items ( *** 877,882 **** --- 877,987 ---- + + Array ELEMENT Foreign Keys + + + ELEMENT foreign key + + + + constraint + Array ELEMENT foreign key + + + + constraint + ELEMENT foreign key + + + + referential integrity + + + + Another option you have with foreign keys is to use a + referencing column which is an array of elements with + the same type (or a compatible one) as the referenced + column in the related table. This feature is called + array element foreign key and is implemented + in PostgreSQL with ELEMENT foreign key constraints, + as described in the following example: + + + CREATE TABLE drivers ( + driver_id integer PRIMARY KEY, + first_name text, + last_name text, + ... + ); + + CREATE TABLE races ( + race_id integer PRIMARY KEY, + title text, + race_day DATE, + ... + final_positions integer[] ELEMENT REFERENCES drivers + ); + + + The above example uses an array (final_positions) + to store the results of a race: for each of its elements + a referential integrity check is enforced on the + drivers table. + Note that ELEMENT REFERENCES is an extension + of PostgreSQL and it is not included in the SQL standard. + + + + Even though the most common use case for array ELEMENT + foreign keys is on a single column key, you can define an array + ELEMENT foreign key constraint on a group + of columns. As the following example shows, it must be written in table + constraint form: + + + CREATE TABLE available_moves ( + kind text, + move text, + description text, + PRIMARY KEY (kind, move) + ); + + CREATE TABLE paths ( + description text, + kind text, + moves text[], + FOREIGN KEY (kind, ELEMENT moves) REFERENCES available_moves (kind, move) + ); + + INSERT INTO available_moves VALUES ('relative', 'LN', 'look north'); + INSERT INTO available_moves VALUES ('relative', 'RL', 'rotate left'); + INSERT INTO available_moves VALUES ('relative', 'RR', 'rotate right'); + INSERT INTO available_moves VALUES ('relative', 'MF', 'move forward'); + INSERT INTO available_moves VALUES ('absolute', 'N', 'move north'); + INSERT INTO available_moves VALUES ('absolute', 'S', 'move south'); + INSERT INTO available_moves VALUES ('absolute', 'E', 'move east'); + INSERT INTO available_moves VALUES ('absolute', 'W', 'move west'); + + INSERT INTO paths VALUES ('L-shaped path', 'relative', '{LN, RL, MF, RR, MF, MF}'); + INSERT INTO paths VALUES ('L-shaped path', 'absolute', '{W, N, N}'); + + + On top of standard foreign key requirements, + array ELEMENT foreign key constraints + require that the referencing column is an array of a compatible + type of the corresponding referenced column. + + + + For more detailed information on array ELEMENT + foreign key options and special cases, please refer to the documentation + for and + . + + + + Exclusion Constraints diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index 445ca40695084489cdd90413a31c17ec28068ef5..982ec77f2c76083003fb745c4373f742e1a29602 100644 *** a/doc/src/sgml/ref/create_table.sgml --- b/doc/src/sgml/ref/create_table.sgml *************** CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY *** 51,57 **** DEFAULT default_expr | UNIQUE index_parameters | PRIMARY KEY index_parameters | ! REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] } [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] --- 51,57 ---- DEFAULT default_expr | UNIQUE index_parameters | PRIMARY KEY index_parameters | ! [ELEMENT] REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] } [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] *************** CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY *** 62,68 **** UNIQUE ( column_name [, ... ] ) index_parameters | PRIMARY KEY ( column_name [, ... ] ) index_parameters | EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] | ! FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] } [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] --- 62,68 ---- UNIQUE ( column_name [, ... ] ) index_parameters | PRIMARY KEY ( column_name [, ... ] ) index_parameters | EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] | ! FOREIGN KEY ( [ELEMENT] column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] } [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] *************** CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY *** 567,576 **** ! REFERENCES reftable [ ( refcolumn ) ] [ MATCH matchtype ] [ ON DELETE action ] [ ON UPDATE action ] (column constraint) ! FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] [ MATCH matchtype ] [ ON DELETE action ] --- 567,577 ---- ! REFERENCES reftable [ ( refcolumn ) ] [ MATCH matchtype ] [ ON DELETE action ] [ ON UPDATE action ] (column constraint) ! ! FOREIGN KEY ( [ELEMENT] column [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] [ MATCH matchtype ] [ ON DELETE action ] *************** CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY *** 592,597 **** --- 593,611 ---- + In case the column name column + is prepended with the ELEMENT keyword and column is an array of elements compatible + with the corresponding refcolumn + in reftable, an + array ELEMENT foreign key constraint is put in place + (see + for more information). + Multi-column keys with more than one ELEMENT column + are currently not allowed. + + + A value inserted into the referencing column(s) is matched against the values of the referenced table and referenced columns using the given match type. There are three match types: MATCH *************** CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY *** 654,660 **** Delete any rows referencing the deleted row, or update the values of the referencing column(s) to the new values of the ! referenced columns, respectively. --- 668,675 ---- Delete any rows referencing the deleted row, or update the values of the referencing column(s) to the new values of the ! referenced columns, respectively. Currently not supported ! with array ELEMENT foreign keys. *************** CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY *** 663,669 **** SET NULL ! Set the referencing column(s) to null. --- 678,685 ---- SET NULL ! Set the referencing column(s) to null. Currently not supported ! with array ELEMENT foreign keys. *************** CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY *** 675,680 **** --- 691,698 ---- Set the referencing column(s) to their default values. (There must be a row in the referenced table matching the default values, if they are not null, or the operation will fail.) + Currently not supported with array ELEMENT + foreign keys. *************** CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY *** 690,695 **** --- 708,768 ---- + + ELEMENT REFERENCES reftable [ ( refcolumn ) ] [ MATCH matchtype ] [ ON DELETE action ] [ ON UPDATE action ] (column constraint) + + + + The ELEMENT REFERENCES definition specifies + an array ELEMENT foreign key, + a special kind of foreign key + constraint requiring the referencing column to be an array of elements + of the same type (or a compatible one) as the referenced column + in the referenced table. The value of each element of the + refcolumn array + will be matched against some row of reftable. + + + + Array ELEMENT foreign keys are an extension + of PostgreSQL and are not included in the SQL standard. + + + + Even with ELEMENT foreign keys, modifications + in the referenced column can trigger actions to be performed on + the referencing array. + Similarly to standard foreign keys, you can specify these + actions using the ON DELETE and + ON UPDATE clauses. + However, only the two following actions for each clause are + currently allowed: + + + + NO ACTION + + + Same as standard foreign key constraints. This is the default action. + + + + + + RESTRICT + + + Same as standard foreign key constraints. + + + + + + + + + DEFERRABLE NOT DEFERRABLE *************** CREATE TABLE employees OF employee_type *** 1444,1449 **** --- 1517,1532 ---- effect can be had using the OID feature. + + + Array <literal>ELEMENT</literal> Foreign Keys + + + Array ELEMENT foreign keys and the + ELEMENT REFERENCES clause + are a PostgreSQL extension. + + diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c index c80df418fafeff925e4687b0b2cb7dce92e75dd5..07d4bbb55006ab133903b2f6ca93c04919d7e001 100644 *** a/src/backend/catalog/heap.c --- b/src/backend/catalog/heap.c *************** StoreRelCheck(Relation rel, char *ccname *** 1934,1939 **** --- 1934,1940 ---- NULL, NULL, NULL, + NULL, 0, ' ', ' ', diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c index 972a528aec5381a09a990a088d8e1fc97c86165b..f90dbc33b58e0fbf2691b175bd75a8e70b6afeab 100644 *** a/src/backend/catalog/index.c --- b/src/backend/catalog/index.c *************** index_constraint_create(Relation heapRel *** 1161,1166 **** --- 1161,1167 ---- NULL, NULL, NULL, + NULL, 0, ' ', ' ', diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.c index 8df3f18e300fb42d7b17d82e294b5de413070742..799dcc2e0ef2c83c7cc9b21653dd2704287dd50e 100644 *** a/src/backend/catalog/pg_constraint.c --- b/src/backend/catalog/pg_constraint.c *************** CreateConstraintEntry(const char *constr *** 55,60 **** --- 55,61 ---- Oid indexRelId, Oid foreignRelId, const int16 *foreignKey, + const char *foreignRefType, const Oid *pfEqOp, const Oid *ppEqOp, const Oid *ffEqOp, *************** CreateConstraintEntry(const char *constr *** 77,82 **** --- 78,84 ---- Datum values[Natts_pg_constraint]; ArrayType *conkeyArray; ArrayType *confkeyArray; + ArrayType *confreftypeArray; ArrayType *conpfeqopArray; ArrayType *conppeqopArray; ArrayType *conffeqopArray; *************** CreateConstraintEntry(const char *constr *** 114,120 **** for (i = 0; i < foreignNKeys; i++) fkdatums[i] = Int16GetDatum(foreignKey[i]); confkeyArray = construct_array(fkdatums, foreignNKeys, ! INT2OID, 2, true, 's'); for (i = 0; i < foreignNKeys; i++) fkdatums[i] = ObjectIdGetDatum(pfEqOp[i]); conpfeqopArray = construct_array(fkdatums, foreignNKeys, --- 116,126 ---- for (i = 0; i < foreignNKeys; i++) fkdatums[i] = Int16GetDatum(foreignKey[i]); confkeyArray = construct_array(fkdatums, foreignNKeys, ! INT2OID, sizeof(int16), true, 's'); ! for (i = 0; i < foreignNKeys; i++) ! fkdatums[i] = CharGetDatum(foreignRefType[i]); ! confreftypeArray = construct_array(fkdatums, foreignNKeys, ! CHAROID, sizeof(char), true, 'c'); for (i = 0; i < foreignNKeys; i++) fkdatums[i] = ObjectIdGetDatum(pfEqOp[i]); conpfeqopArray = construct_array(fkdatums, foreignNKeys, *************** CreateConstraintEntry(const char *constr *** 131,136 **** --- 137,143 ---- else { confkeyArray = NULL; + confreftypeArray = NULL; conpfeqopArray = NULL; conppeqopArray = NULL; conffeqopArray = NULL; *************** CreateConstraintEntry(const char *constr *** 183,188 **** --- 190,200 ---- else nulls[Anum_pg_constraint_confkey - 1] = true; + if (confreftypeArray) + values[Anum_pg_constraint_confreftype - 1] = PointerGetDatum(confreftypeArray); + else + nulls[Anum_pg_constraint_confreftype - 1] = true; + if (conpfeqopArray) values[Anum_pg_constraint_conpfeqop - 1] = PointerGetDatum(conpfeqopArray); else diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 359d478592b2528211f1556a75900ffd77594a77..58f16162d498d9ebdf68e96e8b6d674ebc631c09 100644 *** a/src/backend/commands/tablecmds.c --- b/src/backend/commands/tablecmds.c *************** ATAddForeignKeyConstraint(AlteredTableIn *** 5690,5695 **** --- 5690,5696 ---- Relation pkrel; int16 pkattnum[INDEX_MAX_KEYS]; int16 fkattnum[INDEX_MAX_KEYS]; + char fkreftypes[INDEX_MAX_KEYS]; Oid pktypoid[INDEX_MAX_KEYS]; Oid fktypoid[INDEX_MAX_KEYS]; Oid opclasses[INDEX_MAX_KEYS]; *************** ATAddForeignKeyConstraint(AlteredTableIn *** 5697,5706 **** --- 5698,5709 ---- Oid ppeqoperators[INDEX_MAX_KEYS]; Oid ffeqoperators[INDEX_MAX_KEYS]; int i; + ListCell *lc; int numfks, numpks; Oid indexOid; Oid constrOid; + bool has_array; bool old_check_ok; ListCell *old_pfeqop_item = list_head(fkconstraint->old_conpfeqop); *************** ATAddForeignKeyConstraint(AlteredTableIn *** 5766,5771 **** --- 5769,5775 ---- */ MemSet(pkattnum, 0, sizeof(pkattnum)); MemSet(fkattnum, 0, sizeof(fkattnum)); + MemSet(fkreftypes, 0, sizeof(fkreftypes)); MemSet(pktypoid, 0, sizeof(pktypoid)); MemSet(fktypoid, 0, sizeof(fktypoid)); MemSet(opclasses, 0, sizeof(opclasses)); *************** ATAddForeignKeyConstraint(AlteredTableIn *** 5778,5783 **** --- 5782,5831 ---- fkattnum, fktypoid); /* + * Validate the reference semantics codes, too, and convert list to array + * format to pass to CreateConstraintEntry. + */ + Assert(list_length(fkconstraint->fk_reftypes) == numfks); + has_array = false; + i = 0; + foreach(lc, fkconstraint->fk_reftypes) + { + char reftype = lfirst_int(lc); + + switch (reftype) + { + case FKCONSTR_REF_PLAIN: + /* OK, nothing to do */ + break; + case FKCONSTR_REF_EACH_ELEMENT: + /* At most one FK column can be an array reference */ + if (has_array) + ereport(ERROR, + (errcode(ERRCODE_INVALID_FOREIGN_KEY), + errmsg("foreign keys support only one array column"))); + has_array = true; + break; + default: + elog(ERROR, "invalid fk_reftype: %d", (int) reftype); + break; + } + fkreftypes[i] = reftype; + i++; + } + + /* Array foreign keys support only NO ACTION and RESTRICT actions */ + if (has_array) + { + if ((fkconstraint->fk_upd_action != FKCONSTR_ACTION_NOACTION && + fkconstraint->fk_upd_action != FKCONSTR_ACTION_RESTRICT) || + (fkconstraint->fk_del_action != FKCONSTR_ACTION_NOACTION && + fkconstraint->fk_del_action != FKCONSTR_ACTION_RESTRICT)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_FOREIGN_KEY), + errmsg("array foreign keys support only NO ACTION and RESTRICT actions"))); + } + + /* * If the attribute list for the referenced table was omitted, lookup the * definition of the primary key and use it. Otherwise, validate the * supplied attribute list. In either case, discover the index OID and *************** ATAddForeignKeyConstraint(AlteredTableIn *** 5864,5869 **** --- 5912,5976 ---- eqstrategy = BTEqualStrategyNumber; /* + * If this is an array foreign key, we must look up the operators for + * the array element type, not the array type itself. + */ + if (fkreftypes[i] != FKCONSTR_REF_PLAIN) + { + Oid elemopclass; + + /* We look through any domain here */ + fktype = get_base_element_type(fktype); + if (!OidIsValid(fktype)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_FOREIGN_KEY), + errmsg("foreign key constraint \"%s\" cannot be implemented", + fkconstraint->conname), + errdetail("Key column \"%s\" has type %s which is not an array type.", + strVal(list_nth(fkconstraint->fk_attrs, i)), + format_type_be(fktypoid[i])))); + + /* + * For the moment, we must also insist that the array's element + * type have a default btree opclass that is in the index's + * opfamily. This is necessary because ri_triggers.c relies on + * COUNT(DISTINCT x) on the element type, as well as on array_eq() + * on the array type, and we need those operations to have the + * same notion of equality that we're using otherwise. + * + * XXX this restriction is pretty annoying, considering the effort + * that's been put into the rest of the RI mechanisms to make them + * work with nondefault equality operators. In particular, it + * means that the cast-to-PK-datatype code path isn't useful for + * array-to-scalar references. + */ + elemopclass = GetDefaultOpClass(fktype, BTREE_AM_OID); + if (!OidIsValid(elemopclass) || + get_opclass_family(elemopclass) != opfamily) + { + /* Get the index opclass's name for the error message. */ + char *opcname; + + cla_ht = SearchSysCache1(CLAOID, + ObjectIdGetDatum(opclasses[i])); + if (!HeapTupleIsValid(cla_ht)) + elog(ERROR, "cache lookup failed for opclass %u", + opclasses[i]); + cla_tup = (Form_pg_opclass) GETSTRUCT(cla_ht); + opcname = pstrdup(NameStr(cla_tup->opcname)); + ReleaseSysCache(cla_ht); + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("foreign key constraint \"%s\" cannot be implemented", + fkconstraint->conname), + errdetail("Key column \"%s\" has element type %s which does not have a default btree operator class that's compatible with class \"%s\".", + strVal(list_nth(fkconstraint->fk_attrs, i)), + format_type_be(fktype), + opcname))); + } + } + + /* * There had better be a primary equality operator for the index. * We'll use it for PK = PK comparisons. */ *************** ATAddForeignKeyConstraint(AlteredTableIn *** 5924,5937 **** if (!(OidIsValid(pfeqop) && OidIsValid(ffeqop))) ereport(ERROR, (errcode(ERRCODE_DATATYPE_MISMATCH), ! errmsg("foreign key constraint \"%s\" " ! "cannot be implemented", ! fkconstraint->conname), ! errdetail("Key columns \"%s\" and \"%s\" " ! "are of incompatible types: %s and %s.", strVal(list_nth(fkconstraint->fk_attrs, i)), strVal(list_nth(fkconstraint->pk_attrs, i)), ! format_type_be(fktype), format_type_be(pktype)))); if (old_check_ok) --- 6031,6042 ---- if (!(OidIsValid(pfeqop) && OidIsValid(ffeqop))) ereport(ERROR, (errcode(ERRCODE_DATATYPE_MISMATCH), ! errmsg("foreign key constraint \"%s\" cannot be implemented", ! fkconstraint->conname), ! errdetail("Key columns \"%s\" and \"%s\" are of incompatible types: %s and %s.", strVal(list_nth(fkconstraint->fk_attrs, i)), strVal(list_nth(fkconstraint->pk_attrs, i)), ! format_type_be(fktypoid[i]), format_type_be(pktype)))); if (old_check_ok) *************** ATAddForeignKeyConstraint(AlteredTableIn *** 5960,5965 **** --- 6065,6077 ---- * We may assume that pg_constraint.conkey is not changing. */ old_fktype = tab->oldDesc->attrs[fkattnum[i] - 1]->atttypid; + if (fkreftypes[i] != FKCONSTR_REF_PLAIN) + { + old_fktype = get_base_element_type(old_fktype); + /* this shouldn't happen ... */ + if (!OidIsValid(old_fktype)) + elog(ERROR, "old foreign key column is not an array"); + } new_fktype = fktype; old_pathtype = findFkeyCast(pfeqop_right, old_fktype, &old_castfunc); *************** ATAddForeignKeyConstraint(AlteredTableIn *** 6002,6008 **** new_castfunc == old_castfunc && (!IsPolymorphicType(pfeqop_right) || new_fktype == old_fktype)); - } pfeqoperators[i] = pfeqop; --- 6114,6119 ---- *************** ATAddForeignKeyConstraint(AlteredTableIn *** 6027,6032 **** --- 6138,6144 ---- indexOid, RelationGetRelid(pkrel), pkattnum, + fkreftypes, pfeqoperators, ppeqoperators, ffeqoperators, diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c index 4d3ed9cb62c3b9353a3bacd4f5270f63e9d1e5cc..4d6177d0b855fb5649677268ebe78cd32a0035b3 100644 *** a/src/backend/commands/trigger.c --- b/src/backend/commands/trigger.c *************** CreateTrigger(CreateTrigStmt *stmt, cons *** 435,440 **** --- 435,441 ---- NULL, NULL, NULL, + NULL, 0, ' ', ' ', *************** ConvertTriggerToFK(CreateTrigStmt *stmt, *** 794,799 **** --- 795,801 ---- char fk_matchtype = FKCONSTR_MATCH_SIMPLE; List *fk_attrs = NIL; List *pk_attrs = NIL; + List *fk_reftypes = NIL; StringInfoData buf; int funcnum; OldTriggerInfo *info = NULL; *************** ConvertTriggerToFK(CreateTrigStmt *stmt, *** 823,829 **** --- 825,834 ---- if (i % 2) fk_attrs = lappend(fk_attrs, arg); else + { pk_attrs = lappend(pk_attrs, arg); + fk_reftypes = lappend_int(fk_reftypes, FKCONSTR_REF_PLAIN); + } } /* Prepare description of constraint for use in messages */ *************** ConvertTriggerToFK(CreateTrigStmt *stmt, *** 961,966 **** --- 966,972 ---- fkcon->conname = constr_name; fkcon->fk_attrs = fk_attrs; fkcon->pk_attrs = pk_attrs; + fkcon->fk_reftypes = fk_reftypes; fkcon->fk_matchtype = fk_matchtype; switch (info->funcoids[0]) { diff --git a/src/backend/commands/typecmds.c b/src/backend/commands/typecmds.c index 6cb6fd56fd9a668bf0efa7b17b2a8ad30cfadc7c..9a3f08565090db08624264493d90d17b6a94905f 100644 *** a/src/backend/commands/typecmds.c --- b/src/backend/commands/typecmds.c *************** domainAddConstraint(Oid domainOid, Oid d *** 2930,2935 **** --- 2930,2936 ---- NULL, NULL, NULL, + NULL, 0, ' ', ' ', diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index 9387ee90c9f4a7fd52e2145730758d4bf851376c..15f74cab3aa8ed755bc6620d30dfd017afe1f004 100644 *** a/src/backend/nodes/copyfuncs.c --- b/src/backend/nodes/copyfuncs.c *************** _copyConstraint(const Constraint *from) *** 2370,2375 **** --- 2370,2376 ---- COPY_NODE_FIELD(pktable); COPY_NODE_FIELD(fk_attrs); COPY_NODE_FIELD(pk_attrs); + COPY_NODE_FIELD(fk_reftypes); COPY_SCALAR_FIELD(fk_matchtype); COPY_SCALAR_FIELD(fk_upd_action); COPY_SCALAR_FIELD(fk_del_action); diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index 95a95f477bead7aee3b484c01f3802a6b81efc3b..f231fca3ddbafd965a4f767008ce94481b24a802 100644 *** a/src/backend/nodes/equalfuncs.c --- b/src/backend/nodes/equalfuncs.c *************** _equalConstraint(const Constraint *a, co *** 2184,2189 **** --- 2184,2190 ---- COMPARE_NODE_FIELD(pktable); COMPARE_NODE_FIELD(fk_attrs); COMPARE_NODE_FIELD(pk_attrs); + COMPARE_NODE_FIELD(fk_reftypes); COMPARE_SCALAR_FIELD(fk_matchtype); COMPARE_SCALAR_FIELD(fk_upd_action); COMPARE_SCALAR_FIELD(fk_del_action); diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c index 35c6287bc84e3786276ece8ce7a8425d8a690a78..2780dc60d2f5ad4226f838169983c39fd3851c3e 100644 *** a/src/backend/nodes/outfuncs.c --- b/src/backend/nodes/outfuncs.c *************** _outConstraint(StringInfo str, const Con *** 2666,2671 **** --- 2666,2672 ---- WRITE_NODE_FIELD(pktable); WRITE_NODE_FIELD(fk_attrs); WRITE_NODE_FIELD(pk_attrs); + WRITE_NODE_FIELD(fk_reftypes); WRITE_CHAR_FIELD(fk_matchtype); WRITE_CHAR_FIELD(fk_upd_action); WRITE_CHAR_FIELD(fk_del_action); diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index e4ff76e66e0990d91790ccc54615c26dd64a143e..6f8c221617938c193a127e8b673453aa2e174cc0 100644 *** a/src/backend/parser/gram.y --- b/src/backend/parser/gram.y *************** typedef struct PrivTarget *** 113,118 **** --- 113,125 ---- List *objs; } PrivTarget; + /* Private struct for the result of foreign_key_column_elem production */ + typedef struct FKColElem + { + Node *name; /* name of the column (a String) */ + char reftype; /* FKCONSTR_REF_xxx code */ + } FKColElem; + /* ConstraintAttributeSpec yields an integer bitmask of these flags: */ #define CAS_NOT_DEFERRABLE 0x01 #define CAS_DEFERRABLE 0x02 *************** static RangeVar *makeRangeVarFromAnyName *** 161,166 **** --- 168,174 ---- static void SplitColQualList(List *qualList, List **constraintList, CollateClause **collClause, core_yyscan_t yyscanner); + static void SplitFKColElems(List *fkcolelems, List **names, List **reftypes); static void processCASbits(int cas_bits, int location, const char *constrType, bool *deferrable, bool *initdeferred, bool *not_valid, bool *no_inherit, core_yyscan_t yyscanner); *************** static void processCASbits(int cas_bits, *** 208,213 **** --- 216,222 ---- A_Indices *aind; ResTarget *target; struct PrivTarget *privtarget; + struct FKColElem *fkcolelem; AccessPriv *accesspriv; InsertStmt *istmt; VariableSetStmt *vsetstmt; *************** static void processCASbits(int cas_bits, *** 312,317 **** --- 321,327 ---- %type privilege %type privileges privilege_list %type privilege_target + %type foreign_key_column_elem %type function_with_argtypes %type function_with_argtypes_list %type defacl_privilege_target *************** static void processCASbits(int cas_bits, *** 344,350 **** execute_param_clause using_clause returning_clause opt_enum_val_list enum_val_list table_func_column_list create_generic_options alter_generic_options ! relation_expr_list dostmt_opt_list %type opt_fdw_options fdw_options %type fdw_option --- 354,360 ---- execute_param_clause using_clause returning_clause opt_enum_val_list enum_val_list table_func_column_list create_generic_options alter_generic_options ! relation_expr_list dostmt_opt_list foreign_key_column_list %type opt_fdw_options fdw_options %type fdw_option *************** static void processCASbits(int cas_bits, *** 531,538 **** DEFERRABLE DEFERRED DEFINER DELETE_P DELIMITER DELIMITERS DESC DICTIONARY DISABLE_P DISCARD DISTINCT DO DOCUMENT_P DOMAIN_P DOUBLE_P DROP ! EACH ELSE ENABLE_P ENCODING ENCRYPTED END_P ENUM_P ESCAPE EVENT EXCEPT ! EXCLUDE EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPLAIN EXTENSION EXTERNAL EXTRACT FALSE_P FAMILY FETCH FIRST_P FLOAT_P FOLLOWING FOR FORCE FOREIGN FORWARD --- 541,548 ---- DEFERRABLE DEFERRED DEFINER DELETE_P DELIMITER DELIMITERS DESC DICTIONARY DISABLE_P DISCARD DISTINCT DO DOCUMENT_P DOMAIN_P DOUBLE_P DROP ! EACH ELEMENT ELSE ENABLE_P ENCODING ENCRYPTED END_P ENUM_P ESCAPE ! EVENT EXCEPT EXCLUDE EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPLAIN EXTENSION EXTERNAL EXTRACT FALSE_P FAMILY FETCH FIRST_P FLOAT_P FOLLOWING FOR FORCE FOREIGN FORWARD *************** ColConstraintElem: *** 2795,2802 **** --- 2805,2814 ---- n->contype = CONSTR_FOREIGN; n->location = @1; n->pktable = $2; + /* fk_attrs will be filled in by parse analysis */ n->fk_attrs = NIL; n->pk_attrs = $3; + n->fk_reftypes = list_make1_int(FKCONSTR_REF_PLAIN); n->fk_matchtype = $4; n->fk_upd_action = (char) ($5 >> 8); n->fk_del_action = (char) ($5 & 0xFF); *************** ConstraintElem: *** 2985,2998 **** NULL, yyscanner); $$ = (Node *)n; } ! | FOREIGN KEY '(' columnList ')' REFERENCES qualified_name ! opt_column_list key_match key_actions ConstraintAttributeSpec { Constraint *n = makeNode(Constraint); n->contype = CONSTR_FOREIGN; n->location = @1; n->pktable = $7; - n->fk_attrs = $4; n->pk_attrs = $8; n->fk_matchtype = $9; n->fk_upd_action = (char) ($10 >> 8); --- 2997,3011 ---- NULL, yyscanner); $$ = (Node *)n; } ! | FOREIGN KEY '(' foreign_key_column_list ')' REFERENCES ! qualified_name opt_column_list key_match key_actions ! ConstraintAttributeSpec { Constraint *n = makeNode(Constraint); n->contype = CONSTR_FOREIGN; n->location = @1; + SplitFKColElems($4, &n->fk_attrs, &n->fk_reftypes); n->pktable = $7; n->pk_attrs = $8; n->fk_matchtype = $9; n->fk_upd_action = (char) ($10 >> 8); *************** columnElem: ColId *** 3026,3031 **** --- 3039,3068 ---- } ; + foreign_key_column_list: + foreign_key_column_elem + { $$ = list_make1($1); } + | foreign_key_column_list ',' foreign_key_column_elem + { $$ = lappend($1, $3); } + ; + + foreign_key_column_elem: + ColId + { + FKColElem *n = (FKColElem *) palloc(sizeof(FKColElem)); + n->name = (Node *) makeString($1); + n->reftype = FKCONSTR_REF_PLAIN; + $$ = n; + } + | EACH ELEMENT OF ColId + { + FKColElem *n = (FKColElem *) palloc(sizeof(FKColElem)); + n->name = (Node *) makeString($4); + n->reftype = FKCONSTR_REF_EACH_ELEMENT; + $$ = n; + } + ; + key_match: MATCH FULL { $$ = FKCONSTR_MATCH_FULL; *************** unreserved_keyword: *** 12498,12503 **** --- 12535,12541 ---- | DOUBLE_P | DROP | EACH + | ELEMENT | ENABLE_P | ENCODING | ENCRYPTED *************** SplitColQualList(List *qualList, *** 13470,13475 **** --- 13508,13530 ---- *constraintList = qualList; } + /* Split a list of FKColElem structs into separate name and reftype lists */ + static void + SplitFKColElems(List *fkcolelems, List **names, List **reftypes) + { + ListCell *lc; + + *names = NIL; + *reftypes = NIL; + foreach(lc, fkcolelems) + { + FKColElem *fkcolelem = (FKColElem *) lfirst(lc); + + *names = lappend(*names, fkcolelem->name); + *reftypes = lappend_int(*reftypes, fkcolelem->reftype); + } + } + /* * Process result of ConstraintAttributeSpec, and set appropriate bool flags * in the output command node. Pass NULL for any flags the particular diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c index 95c57e81b584e9e5c82a2efb3a626632c6753cfd..372c0dac4ab9785e8f912c81bcab3204c0534a38 100644 *** a/src/backend/parser/parse_utilcmd.c --- b/src/backend/parser/parse_utilcmd.c *************** transformColumnDefinition(CreateStmtCont *** 537,542 **** --- 537,544 ---- * list of FK constraints to be processed later. */ constraint->fk_attrs = list_make1(makeString(column->colname)); + /* grammar should have set fk_reftypes */ + Assert(list_length(constraint->fk_reftypes) == 1); cxt->fkconstraints = lappend(cxt->fkconstraints, constraint); break; diff --git a/src/backend/utils/adt/ri_triggers.c b/src/backend/utils/adt/ri_triggers.c index 97e68b1b6cb2826e7f6fa2955cb3905109db4572..9ece9666b9a2d1e63948391dd147d5be720bee23 100644 *** a/src/backend/utils/adt/ri_triggers.c --- b/src/backend/utils/adt/ri_triggers.c *************** typedef struct RI_ConstraintInfo *** 114,122 **** --- 114,124 ---- char confupdtype; /* foreign key's ON UPDATE action */ char confdeltype; /* foreign key's ON DELETE action */ char confmatchtype; /* foreign key's match type */ + bool has_array; /* true if any reftype is EACH_ELEMENT */ int nkeys; /* number of key columns */ int16 pk_attnums[RI_MAX_NUMKEYS]; /* attnums of referenced cols */ int16 fk_attnums[RI_MAX_NUMKEYS]; /* attnums of referencing cols */ + char fk_reftypes[RI_MAX_NUMKEYS]; /* reference semantics */ Oid pf_eq_oprs[RI_MAX_NUMKEYS]; /* equality operators (PK = * FK) */ Oid pp_eq_oprs[RI_MAX_NUMKEYS]; /* equality operators (PK = *************** static void ri_GenerateQual(StringInfo b *** 200,206 **** const char *sep, const char *leftop, Oid leftoptype, Oid opoid, ! const char *rightop, Oid rightoptype); static void ri_add_cast_to(StringInfo buf, Oid typid); static void ri_GenerateQualCollation(StringInfo buf, Oid collation); static int ri_NullCheck(HeapTuple tup, --- 202,209 ---- const char *sep, const char *leftop, Oid leftoptype, Oid opoid, ! const char *rightop, Oid rightoptype, ! char fkreftype); static void ri_add_cast_to(StringInfo buf, Oid typid); static void ri_GenerateQualCollation(StringInfo buf, Oid collation); static int ri_NullCheck(HeapTuple tup, *************** RI_FKey_check(TriggerData *trigdata) *** 392,397 **** --- 395,401 ---- if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL) { StringInfoData querybuf; + StringInfoData countbuf; char pkrelname[MAX_QUOTED_REL_NAME_LEN]; char attname[MAX_QUOTED_NAME_LEN]; char paramname[16]; *************** RI_FKey_check(TriggerData *trigdata) *** 403,414 **** --- 407,428 ---- * SELECT 1 FROM ONLY WHERE pkatt1 = $1 [AND ...] FOR SHARE * The type id's for the $ parameters are those of the * corresponding FK attributes. + * + * In case of an array ELEMENT foreign key, the previous query is used + * to count the number of matching rows and see if every combination + * is actually referenced. + * The wrapping query is + * SELECT 1 WHERE + * (SELECT count(DISTINCT y) FROM unnest($1) y) + * = (SELECT count(*) FROM () z) * ---------- */ initStringInfo(&querybuf); quoteRelationName(pkrelname, pk_rel); appendStringInfo(&querybuf, "SELECT 1 FROM ONLY %s x", pkrelname); querysep = "WHERE"; + initStringInfo(&countbuf); + appendStringInfo(&countbuf, "SELECT 1 WHERE "); for (i = 0; i < riinfo->nkeys; i++) { Oid pk_type = RIAttType(pk_rel, riinfo->pk_attnums[i]); *************** RI_FKey_check(TriggerData *trigdata) *** 417,434 **** quoteOneName(attname, RIAttName(pk_rel, riinfo->pk_attnums[i])); sprintf(paramname, "$%d", i + 1); ri_GenerateQual(&querybuf, querysep, attname, pk_type, riinfo->pf_eq_oprs[i], ! paramname, fk_type); querysep = "AND"; queryoids[i] = fk_type; } appendStringInfo(&querybuf, " FOR SHARE OF x"); ! /* Prepare and save the plan */ ! qplan = ri_PlanCheck(querybuf.data, riinfo->nkeys, queryoids, ! &qkey, fk_rel, pk_rel, true); } /* --- 431,471 ---- quoteOneName(attname, RIAttName(pk_rel, riinfo->pk_attnums[i])); sprintf(paramname, "$%d", i + 1); + + /* + * In case of an array ELEMENT foreign key, we check that each + * distinct non-null value in the array is present in the PK + * table. + */ + if (riinfo->fk_reftypes[i] == FKCONSTR_REF_EACH_ELEMENT) + appendStringInfo(&countbuf, + "(SELECT pg_catalog.count(DISTINCT y) FROM pg_catalog.unnest(%s) y)", + paramname); + ri_GenerateQual(&querybuf, querysep, attname, pk_type, riinfo->pf_eq_oprs[i], ! paramname, fk_type, ! riinfo->fk_reftypes[i]); querysep = "AND"; queryoids[i] = fk_type; } appendStringInfo(&querybuf, " FOR SHARE OF x"); ! if (riinfo->has_array) ! { ! appendStringInfo(&countbuf, ! " OPERATOR(pg_catalog.=) (SELECT pg_catalog.count(*) FROM (%s) z)", ! querybuf.data); ! ! /* Prepare and save the plan for array ELEMENT foreign keys */ ! qplan = ri_PlanCheck(countbuf.data, riinfo->nkeys, queryoids, ! &qkey, fk_rel, pk_rel, true); ! } ! else ! /* Prepare and save the plan */ ! qplan = ri_PlanCheck(querybuf.data, riinfo->nkeys, queryoids, ! &qkey, fk_rel, pk_rel, true); } /* *************** ri_Check_Pk_Match(Relation pk_rel, Relat *** 554,560 **** ri_GenerateQual(&querybuf, querysep, attname, pk_type, riinfo->pp_eq_oprs[i], ! paramname, pk_type); querysep = "AND"; queryoids[i] = pk_type; } --- 591,598 ---- ri_GenerateQual(&querybuf, querysep, attname, pk_type, riinfo->pp_eq_oprs[i], ! paramname, pk_type, ! FKCONSTR_REF_PLAIN); querysep = "AND"; queryoids[i] = pk_type; } *************** ri_restrict_del(TriggerData *trigdata, b *** 745,751 **** ri_GenerateQual(&querybuf, querysep, paramname, pk_type, riinfo->pf_eq_oprs[i], ! attname, fk_type); querysep = "AND"; queryoids[i] = pk_type; } --- 783,790 ---- ri_GenerateQual(&querybuf, querysep, paramname, pk_type, riinfo->pf_eq_oprs[i], ! attname, fk_type, ! riinfo->fk_reftypes[i]); querysep = "AND"; queryoids[i] = pk_type; } *************** ri_restrict_upd(TriggerData *trigdata, b *** 968,974 **** ri_GenerateQual(&querybuf, querysep, paramname, pk_type, riinfo->pf_eq_oprs[i], ! attname, fk_type); querysep = "AND"; queryoids[i] = pk_type; } --- 1007,1014 ---- ri_GenerateQual(&querybuf, querysep, paramname, pk_type, riinfo->pf_eq_oprs[i], ! attname, fk_type, ! riinfo->fk_reftypes[i]); querysep = "AND"; queryoids[i] = pk_type; } *************** RI_FKey_cascade_del(PG_FUNCTION_ARGS) *** 1124,1130 **** ri_GenerateQual(&querybuf, querysep, paramname, pk_type, riinfo->pf_eq_oprs[i], ! attname, fk_type); querysep = "AND"; queryoids[i] = pk_type; } --- 1164,1171 ---- ri_GenerateQual(&querybuf, querysep, paramname, pk_type, riinfo->pf_eq_oprs[i], ! attname, fk_type, ! riinfo->fk_reftypes[i]); querysep = "AND"; queryoids[i] = pk_type; } *************** RI_FKey_cascade_upd(PG_FUNCTION_ARGS) *** 1303,1309 **** ri_GenerateQual(&qualbuf, qualsep, paramname, pk_type, riinfo->pf_eq_oprs[i], ! attname, fk_type); querysep = ","; qualsep = "AND"; queryoids[i] = pk_type; --- 1344,1351 ---- ri_GenerateQual(&qualbuf, qualsep, paramname, pk_type, riinfo->pf_eq_oprs[i], ! attname, fk_type, ! riinfo->fk_reftypes[i]); querysep = ","; qualsep = "AND"; queryoids[i] = pk_type; *************** RI_FKey_setnull_del(PG_FUNCTION_ARGS) *** 1469,1475 **** ri_GenerateQual(&qualbuf, qualsep, paramname, pk_type, riinfo->pf_eq_oprs[i], ! attname, fk_type); querysep = ","; qualsep = "AND"; queryoids[i] = pk_type; --- 1511,1518 ---- ri_GenerateQual(&qualbuf, qualsep, paramname, pk_type, riinfo->pf_eq_oprs[i], ! attname, fk_type, ! riinfo->fk_reftypes[i]); querysep = ","; qualsep = "AND"; queryoids[i] = pk_type; *************** RI_FKey_setnull_upd(PG_FUNCTION_ARGS) *** 1645,1651 **** ri_GenerateQual(&qualbuf, qualsep, paramname, pk_type, riinfo->pf_eq_oprs[i], ! attname, fk_type); querysep = ","; qualsep = "AND"; queryoids[i] = pk_type; --- 1688,1695 ---- ri_GenerateQual(&qualbuf, qualsep, paramname, pk_type, riinfo->pf_eq_oprs[i], ! attname, fk_type, ! riinfo->fk_reftypes[i]); querysep = ","; qualsep = "AND"; queryoids[i] = pk_type; *************** RI_FKey_setdefault_del(PG_FUNCTION_ARGS) *** 1811,1817 **** ri_GenerateQual(&qualbuf, qualsep, paramname, pk_type, riinfo->pf_eq_oprs[i], ! attname, fk_type); querysep = ","; qualsep = "AND"; queryoids[i] = pk_type; --- 1855,1862 ---- ri_GenerateQual(&qualbuf, qualsep, paramname, pk_type, riinfo->pf_eq_oprs[i], ! attname, fk_type, ! riinfo->fk_reftypes[i]); querysep = ","; qualsep = "AND"; queryoids[i] = pk_type; *************** RI_FKey_setdefault_upd(PG_FUNCTION_ARGS) *** 2002,2008 **** ri_GenerateQual(&qualbuf, qualsep, paramname, pk_type, riinfo->pf_eq_oprs[i], ! attname, fk_type); querysep = ","; qualsep = "AND"; queryoids[i] = pk_type; --- 2047,2054 ---- ri_GenerateQual(&qualbuf, qualsep, paramname, pk_type, riinfo->pf_eq_oprs[i], ! attname, fk_type, ! riinfo->fk_reftypes[i]); querysep = ","; qualsep = "AND"; queryoids[i] = pk_type; *************** RI_Initial_Check(Trigger *trigger, Relat *** 2307,2312 **** --- 2353,2366 ---- * For MATCH FULL: * (fk.keycol1 IS NOT NULL [OR ...]) * + * In case of an array ELEMENT column, relname is replaced with the + * following subquery: + * + * SELECT unnest("keycol1") k1, "keycol1" ak1 [, ...] + * FROM ONLY "public"."fk" + * + * where all the columns are renamed in order to prevent name collisions. + * * We attach COLLATE clauses to the operators when comparing columns * that have different collations. *---------- *************** RI_Initial_Check(Trigger *trigger, Relat *** 2318,2332 **** { quoteOneName(fkattname, RIAttName(fk_rel, riinfo->fk_attnums[i])); ! appendStringInfo(&querybuf, "%sfk.%s", sep, fkattname); sep = ", "; } quoteRelationName(pkrelname, pk_rel); quoteRelationName(fkrelname, fk_rel); ! appendStringInfo(&querybuf, ! " FROM ONLY %s fk LEFT OUTER JOIN ONLY %s pk ON", ! fkrelname, pkrelname); strcpy(pkattname, "pk."); strcpy(fkattname, "fk."); --- 2372,2417 ---- { quoteOneName(fkattname, RIAttName(fk_rel, riinfo->fk_attnums[i])); ! if (riinfo->has_array) ! if (riinfo->fk_reftypes[i] == FKCONSTR_REF_EACH_ELEMENT) ! appendStringInfo(&querybuf, "%sfk.ak%d %s", sep, i + 1, ! fkattname); ! else ! appendStringInfo(&querybuf, "%sfk.k%d %s", sep, i + 1, ! fkattname); ! else ! appendStringInfo(&querybuf, "%sfk.%s", sep, fkattname); sep = ", "; } quoteRelationName(pkrelname, pk_rel); quoteRelationName(fkrelname, fk_rel); ! ! if (riinfo->has_array) ! { ! sep = ""; ! appendStringInfo(&querybuf, ! " FROM (SELECT "); ! for (i = 0; i < riinfo->nkeys; i++) ! { ! quoteOneName(fkattname, ! RIAttName(fk_rel, riinfo->fk_attnums[i])); ! if (riinfo->fk_reftypes[i] == FKCONSTR_REF_EACH_ELEMENT) ! appendStringInfo(&querybuf, "%spg_catalog.unnest(%s) k%d, %s ak%d", ! sep, fkattname, i + 1, fkattname, i + 1); ! else ! appendStringInfo(&querybuf, "%s%s k%d", sep, fkattname, ! i + 1); ! sep = ", "; ! } ! appendStringInfo(&querybuf, ! " FROM ONLY %s) fk LEFT OUTER JOIN ONLY %s pk ON", ! fkrelname, pkrelname); ! } ! else ! appendStringInfo(&querybuf, ! " FROM ONLY %s fk LEFT OUTER JOIN ONLY %s pk ON", ! fkrelname, pkrelname); strcpy(pkattname, "pk."); strcpy(fkattname, "fk."); *************** RI_Initial_Check(Trigger *trigger, Relat *** 2340,2351 **** quoteOneName(pkattname + 3, RIAttName(pk_rel, riinfo->pk_attnums[i])); ! quoteOneName(fkattname + 3, ! RIAttName(fk_rel, riinfo->fk_attnums[i])); ri_GenerateQual(&querybuf, sep, pkattname, pk_type, riinfo->pf_eq_oprs[i], ! fkattname, fk_type); if (pk_coll != fk_coll) ri_GenerateQualCollation(&querybuf, pk_coll); sep = "AND"; --- 2425,2440 ---- quoteOneName(pkattname + 3, RIAttName(pk_rel, riinfo->pk_attnums[i])); ! if (riinfo->has_array) ! sprintf(fkattname + 3, "k%d", i + 1); ! else ! quoteOneName(fkattname + 3, ! RIAttName(fk_rel, riinfo->fk_attnums[i])); ri_GenerateQual(&querybuf, sep, pkattname, pk_type, riinfo->pf_eq_oprs[i], ! fkattname, fk_type, ! FKCONSTR_REF_PLAIN); if (pk_coll != fk_coll) ri_GenerateQualCollation(&querybuf, pk_coll); sep = "AND"; *************** RI_Initial_Check(Trigger *trigger, Relat *** 2361,2367 **** sep = ""; for (i = 0; i < riinfo->nkeys; i++) { ! quoteOneName(fkattname, RIAttName(fk_rel, riinfo->fk_attnums[i])); appendStringInfo(&querybuf, "%sfk.%s IS NOT NULL", sep, fkattname); --- 2450,2459 ---- sep = ""; for (i = 0; i < riinfo->nkeys; i++) { ! if (riinfo->has_array) ! sprintf(fkattname, "k%d", i + 1); ! else ! quoteOneName(fkattname, RIAttName(fk_rel, riinfo->fk_attnums[i])); appendStringInfo(&querybuf, "%sfk.%s IS NOT NULL", sep, fkattname); *************** quoteRelationName(char *buffer, Relation *** 2535,2559 **** /* * ri_GenerateQual --- generate a WHERE clause equating two variables * ! * The idea is to append " sep leftop op rightop" to buf. The complexity ! * comes from needing to be sure that the parser will select the desired ! * operator. We always name the operator using OPERATOR(schema.op) syntax ! * (readability isn't a big priority here), so as to avoid search-path ! * uncertainties. We have to emit casts too, if either input isn't already ! * the input type of the operator; else we are at the mercy of the parser's ! * heuristics for ambiguous-operator resolution. */ static void ri_GenerateQual(StringInfo buf, const char *sep, const char *leftop, Oid leftoptype, Oid opoid, ! const char *rightop, Oid rightoptype) { HeapTuple opertup; Form_pg_operator operform; char *oprname; char *nspname; opertup = SearchSysCache1(OPEROID, ObjectIdGetDatum(opoid)); if (!HeapTupleIsValid(opertup)) --- 2627,2655 ---- /* * ri_GenerateQual --- generate a WHERE clause equating two variables * ! * The idea is to append " sep leftop op rightop" to buf, or if fkreftype is ! * FKCONSTR_REF_EACH_ELEMENT, append " sep leftop op ANY(rightop)" to buf. ! * ! * The complexity comes from needing to be sure that the parser will select ! * the desired operator. We always name the operator using ! * OPERATOR(schema.op) syntax (readability isn't a big priority here), so as ! * to avoid search-path uncertainties. We have to emit casts too, if either ! * input isn't already the input type of the operator; else we are at the ! * mercy of the parser's heuristics for ambiguous-operator resolution. */ static void ri_GenerateQual(StringInfo buf, const char *sep, const char *leftop, Oid leftoptype, Oid opoid, ! const char *rightop, Oid rightoptype, ! char fkreftype) { HeapTuple opertup; Form_pg_operator operform; char *oprname; char *nspname; + Oid oprright; opertup = SearchSysCache1(OPEROID, ObjectIdGetDatum(opoid)); if (!HeapTupleIsValid(opertup)) *************** ri_GenerateQual(StringInfo buf, *** 2564,2577 **** nspname = get_namespace_name(operform->oprnamespace); appendStringInfo(buf, " %s %s", sep, leftop); if (leftoptype != operform->oprleft) ri_add_cast_to(buf, operform->oprleft); ! appendStringInfo(buf, " OPERATOR(%s.", quote_identifier(nspname)); ! appendStringInfoString(buf, oprname); ! appendStringInfo(buf, ") %s", rightop); ! if (rightoptype != operform->oprright) ! ri_add_cast_to(buf, operform->oprright); ReleaseSysCache(opertup); } --- 2660,2691 ---- nspname = get_namespace_name(operform->oprnamespace); + if (fkreftype == FKCONSTR_REF_EACH_ELEMENT) + { + oprright = get_array_type(operform->oprright); + if (!OidIsValid(oprright)) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("could not find array type for data type %s", + format_type_be(operform->oprright)))); + } + else + oprright = operform->oprright; + appendStringInfo(buf, " %s %s", sep, leftop); if (leftoptype != operform->oprleft) ri_add_cast_to(buf, operform->oprleft); ! ! appendStringInfo(buf, " OPERATOR(%s.%s) ", ! quote_identifier(nspname), oprname); ! ! if (fkreftype == FKCONSTR_REF_EACH_ELEMENT) ! appendStringInfoString(buf, "ANY ("); ! appendStringInfoString(buf, rightop); ! if (rightoptype != oprright) ! ri_add_cast_to(buf, oprright); ! if (fkreftype == FKCONSTR_REF_EACH_ELEMENT) ! appendStringInfoChar(buf, ')'); ReleaseSysCache(opertup); } *************** ri_LoadConstraintInfo(Oid constraintOid) *** 2779,2784 **** --- 2893,2899 ---- bool isNull; ArrayType *arr; int numkeys; + int i; /* * On the first call initialize the hashtable *************** ri_LoadConstraintInfo(Oid constraintOid) *** 2857,2862 **** --- 2972,2991 ---- pfree(arr); /* free de-toasted copy, if any */ adatum = SysCacheGetAttr(CONSTROID, tup, + Anum_pg_constraint_confreftype, &isNull); + if (isNull) + elog(ERROR, "null confreftype for constraint %u", constraintOid); + arr = DatumGetArrayTypeP(adatum); /* ensure not toasted */ + if (ARR_NDIM(arr) != 1 || + ARR_DIMS(arr)[0] != numkeys || + ARR_HASNULL(arr) || + ARR_ELEMTYPE(arr) != CHAROID) + elog(ERROR, "confreftype is not a 1-D char array"); + memcpy(riinfo->fk_reftypes, ARR_DATA_PTR(arr), numkeys * sizeof(char)); + if ((Pointer) arr != DatumGetPointer(adatum)) + pfree(arr); /* free de-toasted copy, if any */ + + adatum = SysCacheGetAttr(CONSTROID, tup, Anum_pg_constraint_conpfeqop, &isNull); if (isNull) elog(ERROR, "null conpfeqop for constraint %u", constraintOid); *************** ri_LoadConstraintInfo(Oid constraintOid) *** 2899,2904 **** --- 3028,3051 ---- if ((Pointer) arr != DatumGetPointer(adatum)) pfree(arr); /* free de-toasted copy, if any */ + /* + * Fix up some stuff for array foreign keys. We need a has_array flag + * indicating whether there's an array foreign key, and we want to set + * ff_eq_oprs[i] to array_eq() for array columns, because that's what + * makes sense for ri_KeysEqual, and we have no other use for ff_eq_oprs + * in this module. (If we did, substituting the array comparator at the + * call point in ri_KeysEqual might be more appropriate.) + */ + riinfo->has_array = false; + for (i = 0; i < numkeys; i++) + { + if (riinfo->fk_reftypes[i] != FKCONSTR_REF_PLAIN) + { + riinfo->has_array = true; + riinfo->ff_eq_oprs[i] = ARRAY_EQ_OP; + } + } + ReleaseSysCache(tup); riinfo->valid = true; diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index b7aff1189b26909a7bc6b0db6c40685ba419f6f4..db769e3ad113586dc8075c6ac080e6e8a2b7d935 100644 *** a/src/backend/utils/adt/ruleutils.c --- b/src/backend/utils/adt/ruleutils.c *************** static char *pg_get_viewdef_worker(Oid v *** 163,168 **** --- 163,171 ---- static char *pg_get_triggerdef_worker(Oid trigid, bool pretty); static void decompile_column_index_array(Datum column_index_array, Oid relId, StringInfo buf); + static void decompile_fk_column_index_array(Datum column_index_array, + Datum fk_reftype_array, + Oid relId, StringInfo buf); static char *pg_get_ruledef_worker(Oid ruleoid, int prettyFlags); static char *pg_get_indexdef_worker(Oid indexrelid, int colno, const Oid *excludeOps, *************** pg_get_constraintdef_worker(Oid constrai *** 1155,1161 **** { case CONSTRAINT_FOREIGN: { ! Datum val; bool isnull; const char *string; --- 1158,1165 ---- { case CONSTRAINT_FOREIGN: { ! Datum colindexes; ! Datum reftypes; bool isnull; const char *string; *************** pg_get_constraintdef_worker(Oid constrai *** 1163,1175 **** appendStringInfo(&buf, "FOREIGN KEY ("); /* Fetch and build referencing-column list */ ! val = SysCacheGetAttr(CONSTROID, tup, ! Anum_pg_constraint_conkey, &isnull); if (isnull) elog(ERROR, "null conkey for constraint %u", constraintId); ! decompile_column_index_array(val, conForm->conrelid, &buf); /* add foreign relation name */ appendStringInfo(&buf, ") REFERENCES %s(", --- 1167,1187 ---- appendStringInfo(&buf, "FOREIGN KEY ("); /* Fetch and build referencing-column list */ ! colindexes = SysCacheGetAttr(CONSTROID, tup, ! Anum_pg_constraint_conkey, ! &isnull); if (isnull) elog(ERROR, "null conkey for constraint %u", constraintId); + reftypes = SysCacheGetAttr(CONSTROID, tup, + Anum_pg_constraint_confreftype, + &isnull); + if (isnull) + elog(ERROR, "null confreftype for constraint %u", + constraintId); ! decompile_fk_column_index_array(colindexes, reftypes, ! conForm->conrelid, &buf); /* add foreign relation name */ appendStringInfo(&buf, ") REFERENCES %s(", *************** pg_get_constraintdef_worker(Oid constrai *** 1177,1189 **** NIL)); /* Fetch and build referenced-column list */ ! val = SysCacheGetAttr(CONSTROID, tup, ! Anum_pg_constraint_confkey, &isnull); if (isnull) elog(ERROR, "null confkey for constraint %u", constraintId); ! decompile_column_index_array(val, conForm->confrelid, &buf); appendStringInfo(&buf, ")"); --- 1189,1203 ---- NIL)); /* Fetch and build referenced-column list */ ! colindexes = SysCacheGetAttr(CONSTROID, tup, ! Anum_pg_constraint_confkey, ! &isnull); if (isnull) elog(ERROR, "null confkey for constraint %u", constraintId); ! decompile_column_index_array(colindexes, ! conForm->confrelid, &buf); appendStringInfo(&buf, ")"); *************** decompile_column_index_array(Datum colum *** 1456,1461 **** --- 1470,1536 ---- } } + /* + * Convert an int16[] Datum and a char[] Datum into a comma-separated + * list of column names for the indicated relation, prefixed by appropriate + * keywords depending on the foreign key reference semantics indicated by + * the char[] entries. Append the text to buf. + */ + static void + decompile_fk_column_index_array(Datum column_index_array, + Datum fk_reftype_array, + Oid relId, StringInfo buf) + { + Datum *keys; + int nKeys; + Datum *reftypes; + int nReftypes; + int j; + + /* Extract data from array of int16 */ + deconstruct_array(DatumGetArrayTypeP(column_index_array), + INT2OID, sizeof(int16), true, 's', + &keys, NULL, &nKeys); + + /* Extract data from array of char */ + deconstruct_array(DatumGetArrayTypeP(fk_reftype_array), + CHAROID, sizeof(char), true, 'c', + &reftypes, NULL, &nReftypes); + + if (nKeys != nReftypes) + elog(ERROR, "wrong confreftype cardinality"); + + for (j = 0; j < nKeys; j++) + { + char *colName; + const char *prefix; + + colName = get_relid_attribute_name(relId, DatumGetInt16(keys[j])); + + switch (DatumGetChar(reftypes[j])) + { + case FKCONSTR_REF_PLAIN: + prefix = ""; + break; + case FKCONSTR_REF_EACH_ELEMENT: + prefix = "EACH ELEMENT OF "; + break; + default: + elog(ERROR, "invalid fk_reftype: %d", + (int) DatumGetChar(reftypes[j])); + prefix = NULL; /* keep compiler quiet */ + break; + } + + if (j == 0) + appendStringInfo(buf, "%s%s", prefix, + quote_identifier(colName)); + else + appendStringInfo(buf, ", %s%s", prefix, + quote_identifier(colName)); + } + } + /* ---------- * get_expr - Decompile an expression tree diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index 5254a57d8a6816b8d1a91b6effab7fc7c2398230..906d1dc4baa71bdb981a8723362cb14ffe976cbf 100644 *** a/src/include/catalog/catversion.h --- b/src/include/catalog/catversion.h *************** *** 53,58 **** */ /* yyyymmddN */ ! #define CATALOG_VERSION_NO 201210071 #endif --- 53,58 ---- */ /* yyyymmddN */ ! #define CATALOG_VERSION_NO 201210221 #endif diff --git a/src/include/catalog/pg_constraint.h b/src/include/catalog/pg_constraint.h index 9a1c8906843778c075a0c07de16bc592d4e58889..92c5154705cfc7cfdb071081305dd6b34d62c192 100644 *** a/src/include/catalog/pg_constraint.h --- b/src/include/catalog/pg_constraint.h *************** CATALOG(pg_constraint,2606) *** 105,112 **** --- 105,120 ---- int16 confkey[1]; /* + * If a foreign key, the reference semantics for each column + */ + char confreftype[1]; + + /* * If a foreign key, the OIDs of the PK = FK equality operators for each * column of the constraint + * + * Note: for array foreign keys, all these operators are for the array's + * element type. */ Oid conpfeqop[1]; *************** typedef FormData_pg_constraint *Form_pg_ *** 151,157 **** * compiler constants for pg_constraint * ---------------- */ ! #define Natts_pg_constraint 24 #define Anum_pg_constraint_conname 1 #define Anum_pg_constraint_connamespace 2 #define Anum_pg_constraint_contype 3 --- 159,165 ---- * compiler constants for pg_constraint * ---------------- */ ! #define Natts_pg_constraint 25 #define Anum_pg_constraint_conname 1 #define Anum_pg_constraint_connamespace 2 #define Anum_pg_constraint_contype 3 *************** typedef FormData_pg_constraint *Form_pg_ *** 170,181 **** #define Anum_pg_constraint_connoinherit 16 #define Anum_pg_constraint_conkey 17 #define Anum_pg_constraint_confkey 18 ! #define Anum_pg_constraint_conpfeqop 19 ! #define Anum_pg_constraint_conppeqop 20 ! #define Anum_pg_constraint_conffeqop 21 ! #define Anum_pg_constraint_conexclop 22 ! #define Anum_pg_constraint_conbin 23 ! #define Anum_pg_constraint_consrc 24 /* Valid values for contype */ --- 178,190 ---- #define Anum_pg_constraint_connoinherit 16 #define Anum_pg_constraint_conkey 17 #define Anum_pg_constraint_confkey 18 ! #define Anum_pg_constraint_confreftype 19 ! #define Anum_pg_constraint_conpfeqop 20 ! #define Anum_pg_constraint_conppeqop 21 ! #define Anum_pg_constraint_conffeqop 22 ! #define Anum_pg_constraint_conexclop 23 ! #define Anum_pg_constraint_conbin 24 ! #define Anum_pg_constraint_consrc 25 /* Valid values for contype */ *************** typedef FormData_pg_constraint *Form_pg_ *** 189,195 **** /* * Valid values for confupdtype and confdeltype are the FKCONSTR_ACTION_xxx * constants defined in parsenodes.h. Valid values for confmatchtype are ! * the FKCONSTR_MATCH_xxx constants defined in parsenodes.h. */ /* --- 198,206 ---- /* * Valid values for confupdtype and confdeltype are the FKCONSTR_ACTION_xxx * constants defined in parsenodes.h. Valid values for confmatchtype are ! * the FKCONSTR_MATCH_xxx constants defined in parsenodes.h. Valid values ! * for elements of confreftype[] are the FKCONSTR_REF_xxx constants defined ! * in parsenodes.h. */ /* *************** extern Oid CreateConstraintEntry(const c *** 218,223 **** --- 229,235 ---- Oid indexRelId, Oid foreignRelId, const int16 *foreignKey, + const char *foreignRefType, const Oid *pfEqOp, const Oid *ppEqOp, const Oid *ffEqOp, diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 09b15e7694cde1932b3640a8bb8f69e7606ea6b9..4ce3434f32785868ac4f9a56d07ff606fe397da5 100644 *** a/src/include/nodes/parsenodes.h --- b/src/include/nodes/parsenodes.h *************** typedef enum ConstrType /* types of co *** 1521,1526 **** --- 1521,1530 ---- #define FKCONSTR_MATCH_PARTIAL 'p' #define FKCONSTR_MATCH_SIMPLE 's' + /* Foreign key column reference semantics codes */ + #define FKCONSTR_REF_PLAIN 'p' + #define FKCONSTR_REF_EACH_ELEMENT 'e' + typedef struct Constraint { NodeTag type; *************** typedef struct Constraint *** 1555,1560 **** --- 1559,1565 ---- RangeVar *pktable; /* Primary key table */ List *fk_attrs; /* Attributes of foreign key */ List *pk_attrs; /* Corresponding attrs in PK table */ + List *fk_reftypes; /* Per-column reference semantics (int List) */ char fk_matchtype; /* FULL, PARTIAL, SIMPLE */ char fk_upd_action; /* ON UPDATE action */ char fk_del_action; /* ON DELETE action */ diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index af60dac08e5cd148d91763242019baeeafe62793..6e68637d9b09926d69354d81b49f34a4af75e5a3 100644 *** a/src/include/parser/kwlist.h --- b/src/include/parser/kwlist.h *************** PG_KEYWORD("domain", DOMAIN_P, UNRESERVE *** 134,139 **** --- 134,140 ---- PG_KEYWORD("double", DOUBLE_P, UNRESERVED_KEYWORD) PG_KEYWORD("drop", DROP, UNRESERVED_KEYWORD) PG_KEYWORD("each", EACH, UNRESERVED_KEYWORD) + PG_KEYWORD("element", ELEMENT, UNRESERVED_KEYWORD) PG_KEYWORD("else", ELSE, RESERVED_KEYWORD) PG_KEYWORD("enable", ENABLE_P, UNRESERVED_KEYWORD) PG_KEYWORD("encoding", ENCODING, UNRESERVED_KEYWORD) diff --git a/src/test/regress/expected/element_foreign_key.out b/src/test/regress/expected/element_foreign_key.out index ...b62f53e72966fb1283ca85bd191b2dc5ed0d5aad . *** a/src/test/regress/expected/element_foreign_key.out --- b/src/test/regress/expected/element_foreign_key.out *************** *** 0 **** --- 1,590 ---- + -- EACH-ELEMENT FK CONSTRAINTS + CREATE TABLE PKTABLEFORARRAY ( ptest1 int PRIMARY KEY, ptest2 text ); + -- Insert test data into PKTABLEFORARRAY + INSERT INTO PKTABLEFORARRAY VALUES (1, 'Test1'); + INSERT INTO PKTABLEFORARRAY VALUES (2, 'Test2'); + INSERT INTO PKTABLEFORARRAY VALUES (3, 'Test3'); + INSERT INTO PKTABLEFORARRAY VALUES (4, 'Test4'); + INSERT INTO PKTABLEFORARRAY VALUES (5, 'Test5'); + -- Check alter table + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], ftest2 int ); + ALTER TABLE FKTABLEFORARRAY ADD CONSTRAINT FKARRAY FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY; + DROP TABLE FKTABLEFORARRAY; + -- Check alter table with rows + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], ftest2 int ); + INSERT INTO FKTABLEFORARRAY VALUES ('{1}', 1); + ALTER TABLE FKTABLEFORARRAY ADD CONSTRAINT FKARRAY FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY; + DROP TABLE FKTABLEFORARRAY; + -- Check alter table with failing rows + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], ftest2 int ); + INSERT INTO FKTABLEFORARRAY VALUES ('{10,1}', 2); + ALTER TABLE FKTABLEFORARRAY ADD CONSTRAINT FKARRAY FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY; + ERROR: insert or update on table "fktableforarray" violates foreign key constraint "fkarray" + DETAIL: Key (ftest1)=({10,1}) is not present in table "pktableforarray". + DROP TABLE FKTABLEFORARRAY; + -- Check create table + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY, ftest2 int ); + CREATE TABLE FKTABLEFORARRAYMDIM ( ftest1 int[][], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY, ftest2 int ); + CREATE TABLE FKTABLEFORARRAYNOTNULL ( ftest1 int[] NOT NULL, FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY, ftest2 int ); + -- Insert successful rows into FK TABLE + INSERT INTO FKTABLEFORARRAY VALUES ('{1}', 3); + INSERT INTO FKTABLEFORARRAY VALUES ('{2}', 4); + INSERT INTO FKTABLEFORARRAY VALUES ('{1}', 5); + INSERT INTO FKTABLEFORARRAY VALUES ('{3}', 6); + INSERT INTO FKTABLEFORARRAY VALUES ('{1}', 7); + INSERT INTO FKTABLEFORARRAY VALUES ('{4,5}', 8); + INSERT INTO FKTABLEFORARRAY VALUES ('{4,4}', 9); + INSERT INTO FKTABLEFORARRAY VALUES (NULL, 10); + INSERT INTO FKTABLEFORARRAY VALUES ('{}', 11); + INSERT INTO FKTABLEFORARRAY VALUES ('{1,NULL}', 12); + INSERT INTO FKTABLEFORARRAY VALUES ('{NULL}', 13); + INSERT INTO FKTABLEFORARRAYMDIM VALUES ('{{4,5},{1,2},{1,3}}', 14); + INSERT INTO FKTABLEFORARRAYMDIM VALUES ('{{4,5},{NULL,2},{NULL,3}}', 15); + -- Insert failed rows into FK TABLE + INSERT INTO FKTABLEFORARRAY VALUES ('{6}', 16); + ERROR: insert or update on table "fktableforarray" violates foreign key constraint "fktableforarray_ftest1_fkey" + DETAIL: Key (ftest1)=({6}) is not present in table "pktableforarray". + INSERT INTO FKTABLEFORARRAY VALUES ('{4,6}', 17); + ERROR: insert or update on table "fktableforarray" violates foreign key constraint "fktableforarray_ftest1_fkey" + DETAIL: Key (ftest1)=({4,6}) is not present in table "pktableforarray". + INSERT INTO FKTABLEFORARRAY VALUES ('{6,NULL}', 18); + ERROR: insert or update on table "fktableforarray" violates foreign key constraint "fktableforarray_ftest1_fkey" + DETAIL: Key (ftest1)=({6,NULL}) is not present in table "pktableforarray". + INSERT INTO FKTABLEFORARRAY VALUES ('{6,NULL,4,NULL}', 19); + ERROR: insert or update on table "fktableforarray" violates foreign key constraint "fktableforarray_ftest1_fkey" + DETAIL: Key (ftest1)=({6,NULL,4,NULL}) is not present in table "pktableforarray". + INSERT INTO FKTABLEFORARRAYMDIM VALUES ('{{1,2},{6,NULL}}', 20); + ERROR: insert or update on table "fktableforarraymdim" violates foreign key constraint "fktableforarraymdim_ftest1_fkey" + DETAIL: Key (ftest1)=({{1,2},{6,NULL}}) is not present in table "pktableforarray". + INSERT INTO FKTABLEFORARRAYNOTNULL VALUES (NULL, 21); + ERROR: null value in column "ftest1" violates not-null constraint + DETAIL: Failing row contains (null, 21). + -- Check FKTABLE + SELECT * FROM FKTABLEFORARRAY; + ftest1 | ftest2 + ----------+-------- + {1} | 3 + {2} | 4 + {1} | 5 + {3} | 6 + {1} | 7 + {4,5} | 8 + {4,4} | 9 + | 10 + {} | 11 + {1,NULL} | 12 + {NULL} | 13 + (11 rows) + + -- Delete a row from PK TABLE (must fail due to ON DELETE NO ACTION) + DELETE FROM PKTABLEFORARRAY WHERE ptest1=1; + ERROR: update or delete on table "pktableforarray" violates foreign key constraint "fktableforarray_ftest1_fkey" on table "fktableforarray" + DETAIL: Key (ptest1)=(1) is still referenced from table "fktableforarray". + -- Check FKTABLE for removal of matched row + SELECT * FROM FKTABLEFORARRAY; + ftest1 | ftest2 + ----------+-------- + {1} | 3 + {2} | 4 + {1} | 5 + {3} | 6 + {1} | 7 + {4,5} | 8 + {4,4} | 9 + | 10 + {} | 11 + {1,NULL} | 12 + {NULL} | 13 + (11 rows) + + -- Update a row from PK TABLE (must fail due to ON UPDATE NO ACTION) + UPDATE PKTABLEFORARRAY SET ptest1=7 WHERE ptest1=1; + ERROR: update or delete on table "pktableforarray" violates foreign key constraint "fktableforarray_ftest1_fkey" on table "fktableforarray" + DETAIL: Key (ptest1)=(1) is still referenced from table "fktableforarray". + -- Check FKTABLE for update of matched row + SELECT * FROM FKTABLEFORARRAY; + ftest1 | ftest2 + ----------+-------- + {1} | 3 + {2} | 4 + {1} | 5 + {3} | 6 + {1} | 7 + {4,5} | 8 + {4,4} | 9 + | 10 + {} | 11 + {1,NULL} | 12 + {NULL} | 13 + (11 rows) + + -- Check UPDATE on FKTABLE + UPDATE FKTABLEFORARRAY SET ftest1=ARRAY[1] WHERE ftest2=4; + -- Check FKTABLE for update of matched row + SELECT * FROM FKTABLEFORARRAY; + ftest1 | ftest2 + ----------+-------- + {1} | 3 + {1} | 5 + {3} | 6 + {1} | 7 + {4,5} | 8 + {4,4} | 9 + | 10 + {} | 11 + {1,NULL} | 12 + {NULL} | 13 + {1} | 4 + (11 rows) + + DROP TABLE FKTABLEFORARRAY; + DROP TABLE FKTABLEFORARRAYNOTNULL; + DROP TABLE FKTABLEFORARRAYMDIM; + -- Allowed references with actions (NO ACTION, RESTRICT) + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE NO ACTION ON UPDATE NO ACTION, ftest2 int ); + DROP TABLE FKTABLEFORARRAY; + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE NO ACTION ON UPDATE RESTRICT, ftest2 int ); + DROP TABLE FKTABLEFORARRAY; + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE RESTRICT ON UPDATE NO ACTION, ftest2 int ); + DROP TABLE FKTABLEFORARRAY; + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE RESTRICT ON UPDATE RESTRICT, ftest2 int ); + DROP TABLE FKTABLEFORARRAY; + -- Not allowed references (SET NULL, SET DEFAULT, CASCADE) + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE NO ACTION ON UPDATE SET DEFAULT, ftest2 int ); + ERROR: array foreign keys support only NO ACTION and RESTRICT actions + DROP TABLE FKTABLEFORARRAY; + ERROR: table "fktableforarray" does not exist + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE NO ACTION ON UPDATE SET NULL, ftest2 int ); + ERROR: array foreign keys support only NO ACTION and RESTRICT actions + DROP TABLE FKTABLEFORARRAY; + ERROR: table "fktableforarray" does not exist + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE RESTRICT ON UPDATE SET DEFAULT, ftest2 int ); + ERROR: array foreign keys support only NO ACTION and RESTRICT actions + DROP TABLE FKTABLEFORARRAY; + ERROR: table "fktableforarray" does not exist + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE RESTRICT ON UPDATE SET NULL, ftest2 int ); + ERROR: array foreign keys support only NO ACTION and RESTRICT actions + DROP TABLE FKTABLEFORARRAY; + ERROR: table "fktableforarray" does not exist + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE CASCADE ON UPDATE NO ACTION, ftest2 int ); + ERROR: array foreign keys support only NO ACTION and RESTRICT actions + DROP TABLE FKTABLEFORARRAY; + ERROR: table "fktableforarray" does not exist + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE CASCADE ON UPDATE RESTRICT, ftest2 int ); + ERROR: array foreign keys support only NO ACTION and RESTRICT actions + DROP TABLE FKTABLEFORARRAY; + ERROR: table "fktableforarray" does not exist + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE CASCADE ON UPDATE SET DEFAULT, ftest2 int ); + ERROR: array foreign keys support only NO ACTION and RESTRICT actions + DROP TABLE FKTABLEFORARRAY; + ERROR: table "fktableforarray" does not exist + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE CASCADE ON UPDATE SET NULL, ftest2 int ); + ERROR: array foreign keys support only NO ACTION and RESTRICT actions + DROP TABLE FKTABLEFORARRAY; + ERROR: table "fktableforarray" does not exist + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE SET DEFAULT ON UPDATE NO ACTION, ftest2 int ); + ERROR: array foreign keys support only NO ACTION and RESTRICT actions + DROP TABLE FKTABLEFORARRAY; + ERROR: table "fktableforarray" does not exist + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE SET DEFAULT ON UPDATE RESTRICT, ftest2 int ); + ERROR: array foreign keys support only NO ACTION and RESTRICT actions + DROP TABLE FKTABLEFORARRAY; + ERROR: table "fktableforarray" does not exist + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE SET DEFAULT ON UPDATE SET DEFAULT, ftest2 int ); + ERROR: array foreign keys support only NO ACTION and RESTRICT actions + DROP TABLE FKTABLEFORARRAY; + ERROR: table "fktableforarray" does not exist + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE SET DEFAULT ON UPDATE SET NULL, ftest2 int ); + ERROR: array foreign keys support only NO ACTION and RESTRICT actions + DROP TABLE FKTABLEFORARRAY; + ERROR: table "fktableforarray" does not exist + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE SET NULL ON UPDATE NO ACTION, ftest2 int ); + ERROR: array foreign keys support only NO ACTION and RESTRICT actions + DROP TABLE FKTABLEFORARRAY; + ERROR: table "fktableforarray" does not exist + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE SET NULL ON UPDATE RESTRICT, ftest2 int ); + ERROR: array foreign keys support only NO ACTION and RESTRICT actions + DROP TABLE FKTABLEFORARRAY; + ERROR: table "fktableforarray" does not exist + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE SET NULL ON UPDATE SET DEFAULT, ftest2 int ); + ERROR: array foreign keys support only NO ACTION and RESTRICT actions + DROP TABLE FKTABLEFORARRAY; + ERROR: table "fktableforarray" does not exist + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE SET NULL ON UPDATE SET NULL, ftest2 int ); + ERROR: array foreign keys support only NO ACTION and RESTRICT actions + DROP TABLE FKTABLEFORARRAY; + ERROR: table "fktableforarray" does not exist + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE NO ACTION ON UPDATE CASCADE, ftest2 int ); + ERROR: array foreign keys support only NO ACTION and RESTRICT actions + DROP TABLE FKTABLEFORARRAY; + ERROR: table "fktableforarray" does not exist + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE RESTRICT ON UPDATE CASCADE, ftest2 int ); + ERROR: array foreign keys support only NO ACTION and RESTRICT actions + DROP TABLE FKTABLEFORARRAY; + ERROR: table "fktableforarray" does not exist + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE CASCADE ON UPDATE CASCADE, ftest2 int ); + ERROR: array foreign keys support only NO ACTION and RESTRICT actions + DROP TABLE FKTABLEFORARRAY; + ERROR: table "fktableforarray" does not exist + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE SET DEFAULT ON UPDATE CASCADE, ftest2 int ); + ERROR: array foreign keys support only NO ACTION and RESTRICT actions + DROP TABLE FKTABLEFORARRAY; + ERROR: table "fktableforarray" does not exist + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE SET NULL ON UPDATE CASCADE, ftest2 int ); + ERROR: array foreign keys support only NO ACTION and RESTRICT actions + DROP TABLE FKTABLEFORARRAY; + ERROR: table "fktableforarray" does not exist + -- Cleanup + DROP TABLE PKTABLEFORARRAY; + -- Check reference on empty table + CREATE TABLE PKTABLEFORARRAY (ptest1 int PRIMARY KEY); + CREATE TABLE FKTABLEFORARRAY (ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY); + INSERT INTO FKTABLEFORARRAY VALUES ('{}'); + DROP TABLE FKTABLEFORARRAY; + DROP TABLE PKTABLEFORARRAY; + -- Repeat a similar test using CHAR(1) keys rather than INTEGER + CREATE TABLE PKTABLEFORARRAY ( ptest1 CHAR(1) PRIMARY KEY, ptest2 text ); + -- Populate the primary table + INSERT INTO PKTABLEFORARRAY VALUES ('A', 'Test A'); + INSERT INTO PKTABLEFORARRAY VALUES ('B', 'Test B'); + INSERT INTO PKTABLEFORARRAY VALUES ('C', 'Test C'); + -- Create the foreign table + CREATE TABLE FKTABLEFORARRAY ( ftest1 char(1)[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON UPDATE RESTRICT ON DELETE RESTRICT, ftest2 int ); + -- Insert valid rows into FK TABLE + INSERT INTO FKTABLEFORARRAY VALUES ('{"A"}', 1); + INSERT INTO FKTABLEFORARRAY VALUES ('{"B"}', 2); + INSERT INTO FKTABLEFORARRAY VALUES ('{"C"}', 3); + INSERT INTO FKTABLEFORARRAY VALUES ('{"A","B","C"}', 4); + -- Insert invalid rows into FK TABLE + INSERT INTO FKTABLEFORARRAY VALUES ('{"D"}', 5); + ERROR: insert or update on table "fktableforarray" violates foreign key constraint "fktableforarray_ftest1_fkey" + DETAIL: Key (ftest1)=({D}) is not present in table "pktableforarray". + INSERT INTO FKTABLEFORARRAY VALUES ('{"A","B","D"}', 6); + ERROR: insert or update on table "fktableforarray" violates foreign key constraint "fktableforarray_ftest1_fkey" + DETAIL: Key (ftest1)=({A,B,D}) is not present in table "pktableforarray". + -- Check FKTABLE + SELECT * FROM FKTABLEFORARRAY; + ftest1 | ftest2 + ---------+-------- + {A} | 1 + {B} | 2 + {C} | 3 + {A,B,C} | 4 + (4 rows) + + -- Delete a row from PK TABLE (must fail due to ON DELETE RESTRICT) + DELETE FROM PKTABLEFORARRAY WHERE ptest1='A'; + ERROR: update or delete on table "pktableforarray" violates foreign key constraint "fktableforarray_ftest1_fkey" on table "fktableforarray" + DETAIL: Key (ptest1)=(A) is still referenced from table "fktableforarray". + -- Check FKTABLE for removal of matched row + SELECT * FROM FKTABLEFORARRAY; + ftest1 | ftest2 + ---------+-------- + {A} | 1 + {B} | 2 + {C} | 3 + {A,B,C} | 4 + (4 rows) + + -- Update a row from PK TABLE (must fail due to ON UPDATE RESTRICT) + UPDATE PKTABLEFORARRAY SET ptest1='D' WHERE ptest1='B'; + ERROR: update or delete on table "pktableforarray" violates foreign key constraint "fktableforarray_ftest1_fkey" on table "fktableforarray" + DETAIL: Key (ptest1)=(B) is still referenced from table "fktableforarray". + -- Check FKTABLE for update of matched row + SELECT * FROM FKTABLEFORARRAY; + ftest1 | ftest2 + ---------+-------- + {A} | 1 + {B} | 2 + {C} | 3 + {A,B,C} | 4 + (4 rows) + + -- Cleanup + DROP TABLE FKTABLEFORARRAY; + DROP TABLE PKTABLEFORARRAY; + -- Repeat a similar test using INT4 keys coerced from INT2 + CREATE TABLE PKTABLEFORARRAY ( ptest1 int4 PRIMARY KEY, ptest2 text ); + -- Populate the primary table + INSERT INTO PKTABLEFORARRAY VALUES (1, 'Test 1'); + INSERT INTO PKTABLEFORARRAY VALUES (2, 'Test 2'); + INSERT INTO PKTABLEFORARRAY VALUES (3, 'Test 3'); + -- Create the foreign table + CREATE TABLE FKTABLEFORARRAY ( ftest1 int2[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY, ftest2 int ); + -- Insert valid rows into FK TABLE + INSERT INTO FKTABLEFORARRAY VALUES (ARRAY[1], 1); + INSERT INTO FKTABLEFORARRAY VALUES (ARRAY[2], 2); + INSERT INTO FKTABLEFORARRAY VALUES (ARRAY[3], 3); + INSERT INTO FKTABLEFORARRAY VALUES (ARRAY[1,2,3], 4); + -- Insert invalid rows into FK TABLE + INSERT INTO FKTABLEFORARRAY VALUES (ARRAY[4], 5); + ERROR: insert or update on table "fktableforarray" violates foreign key constraint "fktableforarray_ftest1_fkey" + DETAIL: Key (ftest1)=({4}) is not present in table "pktableforarray". + INSERT INTO FKTABLEFORARRAY VALUES (ARRAY[1,2,5], 6); + ERROR: insert or update on table "fktableforarray" violates foreign key constraint "fktableforarray_ftest1_fkey" + DETAIL: Key (ftest1)=({1,2,5}) is not present in table "pktableforarray". + -- Cleanup + DROP TABLE FKTABLEFORARRAY; + DROP TABLE PKTABLEFORARRAY; + -- Repeat a similar test using FLOAT8 keys coerced from INTEGER + CREATE TABLE PKTABLEFORARRAY ( ptest1 float8 PRIMARY KEY, ptest2 text ); + -- XXX this really ought to work, but currently we must disallow it + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY, ftest2 int ); + ERROR: foreign key constraint "fktableforarray_ftest1_fkey" cannot be implemented + DETAIL: Key column "ftest1" has element type integer which does not have a default btree operator class that's compatible with class "float8_ops". + -- Cleanup + DROP TABLE PKTABLEFORARRAY; + -- Composite primary keys + CREATE TABLE PKTABLEFORARRAY ( id1 CHAR(1), id2 CHAR(1), ptest2 text, PRIMARY KEY (id1, id2) ); + -- Populate the primary table + INSERT INTO PKTABLEFORARRAY VALUES ('A', 'A', 'Test A'); + INSERT INTO PKTABLEFORARRAY VALUES ('A', 'B', 'Test B'); + INSERT INTO PKTABLEFORARRAY VALUES ('B', 'C', 'Test B'); + -- Create the foreign table + CREATE TABLE FKTABLEFORARRAY ( fid1 CHAR(1), fid2 CHAR(1)[], ftest2 text, FOREIGN KEY (fid1, EACH ELEMENT OF fid2) REFERENCES PKTABLEFORARRAY); + -- Insert valid rows into FK TABLE + INSERT INTO FKTABLEFORARRAY VALUES ('A', ARRAY['A','B'], '1'); + INSERT INTO FKTABLEFORARRAY VALUES ('B', ARRAY['C'], '2'); + -- Insert invalid rows into FK TABLE + INSERT INTO FKTABLEFORARRAY VALUES ('A', ARRAY['A','B', 'C'], '3'); + ERROR: insert or update on table "fktableforarray" violates foreign key constraint "fktableforarray_fid1_fkey" + DETAIL: Key (fid1, fid2)=(A, {A,B,C}) is not present in table "pktableforarray". + INSERT INTO FKTABLEFORARRAY VALUES ('B', ARRAY['A'], '4'); + ERROR: insert or update on table "fktableforarray" violates foreign key constraint "fktableforarray_fid1_fkey" + DETAIL: Key (fid1, fid2)=(B, {A}) is not present in table "pktableforarray". + -- Cleanup + DROP TABLE FKTABLEFORARRAY; + DROP TABLE PKTABLEFORARRAY; + -- Test ELEMENT foreign keys with composite type + CREATE TYPE INVOICEID AS (year_part INTEGER, progressive_part INTEGER); + CREATE TABLE PKTABLEFORARRAY ( id INVOICEID PRIMARY KEY, ptest2 text); + -- Populate the primary table + INSERT INTO PKTABLEFORARRAY VALUES (ROW(2010, 99), 'Last invoice for 2010'); + INSERT INTO PKTABLEFORARRAY VALUES (ROW(2011, 1), 'First invoice for 2011'); + INSERT INTO PKTABLEFORARRAY VALUES (ROW(2011, 2), 'Second invoice for 2011'); + -- Create the foreign table + CREATE TABLE FKTABLEFORARRAY ( id SERIAL PRIMARY KEY, invoice_ids INVOICEID[], FOREIGN KEY (EACH ELEMENT OF invoice_ids) REFERENCES PKTABLEFORARRAY, ftest2 TEXT ); + -- Insert valid rows into FK TABLE + INSERT INTO FKTABLEFORARRAY(invoice_ids, ftest2) VALUES (ARRAY['(2010,99)']::INVOICEID[], 'Product A'); + INSERT INTO FKTABLEFORARRAY(invoice_ids, ftest2) VALUES (ARRAY['(2011,1)','(2011,2)']::INVOICEID[], 'Product B'); + INSERT INTO FKTABLEFORARRAY(invoice_ids, ftest2) VALUES (ARRAY['(2011,2)']::INVOICEID[], 'Product C'); + -- Insert invalid rows into FK TABLE + INSERT INTO FKTABLEFORARRAY(invoice_ids, ftest2) VALUES (ARRAY['(2011,99)']::INVOICEID[], 'Product A'); + ERROR: insert or update on table "fktableforarray" violates foreign key constraint "fktableforarray_invoice_ids_fkey" + DETAIL: Key (invoice_ids)=({"(2011,99)"}) is not present in table "pktableforarray". + INSERT INTO FKTABLEFORARRAY(invoice_ids, ftest2) VALUES (ARRAY['(2011,1)','(2010,1)']::INVOICEID[], 'Product B'); + ERROR: insert or update on table "fktableforarray" violates foreign key constraint "fktableforarray_invoice_ids_fkey" + DETAIL: Key (invoice_ids)=({"(2011,1)","(2010,1)"}) is not present in table "pktableforarray". + -- Check FKTABLE + SELECT * FROM FKTABLEFORARRAY; + id | invoice_ids | ftest2 + ----+-------------------------+----------- + 1 | {"(2010,99)"} | Product A + 2 | {"(2011,1)","(2011,2)"} | Product B + 3 | {"(2011,2)"} | Product C + (3 rows) + + -- Delete a row from PK TABLE + DELETE FROM PKTABLEFORARRAY WHERE id=ROW(2010,99); + ERROR: update or delete on table "pktableforarray" violates foreign key constraint "fktableforarray_invoice_ids_fkey" on table "fktableforarray" + DETAIL: Key (id)=((2010,99)) is still referenced from table "fktableforarray". + -- Check FKTABLE for removal of matched row + SELECT * FROM FKTABLEFORARRAY; + id | invoice_ids | ftest2 + ----+-------------------------+----------- + 1 | {"(2010,99)"} | Product A + 2 | {"(2011,1)","(2011,2)"} | Product B + 3 | {"(2011,2)"} | Product C + (3 rows) + + -- Update a row from PK TABLE + UPDATE PKTABLEFORARRAY SET id=ROW(2011,99) WHERE id=ROW(2011,1); + ERROR: update or delete on table "pktableforarray" violates foreign key constraint "fktableforarray_invoice_ids_fkey" on table "fktableforarray" + DETAIL: Key (id)=((2011,1)) is still referenced from table "fktableforarray". + -- Check FKTABLE for update of matched row + SELECT * FROM FKTABLEFORARRAY; + id | invoice_ids | ftest2 + ----+-------------------------+----------- + 1 | {"(2010,99)"} | Product A + 2 | {"(2011,1)","(2011,2)"} | Product B + 3 | {"(2011,2)"} | Product C + (3 rows) + + -- Cleanup + DROP TABLE FKTABLEFORARRAY; + DROP TABLE PKTABLEFORARRAY; + DROP TYPE INVOICEID; + -- Check FK with cast + CREATE TABLE PKTABLEFORARRAY (c smallint PRIMARY KEY); + CREATE TABLE FKTABLEFORARRAY (c int[], FOREIGN KEY (EACH ELEMENT OF c) REFERENCES PKTABLEFORARRAY); + INSERT INTO PKTABLEFORARRAY VALUES (1), (2); + INSERT INTO FKTABLEFORARRAY VALUES ('{1,2}'); + UPDATE PKTABLEFORARRAY SET c = 3 WHERE c = 2; + ERROR: update or delete on table "pktableforarray" violates foreign key constraint "fktableforarray_c_fkey" on table "fktableforarray" + DETAIL: Key (c)=(2) is still referenced from table "fktableforarray". + DELETE FROM PKTABLEFORARRAY WHERE c = 1; + ERROR: update or delete on table "pktableforarray" violates foreign key constraint "fktableforarray_c_fkey" on table "fktableforarray" + DETAIL: Key (c)=(1) is still referenced from table "fktableforarray". + -- Cleanup + DROP TABLE FKTABLEFORARRAY; + DROP TABLE PKTABLEFORARRAY; + -- Check for an array column referencing another array column (NOT ELEMENT FOREIGN KEY) + -- Create primary table with an array primary key + CREATE TABLE PKTABLEFORARRAY ( id INT[] PRIMARY KEY, ptest2 text); + -- Create the foreign table + CREATE TABLE FKTABLEFORARRAY ( id SERIAL PRIMARY KEY, fids INT[] REFERENCES PKTABLEFORARRAY, ftest2 TEXT ); + -- Populate the primary table + INSERT INTO PKTABLEFORARRAY VALUES ('{1,1}', 'A'); + INSERT INTO PKTABLEFORARRAY VALUES ('{1,2}', 'B'); + -- Insert valid rows into FK TABLE + INSERT INTO FKTABLEFORARRAY (fids, ftest2) VALUES ('{1,1}', 'Product A'); + INSERT INTO FKTABLEFORARRAY (fids, ftest2) VALUES ('{1,2}', 'Product B'); + -- Insert invalid rows into FK TABLE + INSERT INTO FKTABLEFORARRAY (fids, ftest2) VALUES ('{0,1}', 'Product C'); + ERROR: insert or update on table "fktableforarray" violates foreign key constraint "fktableforarray_fids_fkey" + DETAIL: Key (fids)=({0,1}) is not present in table "pktableforarray". + INSERT INTO FKTABLEFORARRAY (fids, ftest2) VALUES ('{2,1}', 'Product D'); + ERROR: insert or update on table "fktableforarray" violates foreign key constraint "fktableforarray_fids_fkey" + DETAIL: Key (fids)=({2,1}) is not present in table "pktableforarray". + -- Cleanup + DROP TABLE FKTABLEFORARRAY; + DROP TABLE PKTABLEFORARRAY; + -- --------------------------------------- + -- Multi-column "ELEMENT" foreign key tests + -- --------------------------------------- + -- Create DIM1 table with two-column primary key + CREATE TABLE DIM1 (X INTEGER NOT NULL, Y INTEGER NOT NULL, PRIMARY KEY (X, Y)); + -- Populate DIM1 table pairs + INSERT INTO DIM1 SELECT x.t, x.t * y.t + FROM (SELECT generate_series(1, 10) AS t) x, + (SELECT generate_series(0, 10) AS t) y; + -- Test with TABLE declaration of an element foreign key constraint (NO ACTION) + CREATE TABLE F1 ( + x INTEGER PRIMARY KEY, y INTEGER[], + FOREIGN KEY (x, EACH ELEMENT OF y) REFERENCES DIM1(x, y) + ); + -- Insert facts + INSERT INTO F1 VALUES (1, '{0,1,2,3,4,5}'); -- OK + INSERT INTO F1 VALUES (2, '{0,2,4,6}'); -- OK + INSERT INTO F1 VALUES (3, '{0,3,6,9,0,3,6,9,0,0,0,0,9,9}'); -- OK (multiple occurrences) + INSERT INTO F1 VALUES (4, '{0,2,4}'); -- FAILS (2 is not present) + ERROR: insert or update on table "f1" violates foreign key constraint "f1_x_fkey" + DETAIL: Key (x, y)=(4, {0,2,4}) is not present in table "dim1". + INSERT INTO F1 VALUES (4, '{0,NULL,4}'); -- OK + INSERT INTO F1 VALUES (5, '{0,NULL,5}'); -- OK + -- Try updates + UPDATE F1 SET y = '{0,2,4,6}' WHERE x = 2; -- OK + UPDATE F1 SET y = '{0,2,3,4,6}' WHERE x = 2; -- FAILS + ERROR: insert or update on table "f1" violates foreign key constraint "f1_x_fkey" + DETAIL: Key (x, y)=(2, {0,2,3,4,6}) is not present in table "dim1". + UPDATE F1 SET x = 20, y = '{0,2,3,4,6}' WHERE x = 2; -- FAILS (20 does not exist) + ERROR: insert or update on table "f1" violates foreign key constraint "f1_x_fkey" + DETAIL: Key (x, y)=(20, {0,2,3,4,6}) is not present in table "dim1". + UPDATE F1 SET y = '{0,4,8}' WHERE x = 4; -- OK + UPDATE F1 SET y = '{0,5,NULL,10}' WHERE x = 5; -- OK + DROP TABLE F1; + -- Test with FOREIGN KEY after TABLE population + CREATE TABLE F1 ( + x INTEGER PRIMARY KEY, y INTEGER[] + ); + -- Insert facts + INSERT INTO F1 VALUES (1, '{0,1,2,3,4,5}'); -- OK + INSERT INTO F1 VALUES (2, '{0,2,4,6}'); -- OK + INSERT INTO F1 VALUES (3, '{0,3,6,9,0,3,6,9,0,0,0,0,9,9}'); -- OK (multiple occurrences) + INSERT INTO F1 VALUES (4, '{0,2,4}'); -- OK (2 is not present) + INSERT INTO F1 VALUES (5, '{0,NULL,5}'); -- OK + -- Add foreign key (FAILS) + ALTER TABLE F1 ADD FOREIGN KEY (x, EACH ELEMENT OF y) REFERENCES DIM1(x, y); + ERROR: insert or update on table "f1" violates foreign key constraint "f1_x_fkey" + DETAIL: Key (x, y)=(4, {0,2,4}) is not present in table "dim1". + DROP TABLE F1; + -- Test with TABLE declaration of a two-dim ELEMENT foreign key constraint (FAILS) + CREATE TABLE F1 ( + x INTEGER[] PRIMARY KEY, y INTEGER[], + FOREIGN KEY (EACH ELEMENT OF x, EACH ELEMENT OF y) REFERENCES DIM1(x, y) + ); + ERROR: foreign keys support only one array column + -- Test with two-dim ELEMENT foreign key after TABLE population + CREATE TABLE F1 ( + x INTEGER[] PRIMARY KEY, y INTEGER[] + ); + INSERT INTO F1 VALUES ('{1}', '{0,1,2,3,4,5}'); -- OK + INSERT INTO F1 VALUES ('{1,2}', '{0,2,4,6}'); -- OK + -- Add foreign key (FAILS) + ALTER TABLE F1 ADD FOREIGN KEY (EACH ELEMENT OF x, EACH ELEMENT OF y) REFERENCES DIM1(x, y); + ERROR: foreign keys support only one array column + DROP TABLE F1; + -- Cleanup + DROP TABLE DIM1; + -- Check for potential name conflicts (with internal integrity checks) + CREATE TABLE x1(x1 int, x2 int, PRIMARY KEY(x1,x2)); + INSERT INTO x1 VALUES + (1,4), + (1,5), + (2,4), + (2,5), + (3,6), + (3,7) + ; + CREATE TABLE x2(x1 int[], x2 int, FOREIGN KEY(EACH ELEMENT OF x1, x2) REFERENCES x1); + INSERT INTO x2 VALUES ('{1,2}',4); + INSERT INTO x2 VALUES ('{1,3}',6); -- FAILS + ERROR: insert or update on table "x2" violates foreign key constraint "x2_x1_fkey" + DETAIL: Key (x1, x2)=({1,3}, 6) is not present in table "x1". + DROP TABLE x2; + CREATE TABLE x2(x1 int[], x2 int); + INSERT INTO x2 VALUES ('{1,2}',4); + INSERT INTO x2 VALUES ('{1,3}',6); + ALTER TABLE x2 ADD CONSTRAINT fk_const FOREIGN KEY(EACH ELEMENT OF x1, x2) REFERENCES x1; -- FAILS + ERROR: insert or update on table "x2" violates foreign key constraint "fk_const" + DETAIL: Key (x1, x2)=({1,3}, 6) is not present in table "x1". + DROP TABLE x2; + DROP TABLE x1; + -- --------------------------------------- + -- Multi-dimensional "ELEMENT" foreign key tests + -- --------------------------------------- + -- Create DIM1 table with two-column primary key + CREATE TABLE DIM1 (X INTEGER NOT NULL PRIMARY KEY, + CODE TEXT NOT NULL UNIQUE); + -- Populate DIM1 table pairs + INSERT INTO DIM1 SELECT t, 'DIM1-' || lpad(t::TEXT, 2, '0') + FROM (SELECT generate_series(1, 10)) x(t); + -- Test with TABLE declaration of an element foreign key constraint (NO ACTION) + CREATE TABLE F1 ( + ID SERIAL PRIMARY KEY, + SLOTS INTEGER[3][3], FOREIGN KEY (EACH ELEMENT OF SLOTS) REFERENCES DIM1 + ); + INSERT INTO F1(SLOTS) VALUES ('{{NULL, 1, NULL}, {NULL, NULL, 3}, {NULL, NULL, 6}}'); -- OK + INSERT INTO F1(SLOTS) VALUES ('{{NULL, 1, NULL}, {NULL, NULL, 11}, {NULL, NULL, 6}}'); -- FAILS + ERROR: insert or update on table "f1" violates foreign key constraint "f1_slots_fkey" + DETAIL: Key (slots)=({{NULL,1,NULL},{NULL,NULL,11},{NULL,NULL,6}}) is not present in table "dim1". + INSERT INTO F1(SLOTS) VALUES ('{{1, 2, 3}, {4, 5, 6}, {7, 8, 9}}'); -- OK + INSERT INTO F1(SLOTS) VALUES ('{1, 2, 3, 4, 5, 6, 7, 8, 9}'); -- OK + UPDATE F1 SET SLOTS = '{{NULL, 1, NULL}, {NULL, NULL, 3}, {7, 8, 10}}' WHERE ID = 1; -- OK + UPDATE F1 SET SLOTS = '{{100, 100, 100}, {NULL, NULL, 20}, {7, 8, 10}}' WHERE ID = 1; -- FAILS + ERROR: insert or update on table "f1" violates foreign key constraint "f1_slots_fkey" + DETAIL: Key (slots)=({{100,100,100},{NULL,NULL,20},{7,8,10}}) is not present in table "dim1". + DROP TABLE F1; + -- Test with postponed foreign key + CREATE TABLE F1 ( + ID SERIAL PRIMARY KEY, + SLOTS INTEGER[3][3] + ); + INSERT INTO F1(SLOTS) VALUES ('{{NULL, 1, NULL}, {NULL, NULL, 3}, {NULL, NULL, 6}}'); -- OK + INSERT INTO F1(SLOTS) VALUES ('{{NULL, 1, NULL}, {NULL, NULL, 11}, {NULL, NULL, 6}}'); -- OK + INSERT INTO F1(SLOTS) VALUES ('{{1, 2, 3}, {4, 5, 6}, {7, 8, 9}}'); -- OK + INSERT INTO F1(SLOTS) VALUES ('{1, 2, 3, 4, 5, 6, 7, 8, 9}'); -- OK + ALTER TABLE F1 ADD FOREIGN KEY (EACH ELEMENT OF SLOTS) REFERENCES DIM1; -- FAILS + ERROR: insert or update on table "f1" violates foreign key constraint "f1_slots_fkey" + DETAIL: Key (slots)=({{NULL,1,NULL},{NULL,NULL,11},{NULL,NULL,6}}) is not present in table "dim1". + DELETE FROM F1 WHERE ID = 2; -- REMOVE ISSUE + ALTER TABLE F1 ADD FOREIGN KEY (EACH ELEMENT OF SLOTS) REFERENCES DIM1; -- NOW OK + INSERT INTO F1(SLOTS) VALUES ('{{NULL, 1, NULL}, {NULL, NULL, 11}, {NULL, NULL, 6}}'); -- FAILS + ERROR: insert or update on table "f1" violates foreign key constraint "f1_slots_fkey" + DETAIL: Key (slots)=({{NULL,1,NULL},{NULL,NULL,11},{NULL,NULL,6}}) is not present in table "dim1". + DROP TABLE F1; + -- Leave tables in the database + CREATE TABLE PKTABLEFORELEMENTFK ( ptest1 int PRIMARY KEY, ptest2 text ); + CREATE TABLE FKTABLEFORELEMENTFK ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORELEMENTFK, ftest2 int ); + -- Check ALTER TABLE ALTER TYPE + ALTER TABLE FKTABLEFORELEMENTFK ALTER FTEST1 TYPE INT[]; diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 663bf8ac56bd157a6f1e8fd4996ae74e5f5b4cfd..12f17e3b0c6f75e7e139fdf2ed4037fc72c83a4a 100644 *** a/src/test/regress/parallel_schedule --- b/src/test/regress/parallel_schedule *************** test: event_trigger *** 98,104 **** # ---------- # Another group of parallel tests # ---------- ! test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combocid tsearch tsdicts foreign_data window xmlmap functional_deps advisory_lock json # ---------- # Another group of parallel tests --- 98,104 ---- # ---------- # Another group of parallel tests # ---------- ! test: select_views portals_p2 foreign_key element_foreign_key cluster dependency guc bitmapops combocid tsearch tsdicts foreign_data window xmlmap functional_deps advisory_lock json # ---------- # Another group of parallel tests diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule index be789e3f442529f9d6aed282f24c4cbeabf83b8f..e1ed96b0c2c5ed365a49cd513b29b351aff46426 100644 *** a/src/test/regress/serial_schedule --- b/src/test/regress/serial_schedule *************** test: event_trigger *** 101,106 **** --- 101,107 ---- test: select_views test: portals_p2 test: foreign_key + test: element_foreign_key test: cluster test: dependency test: guc diff --git a/src/test/regress/sql/element_foreign_key.sql b/src/test/regress/sql/element_foreign_key.sql index ...8c1e4d9601592a179b14ce3e2f852729da5e4aea . *** a/src/test/regress/sql/element_foreign_key.sql --- b/src/test/regress/sql/element_foreign_key.sql *************** *** 0 **** --- 1,452 ---- + -- EACH-ELEMENT FK CONSTRAINTS + + CREATE TABLE PKTABLEFORARRAY ( ptest1 int PRIMARY KEY, ptest2 text ); + + -- Insert test data into PKTABLEFORARRAY + INSERT INTO PKTABLEFORARRAY VALUES (1, 'Test1'); + INSERT INTO PKTABLEFORARRAY VALUES (2, 'Test2'); + INSERT INTO PKTABLEFORARRAY VALUES (3, 'Test3'); + INSERT INTO PKTABLEFORARRAY VALUES (4, 'Test4'); + INSERT INTO PKTABLEFORARRAY VALUES (5, 'Test5'); + + -- Check alter table + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], ftest2 int ); + ALTER TABLE FKTABLEFORARRAY ADD CONSTRAINT FKARRAY FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY; + DROP TABLE FKTABLEFORARRAY; + + -- Check alter table with rows + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], ftest2 int ); + INSERT INTO FKTABLEFORARRAY VALUES ('{1}', 1); + ALTER TABLE FKTABLEFORARRAY ADD CONSTRAINT FKARRAY FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY; + DROP TABLE FKTABLEFORARRAY; + + -- Check alter table with failing rows + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], ftest2 int ); + INSERT INTO FKTABLEFORARRAY VALUES ('{10,1}', 2); + ALTER TABLE FKTABLEFORARRAY ADD CONSTRAINT FKARRAY FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY; + DROP TABLE FKTABLEFORARRAY; + + -- Check create table + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY, ftest2 int ); + CREATE TABLE FKTABLEFORARRAYMDIM ( ftest1 int[][], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY, ftest2 int ); + CREATE TABLE FKTABLEFORARRAYNOTNULL ( ftest1 int[] NOT NULL, FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY, ftest2 int ); + + -- Insert successful rows into FK TABLE + INSERT INTO FKTABLEFORARRAY VALUES ('{1}', 3); + INSERT INTO FKTABLEFORARRAY VALUES ('{2}', 4); + INSERT INTO FKTABLEFORARRAY VALUES ('{1}', 5); + INSERT INTO FKTABLEFORARRAY VALUES ('{3}', 6); + INSERT INTO FKTABLEFORARRAY VALUES ('{1}', 7); + INSERT INTO FKTABLEFORARRAY VALUES ('{4,5}', 8); + INSERT INTO FKTABLEFORARRAY VALUES ('{4,4}', 9); + INSERT INTO FKTABLEFORARRAY VALUES (NULL, 10); + INSERT INTO FKTABLEFORARRAY VALUES ('{}', 11); + INSERT INTO FKTABLEFORARRAY VALUES ('{1,NULL}', 12); + INSERT INTO FKTABLEFORARRAY VALUES ('{NULL}', 13); + INSERT INTO FKTABLEFORARRAYMDIM VALUES ('{{4,5},{1,2},{1,3}}', 14); + INSERT INTO FKTABLEFORARRAYMDIM VALUES ('{{4,5},{NULL,2},{NULL,3}}', 15); + + -- Insert failed rows into FK TABLE + INSERT INTO FKTABLEFORARRAY VALUES ('{6}', 16); + INSERT INTO FKTABLEFORARRAY VALUES ('{4,6}', 17); + INSERT INTO FKTABLEFORARRAY VALUES ('{6,NULL}', 18); + INSERT INTO FKTABLEFORARRAY VALUES ('{6,NULL,4,NULL}', 19); + INSERT INTO FKTABLEFORARRAYMDIM VALUES ('{{1,2},{6,NULL}}', 20); + INSERT INTO FKTABLEFORARRAYNOTNULL VALUES (NULL, 21); + + -- Check FKTABLE + SELECT * FROM FKTABLEFORARRAY; + + -- Delete a row from PK TABLE (must fail due to ON DELETE NO ACTION) + DELETE FROM PKTABLEFORARRAY WHERE ptest1=1; + + -- Check FKTABLE for removal of matched row + SELECT * FROM FKTABLEFORARRAY; + + -- Update a row from PK TABLE (must fail due to ON UPDATE NO ACTION) + UPDATE PKTABLEFORARRAY SET ptest1=7 WHERE ptest1=1; + + -- Check FKTABLE for update of matched row + SELECT * FROM FKTABLEFORARRAY; + + -- Check UPDATE on FKTABLE + UPDATE FKTABLEFORARRAY SET ftest1=ARRAY[1] WHERE ftest2=4; + + -- Check FKTABLE for update of matched row + SELECT * FROM FKTABLEFORARRAY; + + DROP TABLE FKTABLEFORARRAY; + DROP TABLE FKTABLEFORARRAYNOTNULL; + DROP TABLE FKTABLEFORARRAYMDIM; + + -- Allowed references with actions (NO ACTION, RESTRICT) + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE NO ACTION ON UPDATE NO ACTION, ftest2 int ); + DROP TABLE FKTABLEFORARRAY; + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE NO ACTION ON UPDATE RESTRICT, ftest2 int ); + DROP TABLE FKTABLEFORARRAY; + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE RESTRICT ON UPDATE NO ACTION, ftest2 int ); + DROP TABLE FKTABLEFORARRAY; + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE RESTRICT ON UPDATE RESTRICT, ftest2 int ); + DROP TABLE FKTABLEFORARRAY; + -- Not allowed references (SET NULL, SET DEFAULT, CASCADE) + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE NO ACTION ON UPDATE SET DEFAULT, ftest2 int ); + DROP TABLE FKTABLEFORARRAY; + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE NO ACTION ON UPDATE SET NULL, ftest2 int ); + DROP TABLE FKTABLEFORARRAY; + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE RESTRICT ON UPDATE SET DEFAULT, ftest2 int ); + DROP TABLE FKTABLEFORARRAY; + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE RESTRICT ON UPDATE SET NULL, ftest2 int ); + DROP TABLE FKTABLEFORARRAY; + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE CASCADE ON UPDATE NO ACTION, ftest2 int ); + DROP TABLE FKTABLEFORARRAY; + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE CASCADE ON UPDATE RESTRICT, ftest2 int ); + DROP TABLE FKTABLEFORARRAY; + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE CASCADE ON UPDATE SET DEFAULT, ftest2 int ); + DROP TABLE FKTABLEFORARRAY; + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE CASCADE ON UPDATE SET NULL, ftest2 int ); + DROP TABLE FKTABLEFORARRAY; + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE SET DEFAULT ON UPDATE NO ACTION, ftest2 int ); + DROP TABLE FKTABLEFORARRAY; + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE SET DEFAULT ON UPDATE RESTRICT, ftest2 int ); + DROP TABLE FKTABLEFORARRAY; + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE SET DEFAULT ON UPDATE SET DEFAULT, ftest2 int ); + DROP TABLE FKTABLEFORARRAY; + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE SET DEFAULT ON UPDATE SET NULL, ftest2 int ); + DROP TABLE FKTABLEFORARRAY; + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE SET NULL ON UPDATE NO ACTION, ftest2 int ); + DROP TABLE FKTABLEFORARRAY; + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE SET NULL ON UPDATE RESTRICT, ftest2 int ); + DROP TABLE FKTABLEFORARRAY; + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE SET NULL ON UPDATE SET DEFAULT, ftest2 int ); + DROP TABLE FKTABLEFORARRAY; + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE SET NULL ON UPDATE SET NULL, ftest2 int ); + DROP TABLE FKTABLEFORARRAY; + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE NO ACTION ON UPDATE CASCADE, ftest2 int ); + DROP TABLE FKTABLEFORARRAY; + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE RESTRICT ON UPDATE CASCADE, ftest2 int ); + DROP TABLE FKTABLEFORARRAY; + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE CASCADE ON UPDATE CASCADE, ftest2 int ); + DROP TABLE FKTABLEFORARRAY; + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE SET DEFAULT ON UPDATE CASCADE, ftest2 int ); + DROP TABLE FKTABLEFORARRAY; + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE SET NULL ON UPDATE CASCADE, ftest2 int ); + DROP TABLE FKTABLEFORARRAY; + + -- Cleanup + DROP TABLE PKTABLEFORARRAY; + + -- Check reference on empty table + CREATE TABLE PKTABLEFORARRAY (ptest1 int PRIMARY KEY); + CREATE TABLE FKTABLEFORARRAY (ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY); + INSERT INTO FKTABLEFORARRAY VALUES ('{}'); + DROP TABLE FKTABLEFORARRAY; + DROP TABLE PKTABLEFORARRAY; + + -- Repeat a similar test using CHAR(1) keys rather than INTEGER + CREATE TABLE PKTABLEFORARRAY ( ptest1 CHAR(1) PRIMARY KEY, ptest2 text ); + + -- Populate the primary table + INSERT INTO PKTABLEFORARRAY VALUES ('A', 'Test A'); + INSERT INTO PKTABLEFORARRAY VALUES ('B', 'Test B'); + INSERT INTO PKTABLEFORARRAY VALUES ('C', 'Test C'); + + -- Create the foreign table + CREATE TABLE FKTABLEFORARRAY ( ftest1 char(1)[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON UPDATE RESTRICT ON DELETE RESTRICT, ftest2 int ); + + -- Insert valid rows into FK TABLE + INSERT INTO FKTABLEFORARRAY VALUES ('{"A"}', 1); + INSERT INTO FKTABLEFORARRAY VALUES ('{"B"}', 2); + INSERT INTO FKTABLEFORARRAY VALUES ('{"C"}', 3); + INSERT INTO FKTABLEFORARRAY VALUES ('{"A","B","C"}', 4); + + -- Insert invalid rows into FK TABLE + INSERT INTO FKTABLEFORARRAY VALUES ('{"D"}', 5); + INSERT INTO FKTABLEFORARRAY VALUES ('{"A","B","D"}', 6); + + -- Check FKTABLE + SELECT * FROM FKTABLEFORARRAY; + + -- Delete a row from PK TABLE (must fail due to ON DELETE RESTRICT) + DELETE FROM PKTABLEFORARRAY WHERE ptest1='A'; + + -- Check FKTABLE for removal of matched row + SELECT * FROM FKTABLEFORARRAY; + + -- Update a row from PK TABLE (must fail due to ON UPDATE RESTRICT) + UPDATE PKTABLEFORARRAY SET ptest1='D' WHERE ptest1='B'; + + -- Check FKTABLE for update of matched row + SELECT * FROM FKTABLEFORARRAY; + + -- Cleanup + DROP TABLE FKTABLEFORARRAY; + DROP TABLE PKTABLEFORARRAY; + + -- Repeat a similar test using INT4 keys coerced from INT2 + CREATE TABLE PKTABLEFORARRAY ( ptest1 int4 PRIMARY KEY, ptest2 text ); + + -- Populate the primary table + INSERT INTO PKTABLEFORARRAY VALUES (1, 'Test 1'); + INSERT INTO PKTABLEFORARRAY VALUES (2, 'Test 2'); + INSERT INTO PKTABLEFORARRAY VALUES (3, 'Test 3'); + + -- Create the foreign table + CREATE TABLE FKTABLEFORARRAY ( ftest1 int2[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY, ftest2 int ); + + -- Insert valid rows into FK TABLE + INSERT INTO FKTABLEFORARRAY VALUES (ARRAY[1], 1); + INSERT INTO FKTABLEFORARRAY VALUES (ARRAY[2], 2); + INSERT INTO FKTABLEFORARRAY VALUES (ARRAY[3], 3); + INSERT INTO FKTABLEFORARRAY VALUES (ARRAY[1,2,3], 4); + + -- Insert invalid rows into FK TABLE + INSERT INTO FKTABLEFORARRAY VALUES (ARRAY[4], 5); + INSERT INTO FKTABLEFORARRAY VALUES (ARRAY[1,2,5], 6); + + -- Cleanup + DROP TABLE FKTABLEFORARRAY; + DROP TABLE PKTABLEFORARRAY; + + -- Repeat a similar test using FLOAT8 keys coerced from INTEGER + CREATE TABLE PKTABLEFORARRAY ( ptest1 float8 PRIMARY KEY, ptest2 text ); + -- XXX this really ought to work, but currently we must disallow it + CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY, ftest2 int ); + + -- Cleanup + DROP TABLE PKTABLEFORARRAY; + + -- Composite primary keys + CREATE TABLE PKTABLEFORARRAY ( id1 CHAR(1), id2 CHAR(1), ptest2 text, PRIMARY KEY (id1, id2) ); + + -- Populate the primary table + INSERT INTO PKTABLEFORARRAY VALUES ('A', 'A', 'Test A'); + INSERT INTO PKTABLEFORARRAY VALUES ('A', 'B', 'Test B'); + INSERT INTO PKTABLEFORARRAY VALUES ('B', 'C', 'Test B'); + + -- Create the foreign table + CREATE TABLE FKTABLEFORARRAY ( fid1 CHAR(1), fid2 CHAR(1)[], ftest2 text, FOREIGN KEY (fid1, EACH ELEMENT OF fid2) REFERENCES PKTABLEFORARRAY); + + -- Insert valid rows into FK TABLE + INSERT INTO FKTABLEFORARRAY VALUES ('A', ARRAY['A','B'], '1'); + INSERT INTO FKTABLEFORARRAY VALUES ('B', ARRAY['C'], '2'); + + -- Insert invalid rows into FK TABLE + INSERT INTO FKTABLEFORARRAY VALUES ('A', ARRAY['A','B', 'C'], '3'); + INSERT INTO FKTABLEFORARRAY VALUES ('B', ARRAY['A'], '4'); + + -- Cleanup + DROP TABLE FKTABLEFORARRAY; + DROP TABLE PKTABLEFORARRAY; + + -- Test ELEMENT foreign keys with composite type + CREATE TYPE INVOICEID AS (year_part INTEGER, progressive_part INTEGER); + CREATE TABLE PKTABLEFORARRAY ( id INVOICEID PRIMARY KEY, ptest2 text); + + -- Populate the primary table + INSERT INTO PKTABLEFORARRAY VALUES (ROW(2010, 99), 'Last invoice for 2010'); + INSERT INTO PKTABLEFORARRAY VALUES (ROW(2011, 1), 'First invoice for 2011'); + INSERT INTO PKTABLEFORARRAY VALUES (ROW(2011, 2), 'Second invoice for 2011'); + + -- Create the foreign table + CREATE TABLE FKTABLEFORARRAY ( id SERIAL PRIMARY KEY, invoice_ids INVOICEID[], FOREIGN KEY (EACH ELEMENT OF invoice_ids) REFERENCES PKTABLEFORARRAY, ftest2 TEXT ); + + -- Insert valid rows into FK TABLE + INSERT INTO FKTABLEFORARRAY(invoice_ids, ftest2) VALUES (ARRAY['(2010,99)']::INVOICEID[], 'Product A'); + INSERT INTO FKTABLEFORARRAY(invoice_ids, ftest2) VALUES (ARRAY['(2011,1)','(2011,2)']::INVOICEID[], 'Product B'); + INSERT INTO FKTABLEFORARRAY(invoice_ids, ftest2) VALUES (ARRAY['(2011,2)']::INVOICEID[], 'Product C'); + + -- Insert invalid rows into FK TABLE + INSERT INTO FKTABLEFORARRAY(invoice_ids, ftest2) VALUES (ARRAY['(2011,99)']::INVOICEID[], 'Product A'); + INSERT INTO FKTABLEFORARRAY(invoice_ids, ftest2) VALUES (ARRAY['(2011,1)','(2010,1)']::INVOICEID[], 'Product B'); + + -- Check FKTABLE + SELECT * FROM FKTABLEFORARRAY; + + -- Delete a row from PK TABLE + DELETE FROM PKTABLEFORARRAY WHERE id=ROW(2010,99); + + -- Check FKTABLE for removal of matched row + SELECT * FROM FKTABLEFORARRAY; + + -- Update a row from PK TABLE + UPDATE PKTABLEFORARRAY SET id=ROW(2011,99) WHERE id=ROW(2011,1); + + -- Check FKTABLE for update of matched row + SELECT * FROM FKTABLEFORARRAY; + + -- Cleanup + DROP TABLE FKTABLEFORARRAY; + DROP TABLE PKTABLEFORARRAY; + DROP TYPE INVOICEID; + + -- Check FK with cast + CREATE TABLE PKTABLEFORARRAY (c smallint PRIMARY KEY); + CREATE TABLE FKTABLEFORARRAY (c int[], FOREIGN KEY (EACH ELEMENT OF c) REFERENCES PKTABLEFORARRAY); + INSERT INTO PKTABLEFORARRAY VALUES (1), (2); + INSERT INTO FKTABLEFORARRAY VALUES ('{1,2}'); + UPDATE PKTABLEFORARRAY SET c = 3 WHERE c = 2; + DELETE FROM PKTABLEFORARRAY WHERE c = 1; + + -- Cleanup + DROP TABLE FKTABLEFORARRAY; + DROP TABLE PKTABLEFORARRAY; + + -- Check for an array column referencing another array column (NOT ELEMENT FOREIGN KEY) + -- Create primary table with an array primary key + CREATE TABLE PKTABLEFORARRAY ( id INT[] PRIMARY KEY, ptest2 text); + + -- Create the foreign table + CREATE TABLE FKTABLEFORARRAY ( id SERIAL PRIMARY KEY, fids INT[] REFERENCES PKTABLEFORARRAY, ftest2 TEXT ); + + -- Populate the primary table + INSERT INTO PKTABLEFORARRAY VALUES ('{1,1}', 'A'); + INSERT INTO PKTABLEFORARRAY VALUES ('{1,2}', 'B'); + + -- Insert valid rows into FK TABLE + INSERT INTO FKTABLEFORARRAY (fids, ftest2) VALUES ('{1,1}', 'Product A'); + INSERT INTO FKTABLEFORARRAY (fids, ftest2) VALUES ('{1,2}', 'Product B'); + + -- Insert invalid rows into FK TABLE + INSERT INTO FKTABLEFORARRAY (fids, ftest2) VALUES ('{0,1}', 'Product C'); + INSERT INTO FKTABLEFORARRAY (fids, ftest2) VALUES ('{2,1}', 'Product D'); + + -- Cleanup + DROP TABLE FKTABLEFORARRAY; + DROP TABLE PKTABLEFORARRAY; + + -- --------------------------------------- + -- Multi-column "ELEMENT" foreign key tests + -- --------------------------------------- + + -- Create DIM1 table with two-column primary key + CREATE TABLE DIM1 (X INTEGER NOT NULL, Y INTEGER NOT NULL, PRIMARY KEY (X, Y)); + -- Populate DIM1 table pairs + INSERT INTO DIM1 SELECT x.t, x.t * y.t + FROM (SELECT generate_series(1, 10) AS t) x, + (SELECT generate_series(0, 10) AS t) y; + + + -- Test with TABLE declaration of an element foreign key constraint (NO ACTION) + CREATE TABLE F1 ( + x INTEGER PRIMARY KEY, y INTEGER[], + FOREIGN KEY (x, EACH ELEMENT OF y) REFERENCES DIM1(x, y) + ); + -- Insert facts + INSERT INTO F1 VALUES (1, '{0,1,2,3,4,5}'); -- OK + INSERT INTO F1 VALUES (2, '{0,2,4,6}'); -- OK + INSERT INTO F1 VALUES (3, '{0,3,6,9,0,3,6,9,0,0,0,0,9,9}'); -- OK (multiple occurrences) + INSERT INTO F1 VALUES (4, '{0,2,4}'); -- FAILS (2 is not present) + INSERT INTO F1 VALUES (4, '{0,NULL,4}'); -- OK + INSERT INTO F1 VALUES (5, '{0,NULL,5}'); -- OK + -- Try updates + UPDATE F1 SET y = '{0,2,4,6}' WHERE x = 2; -- OK + UPDATE F1 SET y = '{0,2,3,4,6}' WHERE x = 2; -- FAILS + UPDATE F1 SET x = 20, y = '{0,2,3,4,6}' WHERE x = 2; -- FAILS (20 does not exist) + UPDATE F1 SET y = '{0,4,8}' WHERE x = 4; -- OK + UPDATE F1 SET y = '{0,5,NULL,10}' WHERE x = 5; -- OK + DROP TABLE F1; + + + -- Test with FOREIGN KEY after TABLE population + CREATE TABLE F1 ( + x INTEGER PRIMARY KEY, y INTEGER[] + ); + -- Insert facts + INSERT INTO F1 VALUES (1, '{0,1,2,3,4,5}'); -- OK + INSERT INTO F1 VALUES (2, '{0,2,4,6}'); -- OK + INSERT INTO F1 VALUES (3, '{0,3,6,9,0,3,6,9,0,0,0,0,9,9}'); -- OK (multiple occurrences) + INSERT INTO F1 VALUES (4, '{0,2,4}'); -- OK (2 is not present) + INSERT INTO F1 VALUES (5, '{0,NULL,5}'); -- OK + -- Add foreign key (FAILS) + ALTER TABLE F1 ADD FOREIGN KEY (x, EACH ELEMENT OF y) REFERENCES DIM1(x, y); + DROP TABLE F1; + + + -- Test with TABLE declaration of a two-dim ELEMENT foreign key constraint (FAILS) + CREATE TABLE F1 ( + x INTEGER[] PRIMARY KEY, y INTEGER[], + FOREIGN KEY (EACH ELEMENT OF x, EACH ELEMENT OF y) REFERENCES DIM1(x, y) + ); + + + -- Test with two-dim ELEMENT foreign key after TABLE population + CREATE TABLE F1 ( + x INTEGER[] PRIMARY KEY, y INTEGER[] + ); + INSERT INTO F1 VALUES ('{1}', '{0,1,2,3,4,5}'); -- OK + INSERT INTO F1 VALUES ('{1,2}', '{0,2,4,6}'); -- OK + -- Add foreign key (FAILS) + ALTER TABLE F1 ADD FOREIGN KEY (EACH ELEMENT OF x, EACH ELEMENT OF y) REFERENCES DIM1(x, y); + DROP TABLE F1; + + -- Cleanup + DROP TABLE DIM1; + + + -- Check for potential name conflicts (with internal integrity checks) + CREATE TABLE x1(x1 int, x2 int, PRIMARY KEY(x1,x2)); + INSERT INTO x1 VALUES + (1,4), + (1,5), + (2,4), + (2,5), + (3,6), + (3,7) + ; + CREATE TABLE x2(x1 int[], x2 int, FOREIGN KEY(EACH ELEMENT OF x1, x2) REFERENCES x1); + INSERT INTO x2 VALUES ('{1,2}',4); + INSERT INTO x2 VALUES ('{1,3}',6); -- FAILS + DROP TABLE x2; + CREATE TABLE x2(x1 int[], x2 int); + INSERT INTO x2 VALUES ('{1,2}',4); + INSERT INTO x2 VALUES ('{1,3}',6); + ALTER TABLE x2 ADD CONSTRAINT fk_const FOREIGN KEY(EACH ELEMENT OF x1, x2) REFERENCES x1; -- FAILS + DROP TABLE x2; + DROP TABLE x1; + + + -- --------------------------------------- + -- Multi-dimensional "ELEMENT" foreign key tests + -- --------------------------------------- + + -- Create DIM1 table with two-column primary key + CREATE TABLE DIM1 (X INTEGER NOT NULL PRIMARY KEY, + CODE TEXT NOT NULL UNIQUE); + -- Populate DIM1 table pairs + INSERT INTO DIM1 SELECT t, 'DIM1-' || lpad(t::TEXT, 2, '0') + FROM (SELECT generate_series(1, 10)) x(t); + + -- Test with TABLE declaration of an element foreign key constraint (NO ACTION) + CREATE TABLE F1 ( + ID SERIAL PRIMARY KEY, + SLOTS INTEGER[3][3], FOREIGN KEY (EACH ELEMENT OF SLOTS) REFERENCES DIM1 + ); + INSERT INTO F1(SLOTS) VALUES ('{{NULL, 1, NULL}, {NULL, NULL, 3}, {NULL, NULL, 6}}'); -- OK + INSERT INTO F1(SLOTS) VALUES ('{{NULL, 1, NULL}, {NULL, NULL, 11}, {NULL, NULL, 6}}'); -- FAILS + INSERT INTO F1(SLOTS) VALUES ('{{1, 2, 3}, {4, 5, 6}, {7, 8, 9}}'); -- OK + INSERT INTO F1(SLOTS) VALUES ('{1, 2, 3, 4, 5, 6, 7, 8, 9}'); -- OK + UPDATE F1 SET SLOTS = '{{NULL, 1, NULL}, {NULL, NULL, 3}, {7, 8, 10}}' WHERE ID = 1; -- OK + UPDATE F1 SET SLOTS = '{{100, 100, 100}, {NULL, NULL, 20}, {7, 8, 10}}' WHERE ID = 1; -- FAILS + DROP TABLE F1; + + -- Test with postponed foreign key + CREATE TABLE F1 ( + ID SERIAL PRIMARY KEY, + SLOTS INTEGER[3][3] + ); + INSERT INTO F1(SLOTS) VALUES ('{{NULL, 1, NULL}, {NULL, NULL, 3}, {NULL, NULL, 6}}'); -- OK + INSERT INTO F1(SLOTS) VALUES ('{{NULL, 1, NULL}, {NULL, NULL, 11}, {NULL, NULL, 6}}'); -- OK + INSERT INTO F1(SLOTS) VALUES ('{{1, 2, 3}, {4, 5, 6}, {7, 8, 9}}'); -- OK + INSERT INTO F1(SLOTS) VALUES ('{1, 2, 3, 4, 5, 6, 7, 8, 9}'); -- OK + ALTER TABLE F1 ADD FOREIGN KEY (EACH ELEMENT OF SLOTS) REFERENCES DIM1; -- FAILS + DELETE FROM F1 WHERE ID = 2; -- REMOVE ISSUE + ALTER TABLE F1 ADD FOREIGN KEY (EACH ELEMENT OF SLOTS) REFERENCES DIM1; -- NOW OK + INSERT INTO F1(SLOTS) VALUES ('{{NULL, 1, NULL}, {NULL, NULL, 11}, {NULL, NULL, 6}}'); -- FAILS + DROP TABLE F1; + + -- Leave tables in the database + CREATE TABLE PKTABLEFORELEMENTFK ( ptest1 int PRIMARY KEY, ptest2 text ); + CREATE TABLE FKTABLEFORELEMENTFK ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORELEMENTFK, ftest2 int ); + + -- Check ALTER TABLE ALTER TYPE + ALTER TABLE FKTABLEFORELEMENTFK ALTER FTEST1 TYPE INT[];