SQL Server/T-SQL/Constraints/Check Options
Содержание
- 1 A statement that creates a table with two column-level check constraints
- 2 Blocking Empty and Missing Character Input
- 3 check constraints coded at the table level
- 4 Mark constraint with nocheck when creating a constraint
- 5 The syntax of a check constraint: CHECK (condition)
- 6 WITH CHECK OPTION says any INSERT or UPDATE statements must meet that where clause criteria
A statement that creates a table with two column-level check constraints
5>
6> CREATE TABLE Billings
7> (BillingID INT NOT NULL IDENTITY PRIMARY KEY,
8> BillingTotal MONEY NOT NULL CHECK (BillingTotal >= 0),
9> PaymentTotal MONEY NOT NULL DEFAULT 0
10> CHECK (PaymentTotal >= 0))
11> GO
1>
2> drop table Billings;
3> GO
1>
Blocking Empty and Missing Character Input
4> CREATE TABLE T (
5> int1 int IDENTITY PRIMARY KEY,
6> vch1 varchar(5)
7> CHECK (LEN(vch1) > 0),
8> vch2 varchar(5)
9> CONSTRAINT CK_LEN_TOO_SHORT
10> CHECK (LEN(vch2) > 0)
11> )
12> GO
1>
2> INSERT T (vch1, vch2) VALUES("a","b")
3> INSERT T (vch1, vch2) VALUES("","b")
4> INSERT T (vch1, vch2) VALUES("a","")
5> INSERT T DEFAULT VALUES
6> GO
(1 rows affected)
Msg 547, Level 16, State 1, Server J\SQLEXPRESS, Line 3
The INSERT statement conflicted with the CHECK constraint "CK__T__vch1__3D5EEB29". The conflict occurred in database "master", table "dbo.T", column "vch1".
The statement has been terminated.
Msg 547, Level 16, State 1, Server J\SQLEXPRESS, Line 4
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 rows affected)
1>
2>
3>
4> drop table t;
5> GO
1>
check constraints coded at the table level
6>
7>
8> CREATE TABLE Billings
9> (BillingID INT NOT NULL IDENTITY PRIMARY KEY,
10> BillingTotal MONEY NOT NULL,
11> PaymentTotal MONEY NOT NULL DEFAULT 0,
12> CHECK ((BillingTotal >= 0) AND (PaymentTotal >= 0)))
13>
14> GO
1>
2> drop table Billings;
3> GO
Mark constraint with nocheck when creating 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> drop table Customers;
3> GO
The syntax of a check constraint: CHECK (condition)
A column-level check constraint that limits Billings to positive amounts
A statement that defines the check constraint
11> CREATE TABLE Billings
12> (BillingID INT NOT NULL IDENTITY PRIMARY KEY,
13> BillingTotal MONEY NOT NULL CHECK (BillingTotal > 0))
14> GO
1>
2> --An INSERT statement that fails due to the check constraint
3>
4> INSERT Billings VALUES (-100)
5> GO
Msg 547, Level 16, State 1, Server J\SQLEXPRESS, Line 4
The INSERT statement conflicted with the CHECK constraint "CK__Billings__Invoic__6D230CE4". The conflict occurred in database "master", table "dbo.Billings", column "BillingTotal".
The statement has been terminated.
1>
2> drop table Billings;
3> GO
1>
WITH CHECK OPTION says any INSERT or UPDATE statements must meet that where clause criteria
1> create table employee(
2> ID int,
3> name nvarchar (10),
4> salary int,
5> start_date datetime,
6> city nvarchar (10),
7> region char (1))
8> GO
1>
2> insert into employee (ID, name, salary, start_date, city, region)
3> values (1, "Jason", 40420, "02/01/94", "New York", "W")
4> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (2, "Robert",14420, "01/02/95", "Vancouver","N")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (3, "Celia", 24020, "12/03/96", "Toronto", "W")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (4, "Linda", 40620, "11/04/97", "New York", "N")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (5, "David", 80026, "10/05/98", "Vancouver","W")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (6, "James", 70060, "09/06/99", "Toronto", "N")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (7, "Alison",90620, "08/07/00", "New York", "W")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (8, "Chris", 26020, "07/08/01", "Vancouver","N")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (9, "Mary", 60020, "06/09/02", "Toronto", "W")
3> GO
(1 rows affected)
1>
2> select * from employee
3> GO
ID name salary start_date city region
----------- ---------- ----------- ----------------------- ---------- ------
1 Jason 40420 1994-02-01 00:00:00.000 New York W
2 Robert 14420 1995-01-02 00:00:00.000 Vancouver N
3 Celia 24020 1996-12-03 00:00:00.000 Toronto W
4 Linda 40620 1997-11-04 00:00:00.000 New York N
5 David 80026 1998-10-05 00:00:00.000 Vancouver W
6 James 70060 1999-09-06 00:00:00.000 Toronto N
7 Alison 90620 2000-08-07 00:00:00.000 New York W
8 Chris 26020 2001-07-08 00:00:00.000 Vancouver N
9 Mary 60020 2002-06-09 00:00:00.000 Toronto W
(9 rows affected)
1>
2> -- WITH CHECK OPTION says any INSERT or UPDATE statements must meet that where
3> -- clause criteria
4>
5> CREATE VIEW myView
6> AS
7> SELECT ID,
8> Name
9> FROM Employee
10> WHERE Name LIKE "C%"
11> OR Name LIKE "B%"
12> OR Name LIKE "A%"
13> WITH CHECK OPTION
14> GO
1>
2> UPDATE myView SET Name = "Aee"
3> GO
(3 rows affected)
1>
2> select * from Employee
3> GO
ID name salary start_date city region
----------- ---------- ----------- ----------------------- ---------- ------
1 Jason 40420 1994-02-01 00:00:00.000 New York W
2 Robert 14420 1995-01-02 00:00:00.000 Vancouver N
3 Aee 24020 1996-12-03 00:00:00.000 Toronto W
4 Linda 40620 1997-11-04 00:00:00.000 New York N
5 David 80026 1998-10-05 00:00:00.000 Vancouver W
6 James 70060 1999-09-06 00:00:00.000 Toronto N
7 Aee 90620 2000-08-07 00:00:00.000 New York W
8 Aee 26020 2001-07-08 00:00:00.000 Vancouver N
9 Mary 60020 2002-06-09 00:00:00.000 Toronto W
(9 rows affected)
1>
2> drop view myView
3> GO
1>
2> drop table employee
3> GO
1>