SQL Server/T-SQL/Constraints/Column Constraints
Содержание
Add constraints for two columns
<source lang="sql">
1> CREATE TABLE T ( 2> int1 int IDENTITY PRIMARY KEY, 3> vch1 varchar(5) CHECK (LEN(vch1) > 0), 4> vch2 varchar(5) CONSTRAINT CK_LEN_TOO_SHORT CHECK (LEN(vch2) > 0) 5> ) 6> GO 1> 2> INSERT T (vch1, vch2) VALUES("a","b") 3> GO (1 rows affected) 1> 2> INSERT T (vch1, vch2) VALUES("","b") 3> GO Msg 547, Level 16, State 1, Server sqle\SQLEXPRESS, Line 2 The INSERT statement conflicted with the CHECK constraint "CK__T__vch1__7F01C5FD". The conflict occurred in database "master", table "dbo.T", column "vch1". The statement has been terminated. 1> INSERT T (vch1, vch2) VALUES("a","") 2> GO Msg 547, Level 16, State 1, Server sqle\SQLEXPRESS, Line 1 The INSERT statement conflicted with the CHECK constraint "CK_LEN_TOO_SHORT". The conflict occurred in database "master", table "dbo.T", column "vch2". The statement has been terminated. 1> INSERT T DEFAULT VALUES 2> GO (1 rows affected) 1> 2> select * from t 3> GO int1 vch1 vch2
----- -----
1 a b 4 NULL NULL
(2 rows affected) 1> drop table t 2> GO 1>
</source>
Constraint check (Options)
<source lang="sql">
1> 2> CREATE TABLE ClassGrades( 3> ClassID int, 4> StudentID int, 5> GradeLetter varchar(2), 6> Constraint PK_ClassGrades PRIMARY KEY(ClassID, StudentID), 7> Constraint CK_GradeRange_ClassID CHECK (LEFT(UPPER(GradeLetter),1) LIKE "[A-F]" AND ClassID < 1000) 8> ) 9> GO 1> 2> INSERT ClassGrades VALUES(1, 1, "C+") 3> GO Msg 2627, Level 14, State 1, Server sqle\SQLEXPRESS, Line 2 Violation of PRIMARY KEY constraint "PK_ClassGrades". Cannot insert duplicate key in object "dbo.ClassGrades". The statement has been terminated. 1> INSERT ClassGrades VALUES(1, 2, "A+") 2> GO Msg 2627, Level 14, State 1, Server sqle\SQLEXPRESS, Line 1 Violation of PRIMARY KEY constraint "PK_ClassGrades". Cannot insert duplicate key in object "dbo.ClassGrades". The statement has been terminated. 1> INSERT ClassGrades VALUES(1, 3, "V-") 2> GO (1 rows affected) 1> INSERT ClassGrades VALUES(1001, 1, "A") 2> GO (1 rows affected) 1> INSERT ClassGrades VALUES(999, 2, "A") 2> GO (1 rows affected) 1> select * from ClassGrades 2> GO ClassID StudentID GradeLetter
----------- -----------
1 1 A 1 2 B- 1 3 V- 999 2 A 1001 1 A
(5 rows affected) 1> 2> drop table ClassGrades 3> GO 1> 2>
</source>
Constraint: PRIMARY KEY
<source lang="sql">
1> 2> CREATE TABLE T ( 3> int1 int PRIMARY KEY, 4> bit1 bit NOT NULL DEFAULT 0 5> ) 6> GO 1> INSERT T (int1, bit1) VALUES (1, 1) 2> GO (1 rows affected) 1> INSERT T (int1, bit1) VALUES (2, 0) 2> GO (1 rows affected) 1> INSERT T (int1) VALUES (3) 2> GO (1 rows affected) 1> INSERT T (bit1) VALUES (1) 2> GO Msg 515, Level 16, State 2, Server sqle\SQLEXPRESS, Line 1 Cannot insert the value NULL into column "int1", table "master.dbo.T"; column does not allow nulls. INSERT fails. The statement has been terminated. 1> INSERT T (int1, bit1) VALUES (3,1) 2> GO Msg 2627, Level 14, State 1, Server sqle\SQLEXPRESS, Line 1 Violation of PRIMARY KEY constraint "PK__T__07970BFE". Cannot insert duplicate key in object "dbo.T". The statement has been terminated. 1> 2> select * from t 3> GO int1 bit1
----
1 1 2 0 3 0
(3 rows affected) 1> 2> drop table t 3> GO 1> 2>
</source>
Data length: greater than 0
<source lang="sql">
1> CREATE TABLE T ( 2> int1 int IDENTITY PRIMARY KEY, 3> vch1 varchar(5) CHECK (LEN(vch1) > 0) NOT NULL, 4> vch2 varchar(5) CONSTRAINT CK_LEN_TOO_SHORT CHECK (LEN(vch2) > 0) NOT NULL 5> ) 6> GO 1> INSERT T (vch1, vch2) VALUES("a","b") 2> GO (1 rows affected) 1> INSERT T (vch1, vch2) VALUES("","b") 2> GO Msg 547, Level 16, State 1, Server sqle\SQLEXPRESS, Line 1 The INSERT statement conflicted with the CHECK constraint "CK__T__vch1__02D256E1". The conflict occurred in database "master", table "dbo.T", column "vch1". The statement has been terminated. 1> INSERT T (vch1, vch2) VALUES("a","") 2> GO Msg 547, Level 16, State 1, Server sqle\SQLEXPRESS, Line 1 The INSERT statement conflicted with the CHECK constraint "CK_LEN_TOO_SHORT". The conflict occurred in database "master", table "dbo.T", column "vch2". The statement has been terminated. 1> INSERT T DEFAULT VALUES 2> GO Msg 515, Level 16, State 2, Server sqle\SQLEXPRESS, Line 1 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> select * from t 3> GO int1 vch1 vch2
----- -----
1 a b
(1 rows affected) 1> 2> drop table t 3> GO 1> 2>
</source>
Two constraints for one single column: Not NULL and default value
<source lang="sql">
1> CREATE TABLE T ( 2> int1 int IDENTITY PRIMARY KEY, 3> bit1 bit NOT NULL DEFAULT 0 4> ) 5> GO 1> INSERT T (bit1) VALUES (1) 2> GO (1 rows affected) 1> INSERT T (bit1) VALUES (0) 2> GO (1 rows affected) 1> INSERT T DEFAULT VALUES 2> GO (1 rows affected) 1> INSERT T (int1, bit1) VALUES (4,1) 2> GO Msg 544, Level 16, State 1, Server sqle\SQLEXPRESS, Line 1 Cannot insert explicit value for identity column in table "T" when IDENTITY_INSERT is set to OFF. 1> 2> select * from t 3> GO int1 bit1
----
1 1 2 0 3 0
(3 rows affected) 1> 2> drop table t 3> GO 1>
</source>