SQL Server / Intermediate / Lesson 7

Constraints, transactions, and error recovery

Combine several state changes into one business outcome. Use declarative rules, explicit transactions, XACT_ABORT, TRY/CATCH, XACT_STATE, and THROW responsibly.

About 4 min readContent reviewed 2026-10-23

On this page

Lesson goals

Match constraints to persistent invariants.
Explain commit, rollback, and transaction state.
Design a transaction-owning error boundary.

Enforce durable invariants

PRIMARY KEY, UNIQUE, FOREIGN KEY, NOT NULL, and CHECK protect different invariants. Use them even when the application validates the same rule, because imports and other services may write later. A CHECK predicate rejects FALSE but can allow UNKNOWN, so CHECK (Quantity > 0) does not replace NOT NULL. Name constraints clearly so a failure can be mapped to its rule.

A transaction does not make invalid values valid; constraints and transaction boundaries complement each other. For a transfer, each balance row must obey its local rules, while the debit and credit must commit together. Cross-row rules may require a carefully chosen transaction strategy rather than a CHECK referencing other rows through hidden procedural behavior.

Choose the atomic unit

BEGIN TRANSACTION opens an explicit transaction, COMMIT accepts its changes, and ROLLBACK cancels them. SQL Server commonly uses autocommit for individual statements outside an explicit transaction. Two successful autocommit statements are not one atomic business operation. Keep transaction duration short and exclude user interaction or remote HTTP calls while holding database resources.

The example uses a local temporary table because its changes participate in rollback; table-variable transaction behavior should not be assumed to demonstrate the same effect. The final balance remains 100 after rollback. This is a theoretical isolated demonstration, not an instruction to create objects on an existing configured server.

sql Rollback a temporary-table change

CREATE TABLE #Balance (Id int PRIMARY KEY, Amount decimal(10,2));
INSERT #Balance VALUES (1,100);
BEGIN TRANSACTION;
UPDATE #Balance SET Amount=Amount-20 WHERE Id=1;
ROLLBACK TRANSACTION;
SELECT Amount FROM #Balance WHERE Id=1;
DROP TABLE #Balance;

Expected output

Amount
100.00

The update is rolled back while the insert performed before the transaction remains. The temporary table is then explicitly removed.

Handle failure without hiding it

A transaction-owning unit can SET XACT_ABORT ON, begin work in TRY, commit on success, and in CATCH roll back when XACT_STATE() is nonzero before rethrowing. XACT_STATE returns 1 for an active committable transaction, -1 for an active uncommittable one, and 0 for none. An uncommittable transaction cannot be rescued by issuing COMMIT.

THROW without arguments inside CATCH preserves the original error. XACT_ABORT affects many run-time errors and is honored by THROW, but it does not turn all compile errors or connection interruptions into catchable failures. The example deliberately raises a user error and reports its number after cleanup. Application code still needs to observe the raised failure.

sql Inspect state after deliberate rollback

SET XACT_ABORT ON;
BEGIN TRY
 BEGIN TRANSACTION;
 THROW 50001, 'Training rule rejected.', 1;
 COMMIT TRANSACTION;
END TRY
BEGIN CATCH
 IF XACT_STATE()<>0 ROLLBACK TRANSACTION;
 SELECT ERROR_NUMBER() AS error_number, XACT_STATE() AS after_cleanup;
END CATCH;

Expected output

error_number after_cleanup
50001        0

The teaching snippet reports instead of rethrowing so its state is visible. A production transaction boundary should normally use THROW after cleanup.

Define who owns the transaction

A stored procedure may be called inside an existing transaction. SQL Server nested BEGIN TRANSACTION statements increase @@TRANCOUNT, but an inner COMMIT does not independently make its changes durable. An unqualified ROLLBACK can roll back the entire transaction. Therefore a procedure must document whether it owns the transaction or participates in a caller-owned unit.

Do not paste a transaction-owning template into every helper procedure. Either require no ambient transaction and enforce that contract, or implement a reviewed participation/savepoint strategy with clear handling of uncommittable states. Savepoints do not make an uncommittable transaction committable. Test both standalone and nested calls; transaction-count mismatches are interface bugs.

Test the unhappy path explicitly

Place a deliberate failure after the first of two related changes and verify that neither remains. Also test a duplicate key, missing parent, invalid amount, and a zero-row conditional update. Zero affected rows is not automatically an exception, so the business operation may need to THROW deliberately before committing incomplete work.

Capture diagnostic number, procedure, and line for trusted logs while returning an appropriate application response. Never swallow an error and report success merely because rollback completed. Distinguish a rejected business request from an unavailable database and from an unknown outcome after connection loss. A robust transaction contract includes all three outcomes, not only the normal result.

Common errors and fixes

The transaction cannot be committed.

Why it happens: An error left XACT_STATE() at -1.
How to fix it: Roll back and propagate the failure.
Prevent it next time: Inspect state in the owning CATCH boundary.

A CHECK accepted NULL.

Why it happens: The predicate evaluated to UNKNOWN.
How to fix it: Add NOT NULL when the attribute is required.
Prevent it next time: Test missing and invalid values separately.

An inner rollback erased caller work.

Why it happens: Transaction ownership was undocumented.
How to fix it: Adopt a clear ownership or participation contract.
Prevent it next time: Test nested invocation explicitly.

Check your understanding

1. A debit affects one row and a credit affects zero. May the transaction commit?

Show worked solution

Not if both are required for one transfer. Capture each row count, raise a business error when either is wrong, and let the owning CATCH roll back the complete operation.

2. Does an inner COMMIT protect its work from a later outer rollback?