DeletedFlag field and unique keys

I was thinking of using a deleted flag rather then deleting a record so that history (auditing, etc.) could be maintained. In certain tables I would like to preserve a unique key on a field (say name or code) of all non-deleted records. Is there any way to do this. I cannot have a Code + DeletedFlag field because there may be multiple records that have been deleted with the same code. I also don't want to include the deleted "codes" in the unique key because I want the user to be able to see the codes they can't use without viewing the deleted records.May be Code+DeletedFlag+DeletedDateTime could be good idea|||That means there could be duplicate codes for active entries which is what I am trying to avoid.|||You could always keep a separate table for the deleted items (with _deleted at the end of the name, or a similar convention). I do this now and then. The deleted items table wouldn't have the uniqueness constraints on it, and you could still use a view to look at the combined tables. Then slap an AFTER DELETE trigger on the original table to automatically move rows into the deleted items table.
|||

A 'better' way to handle archiving is to have separate archiving tables. Same schema, no IDENTITY fields, no constraints, index only on PK, a couple of additional columns:

ChangeBy varchar(50)DEFAULT system_user ChangeDate datetime DEFAULT getdate()|||I have thought about another table (deleted or archived) for records and that probably is the most sensible way to deal with this issue. Ideally I wanted to create an audit table and for any table I wanted to audit I would have a join table (transaction_audit for example). The audit record would hold time, user, action (cud), ip, etc. This way when looking at any record from an audited table you could see a list of actions that were performed on it. Maybe not the complete history (every changed value) which might be overkill but who did what (in general) to it when and from where. Deleted records would be kept in the database in their last state so that activity would be shown as activity and not masked.|||I agree with Arnie.

I do this all the time. Create a table exactly like the original with no identity or constraints with a few extra fields on the end, like ChangeType, UserID, ChangeDateTime, called tablename_audit. Then setup a trigger to "insert into table_audit select *, "D", @.userid, getdate() from deleted" the audit table.

The only thing you need to remember with this is, when you change the original, you MUST also change the fields in the audit log to match. This can take some time, if you have many audit records.