Cetin and Ed,
My problem has nothing to do with PK nor with recycling records. I'll explain in detail ; periodically I receive sales results by modem from about 100 shops :
For every shop these records records are something like this :
YEAR
MONTH
PROD_ID
QTY
These records are written in a table where the SHOP_ID is added :
SHOP_ID
YEAR
MONTH
PROD_ID
QTY
In this table I don't use a primary key but I have to keep SHOP_ID+YEAR+MONTH+PROD_ID unique, that's why the candidate (not primary) index is on it.
As this table grows rapidly (now 8.000.000 records) I want to remove 'qty=0' records so when I receive such a record, instead of replacing and (maybe) existing record I delete it.
But if one day, after the record has been deleted, a new record arrives for a SHOP_ID+YEAR+MONTH+PROD_ID that ever existed, the system would refuse the record because it wouldn't be unique although a seek wouldn't find the record (not in SET DELETED OFF). So to resolve this problem I make the prod_id the negative recno(). As in my application, negative prod_id's don't exist so that can't be the problem, but I'm sure now the unique key of the deleted record :
- remains unique forever (or untill the table is packed)
- can't be duplicate with future newly arrived records
So to conclude, I think in this case I don't see a reason why not using a filter in the index instead of my initial solution (REPLACE and DELETE).