PostgreSQL: "Error parsing attribute "@relation": The onDelete referential action of a relation must not be set to SetNull when a referenced field is required."

[Jolg42]

I do have a problem with my schema migrating from 4.4.0 to 4.6.1 (postgres):

I have a multi tenant application and therefore I do have composite keys in almost every model

model User {
id String
tenant_id String
tenant              Tenant                @relation(fields: [tenant_id], references: [id], onDelete: Cascade)
tariff_id String?
tariff Tariff? @relation(fields: [tenant_id, tariff_id], references: [tenant_id, id], onDelete: SetNull)
...
@@id([tenant_id, id])
@@index(id)
}

This errors with:

Error parsing attribute "@relation": The `onDelete` referential action of a relation must not be set to `SetNull` when a referenced field is required.
Either choose another referential action, or make the referenced fields optional.

The tenant_id is always present in all models as it scopes the items. The tariff_id can be set to null in case the related Tariff is deleted and this is what should happen here.

Originally posted by @pfried in #9380 (comment)

[Jolg42]

@pfried The validation error you see here, is a recent change added in #14673

PostgreSQL allows the action to be SET NULL on a required field, which will error at runtime since NULL will be an invalid value. This validation error protects you from that runtime error.

• Did you write this schema by hand or is this an existing database?
• Does this make sense to you?

Let us know if something is unclear or if this is a problem for you.

We are actually working on changing this from an error to a warning to have better compatibility with existing schemas. The setting doesn't make much sense (allow NULL on a required field, which would crash at runtime) but it might be used in schemas where changing it is not an easy option, so we will warn instead after prisma/prisma-engines#3440

[pfried]

PostgreSQL allows the action to be SET NULL on a required field, which will error at runtime since NULL will be an invalid value. This validation error protects you from that runtime error.

This is true but I think there is still some confusion because logically speaking the relation exists on a compound key (tenant_id and tariff_id) where only one component is required which is the tenant_key where the tenant key is also used for the entity itself as a compoud key (tenant_id + id). You could now think that you can only set the tariff_id to NULL since also the field tariff_id did not have a NOT NULL constraint. For referential integrity this is certainly true.

Postgres on the other hand tries to set both key components to NULL which seems a bad choice but common (also MySQL afaik). I was not able to dig up some documentation but the index seems to enforce a unique and not null constraint.

There is a discussion here about this behaviour and (then) proposed changes for postgres. The Postgres behaviour will changed that with Version 15.0 as per this commit and these release notes. In the latest version you can pass a list of keys which you want to set to null when deleting a related item.

Possible workarounds could be:

1. Add a BEFORE DELETE trigger to set the tariff_id to null before a constraint is checked.
2. Add two constraints: first on tariff_id only which requires it to be unique with a ON DELETE SET NULL, second on the composite key without an action
3. Ommit using a compiste key (when possible) and normally use a ON DELETE SET NULL constraint

Did you write this schema by hand or is this an existing database?

The schema was written by me, so no existing database

Does this make sense to you?

Ah well it takes some brain bending to why this is coming from a logical standpoint regarding referential integrity

[janpio]

@pfried Do you agree that our planned warning then is correct (ignoring the new syntax introduced in PostgreSQL 15)?

The onDelete referential action of a relation should not be set to SetNull when a referenced field is required. We recommend either to choose another referential action, or to make the referenced fields optional.

In our understanding the referential action would indeed always try to set all columns of the foreign key to null, which would fail as one of the columns is required - and hence a constraint error would be thrown.

In the future we could implement this new PostgreSQL 15 feature, and then also loosen our validation here to only show the warning when the affected (so defined in SET NULL (foo)) column is required (which I assume is probably still allowed for some reason).

[pfried]

Yes the warning is correct. (The only issue i have with this that it is true by implicit terms (index) and not because the field is NOT NULL)

Plans sound good, should work for me