Adding a new record with the same name, with a soft deleted record-CodePudding

I have a table named "Run".Each run has a state, corresponding to enum on the code side. 3 is "Deleted" state. If a "Run"'s state is 3, it is excluded when pulling Runs from the database.In an other saying called as soft delete.

So my problem is, when user delete(makes run's state 3) a "Run" and creates a new "Run" with a same name with a soft deleted "Run", SQLite Error 19: 'UNIQUE constraint failed error appears.

Adding a new "Run" with the same name as a "Run" that has not been soft deleted prevented by in-app validations.But it should be possible to add a new "Run" with the same name as a deleted "Run".

How can i add a new record with a same name with soft deleted record?

CodePudding user response：

Looks like your problem is that you trying to Add in the database same entity that all ready exist. First solution is to change State prop to 3 and Update entity instead of Add if you don't wanna that entity twice in the db. Second solution is my code below if you want same entity in the database but with deferent State

//Take the entity you want from the database and set Status to 3
var run = db.Runs.Where(x => x.Id == 15).Select(x => new Run()
            {
                RunName = x.RunName,
                State = 3, //set to 3
                AmplificationKit = x.AmplificationKit,
                //add all other props without Id 

            }).ToList();

//Save to database new record whit the same properties , only State is changed
db.AddRange(run);
db.SaveChanges();

CodePudding user response：

Don't set a UNIQUE constraint on the column RunName.

Instead use a partial unique index:

CREATE UNIQUE INDEX idx_run_state ON tablename(RunName) WHERE State <> 3;

With the above index you can have any number of rows for a specific RunName with a State equal to 3, but only 1 row with State not equal to 3.

See a simplified demo.