标签
PostgreSQL , 约束 , foreign key , 数组 , 外键
背景
通常，数据库的外键约束是一种一行对一行的约束，然而，在某些场景中，可能要求一行匹配多行的约束。例如：
1、表1：商品表，商品ID为主键
2、表2：用户购买的商品列表，商品ID数组
要求表2数组内的每一个元素都在商品表ID字段中存在，同时要求在删除或更新商品表的商品ID时，可以影响到外键。
PostgreSQL本身支持一类外键（兼容SQL标准），同时支持数组类型，为了实现这个功能，同时性能足够的好，可以结合GIN倒排索引。
patch如下
https://commitfest.postgresql.org/17/1252/
数组外键约束语法
FOREIGN KEY (EACH ELEMENT OF column_name) REFERENCES table_name
例如
+ 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[],
+ FOREIGN KEY <emphasis> (EACH ELEMENT OF final_positions) REFERENCES drivers </emphasis>
+ );
ACTION目前仅支持NO ACTION或RESTRICT（理论上是可以支持其他action的。未来版本迭代，相信会支持其他action）
NO ACTION，如果有任何元素不在主表中，则报错，支持延迟CHECK，如事务结束时CHECK。
RESTRICT，同NO ACTION，但是不支持延迟CHECK。
+-- Allowed references with actions (NO ACTION, RESTRICT)
+CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE NO ACTION ON UPDATE NO ACTION, ftest2 int );
+DROP TABLE FKTABLEFORARRAY;
+CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE NO ACTION ON UPDATE RESTRICT, ftest2 int );
+DROP TABLE FKTABLEFORARRAY;
+CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE RESTRICT ON UPDATE NO ACTION, ftest2 int );
+DROP TABLE FKTABLEFORARRAY;
+CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE RESTRICT ON UPDATE RESTRICT, ftest2 int );
+DROP TABLE FKTABLEFORARRAY;
+-- Not allowed references (SET NULL, SET DEFAULT, CASCADE)
+CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE NO ACTION ON UPDATE SET DEFAULT, ftest2 int );
+ERROR: Array Element Foreign Keys support only NO ACTION and RESTRICT actions
+DROP TABLE IF EXISTS FKTABLEFORARRAY;
+NOTICE: table "fktableforarray" does not exist, skipping
+CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE NO ACTION ON UPDATE SET NULL, ftest2 int );
+ERROR: Array Element Foreign Keys support only NO ACTION and RESTRICT actions
+DROP TABLE IF EXISTS FKTABLEFORARRAY;
+NOTICE: table "fktableforarray" does not exist, skipping
+CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE SET NULL ON UPDATE CASCADE, ftest2 int );
+ERROR: Array Element Foreign Keys support only NO ACTION and RESTRICT actions
性能相关
由于主表UPDATE或DELETE时，需要CHECK数组表内的元素是否都能匹配到主表的正在被变更的值，所以为了加速，需要高效的进行“是否满足约束条件”判断，使得主表的DML操作性能影响较小。
对于普通外键，我们使用普通索引就可以，而对于数组外键，可以用GIN索引，加速数组包含、相交的判断。
例如
1、更新需要满足
array_column @> array[主表正在变更的OLD.VALUE] == false
2、删除需要满足
array_column @> array[主表正在删除的old.VALUE] == false
3、延迟判定，可以使用批量操作，只判定一次
array_column && array[主表正在变更或删除的old.VALUEs] == false
描述
+ <para>
+ It is advisable to index the refrencing column using GIN index as it
+ considerably enhances the performance. Also concerning coercion while using the
+ GIN index:
+
+ <programlisting>
+ CREATE TABLE pktableforarray ( ptest1 int2 PRIMARY KEY, ptest2 text );
+ CREATE TABLE fktableforarray ( ftest1 int4[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY, ftest2 int );
+ </programlisting>
+ This syntax is fine since it will cast ptest1 to int4 upon RI checks,
+
+ <programlisting>
+ CREATE TABLE pktableforarray ( ptest1 int4 PRIMARY KEY, ptest2 text );
+ CREATE TABLE fktableforarray ( ftest1 int2[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY, ftest2 int );
+ </programlisting>
+ however, this syntax will cast ftest1 to int4 upon RI checks, thus defeating the
+ purpose of the index.
+ </para>
元数据
1、获取约束定义
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+--------------------------------+------------------+---------------------+------
pg_catalog | pg_get_constraintdef | text | oid | func
pg_catalog | pg_get_constraintdef | text | oid, boolean | func
pg_catalog | pg_get_partition_constraintdef | text | oid | func
(3 rows)
2、约束定义元数据表
Table "pg_catalog.pg_constraint"
Column | Type | Collation | Nullable | Default
---------------+--------------+-----------+----------+---------
conname | name | | not null |
connamespace | oid | | not null |
contype | "char" | | not null |
condeferrable | boolean | | not null |
condeferred | boolean | | not null |
convalidated | boolean | | not null |
conrelid | oid | | not null |
contypid | oid | | not null |
conindid | oid | | not null |
confrelid | oid | | not null |
confupdtype | "char" | | not null |
confdeltype | "char" | | not null |
confmatchtype | "char" | | not null |
conislocal | boolean | | not null |
coninhcount | integer | | not null |
connoinherit | boolean | | not null |
conkey | smallint[] | | |
confkey | smallint[] | | |
conpfeqop | oid[] | | |
conppeqop | oid[] | | |
conffeqop | oid[] | | |
conexclop | oid[] | | |
conbin | pg_node_tree | | |
consrc | text | | |
3、约束类型解释(p, 普通外键约束。e, 数组元素外键约束)
pg_constraint.confreftype
+ <entry><structfield>confreftype</structfield></entry>
+ <entry><type>char[]</type></entry>
+ <entry></entry>
+ <entry>If a foreign key, the reference semantics for each column:
+ <literal>p</literal> = plain (simple equality),
+ <literal>e</literal> = each element of referencing array must have a match
+ </entry>
+ </row>
例子
+ CREATE TABLE available_moves (
+ kind text,
+ move text,
+ description text,
+ PRIMARY KEY (kind, move)
+ );
+
+ CREATE TABLE paths (
+ description text,
+ kind text,
+ moves text[],
+ <emphasis>FOREIGN KEY (kind, EACH ELEMENT OF moves) REFERENCES available_moves (kind, move)</emphasis>
+ );
+
+ INSERT INTO available_moves VALUES ('relative', 'LN', 'look north');
+ INSERT INTO available_moves VALUES ('relative', 'RL', 'rotate left');
+ INSERT INTO available_moves VALUES ('relative', 'RR', 'rotate right');
+ INSERT INTO available_moves VALUES ('relative', 'MF', 'move forward');
+ INSERT INTO available_moves VALUES ('absolute', 'N', 'move north');
+ INSERT INTO available_moves VALUES ('absolute', 'S', 'move south');
+ INSERT INTO available_moves VALUES ('absolute', 'E', 'move east');
+ INSERT INTO available_moves VALUES ('absolute', 'W', 'move west');
+
+ INSERT INTO paths VALUES ('L-shaped path', 'relative', '{LN, RL, MF, RR, MF, MF}');
+ INSERT INTO paths VALUES ('L-shaped path', 'absolute', '{W, N, N}');
+-- EACH-ELEMENT FK CONSTRAINTS
+CREATE TABLE PKTABLEFORARRAY ( ptest1 int PRIMARY KEY, ptest2 text );
+-- Insert test data into PKTABLEFORARRAY
+INSERT INTO PKTABLEFORARRAY VALUES (1, 'Test1');
+INSERT INTO PKTABLEFORARRAY VALUES (2, 'Test2');
+INSERT INTO PKTABLEFORARRAY VALUES (3, 'Test3');
+INSERT INTO PKTABLEFORARRAY VALUES (4, 'Test4');
+INSERT INTO PKTABLEFORARRAY VALUES (5, 'Test5');
+INSERT INTO FKTABLEFORARRAYMDIM VALUES ('{{1,2},{6,NULL}}', 20);
+ERROR: insert or update on table "fktableforarraymdim" violates foreign key constraint "fktableforarraymdim_ftest1_fkey"
+DETAIL: Key (EACH ELEMENT OF ftest1)=({{1,2},{6,NULL}}) is not present in table "pktableforarray".
+INSERT INTO FKTABLEFORARRAYNOTNULL VALUES (NULL, 21);
+ERROR: null value in column "ftest1" violates not-null constraint
+DETAIL: Failing row contains (null, 21).
+-- Check FKTABLE
+SELECT * FROM FKTABLEFORARRAY;
+ ftest1 | ftest2
+----------+--------
+ {1} | 3
+ {2} | 4
+ {1} | 5
+ {3} | 6
+ {1} | 7
+ {4,5} | 8
+ {4,4} | 9
+ | 10
+ {} | 11
+ {1,NULL} | 12
+ {NULL} | 13
+(11 rows)
+
+-- Delete a row from PK TABLE (must fail due to ON DELETE NO ACTION)
+DELETE FROM PKTABLEFORARRAY WHERE ptest1=1;
+ERROR: update or delete on table "pktableforarray" violates foreign key constraint "fktableforarray_ftest1_fkey" on table "fktableforarray"
+DETAIL: Key (EACH ELEMENT OF ptest1)=(1) is still referenced from table "fktableforarray".
+-- Check FKTABLE for removal of matched row
+SELECT * FROM FKTABLEFORARRAY;
+ ftest1 | ftest2
+----------+--------
+ {1} | 3
+ {2} | 4
+ {1} | 5
+ {3} | 6
+ {1} | 7
+ {4,5} | 8
+ {4,4} | 9
+ | 10
+ {} | 11
+ {1,NULL} | 12
+ {NULL} | 13
+(11 rows)
+
+-- Update a row from PK TABLE (must fail due to ON UPDATE NO ACTION)
+UPDATE PKTABLEFORARRAY SET ptest1=7 WHERE ptest1=1;
+ERROR: update or delete on table "pktableforarray" violates foreign key constraint "fktableforarray_ftest1_fkey" on table "fktableforarray"
+DETAIL: Key (EACH ELEMENT OF ptest1)=(1) is still referenced from table "fktableforarray".
小结
PostgreSQL支持丰富的约束功能，包括：
-
主键
-
唯一键
-
NOT NULL
-
check
-
外键
-
排他
-
数组外键
数组外键使用GIN索引，可以实现高效率的是否满足约束判定，使得业务系统又多了一重数据强约束的保障。
另一方面，主外键的锁粒度也进行了细化，早在2013年的版本中，就支持了4种行锁粒度，降低主外键数据操作的锁冲突。原理参考末尾文档。
参考
《会议室预定系统实践(解放开发) - PostgreSQL tsrange(时间范围类型) + 排他约束》
https://commitfest.postgresql.org/17/1252/
https://www.postgresql.org/docs/10/static/sql-createtable.html
