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

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

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

A statement that creates a table with two column-level check constraints

   <source lang="sql">

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> 

</source>


Blocking Empty and Missing Character Input

   <source lang="sql">


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> 

</source>


check constraints coded at the table level

   <source lang="sql">

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 

</source>


Mark constraint with nocheck when creating 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> drop table Customers; 3> GO 

</source>


The syntax of a check constraint: CHECK (condition)

   <source lang="sql">

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> 

</source>


WITH CHECK OPTION says any INSERT or UPDATE statements must meet that where clause criteria

   <source lang="sql">

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> 

      </source>
Источник — «http://sqle.ru/index.php?title=SQL_Server/T-SQL/Constraints/Check_Options&oldid=6127»