Hi, please find attached version 1 of the patch introducing "Array ELEMENT Foreign Keys" support. This new thread and related patch substitute any previous discussion about "Support for foreign keys with arrays", as anticipated in http://archives.postgresql.org/pgsql-hackers/2012-07/msg01098.php
This patch adds: * support for ELEMENT REFERENCES column constraint on array types - e.g. c1 INT[] ELEMENT REFERENCES t1 * support for array ELEMENT foreign key table constraints - e.g. FOREIGN KEY (ELEMENT c1) REFERENCES t1 * support for array ELEMENT foreign keys in multi-column foreign key table constraints - e.g. FOREIGN KEY (c1, ELEMENT c2) REFERENCES t1 (u1, u2) Array ELEMENT foreign keys are a special kind of foreign key constraint requiring the referencing column to be an array of elements of the same type as (or a compatible type to) the referenced column in the referenced table. Array ELEMENT foreign keys are an extension of PostgreSQL and are not included in the SQL standard. An usage example for this feature is the following: CREATE TABLE drivers ( driver_id integer PRIMARY KEY, first_name text, last_name text, ... ); CREATE TABLE races ( race_id integer PRIMARY KEY, title text, race_day DATE, ... final_positions integer[] ELEMENT REFERENCES drivers ); This initial patch present the following limitations: * Only one "ELEMENT" column allowed in a multi-column key - e.g. FOREIGN KEY (c1, ELEMENT c2, ELEMENT c3) REFERENCES t1 (u1, u2, u3) will throw an error * Supported actions: - NO ACTION - RESTRICT As noted in the last 9.2 commitfest, we feel it is important to consolidate the "array ELEMENT foreign key" syntax and to postpone decisions about referential integrity actions, allowing the community to have a clearer understanding of the feature goals and requirements. However, having array_replace() and array_remove() functions already being committed and using our previous patch as a basis, we are confident that a generally accepted syntax will come out in the next months through community collaborative dynamics. The patch includes documentation and an extensive coverage of tests (element_foreign_key.sql regression test file). Co-authors of this patch are Gabriele and Gianni from our Italian team at 2ndQuadrant. Thank you. Cheers, Marco -- Marco Nenciarini - 2ndQuadrant Italy PostgreSQL Training, Services and Support marco.nenciar...@2ndquadrant.it | www.2ndQuadrant.it
Array-ELEMENT-foreign-key-v1.patch.bz2
Description: application/bzip
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers