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
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
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.
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))