Soft deletes and UniqueConstraint in Postgresql

ServiceStack
1

In the following example:

[UniqueConstraint(nameof(ColumnOne ), nameof(ColumnTwo))]
public class Customer: AuditBase
{
    [AutoIncrement]
    public int Id { get; set;}
 
    public string ColumnOne { get;  set; }

    public int ColumnTwo { get; set; }
}

With soft delete enabled if you create a customer, delete that customer (let’s say by mistake) and recreate that customer with the same initial values in ColumnOne and ColumnTwo we get an error since now we already have a row with those values + the deleted_date and deleted_by column in the database.

Adding the following index manually fixes the problem:

create unique index uc_customer_column_one_column_two_not_deleted
on customer (column_one, column_two) WHERE deleted_date IS NULL;

I’ve started using the ServiceStack migration tool (incredibly awesome by the way) and For now, in the Up() override of my migration, I’m running this:

public override void Up()
{
    Db.CreateTable<Customer>();

    Db.ExecuteNonQuery(
    "create unique index uc_customer_column_one_column_two_not_deleted " +
    "on customer (column_one, column_two) WHERE deleted_date IS NULL;");

 }

Can this be added with an attribute on the Customer class somehow or do you have a better approach to deal with this situation with soft delete?

2

Looking at the current implementation of constraint attributes, conditional unique constraints isn’t currently supported via attributes. You can create a feature request here, but currently, handling it with custom SQL like you are would be the best way to do it.

3

Perfect. No need for a new feature for this.