Wouldn't it be easier to normalize this into 3 tables? People, Dogs, and
Favorites? Then just disallow any inserts if the UID from people
already exists in Favorites?
Thank you,
Jeff Metcalf
ComputerPlus Sales & Service
(864) 801 9003 ext 2016
Someday, in the event that mankind figures out what it is that this
world revolves around, Thousands of people are going to be shocked and
perplexed to find out that it was not them.
-----Original Message-----
From: Mordechai Danielov
[mailto:mssqldba-ezmlmshield-x31233519.[Email address protected]
Sent: Thursday, September 28, 2006 3:10 PM
To: LazyDBA Discussion
Subject: RE: Conditional unique index?
I think you are right Jay, there does not seem to be a way for a
constraint to check other rows in the table, but I ran into a problem
with the trigger, may be you can see what it wrong:
create table test1386
(
KeyID int,
Person varchar(10),
Breed varchar(10),
Favorite varchar(10)
)
CREATE TRIGGER favorite_dog on test1386
FOR INSERT, UPDATE
AS
BEGIN
If exists (
select a.favorite from
test1386 a JOIN inserted b
ON a.person=b.person AND a.favorite=b.favorite where a.favorite='TRUE'
)
ROLLBACK TRANSACTION
END
If you run
insert test1386 VALUES (1,'A','B','FALSE')
insert will work fine, but it favorite is set to 'TRUE' it will not be
inserted regardless of any other conditions...
what do you think?
-----Original Message-----
From: Jay Butler
[mailto:mssqldba-ezmlmshield-x31531497.[Email address protected]
Sent: Thursday, September 28, 2006 2:48 PM
To: LazyDBA Discussion
Subject: 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
---------------------------------------------------------------------
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