Deferrable Constraints in SQL Server

So far SQL Server does not support them. What is the problem you are solving?

deferred constraint checking

There is no native way to do deferred constraint checking in SQL Server.

Likely your best option is to insert a NULL value into the foreign key column in the first table until the second record is inserted, and then as part of the same transaction, update the foreign key column in the first table.

I'm interested - what is the business reason for your circular reference? It's definitely an extraordinary requirement.

Also have a look at this thread, on the same topic.

Does SQL Server allow constraint violations in a transaction as long as it's not committed yet?

No, sorry. SQL Server does not allow deferred contraints in a transaction. It was present in SQL Server 6.5, but removed in SQL Server 2000:

SET DISABLE_DEF_CNST_CHK ON

Each individual statement must be consistent etc, regardless of whether it is in a transaction

Some RDBMS do allow this (e.g. Oracle, Postgres, Interbase)

Connect

There is a Microsoft Connect request, created in 2006, asking for this feature:

Option to defer foreign key constraint checking until transaction commit

There are various "chicken and egg" scenarios where it would be desirable to defer the checking of referential integrity constraints until commit time on a transaction.

Allow deferring of referential integrity constraint checking until commit time on a transaction (as an option). Suggest providing an option on BEGIN TRANSACTION that specifies this.

The last response from Microsoft came a decade ago:

Posted by Sameer [MSFT] on 10/13/2006 at 1:35 PM

Hello Greg,

Thanks for the feedback. We are aware of this and looking into it for a future release.

Sameer Verkhedkar

SQL Engine

[MSFT]

Which is Microsoft speak for "go away".

SQL-92 defines it

The feature was defined in July 1992 with SQL-92. An example syntax would be:

BEGIN TRANSACTION
   SET CONSTRAINTS ALL DEFERRED --applies only to the current transaction

   INSERT Customers ...
   INSERT Orders ...
   UPDATE Customers ... --add the thing we were missing

COMMIT TRANSACTION

Why `not deferrable` constraint is deferred when using `with`?

According to the docs non-deferrable unique constraints are checked for each row, contrary to the standards specification that they are checked only at the end of a statement.

When a UNIQUE or PRIMARY KEY constraint is not deferrable, PostgreSQL checks for uniqueness immediately whenever a row is inserted or modified. The SQL standard says that uniqueness should be enforced only at the end of the statement...

But this exception to the standards spec is only for uniqueness, not for foreign key. Foreign key constraints are checked at the end of the statement if they are either not deferrable or if they are deferrable but not deferred. Since any problems have been cured by the end of the statement in your first two examples, there is no error.

NOT DEFERRABLE versus DEFERRABLE INITIALLY IMMEDIATE

With DEFERRABLE INITIALLY IMMEDIATE you can defer the constraints on demand when you need it.

This is useful if you normally want to check the constraints at statement time, but for e.g. a batch load want to defer the checking until commit time.

The syntax how to defer the constraints is different for the various DBMS though.

With NOT DEFERRABLE you will never ever be able to defer the checking until commit time.

Violating foreign key constraint with deferred constraint

You probably have some issue with the transaction demarcation. I ran a simple test and works wells.

insert into foo (id, foo) values (1, 'Anne');

start transaction;

insert into access (id, foo_id) values (101, 1);

insert into access (id, foo_id) values (107, 7); -- 7 does not exist yet...

insert into foo (id, foo) values (7, 'Ivan'); -- 7 now exists!

commit; -- at this point all is good

See running example at DB Fiddle.

SET CONSTRAINTS ALL DEFERRED not working as expected

Only DEFERRABLE constraints can be deferred.

Let me suggest superior alternatives first:

1. `INSERT` in order

Reverse the sequence of the INSERT statements and nothing needs to be deferred. Simplest and fastest - if at all possible.

2. Single command

Do it in a single command. Then still nothing needs to be deferred, as non-deferrable constraints are checked after each command and CTEs are considered to be part of single command:

WITH ins1 AS (
   INSERT INTO b(j) VALUES(2)
   )
INSERT INTO a(i) VALUES(2);

While being at it, you can reuse the values for the first INSERT: safer / more convenient for certain cases or multi-row inserts:

WITH ins1 AS (
   INSERT INTO b(j) VALUES(3)
   RETURNING j
   )
INSERT INTO a(i)
SELECT j FROM ins1;

But I need deferred constraints! (Really?)

ALTER TABLE b ADD CONSTRAINT fkey_ij FOREIGN KEY (j)
   REFERENCES a (i) MATCH SIMPLE
   ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE;  -- !!!

Then your original code works (a bit slower, as deferred constraints add cost).

db<>fiddle here

Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?

My original answer quoted the manual:

Referential actions other than the NO ACTION check cannot be deferred,
even if the constraint is declared deferrable.

But that was misleading as it only applies to "referential actions", i.e. what happens ON UPDATE or ON DELETE to rows in the referenced table. The case at hand is not one of those - as @zer0hedge pointed out.

Deferrable Constraints in SQL Server