3

As stated in the title I am wondering why the following piece of code will note fail with duplicate key error. As a result of the following test, obviously, the update does not check for duplicity after each row. What happens inside? Is this part of the standard of just SQL Server specific behavior? Thanks!

DECLARE @T TABLE(
    x INT PRIMARY KEY
);

INSERT INTO @T VALUES (1)
INSERT INTO @T VALUES (2)

UPDATE @T SET x = x + 1;

-- DUPLICATE KEY ERROR EXPECTED.

SELECT * FROM @T;

--- VALUES HAVE BEEN INCREMENTED. NO ERROR THROWN.
3
  • 1
    Were you using MySQL before? Jun 4, 2013 at 9:56
  • I was, but I dont think that I have every encountered it. From what I understand the error would be thrown in MySQL as part of its non-ANSI behavior. Thank you for the fiddle, however the link does not seem to work. Jun 5, 2013 at 10:38
  • I have corrected the link. Jun 5, 2013 at 11:55

1 Answer 1

6

The checks (about UNIQUE, PRIMARY KEY and other, NOT NULL and CHECK constraints) are done after the whole UPDATE statement has finished. This is the expected behaviour in SQL. Operations/statements are performed on sets (of rows), not on each row individually.(*)

And while for NOT NULL and CHECK constraints, it doesn't matter if the checking is done after each row change or after the whole statement, for unique constraints (UNIQUE and PRIMARY KEY) it does matter.

In fact, the SQL standard has 2 options. The checks are either done after the statement (when the constraint is NOT DEFERRABLE) or even later, at the end of the transaction (when the constraint is DEFERRABLE) and the statement is part of a transaction.

SQL-Server supports only NOT DEFERRABLE constraints, so checks are after every statement and not at the end of transactions.


(*)
Perhaps you have been used to MySQL, which shows this non-standard behaviour, checking constraints after each row update (or insert.) See the SQL-Fiddle where the update fails (if you change the DBMS from SQL-Server to MySQL.)

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.