SQL Server/T-SQL/Constraints/Column Constraints

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

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>
Источник — «http://sqle.ru/index.php?title=SQL_Server/T-SQL/Constraints/Column_Constraints&oldid=6139»