0

I have a table with those fields

Field 1 > ActivityID (PK, NOT NULL)
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.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Silagy
  • 3,053
  • 2
  • 27
  • 39

3 Answers3

1

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?

Community
  • 1
  • 1
Phil Helmer
  • 1,230
  • 8
  • 8
0

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.

froeschli
  • 2,692
  • 2
  • 28
  • 55
0

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.

Shiv Kumar
  • 9,599
  • 2
  • 36
  • 38