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
- Oracle定義約束 外來鍵約束Oracle
- SQL的主鍵和外來鍵約束SQL
- 教你mysql如何增加外來鍵約束MySql
- oracle外來鍵約束的總結Oracle
- MySQL禁用恢復外來鍵約束MySql
- Mysql-基本練習(06-唯一約束、外來鍵約束、新增、刪除單列)MySql
- mysql 刪除老是報外來鍵約束MySql
- AppBoxFuture(七): 分散式外來鍵約束APP分散式
- mysql不能新增外來鍵約束怎麼辦MySql
- SQL外來鍵約束的含義及建立SQL
- mysql啟動和關閉外來鍵約束MySql
- Oracle外來鍵約束中NULL的處理OracleNull
- 詳解外來鍵約束（foreign key）
- 【實驗】【外來鍵】小議外來鍵約束對應用程式的影響
- 外來鍵約束drop table cascade constraintsAI
- SQL SERVER中找出拙劣的約束，索引，外來鍵SQLServer索引
- 資料完整性約束：主鍵、外來鍵、各種約束的建立刪除語句
- 修改外來鍵為validate時需要驗證資料是否符合外來鍵約束
- mysql資料庫匯入外來鍵約束問題MySql資料庫
- 遷移後處理外來鍵約束的問題
- mysql~資料完整性考慮~外來鍵約束MySql
- 資料庫約束 主鍵-唯一性-Check-外來鍵資料庫
- 查詢外來鍵約束、子表欄位等資訊的SQLSQL
- 生成指令碼，得到所有表的外來鍵約束，然後刪除並重建這些約束指令碼
- 解決無法刪除表，提示被外來鍵約束引用
- MySQL·捉蟲動態·DROPDATABASE外來鍵約束的GTIDBUGMySqlDatabaseTiDB
- 查詢(看)表的主鍵、外來鍵、唯一性約束和索引索引
- 關於外來鍵約束和對應主鍵資訊的查詢指令碼指令碼
- oracle主外來鍵鎖＿lock_約束Oracle
- 【Foreign Key】Oracle外來鍵約束三種刪除行為Oracle
- (轉)oracle資料庫中所有外來鍵約束失效SQL語句Oracle資料庫SQL
- 分散式資料庫環境中，外來鍵約束的問題？？分散式資料庫
- ORACLE: 查詢(看)表的主鍵、外來鍵、唯一性約束和索引Oracle索引
- 利用虛擬列實現虛擬刪除的主外來鍵約束
- 批量刪除MSSQL 中主外來鍵約束SQL
- 聊聊Oracle外來鍵約束（Foreign Key）的幾個操作選項Oracle
- oracle鍵約束控制Oracle