|
This release includes a feature I added [1] to support partial foreign key updates in referential integrity triggers! This is useful for schemas that use a denormalized tenant id across multiple tables, as might be common in a multi-tenant application: 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
);
This schema has a problem. When you delete a user, it will try to set both the tenant_id and author_id columns on the posts table to 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).
When we delete a user, we really only want to clear the author_id column in the posts table, and we want to leave the tenant_id column untouched. The feature I added is a small syntax extension to support doing exactly this. You can provide an explicit column list to the ON DELETE SET NULL / ON DELETE SET DEFAULT actions: 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)
-- Clear only author_id, not tenant_id
REFERENCES users ON DELETE SET NULL (author_id)
-- ^^^^^^^^^^^
);
I initially encountered this problem while converting a database to use composite primary keys in preparation for migrating to Citus [2], and it required adding custom triggers for every single foreign key we created. Now it can be handled entirely by Postgres![1]: https://www.postgresql.org/message-id/flat/CACqFVBZQyMYJV%3D... [2]: https://www.citusdata.com/ |