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?

Solution 1

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

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

Solution 3

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
       )

Comments

[Tim Schmelter]

When you want to set it to 10000 then don't set it to 10040.

[MonsterMMORPG]

i know i can use case. but that requires modification to my all queries. that is why i am asking sql server side possible or not.

[MonsterMMORPG]

would this check constraint cause any performance issue ? thanks.

[Tim Schmelter]

@MonsterMMORPG: You could use a trigger for this, but i wouldn't recommend it because triggers have cost and they are "magic" (what i've mentioned above).

[mikurski]

Any kind of additional check or process has the potential to impact performance. Given that the specified check only compares two integer values, I'd say that the impact would be pretty minimal on a per-row basis.