Is it possible to set a maximum value for a column

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

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 = 
       ( CASE 
           WHEN (PokemonHappiness + 50) > 10000 THEN 10000
           ELSE (PokemonHappiness + 50) 
         END
       )

Answer 2

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).

Answer 3

A trigger. I tested this.

CREATE TRIGGER dbo.Table_2update
ON dbo.Table_2
FOR INSERT, UPDATE 
AS BEGIN
  UPDATE dbo.Table_2 SET dbo.Table_2.memberID = 10000
  FROM INSERTED
  WHERE inserted.id = Table_2.id 
    AND dbo.Table_2.memberID > 10000
END

Answer 4

You can achieve that with this

update mytable set PokemonHappiness=(CASE WHEN (PokemonHappiness+50) > 10000 
                                      THEN 10000 ELSE PokemonHappiness+50 END)

OR with two queries

update mytable set PokemonHappiness=PokemonHappiness+50 

update mytable set PokemonHappiness=10000 where PokemonHappiness > 10000