Soft delete and unique validation

Hello,

I have an entity with an email address that needs to be unqiue. Soft deletes are also enabled.  When I delete the record and insert another new record with the same email address, I get an error related to the fact that I cannot insert a duplicate key in the index.  It seems the DeletedOn column is missing from the unique index.

Would it be possible for migrations to support the creation of unique indexes consiting of multiple columns (like the EmailAddress and DeletedOn) in a way that will work for the various databases.  Once is supported, can LightSpeed add the DeletedOn field to unique indexes if soft deletes is true?

Thanks,

Werner

+1

Hello,

We encountered this issue in production and it prevents some users from registering again.  Any chance you can address this? I would hate to redo my database logic to work around this issue.

Thanks,

Werner

When LightSpeed runs the uniqueness check, it automatically excludes soft-deleted items unless you ask for them to be included.  So if I understand this correctly, the problem is only that you need to create the unique index to be on both the DeletedOn and Email columns, and that migrations doesn't have an API to do this.  (And if it did you'd have to hand-code it, but that's further down the line.)

Is that correct?  If so, a workaround is to write the CREATE INDEX statement by hand, and run it during the migration using the Migration.ExecuteNativeCommand method:

public override void Up() {
  string sql = "CREATE UNIQUE INDEX MyIndex ON MyTable(Email, DeletedOn)";
  this.ExecuteNativeCommand(sql);
}

I realise this isn't as convenient as using an API, let alone auto generation, and may not be portable across databases, but it may serve as a workaround for now.

Hi Ivan,

Your understanding is correct. Thanks for the suggestion. I'll give it a go.

Werner