CODEWITHSUNDEEP
databaseliquibase
000000000000000000000
000000000000000000000

Reputation: 880

Liquibase change existing nullable constraint from true to false

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.

Upvotes: 23

Views: 41891

Answers (4)

danny
danny

Reputation: 3266

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.

Upvotes: 1

Basomis
Basomis

Reputation: 101

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.

Upvotes: 5

user7834552
user7834552

Reputation:

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.

Upvotes: 36

Atty
Atty

Reputation: 801

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

Upvotes: 7

Related Questions