SQL Server add a column constraint to limit data to -1 to 1

Question

I want to constrain a SQL Server decimal column to only allow -1,0,1 as valid values.

Can you show me the SQL syntax for adding such a constraint. (I would like to know how to do it in both the CREATE TABLE statement and/or the ALTER TABLE ADD CONSTRAINT).

Or can this only be accomplished in a trigger?

Answer 1

CREATE TABLE foo (
    bar int NOT NULL CONSTRAINT CK_foo_bar CHECK (bar IN (-1, 0, 1))
)

or

ALTER TABLE foo WITH CHECK ADD --added WITH CHECK
   CONSTRAINT CK_foo_bar CHECK (bar IN (-1, 0, 1)) --not needed "FOR bar"

Edit: thoughts...

• why constrain a decimal? Can you change it to smallint or int?
• what about NULLs? You may need to change my code around to do exactly what you want

Answer 2

You are probably looking for CHECK Constraints