How to limit valid values ​​in a database column 1-5?

Question

I have a table in the database where one of the columns should have a value from 1 to 5. How can I write this restriction to the database? Am I using a restriction? What is the best practice talking about this?

I am using SQL Server 2005

Answer 1

You need to add a constraint to a better named table:

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

In the CREATE TABLE statement, do the following:

 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 state your limitations.

Answer 2

If you want to add this while creating the table, you can do it as follows:

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