SQL Server/T-SQL Tutorial/Constraints/Check
Содержание
- 1 A check constraint uses an expression to qualify records that are acceptable for any Inserts or Updates
- 2 Adding a CHECK Constraint to an Existing Table
- 3 A table-level check constraint that limits Banker IDs to a specific format
- 4 CHECK Clause
- 5 Check Constraint
- 6 CHECK (DATEPART(DAY, GETDATE())
- 7 Check for data length
- 8 Constraints with name
- 9 Mark nocheck for a constraint
- 10 Pattern based constraint
- 11 Use or to link two conditions for check constraint
- 12 Using a Multicolumn CHECK Constraint
- 13 Using CHECK Constraints
A check constraint uses an expression to qualify records that are acceptable for any Inserts or Updates
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>
Adding a CHECK Constraint to an Existing Table
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).
A table-level check constraint that limits Banker IDs to a specific format
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.
CHECK Clause
[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
Check Constraint
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
CHECK (DATEPART(DAY, GETDATE())
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
Check for data length
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
Constraints with name
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
Mark nocheck for a constraint
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>
Pattern based constraint
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>
Use or to link two conditions for check constraint
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
Using a Multicolumn CHECK Constraint
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
Using CHECK Constraints
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.