*** a/doc/src/sgml/ddl.sgml --- b/doc/src/sgml/ddl.sgml *************** *** 764,769 **** CREATE TABLE order_items ( --- 764,796 ---- the last table. + + 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 foreign key arrays, + is described in the following example: + + + 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 + ); + + + The above example lists in an array the citizenships held by + a person and enforces referential integrity checks. + + + CASCADE foreign key action *************** *** 852,857 **** CREATE TABLE order_items ( --- 879,891 ---- + When working with foreign key arrays, you are currently limited + to RESTRICT and NO ACTION + options, as the default behaviour for the other cases is not + clearly and universally determined yet. + + + More information about updating and deleting data is in . *** 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 ---- + 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. + + + 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 *************** *** 634,640 **** CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI Delete any rows referencing the deleted row, or update the value of the referencing column to the new value of the ! referenced column, respectively. --- 640,647 ---- 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). *************** *** 643,649 **** CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI SET NULL ! Set the referencing column(s) to null. --- 650,657 ---- SET NULL ! Set the referencing column(s) to null. Foreign key arrays are not ! supported by this action (as the behaviour is not easily determined). *************** *** 652,658 **** CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI SET DEFAULT ! Set the referencing column(s) to their default values. --- 660,667 ---- SET DEFAULT ! 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). *** 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;