CODEWITHSUNDEEP
sqlsql-server-2005constraintscheck-constraints
osp70
osp70

Reputation: 1102

In SQL Server 2005, how do I set a column of integers to ensure values are greater than 0?

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.

Thanks!

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

Upvotes: 10

Views: 9587

Answers (5)

Hamid Jolany
Hamid Jolany

Reputation: 880

you can alter your table and add new constraint like bellow.

BEGIN TRANSACTION
     GO
     ALTER TABLE dbo.table1 ADD CONSTRAINT
        CK_table1_field1 CHECK (field1>0)
     GO
     ALTER TABLE dbo.table1 SET (LOCK_ESCALATION = TABLE)
     GO
COMMIT

Upvotes: 0

ConcernedOfTunbridgeWells
ConcernedOfTunbridgeWells

Reputation: 66672

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
      ,description
  from sys.check_constraints
 where name = 'Foobar_NonNegative'

Upvotes: 21

Seibar
Seibar

Reputation: 70283

Add a CHECK constraint when creating your table

CREATE TABLE Test(
      [ID] [int]  NOT NULL,
      [MyCol] [int] NOT NULL CHECK (MyCol > 1)
)

Upvotes: 0

Michael Haren
Michael Haren

Reputation: 108356

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)

Upvotes: 6

DOK
DOK

Reputation: 32851

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

ALTER TABLE tableName WITH NOCHECK
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.

Upvotes: 0

Related Questions