May 20, 2004 at 8:05 am
I have a field in a table of type bit called MyField. I want to ensure that no more than 1 record in this table has MyField=1
Is it possible to do this with a CHECK constraint?
Thanks in advance.
Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
May 21, 2004 at 8:46 am
It's not really the way check constraints were meant to be used, but I think you could do it by creating a UDF that performs the logic of checking all the rows if the value entered is a 1, and returning a true or false that the CHECK constraint would evaluate.
But if you're going to write all that code, you might as well create a trigger, which would be more appropriate.
May 21, 2004 at 8:57 am
Yes. A UDF. I knew there was a way of doing this.
Triggers aren't an option for political reasons rather than technical. The client frowns on triggers.
Thanks talleman
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
May 21, 2004 at 2:24 pm
The check constraint is going to be more expensive then a trigger, as you have access to the logical "inserted" and "updated" tables (usually in memory). If you can make the UDF deterministic it's going to perform better than a non-deterministic function.
Usually this sort of problem is best handled in pre-processing, and not in table constraints. IE: allow updates only through a proc(s) that checks/updates the table before doing the actual Insert/Update required.
cl
Signature is NULL
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply