CODEWITHSUNDEEP
sqldatabase-designsql-delete
Sharpmind
Sharpmind

Reputation: 31

Alternative to DELETE records from the Database?

I was told that we never delete records from the table, because it will be needed in future. and can I know is there any alternative than DELETE where I can DEACTIVATE the row than completely removing the row from table?

Upvotes: 2

Views: 3101

Answers (2)

DeanOC
DeanOC

Reputation: 7282

We use the same pattern as bluefeet and it works pretty well.

Some pointers which may help you to implement this.

Make sure your bit column is non-nullable. Logically there is no reason why you wouldn't know whether the record is deleted or not, and it makes coding at easier if you don't have to worry about testing for null.

Also set the bit column to have a default of 0 and then you don't have to worry about referring to it in your insert statements. Again, logically you would never insert a deleted record, so it follows that you shouldn't have to remember to set the value to 0 when you add a record.

Upvotes: 1

Taryn
Taryn

Reputation: 247810

You can add a bit column to your tables - call it something like IsActive or IsDeleted

When you INSERT data make sure that the data is marked with the correct value:

New record - IsActive = true
           - IsDeleted = false

Then when you what to make the records unavailable you will do an UPDATE to flag the records the opposite way:

Update - IsActive = false
       - IsDeleted = true

When you run your queries you will filter the data out according to this flag. We implement this type of soft delete throughout our system and it works to hide records.

Upvotes: 10

Related Questions