>>You can maintain 'uniqueness' either by using 'UNIQUE' or 'CANDIDATE' (depending on your purpose) clause, or by validating user entry when he/she saves a record.
>Ed,
>
>Candidate will insure uniqueness but UNIQUE will NOT insure uniqueness of the key value.
>
>UNIQUE should be avoided at all costs unless one clearly understands all of the ramifications of that type of index. Essentially a UNIQUE index can be depended on the moment after it is created and even then another user in the network can mess up that index.
>
>Contrary to popular belief UNIQUE indexes do not insure uniqueness, they only reflect one record of the key in the index. All other records for that key are still in the table but only one is reflected in the index. ONe place where this type of index might be usefull is when you have a table of customers and you'd lkike to know what states you do business in. An index on;
>
> State TAG States UNIQUE
>
>will show one record for each unique state you do business in. Now imagine that you are located in Mass. and you have 40,000 customers in MA. You create the index and customer record number 12 is reflected in the unique index. Ok you later delete record 12 as they pass away or go out of business. The reference to that record is remove from teh States index, BUT NO OTHER REFERENCE is added in it place. You now have an index that shows no MA when you have 39,999 customer in MA. This index will not reflect MA until you edit and save one of the MA customers.

That's all right, and believed everyone know the difference between 'UNIQUE' and 'CANDIDATE' index. That's way I said "by using 'UNIQUE' or 'CANDIDATE' (depending on your purpose)". For example, you want to bring unique list of key fields (it's informative enough: three fields) having SET DELETED OFF.