PostgreSQL11preview-支援陣列外來鍵約束
標籤
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
《PostgreSQL add 2 DML LOCK TUPLE MODE to 4》
相關文章
- 外來鍵約束
- Javaweb-約束-外來鍵約束JavaWeb
- 關於外來鍵約束
- 約束外來鍵筆記筆記
- AppBoxFuture(七): 分散式外來鍵約束APP分散式
- oracle外來鍵約束的總結Oracle
- 教你mysql如何增加外來鍵約束MySql
- Mysql-基本練習(06-唯一約束、外來鍵約束、新增、刪除單列)MySql
- SQL外來鍵約束的含義及建立SQL
- mysql不能新增外來鍵約束怎麼辦MySql
- mysql~資料完整性考慮~外來鍵約束MySql
- 生成指令碼，得到所有表的外來鍵約束，然後刪除並重建這些約束指令碼
- 聊聊Oracle外來鍵約束（Foreign Key）的幾個操作選項Oracle
- 批量刪除MSSQL 中主外來鍵約束SQL
- 資料遷移無法新增外來鍵約束，錯誤程式碼 1215
- MariaDB資料庫的外來鍵約束例項程式碼介紹詳解資料庫
- 主鍵約束、唯一約束和唯一索引索引
- [資料庫]資料庫中為什麼不推薦使用外來鍵約束資料庫
- 【MySQL】MySQL進階（外來鍵約束、多表查詢、檢視、備份與恢復）MySql
- Typescript 下 Mongoose 外來鍵型別&外來鍵陣列型別定義&型別保護&聯合型別理解TypeScriptGo型別陣列
- 《資料庫系統概論》5.0——常見約束 大學生學習筆記（主鍵 外來鍵）資料庫筆記
- [20180423]關於閃回表與主外來鍵約束.txt
- Mysql關於資料庫是否應該使用外來鍵約束詳解說明創磅MySql資料庫
- 【SQL】15 SQL 約束（Constraints）、NOT NULL 約束、UNIQUE 約束、PRIMARY KEY 約束、FOREIGN KEY 約束、CHECK 約束、DEFAULT約束SQLAINull
- Laravel 學習總結二：get () 和 first () 的區別、@each () 的用法和新增外來鍵約束Laravel
- 支援向量機(SVM)的約束和無約束優化、理論和實現優化
- 主鍵和外來鍵
- sqlserver外來鍵SQLServer
- 約束
- indexedDB 內鍵與外來鍵Index
- cad陣列快捷鍵命令怎麼用 cad環形陣列命令的快捷鍵陣列
- MYSQL的外來鍵MySql
- 10.30 索引，外來鍵索引
- 約束CONSTRAINTAI
- 03約束
- MySQL 約束MySql
- SQL約束SQL
- 約瑟夫環（陣列實現）陣列
- 通過外來鍵找主鍵