How to perform logic delete with unique field in db

I have a table with those fields

Field 1 > ActivityID (PK, NOT NU开发者_如何学GoLL)
Field 2 > Alias (Unique) (NULL)
Field 3 > IsActive (NULL)

I want to do a logic delete by that I mean that I don't want to delete the row. I want to change the field IsActive to 0 and the field Alias to NULL

But my problem is that I can't change the Alias Field to NULL because he is unique.

How can do a logic delete with Unique field, when I need to delete the value in the Unique field also.

If you're using SQL 2008 & up, use a filtered index to not include NULL in the index.

SQL 2005 and earlier, Alias alone cannot have a unique constraint, be NULLable and be of much use. So, you can use the computed column technique to get around that. See this answer for more...

How to create a unique index on a NULL column?

In short: you can't. Having a column declared as unique requires that each value in that column be unique. Having more than one null value violates that constraint.

If I understand you correctly. Your Alias field is unique but allows nulls. That means there can only ever be one record with a null value.

If you need more than one record to have a null value in this field your database design needs to be changed such that your Alias field in not unique.