CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sql
MedicineMan
MedicineMan

Reputation: 15324

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

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

Upvotes: 7

Views: 1518

Answers (2)

A-K
A-K

Reputation: 17090

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.

Upvotes: 17

Scott Ivey
Scott Ivey

Reputation: 41568

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

Upvotes: 4

Related Questions