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?
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.
If you must (such as a Process to cleanse data from an import file), then put the intermediate data into Temp tables or table variables or staging tables and then only do the actions to the real tables with the constraints after you have cleansed it and made the data correct.
SQL Server does not have deferred contraints option. But in some cases you can use Bulk Insert that supports ignoring constraints without problems of disabling. For more information you can see these links:
Then just before committing transaction, you will have to check the constraints on the whole table manually.
ALTERNATIVE you can use Merge commands. They resolve together, bypassing the constraint intermediary state. Here is an example. Before insert, John has pk id=1, Marta has pk id=2. Firstname and lastname are unique. The Merge command switch places and after the MERGE John has PK id=2 and Marta id=1. Switching Marta and Joe names are not possible with normal update commands.
CREATE TABLE dbo.People
(
Id int NOT NULL IDENTITY,
FirstName NVARCHAR(50) NOT NULL,
LastName NVARCHAR(50) NOT NULL,
CONSTRAINT UQ_People UNIQUE (FirstName, LastName)
);
--SELECT * FROM People;
--TRUNCATE TABLE People;
INSERT INTO People (FirstName, LastName)
VALUES
(N'John', N'Doe'),
(N'Marta', N'Smith'),
(N'Julius','Manfred');
MERGE dbo.People AS P
USING (
SELECT 1 AS Id, 'Marta' AS FirstName, 'Smith' AS LastName
UNION
SELECT 2, 'John', 'Doe'
UNION
SELECT 8, 'Martin', 'Berg') AS Flip
ON P.Id = Flip.Id
WHEN MATCHED AND (P.FirstName <> Flip.FirstName OR P.LastName <> Flip.LastName) THEN
UPDATE
SET P.FirstName = Flip.FirstName,
P.LastName = Flip.LastName
WHEN NOT MATCHED BY TARGET THEN
INSERT
(FirstName,
LastName)
VALUES
(Flip.FirstName,
Flip.LastName)
WHEN NOT MATCHED BY SOURCE THEN DELETE
OUTPUT INSERTED.*, DELETED.*, $action;
SELECT *
FROM People;
