CODEWITHSUNDEEP
c#sqlentity-frameworkidentity
Konrad Viltersten
Konrad Viltersten

Reputation: 39078

How to cheatedly update identity column?

In the table, there's an identity column incrementing by one each time we add a row (the lowest value is 1). Now, there's a new requirement - we need to implement soft deletion.

So, my idea was to basically multiply any softly deleted row by -1. That ensures the uniqueness and clearly draws a line between active and softly deleted item.

update Things set Id = -101
where Id = 101

Would you know, the stupid computer doesn't let me do that. The suggested work-around is to:

  1. alternate the column
  2. perform the update
  3. alternate back the column

and to me it seems like a Q&D. However, the only alternative I can see is to add a new column carrying the deletion status.

I'm using EF to perform the work with the extra quirk that when I changed the value of the identity and stored it, the software was kind enough to think for me and actually create a new row (with incrementally set identity that was neither the original one, nor the negative of it).

How should I tackle this issue?

Upvotes: 1

Views: 611

Answers (1)

stakx - no longer contributing
stakx - no longer contributing

Reputation: 84735

I would strongly discourage you from overloading your identity column with any additional meaning. Someone who will look at your database table for the first time has no way of knowing that a negative ID means "deleted".

Introducing a new column Deleted BIT NOT NULL DEFAULT 0 does not have this disadvantage. It is self-explanatory. And it costs almost nothing: in the times of Big Data, an additional BIT column isn't going to fill your hard disk.

All of that being said, if you still want to do that, you could try to SET IDENTITY_INSERT dbo.Things ON before you UPDATE. (I cannot currently verify whether that would work, though.)

Upvotes: 8

Related Questions