I have a database that holds users. Additional data in the database has a user_id associated to it.
Now we want to delete that user.
My data model won't let me physically delete the row due to FK constraints. this is a good thing. So I have an expired_date column and a status column (overkill) to indicate this user is "deleted".
Along comes a new user. This person�s login id happens to be the same login id as the old deleted user. But because I have a UNIQUE index on the login and the old user still physically exists in the database, it can't be done.
I have a category table (for grouping of information) that has the same issue. If someone deletes a category, it's not physically deleted. So in the future if they wanted to re-add that category, they couldn't due to a UNIQUE index on the category name column.
I could code to check for all of this, but I'd rather let the database manage this stuff - less trips to the db etc. I just can't figure out how to allow duplicate data only if all others are expired...
I just can't figure out how to allow duplicate data only if all others are expired...
You can't allow this. Your data model is quite correctly stopping you from doing this. The only ways round this would be to change the constraints (bad - they must be there for a reason) or change the requirement.
You could have the unique constraint be on that field and the "delete" field, but that only helps for the first duplicate. If they add it once, delete, then add it again, it is fine, but if you then "delete" the second one, and a third is tried to be added, then it fails again.
What really needs to be implemented is on the client side, a way for the user to search and see the "deleted" records, and "reactivate" them, instead of them trying to add a "duplicate". That is the approach we have to take at my work. We have a field called inactive.
I actually thought I had it too - I have an expired_date column. If I put a unique constraint on the login AND the expired_date, this would allow for multiple deletes, however (and unfortunately), the UNIQUE index doesn't apply to nulls. When you are active this is null. If I add another login that's the same, it takes it (eventhough the new addition has null too for the expired date). You can have duplicate null values on a unique index constraint.
Originally posted by Mark Spritzler: What really needs to be implemented is on the client side, a way for the user to search and see the "deleted" records, and "reactivate" them, instead of them trying to add a "duplicate".
You have to be careful with this design; you need a way to ensure that the "dbrown" user you are trying to add is really the same as the "dbrown" user that was active a couple of years ago and was "deleted". If there's a possibility that the old one was Dennis and the new one is Dorothy then you would need some more complex design.
I think for the login id, a simple message saying "that user id is taken" will suffice. I mean, who here got the gmail account prefix they wanted anyway - in other words, I think there is a tolerance for not getting the id you wanted.
Regarding the category issue. I like the "reinstate" approach. The only part I need to be careful of is when expiring a category, I need to move all the associated rows to a different category called "unassigned" or at least ask the question if they want it moved otherwise all the associated rows go with it!
fun topic. [ April 27, 2006: Message edited by: Chris Montgomery ]
Is this the real life? Is this just fantasy? Is this a tiny ad?