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?
