On Wed, Jul 14, 2021 at 6:51 PM Peter Eisentraut < peter.eisentr...@enterprisedb.com> wrote:
> > 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. > > > Patch does not apply on head, I am marking the status "Waiting on author" http://cfbot.cputube.org/patch_33_2932.log -- Ibrar Ahmed
