Constraint conundrum

Question

Constraint conundrum

CanuckBuck

Hall of Fame

Points: 3915
More actions
March 26, 2009 at 11:22 am

#207908

Hi.
Is there an elegant way of defining a constraint to limit a NUMERIC(3,1) field to values between 0 and 10 in .5 increments thats not
CHECK ([field_Name] IN (0,.5,1,1.5,2,2.5,3,3.5,4,4.5,5,5.5,6,6.5,7,7.5,8,8.5,9,9.5,10)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Mohit K. Gupta SSChampion Points: 12130 More actions · Answer 1

Michael (3/26/2009)
Hi.
Is there an elegant way of defining a constraint to limit a NUMERIC(3,1) field to values between 0 and 10 in .5 increments thats not
CHECK ([field_Name] IN (0,.5,1,1.5,2,2.5,3,3.5,4,4.5,5,5.5,6,6.5,7,7.5,8,8.5,9,9.5,10)

Hi Michael Try ...

ALTER TABLE TestName

ADD CONSTRAINT ValueInRange CHECK

( (field_Name >= 0) AND

(field_Name <= 10) AND

(

((field_Name - Cast(field_Name AS INT)) = 0.0)

OR

((field_Name - Cast(field_Name AS INT)) = 0.5)))

[font="Arial"]---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE

* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

How to ask for help .. Read Best Practices here[/url].

CanuckBuck Hall of Fame Points: 3915 More actions · Answer 2

That's kind of elegance I was thinking of 😛

Thank you!

Mohit K. Gupta SSChampion Points: 12130 More actions · Answer 3

Your welcome, CAST(Field_Value as INT) will round down so it works.

But if your users enter a value that is two decimal places then this idea has a small glitch. Because the value gets rounded up by the Decimal field.

DECLARE @t decimal(3,1)

SET @t = 65.95

SELECT @t

SELECT CAST(@t as int)

SET @t = 65.94

SELECT @t

SELECT CAST(@t as int)

You can notice the affect in these .. first value gets automatically rounded up to 66 so cast returns 66, second value doesn't get round up so both decimal and cast return 65.

Thanks.

[font="Arial"]---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE

* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

How to ask for help .. Read Best Practices here[/url].

CanuckBuck Hall of Fame Points: 3915 More actions · Answer 4

CanuckBuck

Hall of Fame

Points: 3915

March 27, 2009 at 8:18 am

#967466

Ooh. Good point to note. Thanks.

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 5

Here's an alternative for the check constraint

[font="Courier New"]ALTER TABLE TestName

ADD CONSTRAINT ValueInRange CHECK (

(field_Name BETWEEN 0.0 AND 10.0) AND

(field_Name % 0.5 = 0.0)

)[/font]

GSquared SSC Guru Points: 260824 More actions · Answer 6

Instead of casting as an Integer, take a look at the Floor function in Books Online.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

CanuckBuck Hall of Fame Points: 3915 More actions · Answer 7

Hi Andrew.

Thanks for this.

I actually came across this solution a few hrs ago on my own. Just hadn't gotten back here to document it for completeness.