views:

131

answers:

2

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

+14  A: 

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.

AlexKuznetsov
you beat me to it!
akf
thanks, how would this look if you were defining the table?CREATE TABLE ....
MedicineMan
I code it: **MyColumn>= 1 AND MyColumn<=5)** so there would be no confusion if end points are included or not
KM
@KM: If you subsequently script the CK out using SSMS, it should change the BETWEEN to this anyway. It also changes IN to OR too.
gbn
+3  A: 

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