From d6f96ed94e73052f99a2e545ed17a8b2fdc1fb8a Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Wed, 8 Dec 2021 11:09:44 +0100 Subject: 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 Discussion: https://www.postgresql.org/message-id/flat/CACqFVBZQyMYJV=njbSMxf+rbDHpx=W=B7AEaMKn8dWn9OZJY7w@mail.gmail.com --- doc/src/sgml/catalogs.sgml | 12 ++++++++++++ doc/src/sgml/ddl.sgml | 33 ++++++++++++++++++++++++++++++++- doc/src/sgml/ref/alter_table.sgml | 2 +- doc/src/sgml/ref/create_table.sgml | 24 +++++++++++++++++++----- 4 files changed, 64 insertions(+), 7 deletions(-) (limited to 'doc/src/sgml') diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index c1d11be73f7..025db987633 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -2708,6 +2708,18 @@ SCRAM-SHA-256$<iteration count>:&l + + + confdelsetcols int2[] + (references pg_attribute.attnum) + + + If a foreign key with a SET NULL or SET + DEFAULT delete action, the columns that will be updated. + If null, all of the referencing columns will be updated. + + + conexclop oid[] diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 642ea2a70d0..64d90306522 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -1083,10 +1083,41 @@ CREATE TABLE order_items ( manager to null or a default might be useful. + + The actions SET NULL and SET DEFAULT + can take a column list to specify which columns to set. Normally, all + columns of the foreign-key constraint are set; setting only a subset is + useful in some special cases. Consider the following example: + +CREATE TABLE tenants ( + tenant_id integer PRIMARY KEY +); + +CREATE TABLE users ( + tenant_id integer REFERENCES tenants ON DELETE CASCADE, + user_id integer NOT NULL, + PRIMARY KEY (tenant_id, user_id) +); + +CREATE TABLE posts ( + tenant_id integer REFERENCES tenants ON DELETE CASCADE, + post_id integer NOT NULL, + author_id integer, + PRIMARY KEY (tenant_id, post_id), + FOREIGN KEY (tenant_id, author_id) REFERENCES users ON DELETE SET NULL (author_id) +); + + Without the specification of the column, the foreign key would also set + the column tenant_id to null, but that column is still + required as part of the primary key. + + Analogous to ON DELETE there is also ON UPDATE which is invoked when a referenced - column is changed (updated). The possible actions are the same. + column is changed (updated). The possible actions are the same, + except that column lists cannot be specified for SET + NULL and SET DEFAULT. In this case, CASCADE means that the updated values of the referenced column(s) should be copied into the referencing row(s). diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index bc5dcba59ce..8f14e4a5c4a 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -138,7 +138,7 @@ WITH ( MODULUS numeric_literal, REM referential_action in a FOREIGN KEY/REFERENCES constraint is: -{ NO ACTION | RESTRICT | CASCADE | SET NULL | SET DEFAULT } +{ NO ACTION | RESTRICT | CASCADE | SET NULL [ ( column_name [, ... ] ) ] | SET DEFAULT [ ( column_name [, ... ] ) ] } diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index 57d51a676a7..b97bb9ded15 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -108,7 +108,7 @@ WITH ( MODULUS numeric_literal, REM referential_action in a FOREIGN KEY/REFERENCES constraint is: -{ NO ACTION | RESTRICT | CASCADE | SET NULL | SET DEFAULT } +{ NO ACTION | RESTRICT | CASCADE | SET NULL [ ( column_name [, ... ] ) ] | SET DEFAULT [ ( column_name [, ... ] ) ] } @@ -1169,19 +1169,23 @@ WITH ( MODULUS numeric_literal, REM - SET NULL + SET NULL [ ( column_name [, ... ] ) ] - Set the referencing column(s) to null. + Set all of the referencing columns, or a specified subset of the + referencing columns, to null. A subset of columns can only be + specified for ON DELETE actions. - SET DEFAULT + SET DEFAULT [ ( column_name [, ... ] ) ] - Set the referencing column(s) to their default values. + Set all of the referencing columns, or a specified subset of the + referencing columns, to their default values. A subset of columns + can only be specified for ON DELETE actions. (There must be a row in the referenced table matching the default values, if they are not null, or the operation will fail.) @@ -2223,6 +2227,16 @@ CREATE TABLE cities_partdef + + Foreign-Key Constraint Actions + + + The ability to specify column lists in the foreign-key actions + SET DEFAULT and SET NULL is a + PostgreSQL extension. + + + <literal>NULL</literal> <quote>Constraint</quote> -- cgit v1.2.3