SQL Server/T-SQL/Constraints/Check value range
Add check constaint to only allow positive value
<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> /* 3~ When the salary column is updated the value will be positive 4~ */ 5> ALTER TABLE Employee WITH NOCHECK ADD CONSTRAINT 6> MyCheckName CHECK (([salary] >= 0)) 7> GO 1> 2> INSERT employee (Name, salary) VALUES ("Bedford", -100) 3> GO Msg 547, Level 16, State 1, Server sqle\SQLEXPRESS, Line 2 The INSERT statement conflicted with the CHECK constraint "MyCheckName". The conflict occurred in database "master", table "dbo.employee", column "salary". The statement has been terminated. 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> 3> drop table employee 4> GO 1>
</source>
CHECK Constraints for current date
<source lang="sql">
3> 4> 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> 2> ALTER TABLE Customers 3> ADD CONSTRAINT CN_CustomerDateInSystem 4> CHECK 5> (DateInSystem <= GETDATE ()) 6> GO 1> 2> drop table Customers; 3> GO
</source>
Check the value range
<source lang="sql">
22> 23> CREATE TABLE MySavings(AccountNum Int NOT NULL, 24> Amount Money NOT NULL) 25> 26> CREATE TABLE MyChecking(AccountNum Int NOT NULL, 27> Amount Money NOT NULL) 28> 29> ALTER TABLE MyChecking ADD CONSTRAINT ckMinBalance 30> CHECK (Amount > $100.00) 31> 32> INSERT MySavings VALUES (12345, $1000.00) 33> 34> INSERT MyChecking VALUES (12345, $1000.00) 35> GO (1 rows affected) (1 rows affected) 1> select * from mysavings 2> go AccountNum Amount
---------------------
12345 1000.0000
(1 rows affected) 1> 2> select * from mychecking 3> GO AccountNum Amount
---------------------
12345 1000.0000
(1 rows affected) 1> 2> BEGIN TRANSACTION 3> 4> UPDATE MyChecking SET Amount = Amount - $990.00 5> WHERE AccountNum = 12345 6> 7> UPDATE MySavings SET Amount = Amount + $990.00 8> WHERE AccountNum = 12345 9> 10> COMMIT TRANSACTION 11> GO Msg 547, Level 16, State 1, Server sqle\SQLEXPRESS, Line 4 The UPDATE statement conflicted with the CHECK constraint "ckMinBalance". The conflict occurred in database "master", table "dbo.MyChecking", The statement has been terminated. (1 rows affected) 1> 2> 3> drop table MySavings 4> drop table MyChecking 5> GO 1> 2>
</source>
