If some rows of a writing query fails a unique constraint, will only those writes be rejected, or will the entire query fail?
SQL Server – Does a Unique Constraint Fail a Multi-Row Write
constraintrowsql serverunique-constraint
Related Question
- Sql-server – Unique constraint on multiple nullable columns Sql Server
- SQL Server – How Unique Key Improves Query Performance
- Sql-server – Why does this UPDATE fail with a unique key constraint violation
- SQL Server – Unique Constraint with Non-Unique Data
- SQL Server – Insert Multiple Values with UNIQUE Constraint
- SQL Server – Find Unique Index Without Unique Constraint
Best Answer
Every individual transaction (statement) in SQL Server is atomic, meaning it passes or fails as a unit.
If the 999,999th record fails on a constraint violation in a 1,000,000 row insert, all of the other rows get rolled back and the table is exactly as it was before the failed statement was attempted.
The same applies for updates and deletes, as well as multiple statements in an explicit transaction. For instance, if I have an explicit transaction that does a
DELETEfollowed by anINSERTand anUPDATE, if I encounter an issue in theUPDATEand issue a manualROLLBACKall of those actions get undone.