SQL Server/T-SQL Tutorial/Constraints/Constriant violation

Материал из SQL эксперт
Перейти к: навигация, поиск

A NOT NULL phrase adds a constraint to restrict the input of rows with a null value.

   <source lang="sql">

5> 6> CREATE TABLE T ( 7> int1 int IDENTITY PRIMARY KEY, 8> vch1 varchar(5) 9> CHECK (LEN(vch1) > 0) 10> NOT NULL, 11> vch2 varchar(5) 12> CONSTRAINT CK_LEN_TOO_SHORT 13> CHECK (LEN(vch2) > 0) 14> NOT NULL 15> ) 16> GO 1> 2> INSERT T (vch1) VALUES("40222") 3> INSERT T (vch1) VALUES("4022") 4> INSERT T (vch1) VALUES("r0222") 5> INSERT T DEFAULT VALUES 6> GO Msg 515, Level 16, State 2, Server J\SQLEXPRESS, Line 2 Cannot insert the value NULL into column "vch2", table "master.dbo.T"; column does not allow nulls. INSERT fails. The statement has been terminated. Msg 515, Level 16, State 2, Server J\SQLEXPRESS, Line 3 Cannot insert the value NULL into column "vch2", table "master.dbo.T"; column does not allow nulls. INSERT fails. The statement has been terminated. Msg 515, Level 16, State 2, Server J\SQLEXPRESS, Line 4 Cannot insert the value NULL into column "vch2", table "master.dbo.T"; column does not allow nulls. INSERT fails. The statement has been terminated. Msg 515, Level 16, State 2, Server J\SQLEXPRESS, Line 5 Cannot insert the value NULL into column "vch1", table "master.dbo.T"; column does not allow nulls. INSERT fails. The statement has been terminated. 1> 2> 3> drop table t; 4> GO 1></source>


Exception in a transaction

   <source lang="sql">

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</source>


Trap Both Primary Key and CHECK Constraint Violations

   <source lang="sql">

8> CREATE TABLE T1( 9> pk_col int NOT NULL PRIMARY KEY CHECK (pk_col > 0), 10> ident_col int NOT NULL IDENTITY (1,1) 11> ) 12> 13> DECLARE 14> @myerror AS int 15> INSERT INTO T1 VALUES(0) -- violate the check constraint 16> SET @myerror = @@ERROR 17> IF @myerror = 2627 18> PRINT "PRIMARY KEY constraint violation" 19> ELSE IF @myerror = 547 20> PRINT "CHECK constraint violation" 21> GO Msg 547, Level 16, State 1, Server J\SQLEXPRESS, Line 15 The INSERT statement conflicted with the CHECK constraint "CK__T1__pk_col__39788055". The conflict occurred in database "master", table "dbo.T1", column "pk_col". The statement has been terminated. CHECK constraint violation 1> 2> --Attempt to Capture @@IDENTITY, @@ROWCOUNT, and @@ERROR 3> DECLARE 4> @myerror AS int, 5> @myrowcount AS int, 6> @myidentity AS int 7> INSERT INTO T1 VALUES(10) -- PK violation 8> SELECT @myidentity = @@IDENTITY, 9> @myrowcount = @@ROWCOUNT, 10> @myerror = @@ERROR 11> PRINT "@myidentity: " + CAST(@myidentity AS varchar) 12> PRINT "@myrowcount: " + CAST(@myrowcount AS varchar) 13> PRINT "@myerror  : " + CAST(@myerror AS varchar) 14> GO (1 rows affected) (1 rows affected) @myidentity: 2 (1 rows affected) @myrowcount: 1 (1 rows affected) @myerror  : 0 1> --Output of Successful Attempt to Capture @@IDENTITY, @@ROWCOUNT, and @@ERROR 2> 3> drop table T1; 4> GO</source>


Update statement and check-constraints

   <source lang="sql">

4> CREATE TABLE MySavings( 5> AccountNum Int NOT NULL, 6> Amount Money NOT NULL 7> ); 8> GO 1> CREATE TABLE MyChecking( 2> AcountNum Int NOT NULL, 3> Amount Money NOT NULL 4> ); 5> GO 1> ALTER TABLE MyChecking ADD CONSTRAINT ckMinBalance 2> CHECK (Amount > $100.00) 3> GO 1> INSERT MySavings VALUES (12345, $1000.00) 2> 3> GO (1 rows affected) 1> 2> 3> 4> INSERT MyChecking VALUES (12345, $1000.00) 5> 6> GO (1 rows affected) 1> 2> 3> 4> BEGIN TRANSACTION 5> UPDATE MyChecking SET Amount = Amount - $990.00 6> WHERE AcountNum = 12345 7> UPDATE MySavings SET Amount = Amount + $990.00 8> WHERE AccountNum = 12345 9> COMMIT TRANSACTION 10> GO Msg 547, Level 16, State 1, Server J\SQLEXPRESS, Line 5 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> 3> 4> 5> BEGIN TRANSACTION 6> UPDATE MyChecking SET Amount = Amount - $990.00 7> IF @@ERROR != 0 8> BEGIN 9> ROLLBACK TRANSACTION 10> RETURN 11> END 12> ELSE 13> UPDATE MySavings SET Amount = Amount + $990.00 14> IF @@ERROR != 0 15> BEGIN 16> ROLLBACK TRANSACTION 17> RETURN 18> END 19> ELSE 20> COMMIT TRANSACTION 21> GO Msg 547, Level 16, State 1, Server J\SQLEXPRESS, Line 6 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> 2> drop table MySavings; 3> drop table MyChecking; 4> GO 1></source>