в 13:46, 26 мая 2010

2010-05-26T13:46:16Z

Admin: 1 версия

2010-05-26T10:24:59Z

1 версия

Новая страница

== 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>

← Предыдущая	Версия 13:46, 26 мая 2010
(нет различий)

SQL Server/T-SQL Tutorial/Constraints/Constriant violation - История изменений

в 13:46, 26 мая 2010

Admin: 1 версия