EF Core - why ClientSetNull is default OnDelete behavior for optional relations (rather than SetNull)

Question

For optional relationships (when Foreign Key can accept Null), a new ClientSetNull behavior has been introduced since EF Core 2.0 as the default option for delete behavior DeleteBehavior.ClientSetNull. This has SetNull semantics for tracked entities and Restrict (no action) behavior for database records not loaded into memory.

Microsoft docs say that:

If you want the database to also try to propagate null values to child foreign keys even when the child entity is not loaded, then use SetNull. However, note that the database must support this, and configuring the database like this can result in other restrictions, which in practice often makes this option impractical. This is why SetNull is not the default.

But I think it is usually normal to set FK of dependent entities to Null when the associated parent is deleted (every where in db). And also, what's those "other restrictions, which in practice often makes this option impractical.." as claimed above?

Answer 1

Those other restrictions the docs are referring to are, as far as I know, circular or multi path cascades.

MS Sql Server for example does not allow cascades (both delete and set null) if

• the change would cascade to the same table it originated from
• there are multiple cascade paths to the same table. Like table 'A' affects table 'B' and 'C', both 'B' and 'C' affect 'D'.

You can't even create the constraint.

EF core can circumvent this limitation with ClientSetNull. EF handles the set null operation, but it can only do so if all the affected entities are loaded into memory.