Michael Madigan wrote: > OK, so I have a client database and on that database I > have a collector ID. I have a tag in the compound > index that is created by > > "index on collector tag collector unique". > > This gives me a list of unique collectors using the > system, each one assigned to one or more clients. > > I have a collector 5. If I happen to delete the > record which is in the unique index, there no longer > are any more '5's in the index. Shouldn't it add > another record with a collector='5' to that index? > > Am I understanding the way unique works? > Yes but I would follow some of the other posts here and use candidate instead.
This does make me ask the question. Wouldn't you be better off having the collectors in their own parent table and just relate them into the customer table? then you could just use a query. select distinct customer.coll_id, collector.coll_name ; from customer ; left outer join collector on customer.coll_id == collector.col_id ; where customer.coll_id >0 you could also easily get a count for each collector too select distinct customer.coll_id, collector.coll_name, count(*) as coll_count ; from customer ; left outer join collector on customer.coll_id == collector.col_id ; where customer.coll_id >0 Of course normal indexing rules would apply to optimize the query....... just a thought _______________________________________________ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.