Constraint Questions
-
I have a field defined as a float. I want to limit users to entering numbers between -99 through +99 (inclusive). However, I also want to allow them to enter data after the decimal point but I only want them to be able to enter in either ".0", ".5". So, for example, "-15", "-15.5", "+15.0", "7.5", "0", "0.5" are acceptable. However, "-15.332", "7.4" aren't acceptable. I'd like to match this regex...
[+-]?([1-9][0-9]?(\.5)?)|(0(\.5)?|\.5)
So, basically I guess I have 2 questions:
1) Can I break up this problem into 2 separate constraints? It seems like "ALTER TABLE myTable ADD CONSTRAINT CN_LimitRangeToNegAndPos99 CHECK (myFloatField BETWEEN -99 AND 99)" would limit the numbers entered to the necessary range. So that would take care of one of my rules. However, could I then add a 2nd check constraint that checks the decimal place and still achieve the desired effect?
2) How do I limit the "after decimal" part?
-
Either enforce it in the front-end app or (assuming this is not an option) use a trigger.
Using the trigger you can alter the data from the inserted table to conform to your rules, or delete non-conforming data and raise an error (or pretty much anything else depending on your specific needs). Note that you cannot alter the inserted table directly - you will need to reference it to update the real table (and therefore will need to ensure you have a primary key to identify rows, although you should have one anyway
).I think to check for the ".0" and ".5" you would have to convert to a string and check the rightmost two characters. Can't think of any other way to do this at the moment.
-
- use decimal[(p[, s])] check BOL in stead of float.
- check constaints can help to delimit values :
from BOL :
ALTER TABLE doc_exd
ADD CONSTRAINT exd_check CHECK (column_a between -99 and +99)
alter table dox_exd
ADD CONSTRAINT exd_check_precision CHECK (case column_a - cast(column_a as integer) when 0 then 1 when 0.5 then 1 else 0 end = 1)
- u can also use a trigger to perform these tests
test it
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply