Search code examples
swiftconstraintsvapor

Database constraints to ignore soft-deleted entries when evaluating uniqueness

I have a User model that users can use to create accounts in my Vapor (Swift) application.
User implements the Migration protocol as follows:

static func prepare(on conn: PostgreSQLConnection) -> Future<Void> {
    return Database.create(self, on: conn) { (builder) in
        try addProperties(to: builder)
        builder.unique(on: \.email)
    }
}

Now, this works just fine, but if a user (soft-)deletes their account and tries to sign up with the same email address again, they cannot, because the email has already been used.

How can I tell the builder to ignore values from deleted entries when evaluating uniqueness?

Solution

  • You can't actually tell builder to ignore values from deleted entries, since the builder just adds native mysql/postgres constraint to your table.

    You would have to do this manually when adding a new user, e.g. query the full table, including deleted entries, and go from there.