Hi mark,

You didn't send me this message, but I came across it by accident.

>This seems to be the thread that never dies:)
>Some months back I adopted a technique you described of having an index on deleted() and it has proved very successful for me and has presented no performance issues at all...thanks...I can always depend on you to cut to the centre of my problem.

Where did I do this ? Don't you mean filtered indexes like:
INDEX ON MyTableID TAG MyTableID2 FOR NOT DELETED() ?


As I've always said before it is not wise to use a:
INDEX ON DELETED() TAG Del
As it can give you much headaches in terms of performance.

O.K. lets start again..


ISSUE 1: Avoiding Uniqueness of index "name" is violated (Error 1884)

Some say (or have said) that surrogate keys are a mechanism to avoid duplicate keys without the potential problem of the described error message. This is not correct.

Lets say you've got a table where the column article is unique. You can implement this in two ways:
TABLE 1:
Articleno = Primary key

TABLE 2:
Pk = Primary key (integer surrogate keys)
Articleno = candidate key

The common mistake people make when saying that surrogate keys solve the problem is that they solve the problem in the following manner:

TABLE 3:
Pk = Primary key
Articleno = normal attribute

Well now you've got the same and other problems. Of course you've got to be sure that articleno is unique since it makes no sense to have two records of articleno "BIKE". Well how you're going to do this ? Some people will try to solve it by using SEEK() to check if a key already exist before saving. However this method is not safe. SEEK() fails in certain circumstances (e.g. When holding local READ Buffers and other users are modifying, Inserting or deleting records). Above all, it only checks for dublicates in your program, you can not prevent entering duplicate values from the command window (Except when using triggers). Neiter prevents the existance of duplicate keys, it only prevents adding them (which makes a lot of difference when there was a bug in you duplicate checking mechanism in the past)

The only reliable way the prevent dublicates is either by using Primary or candidate indexes.

CHOOSING TABLE 1

When having deleted() records you can face the problem that when you try to add a new article, it errors you because the same key already exists in a deleted() record. There for you've got to FILTER (FILTER clause in INDEX or ALTER TABLE PRIMARY KEY command) the index for deleted records by adding a FOR !DELETED() filterclause.
To enable rushmore to use the PK for optimization you've got to add a normal index with:
INDEX ON Articleno TAG Artic2

CHOOSING TABLE 2

The PK value is generated automaticly, so there is no need to filter the primary key. However to force uniqueness of the candidate key you've got to add the following index:
INDEX ON Articleno TAG Articleno CANDIDATE FOR NOT DELETED()
and a:
INDEX ON Articleno TAG Artic2
to enable rushmore to optmize queries where the Articleno is used.

When you look at the two solutions you'll come to the conclusion that in both cases the artcile column has two indexes attached: One normal, and one filtered for DELETED() primary or candidate key. Adding a surrogate PK key in table 2 does not change much.


ISSUE 2: RECYCLING INDEXES and THE DELETED() TAG

Many people used the INDEX FOR DELETED() TAG Del index for optimizing performance. About two years ago we discovered that this index tag seldom optimizes performance, but regular decreases performance rather dramatic.

See all discussion about the DELETED() tag of the past two years.

Another, use of the DELETED() tag was to search for deleted() records for recycling. This can be better solved by using a
INDEX ON .T. FOR DELETED() TAG delRec

The index tag will be limited to deleted records only (thus small) and you can simply look for DELETED() records by using SEEK(.t.,"MyTable","DelRec").


>If deleted() is likely to cause performance issues on big tables then perhaps the method for recycling deleted records I was toying with would work.
>Using this method I would have a new counter called PK_DELETED.
>This counter would return negative numbers which would replace the PK for any deleted record in any table.
>The advantage of doing this rather than just reversing the original PK is that records can be appended from other tables even if these other records contain a valid PK.
>The only drawback with this method I can see, is that all deletions must be done with a function to update the PK of the deleted record.
>Is it possible that that this can be done with a Trigger?
>Since the delete command is not used that much in most apps I do not see this as adding too much overhead, compared to the savings to be gained from recycling deleted records.
>Since this is a somewhat dramatic change of direction for me to make I would welcome any comments you may have.

While what you're trying to do might work, it seems unneccesary when using the strategy described above. It will solve your problem nice and clean.

The problem with your approach might be that when adding and deleting the same key twice, will still result in the same errormessage.


Walter,