SQL Server/T-SQL Tutorial/Constraints/Check — различия между версиями

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

Текущая версия на 13:25, 26 мая 2010

A check constraint uses an expression to qualify records that are acceptable for any Inserts or Updates

   <source lang="sql">

7> 8> CREATE TABLE MyTable( 9> MyID Int IDENTITY(1, 1) NOT NULL 10> ,MyDescription nVarChar(50) NOT NULL 11> , Region nVarChar(10) NOT NULL 12> , CONSTRAINT PK_ID PRIMARY KEY CLUSTERED (MyID) 13> , CONSTRAINT CK_Region CHECK (Region 14> IN("PNW","SW","MT","CENTRAL","EAST","SOUTH")) 15> ) 16> GO 1> 2> drop table MyTable; 3> GO 1> 2></source>


Adding a CHECK Constraint to an Existing Table

   <source lang="sql">

You can add a CHECK constraint to an existing table using ALTER TABLE and ADD CONSTRAINT. The syntax is as follows: ALTER TABLE table_name WITH CHECK | WITH NOCHECK ADD CONSTRAINT constraint_name CHECK ( logical_expression )

CHECK With the CHECK option (the default), existing data is validated against the new CHECK constraint. WITH NOCHECK NOCHECK skips validation of new data, limiting the constraint to validation of new values (inserted or updated).</source>


A table-level check constraint that limits Banker IDs to a specific format

   <source lang="sql">

A statement that defines the check constraint

8> CREATE TABLE Bankers 9> (BankerID CHAR(6) NOT NULL PRIMARY KEY, 10> BankerName VARCHAR(50) NOT NULL, 11> CHECK ((BankerID LIKE "[A-Z][A-Z][0-9][0-9][0-9][0-9]") AND 12> (LEFT(BankerID,2) = LEFT(BankerName,2)))) 13> GO 1> 2> --An INSERT statement that fails due to the check constraint 3> 4> INSERT Bankers VALUES ("Mc4559","Castle Printers, Inc.") 5> 6> 7> drop table Bankers; 8> GO Msg 547, Level 16, State 1, Server J\SQLEXPRESS, Line 4 The INSERT statement conflicted with the CHECK constraint "CK__Bankers__6FFF798F". The conflict occurred in database "master", table "dbo.Bankers". The statement has been terminated.</source>


CHECK Clause

   <source lang="sql">

[CONSTRAINT c_name]

       CHECK [NOT FOR REPLICATION] expression

expression must evaluate to a Boolean value (TRUE or FALSE)

11> CREATE TABLE customer 12> (cust_no INTEGER NOT NULL, 13> cust_group CHAR(3) NULL, 14> CHECK (cust_group IN ("c1", "c2", "c10"))) 15> GO 1> 2> drop table customer; 3> GO</source>


Check Constraint

   <source lang="sql">

4> CREATE TABLE employee 5> ( 6> emp_id int NOT NULL PRIMARY KEY DEFAULT 1000 7> CHECK (emp_id BETWEEN 0 AND 1000), 8> 9> emp_name varchar(30) NULL DEFAULT NULL CONSTRAINT no_nums 10> CHECK (emp_name NOT LIKE "%[0-9]%"), 11> 12> mgr_id int NOT NULL DEFAULT (1) REFERENCES 13> employee(emp_id), 14> 15> entered_date datetime NOT NULL CHECK (entered_date >= 16> CONVERT(char(10), CURRENT_TIMESTAMP, 102)) 17> CONSTRAINT def_today DEFAULT 18> (CONVERT(char(10), GETDATE(), 102)), 19> 20> entered_by int NOT NULL DEFAULT SUSER_ID() 21> CHECK (entered_by IS NOT NULL), 22> 23> CONSTRAINT valid_entered_by CHECK (entered_by=SUSER_ID() AND 24> entered_by <> emp_id), 25> 26> CONSTRAINT valid_mgr CHECK (mgr_id <> emp_id OR emp_id=1), 27> 28> CONSTRAINT end_of_month CHECK (DATEPART(DAY, GETDATE()) < 28) 29> ) 30> GO 1> 2> 3> EXEC sp_helpconstraint employee 4> GO Object Name





employee


constraint_type constraint_name

                                                                           delete_action update_action status_enabled status_for_replication constraint_keys




-----------------------------------------------------
------------- ------------- -------------- ---------------------- ----------------------------------------------------------










CHECK on column emp_id CK__employee__emp_id__66EB10A1

                                                                           (n/a)         (n/a)         Enabled        Is_For_Replication     ([emp_id]>=(0) AND [emp_id]<=(1000))



