CODEWITHSUNDEEP
liquibaseunique-constraint
naXa stands with Ukraine
naXa stands with Ukraine

Reputation: 37916

How to add a conditional unique constraint in Liquibase?

Problem: When a user is deleted the associated record is not deleted from the database. Instead, I set user.delete column to true. Now I need to put a unique constraint on user.email but only for active users (not deleted).

How can I do this?

databaseChangeLog:
  - changeSet:
      id: add-user-unique-constraint
      author: me
      changes:
        - addUniqueConstraint:
            columnNames: email, delete
            constraintName: unique-email-not-deleted
            tableName: users

The above naive approach puts a composite constraint on (email, delete) columns, but it doesn't work because in the following flow user is blocked from deleting and creating an account:

  1. register a new user with email "E"
  2. delete the user with email "E"
  3. register again new user with email "E"
  4. delete the user with email "E" -> error: constraint violation
  5. register new user with email "E" -> error: constraint violation

ps. databases are H2 and PostgreSQL

Upvotes: 5

Views: 3802

Answers (1)

Siarhei Skavarodkin
Siarhei Skavarodkin

Reputation: 118

This feature is DB-specific and currently liquibase doesn't support it.

For PostgreSQL you may use direct SQL:

databaseChangeLog:
  - changeSet:
      id: add-user-unique-constraint
      author: me
      changes:
        - sql:
            "sql": "CREATE UNIQUE INDEX user_email_idx ON user (email) WHERE delete = 'false'"

however this will NOT work in H2.

Possible options for H2:

  • if it's used in tests then you may migrate to PostgreSQL using testcontainers technology (but your build environment will depend on docker)

  • have custom changesets for PostgreSQL and H2 - you may use preconditions

Upvotes: 5

Related Questions