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.