Take the 2-minute tour ×

This is probably a simple answer but I can't find it. I have a table with a column of integers and I want to ensure that when a row is inserted that the value in this column is greater than zero. I could do this on the code side but thought it would be best to enforce it on the table.


I was in error with my last comment all is good now.

share|improve this question
Could you clarify the edit a bit? I would expect the system to raise an exception if you try to write a value that violates the constraint. –  ConcernedOfTunbridgeWells Oct 15 '08 at 17:25
what happened to make the first not work? Can you post the exact constraint code you used and the error or problem you got when you tried to insert data? –  HLGEM Oct 15 '08 at 17:40
add comment

4 Answers

up vote 13 down vote accepted

You can use a check constraint on the column. IIRC the syntax for this looks like:

create table foo (
   ,Foobar int not null check (Foobar > 0)

As the poster below says (thanks Constantin), you should create the check constraint outside the table definition and give it a meaningful name so it is obvious which column it applies to.

alter table foo
  add constraint Foobar_NonNegative
      check (Foobar > 0)

You can get out the text of check constraints from the system data dictionary in sys.check_constraints:

select name
  from sys.check_constraints
 where name = 'Foobar_NonNegative'
share|improve this answer
Will that not just check that it's greater than or equal to zero..? Get rid of the equal sign. –  Galwegian Oct 15 '08 at 15:16
Good thinking 99. –  ConcernedOfTunbridgeWells Oct 15 '08 at 15:18
Unnamed constraint may complicate your life later on. Better name your objects from the beginning. –  Constantin Oct 16 '08 at 16:09
@Constantin: would vote up your comment if I could. Sound advice. –  Mitch Wheat Jan 20 '09 at 13:24
@Constantin, @Mitch Wheat. Ditto - good thinking 99. Post updated to point this out. –  ConcernedOfTunbridgeWells Jan 25 '09 at 17:46
add comment

Create a database constraint:

ALTER TABLE Table1 ADD CONSTRAINT Constraint1 CHECK (YourCol > 0)

You can have pretty sophisticated constraints, too, involving multiple columns. For example:

ALTER TABLE Table1 ADD CONSTRAINT Constraint2 CHECK (StartDate<EndDate OR EndDate IS NULL)
share|improve this answer
Don't forget the "NOT NULL". CHECK alone will not reject NULLs. So "EndDate is null" in second example is redundant. –  Constantin Oct 16 '08 at 16:15
add comment

I believe you want to add a CONSTRAINT to the table field:

ADD CONSTRAINT constraintName CHECK (columnName > 0)

That optional NOCHECK is used to keep the constraint from being applied to existing rows of data (which could contain invalid data) & to allow the constraint to be added.

share|improve this answer
add comment

Add a CHECK constraint when creating your table

      [ID] [int]  NOT NULL,
      [MyCol] [int] NOT NULL CHECK (MyCol > 1)
share|improve this answer
add comment

Your Answer


