SQL Server add a column constraint to limit data t

2020-07-20 03:01发布

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?

3条回答
▲ chillily
2楼-- · 2020-07-20 03:41
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
查看更多
再贱就再见
3楼-- · 2020-07-20 03:47

You are probably looking for CHECK Constraints

查看更多
家丑人穷心不美
4楼-- · 2020-07-20 03:52
create table (
    x integer check (x = -1 or x = 0 or x = 1), 
    y integer
);


insert test values(5,5)
insert test values(-1, 5)
insert test values(0, 5)
insert test values(1, 5)

alter table test with nocheck add check(y = -1 or y = 0 or y = 1);
-- with no check prevents errors for illegal values already in the table
insert test values(-1, 1)
insert test values(0, -1)
insert test values(1, 0)

select * from test
查看更多
登录 后发表回答