Re: [PATCH] Partial foreign key updates in referential integrity triggers

On Wed, Jul 14, 2021 at 6:51 PM Peter Eisentraut <
peter.eisentraut@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