Not really, because that would let me have more than one favorite breed.

Dan, Cocker, True
Dan, Great Dane, True

both of these records are unique, but doesn't serve my purpose of only
allowing one true favorite response, per person


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




Spencer Starr
<[Email address protected]
om> To
Dan Guzman/NEW/USEPA/[Email Address Removed]
09/28/2006 08:56 cc
AM
Subject
RE: Conditional unique index?










**A LazyDBA.com subscriber has responded to your lazydba.com post**
**LazyDBA.com mail shield has forwarded you this email,
**and removed any attachments, and kept your email address secret
**from this person, and any viruses/trojans.
**If you reply to this email, the person will see your email address as
normal
**Anything below this line is the original email text


I could get flamed for this, but...
Could you create the primary key to look like:

Person + Dog + favorite?

Or is this not all on the same table?

Thanks,
Starr

-----Original Message-----
From: Guzman
[Email address protected]
Sent: Thursday, September 28, 2006 10: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






MS Sql Server LazyDBA home page