Adding custom constraints in model builder: Deferrable SQL Constraints

[bugproof]

Some databases support deferrable columns (marked with DEFERRED). Is there any way to mark somehow entity property so it will map with DEFERRED in OnModelCreating override?

https://begriffs.com/posts/2017-08-27-deferrable-sql-constraints.html#why-defer

[bugproof]

Moved here: https://github.com/npgsql/Npgsql.EntityFrameworkCore.PostgreSQL/issues/383

[roji]

Took another look at this because of https://github.com/npgsql/efcore.pg/issues/1836 - deferrable constraints seem to be part of the SQL standard, and somewhat supported across databases. Here's a mapping of the landscape:

PostgreSQL

Doc page: CREATE TABLE, SET CONSTRAINTS

• Constraints can be configured as DEFERRABLE INITIALLY DEFERRED, DEFERRABLE INITIALLY IMMEDIATE, or NOT DEFERRABLE (the default)
• SET CONSTRAINTS can be used to make INITIALLY IMMEDIATE constraints deferred in the current transaction.
• Supports primary key, foreign key, unique and exclude constraints (but not check or "not null" constraints which are always immediate).

Oracle

Supported as in PostgreSQL.

• ALTER SESSION can be used to make INITIALLY IMMEDIATE constraints deferred in the current transaction.
• Constraints can also be disabled/enabled on the table (so outside of the current session) with ALTER TABLE tab1 MODIFY CONSTRAINT ....

Sqlite

Partially supported.

• Only foreign key constraints are deferrable.
• Foreign keys can be either immediate or deferred - there's no concept of "deferrable with this or that default". However, Sqlite does support all standard syntax combinations, like PG and Oracle.
• The defer_foreign_keys pragma allows global disabling of all constraints, regardless of how they're defined.

SQL Server

Not supported

MySQL

Not supported (and other resources).

Note that EF Core's update pipeline apparently doesn't support cycles at the moment,

[roji]

Triage decision: this makes sense as a relational feature, putting in the backlog.

[benlongo]

Example of a concrete use case:

We have a table with an integer column that provides a total order. I don't believe it is currently possible to swap the order of two entities in a single transaction without custom SQL.

[karlosss]

any progress on this? how do i currently solve chicken-and-egg problems such as a manager has to work in a company and a company has to have a manager?

[roji]

@karlosss as with most unsupported schema (DDL) operations, you can simply use SQL in your migrations. It isn't feasible for EF to cover all possible DDL variants across all databases, and even if we do think this one should be supported, it will likely be a while before we get around to it.

[bbascarevic]

@roji While it's possible to add this as a part of the migration, how do we make it a part of the model snapshot as well? HasData works both for migrations and snapshots, would a HasDDL(string upDdl, string downDdl) be possible that would cover both this and many other DDL-related use cases?

[roji]

@bbascarevic if you go with custom SQL, then that's just part of your migration, and cannot be part of the model snapshot; the snapshot represents, well, a snapshot of the database state, and since EF has no knowledge or representation of this aspect, it can't be part of it.

But you generally shouldn't need to have such a thing in the model. You can continue managing the constraint (or whatever) in later migrations in the same way, via custom SQL.