Integer column with a min/max value

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

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.

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources