Adding a Check Constraint to a Table–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I had to do this the other day, and while I guessed at the syntax, I wasn’t sure of it, so I thought this would be a good post.

The Check Constraint docs are good, and a quick search got me over there.

I was trying to add a column to a table that has a domain of values. In my case, I was aiming for something like this:

CREATE TABLE MyTable (somekey int, result tinyint)

The result value can be 0-4, and while front end validation can handle this, it’s easy to get lazy and assume they will. It’s easy for someone to forget, or use an application like SSMS or Access to edit data, or more.

Add a constraint. It will help with data quality.

My thought was to do this:

CREATE TABLE MyTable (somekey int,
result tinyint check result <= 4)

That actually is fine, and it’s almost what the docs show. After my column, I include the CHECK keyword, and then I can give the expression. The problem with this code is that I need to parenthesis added, like this:

CREATE TABLE MyTable (somekey int,
result tinyint (check result <= 4)
)

 

SQLNewBlogger

This was a 5 minute post, showing something I thought I knew, had to check, and then corrected. A good case of how I am improving skills.

Write a post like this for something you did in your job.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.