PostgreSQL 15 preview - Allow specifying column list for foreign key ON DELETE SET null|default actions

作者

digoal

日期

2021-12-10

标签

PostgreSQL , foreign table , on delete set null|default

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=d6f96ed94e73052f99a2e545ed17a8b2fdc1fb8a

Allow specifying column list for foreign key ON DELETE SET actions  
author  Peter Eisentraut <peter@eisentraut.org>   
Wed, 8 Dec 2021 10:09:44 +0000 (11:09 +0100)  
committer   Peter Eisentraut <peter@eisentraut.org>   
Wed, 8 Dec 2021 10:13:57 +0000 (11:13 +0100)  
commit  d6f96ed94e73052f99a2e545ed17a8b2fdc1fb8a  
tree    621d033b72ab7da8a21acb729b41c015b6322747    tree  
parent  e464cb7af317e216fef9bfe19a7c4df542817012    commit | diff  
Allow specifying column list for foreign key ON DELETE SET actions  
Extend the foreign key ON DELETE actions SET NULL and SET DEFAULT by  
allowing the specification of a column list, like  
    CREATE TABLE posts (  
        ...  
        FOREIGN KEY (tenant_id, author_id) REFERENCES users ON DELETE SET NULL (author_id)  
    );  
If a column list is specified, only those columns are set to  
null/default, instead of all the columns in the foreign-key  
constraint.  
This is useful for multitenant or sharded schemas, where the tenant or  
shard ID is included in the primary key of all tables but shouldn't be  
set to null.  
Author: Paul Martinez <paulmtz@google.com>  
Discussion: https://www.postgresql.org/message-id/flat/CACqFVBZQyMYJV=njbSMxf+rbDHpx=W=B7AEaMKn8dWn9OZJY7w@mail.gmail.com  
复制

+DROP TABLE FKTABLE;  
+DROP TABLE PKTABLE;  
+-- Test for ON DELETE SET NULL/DEFAULT (column_list);  
+CREATE TABLE PKTABLE (tid int, id int, PRIMARY KEY (tid, id));  
+CREATE TABLE FKTABLE (tid int, id int, foo int, FOREIGN KEY (tid, id) REFERENCES PKTABLE ON DELETE SET NULL (bar));  
+ERROR:  column "bar" referenced in foreign key constraint does not exist  
+CREATE TABLE FKTABLE (tid int, id int, foo int, FOREIGN KEY (tid, id) REFERENCES PKTABLE ON DELETE SET NULL (foo));  
+ERROR:  column "foo" referenced in ON DELETE SET action must be part of foreign key  
+CREATE TABLE FKTABLE (tid int, id int, foo int, FOREIGN KEY (tid, foo) REFERENCES PKTABLE ON UPDATE SET NULL (foo));  
+ERROR:  a column list with SET NULL is only supported for ON DELETE actions  
+LINE 1: ...oo int, FOREIGN KEY (tid, foo) REFERENCES PKTABLE ON UPDATE ...  
+                                                             ^  
+CREATE TABLE FKTABLE (  
+  tid int, id int,  
+  fk_id_del_set_null int,  
+  fk_id_del_set_default int DEFAULT 0,  
+  FOREIGN KEY (tid, fk_id_del_set_null) REFERENCES PKTABLE ON DELETE SET NULL (fk_id_del_set_null),  
+  FOREIGN KEY (tid, fk_id_del_set_default) REFERENCES PKTABLE ON DELETE SET DEFAULT (fk_id_del_set_default)  
+);  
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'fktable'::regclass::oid ORDER BY oid;  
+                                                pg_get_constraintdef                                                  
+--------------------------------------------------------------------------------------------------------------------  
+ FOREIGN KEY (tid, fk_id_del_set_null) REFERENCES pktable(tid, id) ON DELETE SET NULL (fk_id_del_set_null)  
+ FOREIGN KEY (tid, fk_id_del_set_default) REFERENCES pktable(tid, id) ON DELETE SET DEFAULT (fk_id_del_set_default)  
+(2 rows)  
+  
+INSERT INTO PKTABLE VALUES (1, 0), (1, 1), (1, 2);  
+INSERT INTO FKTABLE VALUES  
+  (1, 1, 1, NULL),  
+  (1, 2, NULL, 2);  
+DELETE FROM PKTABLE WHERE id = 1 OR id = 2;  
+SELECT * FROM FKTABLE ORDER BY id;  
+ tid | id | fk_id_del_set_null | fk_id_del_set_default   
+-----+----+--------------------+-----------------------  
+   1 |  1 |                    |                        
+   1 |  2 |                    |                     0  
+(2 rows)  
+  
 DROP TABLE FKTABLE;  
 DROP TABLE PKTABLE;  
 CREATE TABLE PKTABLE (ptest1 int PRIMARY KEY);  
@@ -1734,6 +1772,39 @@ SELECT * FROM fk_partitioned_fk WHERE b = 142857;  
  2501 | 142857  
 (1 row)  
+-- ON DELETE SET NULL column_list  
+ALTER TABLE fk_partitioned_fk DROP CONSTRAINT fk_partitioned_fk_a_b_fkey;  
+ALTER TABLE fk_partitioned_fk ADD FOREIGN KEY (a, b)  
+  REFERENCES fk_notpartitioned_pk  
+  ON DELETE SET NULL (a);  
+BEGIN;  
+DELETE FROM fk_notpartitioned_pk WHERE b = 142857;  
+SELECT * FROM fk_partitioned_fk WHERE a IS NOT NULL OR b IS NOT NULL ORDER BY a NULLS LAST;  
+  a   |   b      
+------+--------  
+ 2502 |         
+      | 142857  
+(2 rows)  
+  
+ROLLBACK;  
+-- ON DELETE SET DEFAULT column_list  
+ALTER TABLE fk_partitioned_fk DROP CONSTRAINT fk_partitioned_fk_a_b_fkey;  
+ALTER TABLE fk_partitioned_fk ADD FOREIGN KEY (a, b)  
+  REFERENCES fk_notpartitioned_pk  
+  ON DELETE SET DEFAULT (a);  
+BEGIN;  
+DELETE FROM fk_partitioned_fk;  
+DELETE FROM fk_notpartitioned_pk;  
+INSERT INTO fk_notpartitioned_pk VALUES (500, 100000), (2501, 100000);  
+INSERT INTO fk_partitioned_fk VALUES (500, 100000);  
+DELETE FROM fk_notpartitioned_pk WHERE a = 500;  
+SELECT * FROM fk_partitioned_fk ORDER BY a;  
+  a   |   b      
+------+--------  
+ 2501 | 100000  
+(1 row)  
+  
+ROLLBACK;  
复制

期望 PostgreSQL 增加什么功能?

PolarDB for PostgreSQL云原生分布式开源数据库

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.