Group By Count Value In Check Constraint

[Noonoo]

Hi people.

I'd like to set a check constraint on a table that says, if the count of ON bit fields for a particular ID is greater than one then don't allow the update/insert. i.e. SELECT Count(ID) FROM tableName WHERE bitField = 1 GROUP BY ID HAVING COUNT(*) > 1
should be zero or null

I'm can't seem to find any examples online of this being done. Is it possible to use aggregate functions in constraint syntax and if so does anyone have an example.

Thanks.

 

[AlexCuse]

I don't have anything available to test this atm, but I *think* that you can do this if you put your logic to check into a function. I've used this technique before for constraints, but not using aggregat functions (to tell you the truth, I don't even remember why I had to do it, but I did). Needless to say, be careful.

Then your constraint would be something like

dbo.MyFunction(myRowID) <= 1

Your query appears flawed also, I think it needs to be something like:

SELECT Count(ID) FROM tableName WHERE bitField = 1 [b]
AND ID = @ID GROUP BY ID

You'll need to add some logic to your function to return 0 if the ID is not in the DB yet also.

As an aside, you may want to read up on normalization as well.

http://www.r937.com/relational.html

Hope this helps,

Alex

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page

 

[Noonoo]

Thanks for your time. The syntax does’nt work but I tried your general approach of calling a function in the constraint, which seems like a good trick. Not passing any parameters but returning a count of IDs with more than one ‘On’ Bit field. The check constraint could’nt cope though and would only tell me if the data already broke the condition but not if the current insert/update would. Odd!

I think what I need is an example that’s been tested. If noyone has one I can always get round the problem by calling a checking stored proc from VB before attempting to make inserts/updates to my table.

 

[AlexCuse]

you don't want to check for a resultset of ID's that have >1 set to "on".

You want to check for the ID being inserted/updated how many rows are set to "on". If its' 0, allow. Otherwise, disallow. Make sense?

I'm going to test this out right now to see if it works.

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page

 

[AlexCuse]

it doesn't seem to be working for updates. Maybe a trigger would be a better option?

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page

 

[ESquared]

Check constraints that compare to other rows gets really dicey because the state of the table during the check constraint running has some kind of logical inconsistency that I can't remember now.

It was something like, for inserts the check constraint runs on the table before the rows are inserted, but for the update it runs after the update is made. Or maybe vice versa.

So if you want table-level check constraints that allow or prevent data modification I think you'll have to use triggers.

For what it's worth, my testing was in SQL 2000, not 2005. It's possible MS addressed this inconsistency in 2005.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

[sub]The best part about anything that has cheese is the cheese.[/sub][/color]

 

[Noonoo]

Thanks for all your comments. It's interesting to know there's a problem with these types of constraints. I'll avoid trying them in future.

 

[ESquared]

In fact I do believe it was the reverse:

For inserts the check constraint runs on the table after the rows are inserted, but for the update it runs before the update is made.

Though I'm still going from memory and need to try it out again.