Re: [PROPOSAL] ON DELETE SET NULL () for Foreign Key Constraints

On Sat, Jan 19, 2019 at 5:12 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Paul Martinez <hellopfm@gmail.com> writes:
> > I have a proposal for a feature to add to Postgres. I believe it is a natural
> > extension to the current standard SQL ON DELETE SET NULL behavior when using
> > composite foreign keys. The basic idea is that you can specify which columns to
> > set to NULL in the DELETE trigger created by a foreign key constraint.
>
> This seems like kind of a kluge, because it can only work in MATCH SIMPLE
> mode, not MATCH FULL or MATCH PARTIAL.  (We don't have MATCH PARTIAL atm,
> but it's in the spec so I imagine somebody will get around to implementing
> it someday.  Anyway MATCH FULL is there now.)  In the latter two modes,
> setting a subset of the referencing columns to null isn't sufficient to
> make the row pass the constraint.

I don't see why this is an issue. Currently Postgres allows you to combine
the foreign key features in non-sensical ways. For example, you can create a
not nullable column that has ON DELETE SET NULL behavior:

CREATE TABLE foo (a int PRIMARY KEY);
CREATE TABLE bar (b int NOT NULL REFERENCES foo(a) ON DELETE SET NULL);
INSERT INTO foo VALUES (1);
INSERT INTO bar VALUES (1);
DELETE FROM foo WHERE a = 1;
ERROR:  null value in column "b" violates not-null constraint

The incongruence of MATCH FULL with this feature doesn't seem like a problem.
We could raise an error when MATCH FULL and a proper subset of the constrained
columns are supplied in the SET NULL clause if we were worried about users
mis-using the feature, but we don't raise an error for the similar logic error
above. I don't entirely understand the use-cases for MATCH PARTIAL, but it
doesn't seem like combining MATCH PARTIAL with this feature would be blatantly
wrong. This feature provides a real benefit when using MATCH SIMPLE, which is
the default behavior.

There are good reasons for using denormalizing tenant_ids in a multi-tenant
application beyond just performance. It actually improves referential integrity
because it makes mixing data between tenants impossible. Consider the following
example:

-- denormalized tenant_id, but simple primary keys
CREATE TABLE tenants (id int PRIMARY KEY);
CREATE TABLE users (tenant_id int REFERENCES tenants, id int PRIMARY KEY);
CREATE TABLE messages (
  tenant_id int REFERENCES tenants,
  id int PRIMARY KEY,
  from_id int REFERENCES users,
  to_id int REFERENCES users,
  content text
);
-- Create three tenants
INSERT INTO tenants VALUES (1), (2), (3);
-- Create users in tenants 1 and 2
INSERT INTO users VALUES (1, 101), (2, 102);
-- Create message in tenant 3 sent from user in tenant 1 to user in tenant 2
INSERT INTO messages VALUES (3, 201, 101, 102, 'poor referential integrity');

If you create the users and messages tables with composite primary keys the
last query will fail:

-- composite primary keys of the form (tenant_id, id)
CREATE TABLE cpk_users (
  tenant_id int REFERENCES tenants,
  id int,
  PRIMARY KEY (tenant_id, id)
);
CREATE TABLE cpk_messages (
  tenant_id int REFERENCES tenants,
  id int,
  from_id int,
  to_id int,
  content text,
  PRIMARY KEY (tenant_id, id),
  FOREIGN KEY (tenant_id, from_id) REFERENCES cpk_users,
  FOREIGN KEY (tenant_id, to_id) REFERENCES cpk_users
);
-- Create cpk_users in tenants 1 and 2
INSERT INTO cpk_users VALUES (1, 101), (2, 102);
-- Create cpk_message in tenant 3 sent from user in tenant 1 to user in tenant 2
INSERT INTO cpk_messages VALUES (3, 201, 101, 102, 'great referential
integrity');
ERROR:  insert or update on table "cpk_messages" violates foreign key
        constraint "cpk_messages_tenant_id_from_id_fkey"
DETAIL:  Key (tenant_id, from_id)=(3, 101) is not present in table "cpk_users".

So there are strong reasons in favor of using composite primary keys. Postgres
has great support for composite primary and foreign keys, but SET NULL behavior
that would have worked fine in the schema using simple primary keys no longer
works in the composite primary key schema. Users could manually implement
triggers to get the desired behavior (as I have done in the use-case that led
me to think of this feature), but it'd be great if switching to composite
primary keys didn't force the user to make compromises elsewhere.

- Paul

On Sat, Jan 19, 2019 at 5:12 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Paul Martinez <hellopfm@gmail.com> writes:
> > I have a proposal for a feature to add to Postgres. I believe it is a natural
> > extension to the current standard SQL ON DELETE SET NULL behavior when using
> > composite foreign keys. The basic idea is that you can specify which columns to
> > set to NULL in the DELETE trigger created by a foreign key constraint.
>
> This seems like kind of a kluge, because it can only work in MATCH SIMPLE
> mode, not MATCH FULL or MATCH PARTIAL.  (We don't have MATCH PARTIAL atm,
> but it's in the spec so I imagine somebody will get around to implementing
> it someday.  Anyway MATCH FULL is there now.)  In the latter two modes,
> setting a subset of the referencing columns to null isn't sufficient to
> make the row pass the constraint.
>
>                         regards, tom lane