Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Integer column with a min/max value

Author  Topic 

ITTrucker
Yak Posting Veteran

64 Posts

Posted - 2015-05-06 : 09:37:59
Can I put a constraint on an integer column that will only allow a certain range of numbers to be entered, or do I have to put that into the application layer only?

I'd like the range to be 0 to 30 as the only allowable values. The only thing that I could think of was to create another table and populate with 0,1,2...,30 and put a foreign key on the new column that wouldn't allow anything not in that list but I was wondering if there was a better way.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-05-06 : 09:47:56
sure

create table foo(bar int check (bar > 1 and bar < 3))
insert into foo(bar) values(0)


yields:

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "CK__foo__bar__3F3DA758". The conflict occurred in database "tempdb", table "dbo.foo", column 'bar'.
The statement has been terminated.


Gerald Britton, MCSA
Toronto PASS Chapter
Go to Top of Page

ITTrucker
Yak Posting Veteran

64 Posts

Posted - 2015-05-06 : 10:01:46
Yeah, that's way easier than the reference table nonsense.

Works great, thanks for the help.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2015-05-06 : 10:02:30
CHECK constraint best for the job as described, I think, but for something more complicated you might consider a TRIGGER which can rollback the transaction if an attempt is made to INSERT/UPDATE data outside the permitted scope.
Go to Top of Page
   

- Advertisement -