Creating a Check Constraint

Question

Post reply

Creating a Check Constraint

daniel-703741

Valued Member

Points: 68
More actions
April 8, 2008 at 6:17 am

#121229

Hello. I am obviously very new to SQL Server 2005 Express. I am an Access developer and I am giving a try to SQL Server Express. Maybe my question will be answered faster this way 8)
I am going through a book (... In 24 Hours series) and every time I try to complete a simple exercise about creating a (seems like a) Check Constraint I get an error. Here's what I am doing:
The field is State: Char (2) and we want to apply the constraint where only the following values are accepted: CA, UT, AZ WY, and a few others.
My steps:
I click the selected to the left of the State field and click Manage Check Constraints.
In the Check Constraint dialog I click Add and the constraint CK_Table_1* appears. The name is simply CK_Table_1.
I name the constraint ckState (like in the book)
In the Expression I type @State In('CA', 'UT', 'AZ', 'WY') and as soon as I hit Enter a message appears that there is an error in the constraint and ask me if I want to Edit it.
I am at loss here because this matches the screen shot in the book.
Can anyone help?
I hope I provided enough information.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

Remove the @ symbol

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

"State" is referring to your field name.

daniel-703741 Valued Member Points: 68 More actions · Answer 2

Thank you so much Michael,

The book does specify the @ but it is incorrect! Thank you again. I will remember.

Daniel

zgembo SSC Rookie Points: 34 More actions · Answer 3

Remove @ from your statement( @State In('CA', 'UT', 'AZ', 'WY'))

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 4

Michael Earl-395764

SSC Guru

Points: 53873

April 8, 2008 at 7:03 am

#799701

don't believe everything you read.

marina.x.rotua Valued Member Points: 54 More actions · Answer 5

hello, I'm a beginner in SQL Server 2005, i'm trying to create check constraint, and when i'm trying to create it, an error message was appear.

my steps are:

1) The field is ZIP using nchar(5),

2) in the Expression I type: zip LIKE '[0-9][0-9][0-9][0-9][0-9]'

3) When I click Save button, there was an error message and unable to save the changes

kindly help...

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Please post new questions in a new thread. Thanks.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

kaza_rohan SSC-Addicted Points: 435 More actions · Answer 7

Check:

Check limits the values that are accepted by one or more columns.

The Below code accepts only values A or k or R or S, it will not accept other than this.

USE Rohan

GO

CREATE TABLE CHEECK (ID INT, SHORT VARCHAR(2) CHECK (SHORT LIKE '[A,K,R,S]'))

Thanks,

Rohan k

UsefulSite:

CheckConstraint