int value with positive only entry

Question

I'm trying to create a table and I can only have positive values for an INT, how would I do that?

CREATE TABLE Ingredients(
IngredientID    INTEGER     PRIMARY KEY     NOT NULL,
IngredientName  VARCHAR(255),
IngredientClassID   SMALLINT    NOT NULL,
MeasureAmountID      SMALLINT    NOT NULL
);

Answer 1

try this...

CREATE TABLE Ingredients(
IngredientID    INTEGER     PRIMARY KEY,
IngredientName  VARCHAR(255),
IngredientClassID   SMALLINT    NOT NULL,
MeasureAmountID      SMALLINT    NOT NULL,
CHECK (IngredientClassID>0),
CHECK (MeasureAmountID>0)

);

Answer 2

You can create a Check constraint for each column to accept only positive values.

CREATE TABLE Ingredients(
IngredientID    INTEGER PRIMARY KEY constraint IngredientID_Positive
check (IngredientID >= 0),
IngredientName  VARCHAR(255),
IngredientClassID   SMALLINT    NOT NULL,
MeasureAmountID      SMALLINT    NOT NULL
);