Exception in a transaction : Constriant violation « Constraints « SQL Server / T-SQL Tutorial





2> CREATE TABLE MySavings(
3>     AccountNum Int NOT NULL,
4>     Amount Money NOT NULL
5> );
6> GO
1>
2> CREATE TABLE MyChecking(
3>     AcountNum Int NOT NULL,
4>     Amount Money NOT NULL
5> );
6> GO
1>
2> ALTER TABLE MyChecking ADD CONSTRAINT ckMinBalance
3> CHECK (Amount > $100.00)
4> GO
1> INSERT MySavings VALUES (12345, $1000.00)
2> GO

(1 rows affected)
1>
2> INSERT MyChecking VALUES (12345, $1000.00)
3> GO

(1 rows affected)
1>
2>
3> BEGIN TRANSACTION
4> UPDATE MyChecking SET Amount = Amount - $990.00
5> WHERE AcountNum = 12345
6> UPDATE MySavings SET Amount = Amount + $990.00
7> WHERE AccountNum = 12345
8> COMMIT TRANSACTION
9> GO
Msg 547, Level 16, State 1, Server J\SQLEXPRESS, Line 4
The UPDATE statement conflicted with the CHECK constraint "ckMinBalance". The conflict occurred in database "master", table "dbo.MyChecking", column 'Amount'.
The statement has been terminated.

(1 rows affected)
1>
2> drop table MySavings;
3> drop table MyChecking;
4> GO










7.12.Constriant violation
7.12.1.Trap Both Primary Key and CHECK Constraint Violations
7.12.2.Exception in a transaction
7.12.3.A NOT NULL phrase adds a constraint to restrict the input of rows with a null value.
7.12.4.Update statement and check-constraints