How do you handle duplicate key if the "record status" column is set as deleted

Question

First of all, I'm sorry if the title is a bit confusing.

I was reading this question (Is it a bad practice to have a "record status" column in a database table?), and like the idea of using record status for some tables. So intead of deleting records from the table, I would set the record status to "deleted". My question is if, for example, the record is in the product table. And the product table will have unique "SKU" column. If a record is marked as deleted, then user won't see that record again. In the future, user might want to insert a new record with the same SKU as the one that was previously (logically) deleted. But it will still raise Primary key duplication in database. How should I handle this situation?

• Jerry Jeremiah: The unique key needs to include the SKU and the record status.
• candied_orange: @JerryJeremiah cute but that breaks if I want to delete the same SKU twice.
• Jerry Jeremiah: Ok I obviously didn't think hard enough. My tables have a record status column but they also have a change user and change time column - and of course my primary key includes all of them so just having record status isn't actually enough...
• Erik Eidt: It's like you need the PK to be SKU + sku-version-number, which makes each one unique again.
• Luc Franken: Use just a unique ID as the identifier of the record is a practical solution. Also allows you to fix a wrongly entered SKU for example.

Answer 1

Usually logical deletion of records is used when the PK of the table is a autoincremental sequence.

I think the product table is not a good candidate for a logical "deleted" column. That solution is better suited for things like invoices.

That said a good solution is telling the user: "The product you are trying to insert already exists, although with an inactive status. Do you want to bring it back to an active status?"

• Reynaldi: Thats a great suggestion. So for product page, instead of deleting, user should set the record as inactive. Use "deleted" status for transactional tables, eg. invoice. Is that correct?
• Tulains Córdova: @Reynaldi That's right.