Is It Possible To Set A Maximum Value For A Column In Sql Server 2008 R2

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

Solution 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:

ALTERTABLE dbo.mytable ADDCONSTRAINT 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).

Solution 2:

When you want to set it to 10000 then don't set it to 10040. Your "auto-update" would have side-effect and would be very error-prone(consider that you'll forget it or someone doesn't know it). But you could use a CASE:

UPDATE dbo.MyTable 
SET PokemonHappiness = 
       ( CASEWHEN (PokemonHappiness +50) >10000THEN10000ELSE (PokemonHappiness +50) 
         END
       )

Solution 3:

A trigger. I tested this.

CREATETRIGGER dbo.Table_2update
ON dbo.Table_2
FORINSERT, UPDATEASBEGINUPDATE dbo.Table_2 SET dbo.Table_2.memberID =10000FROM INSERTED
  WHERE inserted.id = Table_2.id 
    AND dbo.Table_2.memberID >10000END

Solution 4:

You can achieve that with this

update mytable set PokemonHappiness=(CASEWHEN (PokemonHappiness+50) >10000THEN10000ELSE PokemonHappiness+50END)

OR with two queries

update mytable set PokemonHappiness=PokemonHappiness+50update mytable set PokemonHappiness=10000where PokemonHappiness >10000