>>>>>>Hate this "marked for deletion" crap. I come from an SQL environment that when you deleted a record, it was gone. If you wanted to mark records for deleteion, you added a delete flag column to your table. We never allow our clients to see deleted records, and they never have the opportunity to undelete. Yet they have to run a pack and reindex periodically.
>>>>>>
>>>>>>Is there a way to truly (madly, deeply) delete records from a table?
>>>>>>
>>>>>>Brenda
>>>>
>>>>>If you want the record gone, but can't take the hit of grabbing the file with an exclusive lock, just blank the record, then delete it and set deleted on. Be aware that set deleted doesn't impact sql queries unless you add the where not deleted() into the select statement.
>>>>
>>>>>After you blank the records and since they are marked for deletion you can pack the table later.
>>>>
>>>>What does blanking the record in a table that has unique indexes do? Part of what prompted my "crap" outburst is that I want to add unique indexes to our production tables. This would enforce uniqueness. But none of our updating code reuseses deleted records. We have many duplicate records in our tables (one deleted, one not deleted). In code we totally ignore deleted records. It would be an impossible task for me to rewrite all the undate code, so it is impossible for me to add unique indexes to our tables.
>>>>
>>>>Brenda
>>>
>>>I thought unique indexes were something to be avoided at all costs...
>>>
>>>-Michelle
>>
>>Michelly, I hope you don't mind if I use your reply to re-enter to this thread. I guess, actually, Brenda means CANDIDATE tags, and you're right that UNIQUE index should be avoided. In regard to CANDIDATE/DELETED problem: application should generate deleted record identifier, the same way as it generates normal record ID for primary key, when new record is created, i.e. when application deletes a record it generates another id (or some character field) and replace potentially dangerous field (part of candidate key) with newly generated value. Certainly, this way should be applied realistically.
>
>Enter away. I don't think anyone's called me "Michelly" before. :)

Sorry, it was unintended typo.

>That's a good idea. Most of my pks are auto-incremented integers, but I have some places where I use a character for an index. Right now there's nothing preventing duplication, but I'll need to do that before releasing the app.
>
>-Michelle