On 05.01.21 22:40, Paul Martinez wrote:
I've created a patch to better support referential integrity constraints when
using composite primary and foreign keys. This patch allows creating a foreign
key using the syntax:
FOREIGN KEY (tenant_id, fk_id) REFERENCES fktable ON DELETE SET NULL (fk_id)
which means that only the fk_id column will be set to NULL when the referenced
row is deleted, rather than both the tenant_id and fk_id columns.
I think this is an interesting feature with a legitimate use case.
I'm wondering a bit about what the ON UPDATE side of this is supposed to
mean. Consider your example:
CREATE TABLE tenants (id serial PRIMARY KEY);
CREATE TABLE users (
tenant_id int REFERENCES tenants ON DELETE CASCADE,
id serial,
PRIMARY KEY (tenant_id, id),
);
CREATE TABLE posts (
tenant_id int REFERENCES tenants ON DELETE CASCADE,
id serial,
author_id int,
PRIMARY KEY (tenant_id, id),
FOREIGN KEY (tenant_id, author_id) REFERENCES users ON DELETE SET NULL
);
INSERT INTO tenants VALUES (1);
INSERT INTO users VALUES (1, 101);
INSERT INTO posts VALUES (1, 201, 101);
DELETE FROM users WHERE id = 101;
ERROR: null value in column "tenant_id" violates not-null constraint
DETAIL: Failing row contains (null, 201, null).
Consider what should happen when you update users.id. Per SQL standard,
for MATCH SIMPLE an ON UPDATE SET NULL should only set to null the
referencing column that corresponds to the referenced column actually
updated, not all of them. PostgreSQL doesn't do this, but if it did,
then this would work just fine.
Your feature requires specifying a fixed column or columns to update, so
it cannot react differently to what column actually updated. In fact,
you might want different referential actions depending on what columns
are updated, like what you can do with general triggers.
So, unless I'm missing an angle here, I would suggest leaving out the ON
UPDATE variant of this feature.
