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'); |
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); |
--second attempt with a wrong state and zipCode combination insert into customers values('Jane','Doe','CA',11594); |
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