UNIQUE constraint on multiple columns and Soft Delete

Question

I wanted to enforce uniqueness based on 3 columns to avoid duplicate entries. While this works as expected just hit a gotcha with regard to Soft Delete for table entry.

When an entry is deleted, the columns (for UNIQUE constraint) will still be there due to Soft Delete because a Soft Delete simply changes the IsDeleted column to True. In practice this means I cannot delete an entry and write back the same entry. I assumed SQL treats deleted entry differently but not unless I missed something obvious.

1) Is there a way around this without disabling the Soft Delete feature which is a nice thing to have (following accidental deletes as intended)?

2) If there is no other workaround, how do I disable Soft Delete for a table in Azure SQL Database? I looked around in the Azure Portal UI and no luck (though I can see for other resources such as Secret Vaults).

Answer 1

anonymous user-4673 Welcome to Microsoft Q&A and thank you for your interest in Azure SQL Database.

"Soft Delete" by updating the IsDeleted column to True, is a way your application is marking these records as deleted.
This is a not a feature of Azure SQL Database by itself.
For disabling this, you will need to change your application logic.

Coming to your issue with Unique constraint
You could modify the constraint to enforce conditional uniqueness

CREATE UNIQUE INDEX YourIndexName   
ON YourTableName (Column1, Column2, Column3)  
WHERE IsDeleted = 0  

This way you check for uniqueness only on the active combination of 3 columns.
Please let me know if you have further questions.

----------

If an answer is helpful, please "Accept answer" or "Up-Vote" for the same which might be beneficial to other community members reading this thread.