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

On 05.01.21 22:40, Paul Martinez wrote:
> 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).

I was looking through this example to see if it could be adapted for the 
documentation.

The way the users table is defined, it appears that "id" is actually 
unique and the primary key ought to be just (id).  The DELETE command 
you show also just uses the id column to find the user, which would be 
bad if the user id is not unique across tenants.  If the id were unique, 
then the foreign key from posts to users would just use the user id 
column and the whole problem of the ON DELETE SET NULL action would go 
away.  If the primary key of users is indeed supposed to be (tenant_id, 
id), then maybe the definition of the users.id column should not use 
serial, and the DELETE command should also look at the tenant_id column. 
  (The same question applies to posts.id.)

Also, you initially wrote that this is a denormalized schema.  I think 
if we keep the keys the way you show, then this isn't denormalized.  But 
if we considered users.id globally unique, then there would be 
normalization concerns.

What do you think?