Friday, March 9, 2012

Error Validating Any Check Constraint

Hey Guys,

I just this day started using SQL sever 2005. I created a database and

then created a table. Then I started adding some fields. I wanted to

add a check constraint to one of the fields called state but I keep

getting the same error. I right click on the field while editing the

table and select check constraint. I then click add on the check

constraint dialogue and in the expression caption I input the

following:

<code>

@.State In('CA', 'AZ', 'UT', 'CO')

</code>

I am using a book and have straight copied the above example from

the book. However when I input the check constraint I get the following

error;

"Error validating constraint 'ck_myfirstdatabase'

I have tried this with other fields and other types of check

constraints and I still get the same error. I have tried to delete the

database and recreate it. I have tried everything I can think of and I

cannot seem to get check constraints to work. I have no idea why I keep

getting this message. I have checked the

examples a

thousand times, the syntax is definately correct. This is getting

extremely annoying as I cannot continue unless I do this. I'm all out

of ideas. Can anyone please tell me

why it could not be working? Any ideas would be greatly appreciated.

hi,

i think this

@.State In('CA', 'AZ', 'UT', 'CO')

should be like this

State In('CA', 'AZ', 'UT', 'CO')

@.state is a varaible and constraints dont use it.

Anyway the best way to learn constraint is to use TSQL scripts and here's an example

create table mytable
(
chardate char(10),
charfloat char(5)
)
GO

ALTER TABLE MYTABLE
ADD
CONSTRAINT column_chardate_chk
CHECK
(
chardate like '[0-1][0-9]/[3-9][0-9]/[0-9][0-9][0-9][0-9]'
)
GO
ALTER TABLE MYTABLE
ADD
CONSTRAINT column_int_chk CHECK
(
charfloat like '[0-9][0-9].[0-9][0-9]'
)
GO

INSERT MYTABLE VALUES('01/01/2005','01.20') this will insert

GO
INSERT MYTABLE VALUES('01/01/2005','001.20') this will fail

SELECT * FROM MYTABLE

No comments:

Post a Comment