RE: Conditional unique index?

RE: Conditional unique index?

 

  

Did you figure this one out yet? Can it be done as a check constraint?

This sounds like the type of rule that would be easy to enforce via trigger.

Jay



From: Guzman
Sent: Thu 28-Sep-06 12:59
To: LazyDBA Discussion
Subject: RE: Conditional unique index?


That's the hint I was looking for. BOL makes it sound like this is my
answer, but the syntax examples are too simple. How do I write an
evaluation query for this?
Here is the query to show the count of favorite dogs I have, and in the
constraint I would want to limit it to 1 or less.

SELECT [Person], COUNT([Favorite]) AS [Count]
FROM [tblmyDogs]
WHERE ([Favorite]= 1)
GROUP BY [Person]
HAVING ([Person]= 'Dan')


So, the constraint field seems to be a variant on the WHERE clasue
theme. I just can't seem to fit my head around how to make this say
'where the count of the favorites per person is less than or equal to 1'

Here is a stab:

'COUNT([Favorite]) <=1 AND [Favorite]=1 GROUP BY [Person]'

------------------------------------------------------------------
Dan



Mordechai
Danielov
<mssqldba-ezmlms To
hield-x68153148. LazyDBA Discussion
[Email Address Removed] <[Email address protected]
DBA.com> cc

09/28/2006 09:13 Subject
AM RE: Conditional unique index?










Sounds like a candidate for a table check constraint, that will allow
only
one combination of person/breed/ and favorite=true

-----Original Message-----
From: Guzman [mailto:mssqldba-ezmlmshield-x92079819.[Email address
protected]

Sent: Thursday, September 28, 2006 11:55 AM
To: LazyDBA Discussion
Subject: RE: Conditional unique index?

In this case, just the three listed and rowguid for replication, oh, and
a table key. So:

KeyID, Person, Breed, Favorite
1, Dan, Cocker Spaniel, False
2, Dan, Lab, False
3, Dan, Bulldog, False
4, Dan, Great Dane, True


------------------------------------------------------------------
Dan



Mordechai
Danielov
<mssqldba-ezmlms To
hield-x61402050. LazyDBA Discussion
[Email Address Removed] <[Email address protected]

DBA.com> cc

09/28/2006 08:50 Subject
AM RE: Conditional unique index?










What columns do you have in this table?

-----Original Message-----
From: Guzman [mailto:mssqldba-ezmlmshield-x58686773.[Email address
protected]

Sent: Thursday, September 28, 2006 11:44 AM
To: LazyDBA Discussion
Subject: Conditional unique index?

Can you create a conditional unique index? An example: Each person can
have more than one breed of dog, but only one can be they're favorite.
So the primary key is Person/breed, but can I create a conditional index
that allows for only one Person/Favorite, but allows the rest to be
false? Something like 'WHERE count([Favorite])=1


Dan, Cocker Spaniel, False
Dan, Lab, False
Dan, Bulldog, False
Dan, Great Dane, True

I know there is a difference between better and easy. Better would be a
separate table of Favorites, with Person as primary, but an index would
allow me to save a step or three in combobox dropdown list creation
(adding a column to the dropdown list is way easier than creating
another subform, subquery and combobox with dropdown)

Dan



---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html





---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html





---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html

MS Sql Server LazyDBA home page