foreign-key-arrays.patch.v1
text/plain
Filename: foreign-key-arrays.patch.v1
Type: text/plain
Part: 0
*** a/doc/src/sgml/ddl.sgml
--- b/doc/src/sgml/ddl.sgml
***************
*** 764,769 **** CREATE TABLE order_items (
--- 764,796 ----
the last table.
</para>
+ <para>
+ Another option you have with foreign keys is to use a referencing column
+ which is an array of elements with the same type as the referenced column
+ in the related table. This feature, also known as <firstterm>foreign key arrays</firstterm>,
+ is described in the following example:
+
+ <programlisting>
+ CREATE TABLE countries (
+ country_id integer PRIMARY KEY,
+ name text,
+ ...
+ );
+
+ CREATE TABLE people (
+ person_id integer PRIMARY KEY,
+ first_name text,
+ last_name text,
+ ...
+ citizenship_ids integer[] REFERENCES countries
+ );
+ </programlisting>
+
+ The above example lists in an array the citizenships held by
+ a person and enforces referential integrity checks.
+
+ </para>
+
<indexterm>
<primary>CASCADE</primary>
<secondary>foreign key action</secondary>
***************
*** 852,857 **** CREATE TABLE order_items (
--- 879,891 ----
</para>
<para>
+ When working with foreign key arrays, you are currently limited
+ to <literal>RESTRICT</literal> and <literal>NO ACTION</literal>
+ options, as the default behaviour for the other cases is not
+ clearly and universally determined yet.
+ </para>
+
+ <para>
More information about updating and deleting data is in <xref
linkend="dml">.
</para>
*** a/doc/src/sgml/ref/create_table.sgml
--- b/doc/src/sgml/ref/create_table.sgml
***************
*** 576,581 **** CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
--- 576,587 ----
</para>
<para>
+ If the referencing column is an array of elements of the same type as
+ the referenced column in the referenced table, the value of each element
+ of the array will be matched against some row of the referenced table.
+ </para>
+
+ <para>
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: <literal>MATCH
***************
*** 634,640 **** CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
<para>
Delete any rows referencing the deleted row, or update the
value of the referencing column to the new value of the
! referenced column, respectively.
</para>
</listitem>
</varlistentry>
--- 640,647 ----
<para>
Delete any rows referencing the deleted row, or update the
value of the referencing column to the new value of the
! referenced column, respectively. Foreign key arrays are not
! supported by this action (as the behaviour is not easily determined).
</para>
</listitem>
</varlistentry>
***************
*** 643,649 **** CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
<term><literal>SET NULL</literal></term>
<listitem>
<para>
! Set the referencing column(s) to null.
</para>
</listitem>
</varlistentry>
--- 650,657 ----
<term><literal>SET NULL</literal></term>
<listitem>
<para>
! Set the referencing column(s) to null. Foreign key arrays are not
! supported by this action (as the behaviour is not easily determined).
</para>
</listitem>
</varlistentry>
***************
*** 652,658 **** CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
<term><literal>SET DEFAULT</literal></term>
<listitem>
<para>
! Set the referencing column(s) to their default values.
</para>
</listitem>
</varlistentry>
--- 660,667 ----
<term><literal>SET DEFAULT</literal></term>
<listitem>
<para>
! Set the referencing column(s) to their default values. Foreign key arrays are not
! supported by this action (as the behaviour is not easily determined).
</para>
</listitem>
</varlistentry>
*** a/src/backend/commands/tablecmds.c
--- b/src/backend/commands/tablecmds.c
***************
*** 5705,5710 **** ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,
--- 5705,5735 ----
Oid ffeqop;
int16 eqstrategy;
+ /* Check if foreign key is an array of primary key types */
+ const bool is_foreign_key_array = (fktype == get_array_type (pktype));
+
+ /* Enforce foreign key array restrictions */
+ if (is_foreign_key_array)
+ {
+ /*
+ * Foreign key array must not be part of a multi-column foreign key
+ */
+ if (is_foreign_key_array && numpks > 1)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_FOREIGN_KEY),
+ errmsg("foreign key arrays must not be part of a multi-column foreign key")));
+
+ /*
+ * We have to restrict foreign key array to NO ACTION and RESTRICT mode
+ * until the behaviour triggered by the other actions is clearer and well defined
+ */
+ 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("NO ACTION and RESTRICT are the only supported actions for foreign key arrays")));
+ }
+
/* We need several fields out of the pg_opclass entry */
cla_ht = SearchSysCache1(CLAOID, ObjectIdGetDatum(opclasses[i]));
if (!HeapTupleIsValid(cla_ht))
***************
*** 5766,5772 **** ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,
Oid target_typeids[2];
input_typeids[0] = pktype;
! input_typeids[1] = fktype;
target_typeids[0] = opcintype;
target_typeids[1] = opcintype;
if (can_coerce_type(2, input_typeids, target_typeids,
--- 5791,5801 ----
Oid target_typeids[2];
input_typeids[0] = pktype;
! /* When is FKA we must use for FK the same type of PK */
! if (is_foreign_key_array)
! input_typeids[1] = pktype;
! else
! input_typeids[1] = fktype;
target_typeids[0] = opcintype;
target_typeids[1] = opcintype;
if (can_coerce_type(2, input_typeids, target_typeids,
*** a/src/backend/utils/adt/ri_triggers.c
--- b/src/backend/utils/adt/ri_triggers.c
***************
*** 460,465 **** RI_FKey_check(PG_FUNCTION_ARGS)
--- 460,466 ----
char paramname[16];
const char *querysep;
Oid queryoids[RI_MAX_NUMKEYS];
+ bool is_foreign_key_array = false;
/* ----------
* The query string built is
***************
*** 476,493 **** RI_FKey_check(PG_FUNCTION_ARGS)
{
Oid pk_type = RIAttType(pk_rel, riinfo.pk_attnums[i]);
Oid fk_type = RIAttType(fk_rel, riinfo.fk_attnums[i]);
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,
--- 477,524 ----
{
Oid pk_type = RIAttType(pk_rel, riinfo.pk_attnums[i]);
Oid fk_type = RIAttType(fk_rel, riinfo.fk_attnums[i]);
+ is_foreign_key_array = (fk_type == get_array_type (pk_type));
quoteOneName(attname,
RIAttName(pk_rel, riinfo.pk_attnums[i]));
sprintf(paramname, "$%d", i + 1);
! /*
! * In case of an array foreign key, we check that every
! * DISTINCT NOT NULL value in the array is present in the PK table.
! * XXX: This works because the query is executed with LIMIT 1,
! * but may not work properly with SSI (a better approach would be
! * to inspect the array and skip the check in case of empty arrays).
! */
! if (is_foreign_key_array)
! {
! appendStringInfo(&querybuf, " %s (SELECT count(*) FROM (SELECT DISTINCT UNNEST(%s)) y WHERE y IS NOT NULL)", querysep, paramname);
! appendStringInfo(&querybuf, " = (SELECT count(*) FROM (SELECT 1 FROM ONLY %s y", pkrelname);
! ri_GenerateQual(&querybuf, "WHERE",
! attname, pk_type,
! riinfo.pf_eq_oprs[i],
! paramname, fk_type);
! /*
! * We lock for share every row in the pkreltable that is
! * referenced by the array elements
! */
! appendStringInfo(&querybuf, " FOR SHARE OF y) z)");
! }
! else
! {
! ri_GenerateQual(&querybuf, querysep,
! attname, pk_type,
! riinfo.pf_eq_oprs[i],
! paramname, fk_type);
! }
querysep = "AND";
queryoids[i] = fk_type;
}
! /*
! * We skip locking for share in case of foreign key arrays
! * as it has been done in the inner subquery
! */
! if (! is_foreign_key_array)
! appendStringInfo(&querybuf, " FOR SHARE OF x");
/* Prepare and save the plan */
qplan = ri_PlanCheck(querybuf.data, riinfo.nkeys, queryoids,
***************
*** 2949,2957 **** ri_GenerateQual(StringInfo buf,
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);
}
--- 2980,3002 ----
ri_add_cast_to(buf, operform->oprleft);
appendStringInfo(buf, " OPERATOR(%s.", quote_identifier(nspname));
appendStringInfoString(buf, oprname);
! /*
! * If rightoptype is an array of leftoptype check equality using ANY().
! * Needed for array support in foreign keys.
! */
! if (rightoptype == get_array_type (leftoptype))
! {
! appendStringInfo(buf, ") ANY (%s", rightop);
! if (rightoptype != get_array_type (operform->oprright))
! ri_add_cast_to(buf, get_array_type (operform->oprright));
! appendStringInfo(buf, ")");
! }
! else
! {
! appendStringInfo(buf, ") %s", rightop);
! if (rightoptype != operform->oprright)
! ri_add_cast_to(buf, operform->oprright);
! }
ReleaseSysCache(opertup);
}
*** a/src/test/regress/expected/foreign_key.out
--- b/src/test/regress/expected/foreign_key.out
***************
*** 968,978 **** drop table pktable;
drop table pktable_base;
-- 2 columns (1 table), mismatched types
create table pktable_base(base1 int not null, base2 int);
- create table pktable(ptest1 inet, ptest2 inet[], primary key(base1, ptest1), foreign key(base2, ptest2) references
- pktable(base1, ptest1)) inherits (pktable_base);
- NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
- ERROR: foreign key constraint "pktable_base2_fkey" cannot be implemented
- DETAIL: Key columns "ptest2" and "ptest1" are of incompatible types: inet[] and inet.
create table pktable(ptest1 inet, ptest2 inet, primary key(base1, ptest1), foreign key(base2, ptest2) references
pktable(ptest1, base1)) inherits (pktable_base);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
--- 968,973 ----
***************
*** 1319,1321 **** begin;
--- 1314,1537 ----
(2 rows)
commit;
+ -- ARRAY FK
+ --
+ CREATE TABLE PKTABLEFORARRAY ( ptest1 int PRIMARY KEY, ptest2 text );
+ NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktableforarray_pkey" for table "pktableforarray"
+ -- 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 (ftest1) REFERENCES PKTABLEFORARRAY;
+ DROP TABLE FKTABLEFORARRAY;
+ -- Check create table
+ CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] REFERENCES PKTABLEFORARRAY, ftest2 int );
+ CREATE TABLE FKTABLEFORARRAYMDIM ( ftest1 int[][] REFERENCES PKTABLEFORARRAY, ftest2 int );
+ CREATE TABLE FKTABLEFORARRAYNOTNULL ( ftest1 int[] NOT NULL REFERENCES PKTABLEFORARRAY, ftest2 int );
+ -- Insert successful rows into FK TABLE
+ INSERT INTO FKTABLEFORARRAY VALUES ('{1}', 2);
+ INSERT INTO FKTABLEFORARRAY VALUES ('{2}', 3);
+ INSERT INTO FKTABLEFORARRAY VALUES ('{1}', 2);
+ INSERT INTO FKTABLEFORARRAY VALUES ('{3}', 4);
+ INSERT INTO FKTABLEFORARRAY VALUES ('{1}', 2);
+ INSERT INTO FKTABLEFORARRAY VALUES ('{4,5}', 2);
+ INSERT INTO FKTABLEFORARRAY VALUES ('{4,4}', 2);
+ INSERT INTO FKTABLEFORARRAY VALUES (NULL, 1);
+ INSERT INTO FKTABLEFORARRAY VALUES ('{}', 5);
+ INSERT INTO FKTABLEFORARRAY VALUES ('{1,NULL}', 6);
+ INSERT INTO FKTABLEFORARRAY VALUES ('{NULL}', 7);
+ INSERT INTO FKTABLEFORARRAYMDIM VALUES ('{{4,5},{1,2},{1,3}}', 8);
+ INSERT INTO FKTABLEFORARRAYMDIM VALUES ('{{4,5},{NULL,2},{NULL,3}}', 9);
+ -- Insert failed rows into FK TABLE
+ INSERT INTO FKTABLEFORARRAY VALUES ('{6}', 3);
+ 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}', 4);
+ 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}', 5);
+ 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}', 6);
+ 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}}', 7);
+ 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, 1);
+ ERROR: null value in column "ftest1" violates not-null constraint
+ -- Check FKTABLE
+ SELECT * FROM FKTABLEFORARRAY;
+ ftest1 | ftest2
+ ----------+--------
+ {1} | 2
+ {2} | 3
+ {1} | 2
+ {3} | 4
+ {1} | 2
+ {4,5} | 2
+ {4,4} | 2
+ | 1
+ {} | 5
+ {1,NULL} | 6
+ {NULL} | 7
+ (11 rows)
+
+ -- Delete a row from PK TABLE (must fail)
+ 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} | 2
+ {2} | 3
+ {1} | 2
+ {3} | 4
+ {1} | 2
+ {4,5} | 2
+ {4,4} | 2
+ | 1
+ {} | 5
+ {1,NULL} | 6
+ {NULL} | 7
+ (11 rows)
+
+ -- Update a row from PK TABLE (must fail)
+ 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} | 2
+ {2} | 3
+ {1} | 2
+ {3} | 4
+ {1} | 2
+ {4,5} | 2
+ {4,4} | 2
+ | 1
+ {} | 5
+ {1,NULL} | 6
+ {NULL} | 7
+ (11 rows)
+
+ DROP TABLE FKTABLEFORARRAY;
+ DROP TABLE FKTABLEFORARRAYNOTNULL;
+ DROP TABLE FKTABLEFORARRAYMDIM;
+ -- Allowed references with actions 'RESTRICT' and 'NO ACTION'
+ CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] REFERENCES PKTABLEFORARRAY ON DELETE RESTRICT ON UPDATE RESTRICT, ftest2 int );
+ DROP TABLE FKTABLEFORARRAY;
+ CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] REFERENCES PKTABLEFORARRAY ON DELETE NO ACTION ON UPDATE NO ACTION, ftest2 int );
+ DROP TABLE FKTABLEFORARRAY;
+ -- Failed attempts of creating references with actions other than 'RESTRICT' and 'NO ACTION'
+ CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] REFERENCES PKTABLEFORARRAY ON DELETE CASCADE ON UPDATE RESTRICT, ftest2 int );
+ ERROR: NO ACTION and RESTRICT are the only supported actions for foreign key arrays
+ CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] REFERENCES PKTABLEFORARRAY ON DELETE RESTRICT ON UPDATE CASCADE, ftest2 int );
+ ERROR: NO ACTION and RESTRICT are the only supported actions for foreign key arrays
+ CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] REFERENCES PKTABLEFORARRAY ON DELETE CASCADE ON UPDATE CASCADE, ftest2 int );
+ ERROR: NO ACTION and RESTRICT are the only supported actions for foreign key arrays
+ CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] REFERENCES PKTABLEFORARRAY ON DELETE CASCADE ON UPDATE NO ACTION, ftest2 int );
+ ERROR: NO ACTION and RESTRICT are the only supported actions for foreign key arrays
+ CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] REFERENCES PKTABLEFORARRAY ON DELETE NO ACTION ON UPDATE CASCADE, ftest2 int );
+ ERROR: NO ACTION and RESTRICT are the only supported actions for foreign key arrays
+ CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] REFERENCES PKTABLEFORARRAY ON DELETE CASCADE ON UPDATE CASCADE, ftest2 int );
+ ERROR: NO ACTION and RESTRICT are the only supported actions for foreign key arrays
+ CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] REFERENCES PKTABLEFORARRAY ON DELETE SET NULL ON UPDATE SET NULL, ftest2 int );
+ ERROR: NO ACTION and RESTRICT are the only supported actions for foreign key arrays
+ CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] REFERENCES PKTABLEFORARRAY ON DELETE SET DEFAULT ON UPDATE SET DEFAULT, ftest2 int );
+ ERROR: NO ACTION and RESTRICT are the only supported actions for foreign key arrays
+ -- Cleanup
+ DROP TABLE PKTABLEFORARRAY;
+ -- Repeat a similar test using CHAR(1) keys rather than INTEGER
+ CREATE TABLE PKTABLEFORARRAY ( ptest1 CHAR(1) PRIMARY KEY, ptest2 text );
+ NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktableforarray_pkey" for table "pktableforarray"
+ -- 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)[] REFERENCES PKTABLEFORARRAY, 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".
+ -- Cleanup
+ DROP TABLE FKTABLEFORARRAY;
+ DROP TABLE PKTABLEFORARRAY;
+ -- Composite primary keys (unsupported)
+ CREATE TABLE PKTABLEFORARRAY ( id1 CHAR(1), id2 CHAR(1), ptest2 text, PRIMARY KEY (id1, id2) );
+ NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktableforarray_pkey" for table "pktableforarray"
+ -- 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');
+ -- Must fail (cannot use arrays in composite foreign keys - use an array of composite types)
+ CREATE TABLE FKTABLEFORARRAY ( fid1 CHAR(1), fid2 CHAR(1)[], ftest2 text, FOREIGN KEY (fid1, fid2) REFERENCES PKTABLEFORARRAY (id1, id2));
+ ERROR: foreign key arrays must not be part of a multi-column foreign key
+ -- Cleanup
+ DROP TABLE PKTABLEFORARRAY;
+ -- Test foreign key arrays with composite type
+ CREATE TYPE INVOICEID AS (year_part INTEGER, progressive_part INTEGER);
+ CREATE TABLE PKTABLEFORARRAY ( id INVOICEID PRIMARY KEY, ptest2 text);
+ NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktableforarray_pkey" for table "pktableforarray"
+ -- 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[] REFERENCES PKTABLEFORARRAY, ftest2 TEXT );
+ NOTICE: CREATE TABLE will create implicit sequence "fktableforarray_id_seq" for serial column "fktableforarray.id"
+ NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "fktableforarray_pkey" for table "fktableforarray"
+ -- 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".
+ -- Cleanup
+ DROP TABLE FKTABLEFORARRAY;
+ DROP TABLE PKTABLEFORARRAY;
+ -- Create primary table with a primary key array
+ CREATE TABLE PKTABLEFORARRAY ( id INT[] PRIMARY KEY, ptest2 text);
+ NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktableforarray_pkey" for table "pktableforarray"
+ -- Create the foreign table
+ CREATE TABLE FKTABLEFORARRAY ( id SERIAL PRIMARY KEY, fids INT[] REFERENCES PKTABLEFORARRAY, ftest2 TEXT );
+ NOTICE: CREATE TABLE will create implicit sequence "fktableforarray_id_seq" for serial column "fktableforarray.id"
+ NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "fktableforarray_pkey" for table "fktableforarray"
+ -- 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;
*** a/src/test/regress/sql/foreign_key.sql
--- b/src/test/regress/sql/foreign_key.sql
***************
*** 602,609 **** drop table pktable_base;
-- 2 columns (1 table), mismatched types
create table pktable_base(base1 int not null, base2 int);
- create table pktable(ptest1 inet, ptest2 inet[], primary key(base1, ptest1), foreign key(base2, ptest2) references
- pktable(base1, ptest1)) inherits (pktable_base);
create table pktable(ptest1 inet, ptest2 inet, primary key(base1, ptest1), foreign key(base2, ptest2) references
pktable(ptest1, base1)) inherits (pktable_base);
create table pktable(ptest1 inet, ptest2 inet, primary key(base1, ptest1), foreign key(ptest2, base2) references
--- 602,607 ----
***************
*** 943,945 **** begin;
--- 941,1111 ----
update selfref set a = 456 where a = 123;
select a, b from selfref;
commit;
+
+ -- ARRAY FK
+ --
+ 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 (ftest1) REFERENCES PKTABLEFORARRAY;
+ DROP TABLE FKTABLEFORARRAY;
+
+ -- Check create table
+ CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] REFERENCES PKTABLEFORARRAY, ftest2 int );
+ CREATE TABLE FKTABLEFORARRAYMDIM ( ftest1 int[][] REFERENCES PKTABLEFORARRAY, ftest2 int );
+ CREATE TABLE FKTABLEFORARRAYNOTNULL ( ftest1 int[] NOT NULL REFERENCES PKTABLEFORARRAY, ftest2 int );
+
+ -- Insert successful rows into FK TABLE
+ INSERT INTO FKTABLEFORARRAY VALUES ('{1}', 2);
+ INSERT INTO FKTABLEFORARRAY VALUES ('{2}', 3);
+ INSERT INTO FKTABLEFORARRAY VALUES ('{1}', 2);
+ INSERT INTO FKTABLEFORARRAY VALUES ('{3}', 4);
+ INSERT INTO FKTABLEFORARRAY VALUES ('{1}', 2);
+ INSERT INTO FKTABLEFORARRAY VALUES ('{4,5}', 2);
+ INSERT INTO FKTABLEFORARRAY VALUES ('{4,4}', 2);
+ INSERT INTO FKTABLEFORARRAY VALUES (NULL, 1);
+ INSERT INTO FKTABLEFORARRAY VALUES ('{}', 5);
+ INSERT INTO FKTABLEFORARRAY VALUES ('{1,NULL}', 6);
+ INSERT INTO FKTABLEFORARRAY VALUES ('{NULL}', 7);
+ INSERT INTO FKTABLEFORARRAYMDIM VALUES ('{{4,5},{1,2},{1,3}}', 8);
+ INSERT INTO FKTABLEFORARRAYMDIM VALUES ('{{4,5},{NULL,2},{NULL,3}}', 9);
+
+ -- Insert failed rows into FK TABLE
+ INSERT INTO FKTABLEFORARRAY VALUES ('{6}', 3);
+ INSERT INTO FKTABLEFORARRAY VALUES ('{4,6}', 4);
+ INSERT INTO FKTABLEFORARRAY VALUES ('{6,NULL}', 5);
+ INSERT INTO FKTABLEFORARRAY VALUES ('{6,NULL,4,NULL}', 6);
+ INSERT INTO FKTABLEFORARRAYMDIM VALUES ('{{1,2},{6,NULL}}', 7);
+ INSERT INTO FKTABLEFORARRAYNOTNULL VALUES (NULL, 1);
+
+ -- Check FKTABLE
+ SELECT * FROM FKTABLEFORARRAY;
+
+ -- Delete a row from PK TABLE (must fail)
+ DELETE FROM PKTABLEFORARRAY WHERE ptest1=1;
+
+ -- Check FKTABLE for removal of matched row
+ SELECT * FROM FKTABLEFORARRAY;
+
+ -- Update a row from PK TABLE (must fail)
+ UPDATE PKTABLEFORARRAY SET ptest1=7 WHERE ptest1=1;
+
+ -- Check FKTABLE for update of matched row
+ SELECT * FROM FKTABLEFORARRAY;
+
+ DROP TABLE FKTABLEFORARRAY;
+ DROP TABLE FKTABLEFORARRAYNOTNULL;
+ DROP TABLE FKTABLEFORARRAYMDIM;
+
+ -- Allowed references with actions 'RESTRICT' and 'NO ACTION'
+ CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] REFERENCES PKTABLEFORARRAY ON DELETE RESTRICT ON UPDATE RESTRICT, ftest2 int );
+ DROP TABLE FKTABLEFORARRAY;
+ CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] REFERENCES PKTABLEFORARRAY ON DELETE NO ACTION ON UPDATE NO ACTION, ftest2 int );
+ DROP TABLE FKTABLEFORARRAY;
+
+ -- Failed attempts of creating references with actions other than 'RESTRICT' and 'NO ACTION'
+ CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] REFERENCES PKTABLEFORARRAY ON DELETE CASCADE ON UPDATE RESTRICT, ftest2 int );
+ CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] REFERENCES PKTABLEFORARRAY ON DELETE RESTRICT ON UPDATE CASCADE, ftest2 int );
+ CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] REFERENCES PKTABLEFORARRAY ON DELETE CASCADE ON UPDATE CASCADE, ftest2 int );
+ CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] REFERENCES PKTABLEFORARRAY ON DELETE CASCADE ON UPDATE NO ACTION, ftest2 int );
+ CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] REFERENCES PKTABLEFORARRAY ON DELETE NO ACTION ON UPDATE CASCADE, ftest2 int );
+ CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] REFERENCES PKTABLEFORARRAY ON DELETE CASCADE ON UPDATE CASCADE, ftest2 int );
+ CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] REFERENCES PKTABLEFORARRAY ON DELETE SET NULL ON UPDATE SET NULL, ftest2 int );
+ CREATE TABLE FKTABLEFORARRAY ( ftest1 int[] REFERENCES PKTABLEFORARRAY ON DELETE SET DEFAULT ON UPDATE SET DEFAULT, ftest2 int );
+
+ -- Cleanup
+ 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)[] REFERENCES PKTABLEFORARRAY, 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);
+
+ -- Cleanup
+ DROP TABLE FKTABLEFORARRAY;
+ DROP TABLE PKTABLEFORARRAY;
+
+ -- Composite primary keys (unsupported)
+ 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');
+
+ -- Must fail (cannot use arrays in composite foreign keys - use an array of composite types)
+ CREATE TABLE FKTABLEFORARRAY ( fid1 CHAR(1), fid2 CHAR(1)[], ftest2 text, FOREIGN KEY (fid1, fid2) REFERENCES PKTABLEFORARRAY (id1, id2));
+
+ -- Cleanup
+ DROP TABLE PKTABLEFORARRAY;
+
+ -- Test foreign key arrays 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[] 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');
+
+ -- Cleanup
+ DROP TABLE FKTABLEFORARRAY;
+ DROP TABLE PKTABLEFORARRAY;
+
+ -- Create primary table with a primary key array
+ 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;