Is it possible to set a maximum value for a column in SQL Server 2008 r2

Question

My question is pretty simple but I'm not sure if it's possible.

Assume I have a table and it contains the column below

PokemonHappiness    smallint

Is it possible to set a maximum value for that column?

For instance if I set the maximum to 10000 and send a query like this

--Assume that PokemonHappiness is equal to 9990
update mytable set PokemonHappiness = PokemonHappiness+50   

it should become 10000 instead of 10040

Is this possible?

Answer 1

If you want to set a maximum value on a column of a SQL table, one thing you can do is add a CHECK constraint with specific criteria for that column:

ALTER TABLE dbo.mytable ADD CONSTRAINT CK_HAPPINESS_MAX CHECK (PokemonHappiness <= 10000)

However, this won't handle out-of-bounds input in a graceful fashion; if your input violates the CHECK constraint, SQL will simply throw an error.

To properly handle this sort of input, you should probably use a CASE expression as others suggest, and maybe use a CHECK constraint as a hard bound to what can be inserted (just in case of unmoderated input values).