Creating a check constraint using liquibase

Question

What are the options for adding check constraints to columns in tables when using Liquibase?

I have tried using the Liquibase checkConstraint attribute, but it doesn't seem to work, possibly because it is a Liquibase Pro change type.

<column name="age" type="INT" >
    <constraints checkConstraint="CHECK (age &gt;= 0 AND age &lt;= 1000)"/>
</column>

Another option I found is using the tag to execute an ALTER TABLE statement, but I'm concerned about potential compatibility issues with different databases.

<sql>
    ALTER TABLE person_jn ADD CONSTRAINT check_age CHECK (age &gt;=0 AND age &lt;= 1000)
</sql>

Are there any other recommended approaches or best practices for adding check constraints to columns in Liquibase?

Answer 1

You're right, check constraint change is for LiquibasePro only.

You can use <sql> change and add it in plain SQL.

If you're worried about compatibility issues, you can always use dbms attribute of the changeSet, which allows you to create different changeSets for different databases.

E.g.:

<changeSet id="foo-1" author="bar" dbms="mysql">
    <!-- preConditions -->
    <sql>
        <!-- SQL for MySQL -->
    </sql>
</changeSet>

<changeSet id="foo-2" author="bar" dbms="postgresql">
    <!-- preConditions -->
    <sql>
        <!-- SQL for Postgres -->
    </sql>
</changeSet>

From the changeSet documentation

dbms (optional) - Specifies which database type(s) a changeset is to be used for. See valid database type names on Liquibase Database Tutorials. Separate multiple databases with commas. Specify that a changeset is not applicable to a particular database type by prefixing with !. The keywords all and none are also available.

Here's are lists of supported databases:

https://docs.liquibase.com/start/tutorials/home.html

https://contribute.liquibase.com/extensions-integrations/directory/database-tutorials/