CHECK on column entered_date CK__employee__entere__6BAFC5BE

                                                                           (n/a)         (n/a)         Enabled        Is_For_Replication     ([entered_date]>=CONVERT([char](10),getdate(),(102)))



CHECK on column entered_by CK__employee__entere__6E8C3269

                                                                           (n/a)         (n/a)         Enabled        Is_For_Replication     ([entered_by] IS NOT NULL)



DEFAULT on column entered_date def_today

                                                                           (n/a)         (n/a)         (n/a)          (n/a)                  (CONVERT([char](10),getdate(),(102)))



DEFAULT on column emp_id DF__employee__emp_id__65F6EC68

                                                                           (n/a)         (n/a)         (n/a)          (n/a)                  ((1000))



DEFAULT on column emp_name DF__employee__emp_na__67DF34DA

                                                                           (n/a)         (n/a)         (n/a)          (n/a)                  (NULL)



DEFAULT on column entered_by DF__employee__entere__6D980E30

                                                                           (n/a)         (n/a)         (n/a)          (n/a)                  (suser_id())



DEFAULT on column mgr_id DF__employee__mgr_id__69C77D4C

                                                                           (n/a)         (n/a)         (n/a)          (n/a)                  ((1))



CHECK Table Level end_of_month

                                                                           (n/a)         (n/a)         Enabled        Is_For_Replication     (datepart(day,getdate())<(28))



FOREIGN KEY FK__employee__mgr_id__6ABBA185

                                                                           No Action     No Action     Enabled        Is_For_Replication     mgr_id




                                                                                                                                             REFERENCES master.dbo.employee (emp_id)



CHECK on column emp_name no_nums

                                                                           (n/a)         (n/a)         Enabled        Is_For_Replication     (NOT [emp_name] like "%[0-9]%")



PRIMARY KEY (clustered) PK__employee__6502C82F

                                                                           (n/a)         (n/a)         (n/a)          (n/a)                  emp_id



CHECK Table Level valid_entered_by

                                                                           (n/a)         (n/a)         Enabled        Is_For_Replication     ([entered_by]=suser_id() AND [entered_by]<>[emp_id])



CHECK Table Level valid_mgr

                                                                           (n/a)         (n/a)         Enabled        Is_For_Replication     ([mgr_id]<>[emp_id] OR [emp_id]=(1))




Table is referenced by foreign key




master.dbo.employee: FK__employee__mgr_id__6ABBA185

1> 2> INSERT employee DEFAULT VALUES 3> GO Msg 547, Level 16, State 1, Server J\SQLEXPRESS, Line 2 The INSERT statement conflicted with the FOREIGN KEY SAME TABLE constraint "FK__employee__mgr_id__6ABBA185". The conflict occurred in database "master", table "dbo.employee", column "emp_id". The statement has been terminated. 1> 2> 3> drop table employee; 4> GO</source>


