liquibaseliquibase-hibernateliquibase-sql

How to Add Min/Max Value Constraints to an Existing Integer Column using Liquibase?


Here is what I have tried so far:

<changeSet id="1" author="nobody" context="x">
    <comment>Ensure rank is between 0 and 100 inclusive</comment>
    <sql>
        ALTER TABLE User ADD CONSTRAINT chk_your_column CHECK (rank BETWEEN 1 AND 100);

    </sql>

Does Liquibase provide a built-in feature or tag to add min/max value constraints for an integer column, or is using raw SQL the only option? I haven't found it in Liquibase documentation.

  1. If Liquibase does support this directly, what is the correct way to implement it?
  2. If not, are there any recommended best practices or alternative approaches for adding such constraints using Liquibase?

Note: uses 3 databases : mssql, mysql ,oracle


Solution

  • Liquibase has addCheckConstraint tag but it's only supported by Liquibase Pro.

    Here's the example from the docs

    <changeSet  author="liquibase-docs"  id="addCheckConstraint-example">
        <pro:addCheckConstraint  catalogName="cat"
            constraintName="const_name"
            disabled="true"
            schemaName="public"
            tableName="person"
            validate="true">A String</pro:addCheckConstraint>
    </changeSet>
    

    Note that the databases supported are:

    DB2/LUW, DB2/z, MySQL 8.0, Oracle, PostgreSQL, SQL Server

    If you don't use Liquibase Pro, then I guess you'll have to do it using plain SQL.