Reckoned Force

Full Service Business Data Solutions

CHECK Constraints

By

CHECK Constraints are similar to foreign key constraints they both validate a data that goes to a column except foreign key constraint gets a valid values that goes to that column from a different table while CHECK constraint get a valid values from a logical expression.
To create a check constraint on a new table

CREATE TABLE table1 
  ( 
     col1 INT, 
     col2 VARCHAR(20), 
     CONSTRAINT chk_col1 check (col1>0) 
  ); 
go

To add check constraint on existing table

ALTER TABLE table1 
  ADD CONSTRAINT chk_col1 check (col1>0); 
go

To see our constraint is created properly we could check as

SELECT * 
FROM   sys.check_constraints 
WHERE  parent_object_id = Object_id('dbo.table1');

CHECK Constraints Step0
Let for example we want to create a database application that we need to validate there is a correct sate and zip code combination before inserting data to our table. We first need to create a lookup table to hold the correct zip and state combination for us then create a scalar function that will help us validate those zip codes. Then we could create our customers table with CHECK constraint as follows

--create a lookup table
create table tStateList(
	[State] char(2),
	ZIPCodeRange char(11),
	MinVal int,
	MaxVal int
);
go
--insert zip code values
INSERT INTO tStateList ([State],ZIPCodeRange,MinVal,MaxVal)
    VALUES        ('AK','99500-99999','99500','99999'),
		  ('AL','35000-36999','35000','36999'),
		  ('AR','71600-72999','71600','72999'),
		  ('AZ','58000-86599','58000','86599'),
		  ('CA','90000-96199','90000','96199'),
		  ('CO','80000-81699','80000','81699'),
		  ('CT','06800-06999','06800','06999'),
		  ('DC','20001-20599','20001','20599'),
		  ('DE','19700-19999','19700','19999'),
		  ('FL','32100-34999','32100','34999'),
		  ('GA','30000-31999','30000','31999'),
		  ('HI','96700-96899','96700','96899'),
		  ('IA','50000-52899','50000','52899'),
		  ('ID','83200-83899','83200','83899'),
		  ('IL','60000-62999','60000','62999'),
		  ('IO','83200-83899','83200','83899'),
		  ('IN','46000-47999','46000','47999'),
		  ('KS','66000-64799','66000','64799'),
		  ('KY','40000-42799','40000','42799'),
		  ('LA','70000-71499','70000','71499'),
		  ('MA','01000-02799','01000','02799'),
		  ('MD','20600-21999','20600','21999'),
		  ('ME','03000-04999','03000','04999'),
		  ('MI','48000-49799','48000','49799'),
		  ('MN','55000-56799','55000','56799'),
		  ('MO','63000-65899','63000','65899'),
		  ('MS','38600-39599','38600','39599'),
		  ('MT','59000-59999','59000','59999'),
		  ('NC','27000-28999','27000','28999'),
		  ('ND','58000-58899','58000','58899'),
		  ('NE','68000-69399','68000','69399'),
		  ('NH','03000-03899','03000','03899'),
		  ('NJ','07000-08999','07000','08999'),
		  ('NM','87000-88499','87000','88499'),
		  ('NV','89000-89899','89000','89899'),
		  ('NY','10000-14999','10000','14999'),
		  ('OH','43000-45899','43000','45899'),
		  ('OK','73000-74999','73000','74999'),
		  ('OR','97000-97999','97000','97999'),
		  ('PA','15000-16999','15000','16999'),
		  ('PR','00600-00799','00600','00799'),
		  ('RI','02800-02999','02800','02999'),
		  ('SC','29000-29999','29000','29999'),
		  ('SD','57000-57799','57000','57799'),
		  ('TN','37000-35899','37000','35899'),
		  ('TX','75000-79999','75000','79999'),
		  ('UT','84000-84799','84000','84799');
go

Now let us create a scalar function that will help us validate zip codes and state combination.

--create a scalar funcunction that validate zipCodes
if object_id('fnCheckZipCode','FN') is not null
	drop function fnCheckZipCode;
go
create function fnCheckZipCode(@state char(2), @zip int)
returns bit
as
begin
	declare @zipExists bit=0;
	if exists(select * from tStateList 
                  where [State] = @state and @zip between MinVal and MaxVal)	
		set @zipExists = 1;
	else
		set @zipExists = 0;
	return @zipExists;
end
go
--sample function call
--select dbo.fnCheckZipCode( 'co', 90000)

Now let us create our customer table with zipCode validation CHECK constraint

--==============================================
--Create our table and test our CHECK constraint
--==============================================
if object_id('customers','U') is not null
	drop table customers;
create table customers(
	customerid int not null identity(1,1),
	firtName varchar(30),
	lastName varchar(30),
	[state]  char(2),
	zipCode  int,
	CONSTRAINT chkZipCode CHECK (dbo.fnCheckZipCode([state],zipCode)=1)
);
 
--first attempt with correct state and zipCode combination
insert into customers values('John','Doe','CO',80202);

CHECK Constraints Step1

--second attempt with a wrong state and zipCode combination
insert into customers values('Jane','Doe','CA',11594);

CHECK Constraints Step2
This way we manage to create a data validation on customers table using CHECK constraint.
Read More
http://technet.microsoft.com/en-us/library/ms188258(v=sql.105).aspx