CHECK (DATEPART(DAY, GETDATE())

   <source lang="sql">

4> CREATE TABLE employee 5> ( 6> emp_id int NOT NULL PRIMARY KEY DEFAULT 1000 7> CHECK (emp_id BETWEEN 0 AND 1000), 8> 9> emp_name varchar(30) NULL DEFAULT NULL CONSTRAINT no_nums 10> CHECK (emp_name NOT LIKE "%[0-9]%"), 11> 12> mgr_id int NOT NULL DEFAULT (1) REFERENCES 13> employee(emp_id), 14> 15> entered_date datetime NOT NULL CHECK (entered_date >= 16> CONVERT(char(10), CURRENT_TIMESTAMP, 102)) 17> CONSTRAINT def_today DEFAULT 18> (CONVERT(char(10), GETDATE(), 102)), 19> 20> entered_by int NOT NULL DEFAULT SUSER_ID() 21> CHECK (entered_by IS NOT NULL), 22> 23> CONSTRAINT valid_entered_by CHECK (entered_by=SUSER_ID() AND 24> entered_by <> emp_id), 25> 26> CONSTRAINT valid_mgr CHECK (mgr_id <> emp_id OR emp_id=1), 27> 28> CONSTRAINT end_of_month CHECK (DATEPART(DAY, GETDATE()) < 28) 29> ) 30> GO 1> 2> 3> EXEC sp_helpconstraint employee 4> GO Object Name





employee


constraint_type constraint_name

                                                                           delete_action update_action status_enabled status_for_replication constraint_keys




-----------------------------------------------------
------------- ------------- -------------- ---------------------- ----------------------------------------------------------










CHECK on column emp_id CK__employee__emp_id__66EB10A1

                                                                           (n/a)         (n/a)         Enabled        Is_For_Replication     ([emp_id]>=(0) AND [emp_id]<=(1000))



CHECK on column entered_date CK__employee__entere__6BAFC5BE

                                                                           (n/a)         (n/a)         Enabled        Is_For_Replication     ([entered_date]>=CONVERT([char](10),getdate(),(102)))



CHECK on column entered_by CK__employee__entere__6E8C3269

                                                                           (n/a)         (n/a)         Enabled        Is_For_Replication     ([entered_by] IS NOT NULL)



DEFAULT on column entered_date def_today

                                                                           (n/a)         (n/a)         (n/a)          (n/a)                  (CONVERT([char](10),getdate(),(102)))



DEFAULT on column emp_id DF__employee__emp_id__65F6EC68

                                                                           (n/a)         (n/a)         (n/a)          (n/a)                  ((1000))



DEFAULT on column emp_name DF__employee__emp_na__67DF34DA

                                                                           (n/a)         (n/a)         (n/a)          (n/a)                  (NULL)



DEFAULT on column entered_by DF__employee__entere__6D980E30

                                                                           (n/a)         (n/a)         (n/a)          (n/a)                  (suser_id())



DEFAULT on column mgr_id DF__employee__mgr_id__69C77D4C

                                                                           (n/a)         (n/a)         (n/a)          (n/a)                  ((1))



CHECK Table Level end_of_month

                                                                           (n/a)         (n/a)         Enabled        Is_For_Replication     (datepart(day,getdate())<(28))



FOREIGN KEY FK__employee__mgr_id__6ABBA185

                                                                           No Action     No Action     Enabled        Is_For_Replication     mgr_id




                                                                                                                                             REFERENCES master.dbo.employee (emp_id)



CHECK on column emp_name no_nums

                                                                           (n/a)         (n/a)         Enabled        Is_For_Replication     (NOT [emp_name] like "%[0-9]%")



PRIMARY KEY (clustered) PK__employee__6502C82F

                                                                           (n/a)         (n/a)         (n/a)          (n/a)                  emp_id



CHECK Table Level valid_entered_by

                                                                           (n/a)         (n/a)         Enabled        Is_For_Replication     ([entered_by]=suser_id() AND [entered_by]<>[emp_id])



CHECK Table Level valid_mgr

                                                                           (n/a)         (n/a)         Enabled        Is_For_Replication     ([mgr_id]<>[emp_id] OR [emp_id]=(1))




Table is referenced by foreign key




master.dbo.employee: FK__employee__mgr_id__6ABBA185

1> 2> INSERT employee DEFAULT VALUES 3> GO Msg 547, Level 16, State 1, Server J\SQLEXPRESS, Line 2 The INSERT statement conflicted with the FOREIGN KEY SAME TABLE constraint "FK__employee__mgr_id__6ABBA185". The conflict occurred in database "master", table "dbo.employee", column "emp_id". The statement has been terminated. 1> 2> 3> drop table employee; 4> GO</source>


Check for data length

   <source lang="sql">

3> CREATE TABLE T ( 4> int1 int IDENTITY PRIMARY KEY, 5> vch1 varchar(5) 6> CHECK (LEN(vch1) > 0) 7> NOT NULL, 8> vch2 varchar(5) 9> CONSTRAINT CK_LEN_TOO_SHORT 10> CHECK (LEN(vch2) > 0) 11> NOT NULL 12> ) 13> GO 1> 2> 3> drop table t; 4> GO</source>


Constraints with name

   <source lang="sql">

5> 6> 7> 8> CREATE TABLE Customers 9> ( 10> CustomerNo int IDENTITY NOT NULL 11> PRIMARY KEY, 12> CustomerName varchar(30) NOT NULL, 13> Address1 varchar(30) NOT NULL, 14> Address2 varchar(30) NOT NULL, 15> City varchar(20) NOT NULL, 16> State char(2) NOT NULL, 17> Zip varchar(10) NOT NULL, 18> Contact varchar(25) NOT NULL, 19> Phone char(15) NOT NULL, 20> FedIDNo varchar(9) NOT NULL, 21> DateInSystem smalldatetime NOT NULL 22> ) 23> GO 1> 2> ALTER TABLE Customers 3> ADD CONSTRAINT CN_CustomerPhoneNo 4> CHECK 5> (Phone LIKE "([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]") 6> go 1> 2> drop table Customers; 3> GO</source>


Mark nocheck for a constraint

   <source lang="sql">

4> CREATE TABLE Customers 5> ( 6> CustomerNo int IDENTITY NOT NULL 7> PRIMARY KEY, 8> CustomerName varchar(30) NOT NULL, 9> Address1 varchar(30) NOT NULL, 10> Address2 varchar(30) NOT NULL, 11> City varchar(20) NOT NULL, 12> State char(2) NOT NULL, 13> Zip varchar(10) NOT NULL, 14> Contact varchar(25) NOT NULL, 15> Phone char(15) NOT NULL, 16> FedIDNo varchar(9) NOT NULL, 17> DateInSystem smalldatetime NOT NULL 18> ) 19> GO 1> ALTER TABLE Customers 2> WITH NOCHECK 3> ADD CONSTRAINT CN_CustomerPhoneNo 4> CHECK 5> (Phone LIKE "([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]") 6> go 1> 2> ALTER TABLE Customers 3> NOCHECK 4> CONSTRAINT CN_CustomerPhoneNo 5> 6> GO 1> 2> drop table Customers; 3> GO 1></source>


Pattern based constraint

   <source lang="sql">

5> CREATE TABLE Customers 6> ( 7> CustomerNo int IDENTITY NOT NULL 8> PRIMARY KEY, 9> CustomerName varchar(30) NOT NULL, 10> Address1 varchar(30) NOT NULL, 11> Address2 varchar(30) NOT NULL, 12> City varchar(20) NOT NULL, 13> State char(2) NOT NULL, 14> Zip varchar(10) NOT NULL, 15> Contact varchar(25) NOT NULL, 16> Phone char(15) NOT NULL, 17> FedIDNo varchar(9) NOT NULL, 18> DateInSystem smalldatetime NOT NULL 19> ) 20> GO 1> ALTER TABLE Customers 2> WITH NOCHECK 3> ADD CONSTRAINT CN_CustomerPhoneNo 4> CHECK 5> (Phone LIKE "([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]") 6> go 1> 2> 3> ALTER TABLE Customers 4> CHECK 5> CONSTRAINT CN_CustomerPhoneNo 6> 7> GO 1> 2> drop table Customers; 3> GO 1> 2> 3></source>


Use or to link two conditions for check constraint

   <source lang="sql">

2> CREATE TABLE publishers 3> ( 4> pub_id char(4) NOT NULL 5> CONSTRAINT UPKCL_pubind PRIMARY KEY CLUSTERED 6> CHECK (pub_id in ("1389", "0736", "0877", "1622", "1756") OR pub_id like "99[0-9][0-9]"), 7> 8> pub_name varchar(40) NULL, 9> city varchar(20) NULL, 10> state char(2) NULL, 11> 12> country varchar(30) NULL 13> 14> DEFAULT("USA") 15> ) 16> 17> GO</source>


Using a Multicolumn CHECK Constraint

   <source lang="sql">

4> CREATE TABLE ClassGrades( 5> ClassID int, 6> StudentID int, 7> GradeLetter varchar(2), 8> Constraint PK_ClassGrades 9> PRIMARY KEY(ClassID, StudentID), 10> Constraint CK_GradeRange_ClassID 11> CHECK (LEFT(UPPER(GradeLetter),1) 12> LIKE "[A-F]" AND ClassID < 1000) 13> ) 14> GO 1> 2> INSERT ClassGrades VALUES(1, 1, "C+") 3> INSERT ClassGrades VALUES(1, 2, "A+") 4> INSERT ClassGrades VALUES(1, 3, "V-") 5> INSERT ClassGrades VALUES(1001, 1, "A") 6> INSERT ClassGrades VALUES(999, 2, "A") 7> GO (1 rows affected) (1 rows affected) Msg 547, Level 16, State 1, Server J\SQLEXPRESS, Line 4 The INSERT statement conflicted with the CHECK constraint "CK_GradeRange_ClassID". The conflict occurred in database "master", table "dbo.ClassGrades". The statement has been terminated. Msg 547, Level 16, State 1, Server J\SQLEXPRESS, Line 5 The INSERT statement conflicted with the CHECK constraint "CK_GradeRange_ClassID". The conflict occurred in database "master", table "dbo.ClassGrades". The statement has been terminated. (1 rows affected) 1> 2> drop table ClassGrades; 3> GO</source>


Using CHECK Constraints

   <source lang="sql">

CHECK constraint is used to define what format and values are allowed for a column. The syntax of the CHECK constraint is as follows: CHECK ( logical_expression ) If the logical expression of CHECK evaluates to TRUE, the row will be inserted. If the CHECK constraint expression evaluates to FALSE, the row insert will fail.</source>