Unique indexes are bad and the behavior you are observing is how they are designed. Direct from HackFox:
"A so-called unique index contains a key only for the first record that has a particular key value. That is, once a key value occurs, no other records with that key value get added to the index. There's no mechanism to enforce uniqueness here, just a way to find one of each. However, unique indexes are not properly maintained. If you delete a record that's represented in the index, FoxPro does not add the next record in the table that has the same key value. Don't ever use "unique" indexesthere's always a better way to do it." Use candidate index tags to get and enforce uniqueness or be prepared to rebuild the index each time a record is deleted. Rick White Light Computing, Inc. www.whitelightcomputing.com www.rickschummer.com 586.254.2530 - office 586.254.2539 - fax -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Michael Madigan Sent: Wednesday, January 17, 2007 03:04 PM To: [EMAIL PROTECTED] Subject: Index on collector tag collector unique 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? [excessive quoting removed by server] _______________________________________________ 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.
