>I don´t know whether I´m missing anything, but I´m beginning to think that the CANDIDATE indexes (indices?) are about as useful as the old UNIQUE ones, at least if you work with SET DELETED ON.
>
>The following program should not (IMHO) produce an error, but it does:
>
> SET DELETED ON
> CREATE TABLE TRY (KEYFIELD N(3))
> INDEX ON KEYFIELD TAG KEYFIELD CANDIDATE
> INSERT INTO TRY VALUE (1)
> DELETE FOR KEYFIELD=1
> INSERT INTO TRY VALUE (1)
>
>What does this mean? For example, assume an user had (once) entered a record with a key value of 1, and later on he deleted it. If you list the table, the key 1 does not show up. If you try to SEEK it, it´s not there and FOUND() returns .F.; ok! But, after all that evidence, if you try to INSERT a new record with key 1, it fails!
>
>Am I missing anything, or is this just a VFP botch up? Opinions?
Fredrico,

The enforcement of PK and Candidate indexes is according to the rules of relational databases. The rules state that a Primary or candidate key is absolutely Unique within the table. Set deleted on does NOT remove the record from the table, the record is still there and still has that key, and the record could be recalled in the future So the behavior you see is the only way it could ever work.

To solve the perceived problem with this, use surrogate keys so you don't care what the value is as long as it is unique. Don't ever reuse a key. If you use Integer field for this you are limited to only 2,000,000,000 positive values stored in 4 bytes.