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?

Answer 1

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>

Answer 2

I am pretty certain that:

1. 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.
2. 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.