Adding composite unique constraint in Liquibase

Question

I'm creating a link table which has 3 columns; id, product_id, tournament_id.

Adding a uniqueConstraint to the "id" column is trivial, but I want to ensure that any pair of (product_id, tournament_id) is unique.

The example at Liquibase.org shows

<changeSet author="liquibase-docs" id="addUniqueConstraint-example">
<addUniqueConstraint catalogName="cat"
        columnNames="id, name"
        constraintName="const_name"
        deferrable="true"
        disabled="true"
        initiallyDeferred="true"
        schemaName="public"
        tableName="person"
        tablespace="A String"/>
</changeSet>

but is it possible to accomplish this within a <createTable> block?

Also, just to confirm; does this create a composite unique constraint on the two columns, or does it create two separate unique constraints?

ciostek223 · Answer 1 · 2021-09-16T08:37:50.027

37

You can read liquibase manual also similar problem you can find here

In your case it should be:

<changeSet author="liquibase-docs" id="addUniqueConstraint-example">
<addUniqueConstraint
        columnNames="product_id, tournament_id"
        constraintName="your_constraint_name"
        tableName="person"
        />
</changeSet>

edited Sep 16 '21 at 08:37

answered Feb 06 '18 at 07:53

ciostek223

534
5
6

shouldn't it be `columnNames="product_id, tournament_id"`? – domids Aug 23 '21 at 13:05
@Snozzlebert you are right. I've edited my answer. – ciostek223 Sep 16 '21 at 08:26

score 28 · Accepted Answer · answered Jan 28 '15 at 17:58

I am pretty certain that:

You can't do it inside the createTable tag itself, but you can do it within the same changeset as when the table is created.
It does create a composite unique constraint on the two columns. One way you can check is to run liquibase with the command to generate the SQL for update rather than running the update command and check what it does for your database. On the command line, rather than running liquibase update you would run liquibase updateSQL.

score 7 · Answer 3 · answered Oct 10 '22 at 16:29

In case people like myself are still asking themselves this question years later: (by now) it is possible to do this within the createTable tag itself if you provide the same name for the constraint:

<changeSet id="composite-unique-example" author="composite-unique-example">
  <createTable tableName="example">
    <column name="foo" type="bigint">
      <constraints unique="true" uniqueConstraintName="foo-bar_unique"/>
    </column>
    <column name="bar" type="bigint">
      <constraints unique="true" uniqueConstraintName="foo-bar_unique"/>
    </column>
  </createTable>
</changeSet>

Running Liquibase 4.5.0 and MySQL 5.7:

mysql> insert into example (foo, bar) values (1, 2), (2, 1), (1, 3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from example;
+------+------+
| foo  | bar  |
+------+------+
|    1 |    2 |
|    1 |    3 |
|    2 |    1 |
+------+------+
3 rows in set (0.00 sec)

mysql> insert into example (foo, bar) values (1, 2);
ERROR 1062 (23000): Duplicate entry '1-2' for key 'foo-bar_unique'

This solution works and, in my opinion, is the healthiest solution ✅ Otherwise, we would have to create a constraint outside the table, which is very unhealthy. Having the same constraint names and defining a unique constraint in this way is quite readable and practical. — agitrubard, Apr 14 '23 at 16:18

Adding composite unique constraint in Liquibase

3 Answers3

Linked