Check Constraint

I have an existing table with field ZIPCODE defined as VARCHAR(5).
I want to add a check constraint to allow only number from 0 to 9.

This is what I did but it gave me error:

alter table test
with check
add constraint ck_test
check (zip between '0' and '9')

error:

ALTER TABLE statement conflicted with COLUMN CHECK constraint 'ck_test'.
The conflict occurred in database 'lahdProperty', table 'test', column 'Zip'.

What did I do wrong. Thanks for your help.Don't have access to bol and my machine...I'll figure it out tomorrow, unless you get it tonight

BUT

Look at LIKE [0-9][0-9][0-9][0-9][0-9]|||I guess u r trying to check a single digit as zip.
existing data in the table conflicts with the constraint u r trying to add
so try to alter table with nocheck option.
or else drop the column and add it in which ever way u want.
------------------------
alter table test
with nocheck
add constraint ck_test
check (zip between '0' and '9')
------------------------

Originally posted by ttkh
I have an existing table with field ZIPCODE defined as VARCHAR(5).
I want to add a check constraint to allow only number from 0 to 9.

This is what I did but it gave me error:

alter table test
with check
add constraint ck_test
check (zip between '0' and '9')

error:

ALTER TABLE statement conflicted with COLUMN CHECK constraint 'ck_test'.
The conflict occurred in database 'lahdProperty', table 'test', column 'Zip'.

What did I do wrong. Thanks for your help.