Does SQL Server Allow Constraint Violations in a Transaction as Long as It's Not Committed Yet

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

SQL Foreign Key before commit

Until and unless you rollback after you insert into the parent table the child table will allow to insert rows corresponding to the newly inserted row in the parent table even if you don't commit after inserting into the parent table. So it is hardly a matter of worry. The impact of the DML operation persists till the next rollback and becomes permanent with a commit statement. So if you don't rollback explicitly after the insert operation in the parent table the child table will always allow insert corresponding to all the rows in the parent table irrespective of any commit operation.

So the problem shouldn't even occur even if the FOREIGN_KEY_CHECKS=1.

Integrity constraint violated just when I commit the transaction

After few days struggling with this, I eventually found what the problem was and, obviously, the solution. As far as I know, the mapping shown above is correct (at least, the application works properly). The only problem was in the database, where there was a trigger for inserting the sequence. This way, everytime I tried to insert an employee (and its address), Hibernate gave me two sequence numbers (one for the parent and another for the child), which were correctly set. However, at the precise moment of doing the commit, Oracle gave me two more sequence numbers, and this time incorrectly set, I mean, the foreign key didn't match the parent's primary key.

But as I need triggers, I have to do one more step: I have to modify these ones so they check whether the :new.ID value is NULL or not. If it is, it means that the trigger has been triggered from outside Hibernate (i.e. another application), and therefore I take the nextval from the sequence, otherwise I leave the value that comes from Hibernate.

How to continue processing after a constraint violation exception?

You can't "continue". A transaction is marked for rollback and the persistence context is unusable after a constraint violation happens.

You can either try to avoid the constraint violation, by checking if the DB contains an entry before persisting/updating, or you run the rest of your code in a separate transaction if a constraint violation happens.

Atomically set SERIAL value when committing transaction

Postgres 9.5 introduced a new feature related to this problem: commit timestamps.

You just need to activate track_commit_timestamp in postgresql.conf (and restart!) to start tracking commit timestamps. Then you can query:

SELECT * FROM tbl
WHERE  pg_xact_commit_timestamp(xmin) >= '2015-11-26 18:00:00+01';

Read the chapter "Commit timestamp tracking" in the Postgres Wiki.

Related utility functions in the manual.

Function volatility is only VOLATILE because transaction IDs (xid) can wrap around per definition. So you cannot create a functional index on it.

You could fake IMMUTABLE volatility in a function wrapper for applications in a limited time frame, but you need to be aware of implications. Related case with more explanation:

• Does PostgreSQL support "accent insensitive" collations?
• How do IMMUTABLE, STABLE and VOLATILE keywords effect behaviour of function?

For many use cases (like yours?) that are only interested in the sequence of commits (and not absolute time) it might be more efficient to work with xmin cast to bigint "directly" (xmin::text::bigint) instead of commit timestamps. (xid is an unsigned integer internally, the upper half that does not fit into a signed integer.) Again, be aware of limitations due to possible xid wraparound.

For the same reason, commit timestamps are not preserved indefinitely. For small to medium databases, xid wraparound hardly ever happens - but it will eventually if the cluster is live for long enough. Read the chapter "Preventing Transaction ID Wraparound Failures" in the manual for details.

SQL: Is it possible to block a table insert just prior to the completion of a transaction?

No, it's not possible to do what you want in MSSQLSever, but it might be in PostGres or Oracle.

Reason part 1: It's not possible to insert to two different tables in the same statement.

Reason part 2: "SQL Server [does not] allow constraint violations in a transaction as long as the transaction has not been committed yet."

Therefore it is not possible in SQLServer to have a single constraint for table insertion of more than one table that will block just prior to the completion of arbitrary transactions.

It's also worth mentioning that what you want is called a deferrable constraint. See more about that here.

---

Related Topics