This patch adds basic support of arrays in foreign keys, by allowing to
define a referencing column as an array of elements having the same type
as the referenced column in the referenced table.
Every NOT NULL element in the referencing array is matched against the
referenced table.
Example:
CREATE TABLE pt (
id INTEGER PRIMARY KEY,
...
);
CREATE TABLE ft (
id SERIAL PRIMARY KEY,
pids INTEGER[] REFERENCES pt,
...
);
This patch is for discussion and has been built against HEAD.
It compiles and passes all regressions tests (including specific ones -
see the src/test/regress/sql/foreign_key.sql file).
Empty arrays, multi-dimensional arrays, duplicate elements and NULL
values are allowed.
We had to enforce some limitations, due to the lack (yet) of a clear and
universally accepted behaviour and strategy.
For example, consider the ON DELETE action on the above tables: in case
of delete of a record in the 'pt' table, should we remove the whole row
or just the values from the array?
We hope we can start a discussion from here.
Current limitations:
* Only arrays of the same type as the primary key in the referenced
table are supported
* multi-column foreign keys are not supported (only single column)
* Only RESTRICT and NO ACTION methods for referential integrity
enforcement are currently supported
TODO:
* Improve check for empty arrays, which might interfere with SSI (see below)
* Verify interaction with serializable transactions
AUTHORS:
* Gabriele Bartolini <gabriele.bartol...@2ndquadrant.it>
* Marco Nenciarini <marco.nenciar...@2ndquadrant.it>
Cheers,
Gabriele (and Marco)
--
Gabriele Bartolini - 2ndQuadrant Italia
PostgreSQL Training, Services and Support
gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it
*** 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;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
