How do I limit the acceptable values in a database column to be 1 to 5?

Question

I have a table in a database where one of the columns should have a value from 1 to 5. How can I write this limitation into the database? Do I use a constraint? What's the best practice say about this kind of thing?

I am using SQL Server 2005

Answer 1

You need to add a constraint to your table, better a named one:

ALTER TABLE YourSchema.YourTable ADD CONSTRAINT CHK_YourTable_YourColumn_ValidLimits
CHECK(YourColumn BETWEEN 1 AND 5)

In your CREATE TABLE statement, do this:

CREATE TABLE YourSchema.YourTable(YourColumn INT NOT NULL CONSTRAINT CHK_YourTable_YourColumn_ValidLimits
CHECK(YourColumn BETWEEN 1 AND 5),
SomeOtherColumns VARCHAR(10)
);

The best practice is to explicitly name your constraints.

Answer 2

If you wanted to add this during your table creation, you could do it as follows...

create table MyTable
    (MyColumn tinyint CONSTRAINT MyColumn_CheckLimit CHECK (MyColumn BETWEEN 1 AND 5))