Menu
Does SQL Server allow constraint violations (i.e. deferred constraints) in a transaction as long as the transaction has not been committed yet?
I have a running, uncommitted transaction and while this transaction is running, I will change my data so that it will violate some constraints (like having duplicate primary keys for example). When I commit the transaction, the data will be in consistent, valid state. Is this generally allowed in SQL and specifically in MS SQL Server?
644
As long as you don't COMMIT or ROLLBACK a transaction, it's still "running" and potentially holding locks. If your client (application or user) closes the connection to the database before committing, any still running transactions will be rolled back and terminated.
A unique constraint violation occurs when an UPDATE or INSERT statement attempts to insert a record with a key that already exists in the table. Take a look at the package that is throwing the error. There are a few things that can cause this, including: 1) If the document(shipper, ASN, work order, etc.)
Solution that is possible to correct such violation is if any insertion violates any of the constraints, then the default action is to reject such operation. Deletion operation: On deleting the tuples in the relation, it may cause only violation of Referential integrity constraints.
A constraint violation is simply a grammatical error or a value that does not adhere to the LDAP schema. For example , you may be creating a user and providing characters that are not allowed for an attribute. Example: The telephone number attribute has schema that allows only numbers.
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)
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".
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 You can disable your constraints while running your transaction, and then reenabling them when you are done.
ALTER TABLE mytable NOCHECK CONSTRAINT myconstraint --... RUN TRANSACTION ALTER TABLE mytable WITH CHECK CHECK CONTRAINT ALL Warning: This will affect all connections.
20
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
