May 25, 2015 at 6:19 am
I have groups of records in a table, and I would like to set a necessary condition on each group. The condition is that EXACTLY ONE of the records in each group has a flag field set to True (bit = 1). I can naturally write triggers for update, insert and delete events that test for such a condition, but I was wondering if anyone knows of a more elegant way to do it.
Something along the lines of this condition:
(select count(ClovekAutoID)
from TableOfClovekNames tCN
where JeHlavni = 1
group by ClovekAutoID
having COUNT(JeHlavni ) > 1) = 1
In fact, I tried this just on whim, but naturally, the SS engine told me to go roll my hoop, that subqueries are not allowed in constraint expressions.
May 25, 2015 at 6:37 am
I would use a filtered index for that (well, part of that).
You want to ensure that there is only ever one row with JeHlavni = 1 for each ClovekAutoID, but there can be many rows with JeHlavni = 0 for each ClovekAutoID
Is that correct?
Filtered index can ensure that there's never more than one, but it can't ensure that there is one, for that you'll probably need to use triggers (update and delete)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 25, 2015 at 7:10 am
GilaMonster (5/25/2015)
I would use a filtered index for that (well, part of that).You want to ensure that there is only ever one row with JeHlavni = 1 for each ClovekAutoID, but there can be many rows with JeHlavni = 0 for each ClovekAutoID
Is that correct?
Filtered index can ensure that there's never more than one, but it can't ensure that there is one, for that you'll probably need to use triggers (update and delete)
Thank you, Gail. Yes, that's right - sorry I didn't make it clear enough in my initial post.
For each group of ClovekAutoID,
JeHlavni = 1 exactly once;
JeHlavni = 0 any number of times, including none.
Even the constraint of no more than one would be a big help, and covers the situation that I ran in to this morning - multiple cases of JeHlavni = 1 within a group, which got through the application code.
Where would I put this condition in the filtered index? I just tried putting it in the filter expression box, and got the same error message, that subqueries are not allowed, only scalar expressions are allowed.
May 25, 2015 at 7:18 am
May 25, 2015 at 7:34 am
CREATE UNIQUE INDEX idx_TableOfClovekNames_ClovekAutoID
ON TableOfClovekNames (ClovekAutoID)
WHERE JeHlavni = 1
The unique requirement on that only applies to rows where JeHlavni is 1, and it forces that there may not be more than one row with the same ClovekAutoID with JeHlavni = 1. Can't help with requiring that there's exactly 1, that will require either a trigger or application logic (I'd recommend trigger so that someone altering directly will still be rejected)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 25, 2015 at 7:46 am
GilaMonster (5/25/2015)
CREATE UNIQUE INDEX idx_TableOfClovekNames_ClovekAutoIDON TableOfClovekNames (ClovekAutoID)
WHERE JeHlavni = 1
The unique requirement on that only applies to rows where JeHlavni is 1, and it forces that there may not be more than one row with the same ClovekAutoID with JeHlavni = 1. Can't help with requiring that there's exactly 1, that will require either a trigger or application logic (I'd recommend trigger so that someone altering directly will still be rejected)
Got it, thanks. Even found one more case of duplicate flags, that I missed earlier. SS wouldn't let me create the index until I fixed it. The requirement of no more than one is the more important of the two. I'll make triggers and default values for the rest of it. Appreciate the help.
May 25, 2015 at 7:48 am
spaghettidba (5/25/2015)
You could also use indexed views. I have a blog post on the subject here[/url].
Thanks for the tip. I'm looking at your blog, but it's a bit technical and my office is kind of noisy right now. I'll take some more time to play with it when everyone else leaves and it quiets down here.
May 25, 2015 at 8:36 am
Now that I think about it some more, I'm not even sure that it's possible to enforce an 'exactly one' condition, even with code. It would never be possible for an app to unmark on record and subsequently mark another. If at all, it would have to be done in an update query that simultaneously changed both records in one statement. I don't even think that a transaction would be enough, because with two separate statements, the first would violate one of the conditions, either no more than one, or no less than one.
Maybe a simpler way to code the trigger would be to:
1. Not allow change of JeHlavni from 1 to 0
2. Any change of JeHlavni = 0 to JeHlavni = 1 would automatically change all other records in the group to JeHlavni = 0
3. Not allow deletion of a record with JeHlavni = 1, unless it was the only record in a group.
4. Force JeHlavni = 1 on addition of the first record of a group.
May 25, 2015 at 9:18 am
pdanes (5/25/2015)
If at all, it would have to be done in an update query that simultaneously changed both records in one statement.
Correct, it would.
Maybe instead have a daily (hourly) job that checks for any violations (groups without a row with JeHlavni = 1) and flags it for someone to check
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 26, 2015 at 12:19 am
Not a bad idea, but that someone would most likely be me, so I think it's better to build triggers that prevent the situation from occurring in the first place. Anyway, it'll be good practice in coding a trigger.
May 26, 2015 at 11:03 am
You can also reference scalar user defined functions within check constraints.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 26, 2015 at 11:27 am
Eric M Russell (5/26/2015)
You can also reference scalar user defined functions within check constraints.
You can, but it's a recipe for disaster with large data modifications. A trigger is much more efficient in this regard.
-- Gianluca Sartori
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply