Liquibase change existing nullable constraint from true to false

Question

I added a column to an existing table using Liquibase changelog, and I set the nullable constraint to true.

Code:

<changeSet id="10" author="000000">
    <addColumn tableName="NCV_ATTRIBUTE">
        <column name="AlternativeListPrice" type="double" defaultValue="0.0">
        <constraints nullable="true"/>
        </column>
    </addColumn>
</changeSet>

I want to change the nullable constraint from true to false in changeSet 11. What is the simplest way to achieve this?

Thanks.

Please do not tag this as a Java question when it has absolutely nothing to do with Java. — Joe C, Feb 13 '17 at 22:28

score 24 · Answer 1 · edited Jun 09 '21 at 21:49

I found the exact method to use here.

Here is how to remove the nullable constraint using changelog:

<changeSet id="11" author="000000">
    <dropNotNullConstraint tableName="NCV_ATTRIBUTE" columnName="AlternativeListPrice" columnDataType="double"/>
</changeSet>

The keyword is dropNotNullConstraint.

In this example, if you use this keyword followed by the table name and column name, you will be able to remove the nullable constraint previously set, and the nullable value will be changed to false.

score 6 · Answer 2 · edited Jun 09 '21 at 21:48

To run the changeset only after making sure the constraint exists (in oracle) -

<changeSet id="111" author="ME">
  <preConditions onFail="MARK_RAN" onError="CONTINUE">
    <sqlCheck expectedResult="N">
      SELECT Nullable
      FROM user_tab_columns
      WHERE table_name = 'table_name'
      AND column_name = 'column_name'
    </sqlCheck>
  </preConditions>
  <dropNotNullConstraint tableName="BULK_REQUEST" columnName="REMARKS"/>
</changeSet>

Preconditions for various databases could be found here

score 4 · Answer 3 · answered Aug 26 '21 at 06:14

Answer provided by the @user7834552 is incorrect as question is about setting non null constraint.

Setting nullability constraint to false means you want to add the constraint to the column. In this case, you need to use change type of addNotNullConstraint.

score 1 · Answer 4 · answered May 29 '23 at 23:30

liquibase addNotNullConstraint should be the answer. it's also better to use the defaultNullValue attribute to make sure the change successful. below is from the offical document here

You can typically use the addNotNullConstraint Change Type when you want to apply a NOT NULL constraint value to a specific table instead of having nullable columns in that table. The addNotNullConstraint Change Type enforces a column to always contain a value and not to accept NULL values so that you cannot insert or update a record without adding a value to the column containing this constraint.

If a defaultNullValue attribute is passed, all null values for the column will be updated to the passed value before the constraint is applied. If null values exist in your database, and if you don’t not use a defaultNullValue attribute, the change will fail.

Liquibase change existing nullable constraint from true to false

4 Answers4