Liquibase preconditions: How do I check for a column being non-nullable?

Question

I have a db upgrade script to remove the non-null constraint on a column. I want to do a precondition check, and call ALTER TABLE only when it is non-null.

The master.xml script is a progressive one where I keep adding scripts and the entire thing runs everytime. After the first time my Alter Table script has run, I do not want it to be run again.

Couldn't find a predefined precondition for this, and could not write an sqlcheck either.

How do I write a precondition to check for a non-nullable constraint being present on a column? — ashes, Jul 20 '13 at 15:26

Yury Kisliak · Answer 1 · 2015-04-29T00:02:25.723

Can be done with sqlCheck.

For MySql

<preConditions onFail="MARK_RAN" onError="CONTINUE">
    <sqlCheck expectedResult="NO">
        SELECT is_Nullable
        FROM  INFORMATION_SCHEMA.COLUMNS 
        WHERE table_name='<table_name>' 
        AND column_name='<column_name>' 
    </sqlCheck>   
</preConditions>

For Oracle:

<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>

For SQL Server:

<preConditions onFail="MARK_RAN" onError="CONTINUE">
    <sqlCheck expectedResult="0">
      SELECT is_nullable 
      FROM sys.columns
      WHERE  object_id = OBJECT_ID('<table_name>')  
      AND name = '<column_name>' 
    </sqlCheck>
</preConditions>

The OP tagged this mySQL. It would be much more helpful to provide that syntax than answering two different questions — nomistic, Apr 28 '15 at 23:20

score 3 · Answer 2 · answered Aug 11 '21 at 18:22

For PostgreSQL:

<preConditions onFail="MARK_RAN" onError="HALT">
    <sqlCheck expectedResult="NO">
        SELECT is_nullable FROM information_schema.columns
        WHERE
               table_schema = '<schema_name>'
           AND table_name   = '<table_name>'
           AND column_name  = '<column_name'
    </sqlCheck>
</preConditions>

Mark O'Connor · Answer 3 · 2013-07-19T18:34:00.520

2

Revising my answer. Liquibase supports an add not null constraint operation as follows:

<changeSet author="me" id="example-001">
    <addNotNullConstraint 
        tableName="mytable"    
        columnName="mycol"
        columnDataType="VARCHAR(10)"
        defaultNullValue="NULL"/>
</changeSet>

This automatically handles columns that are null, in my example populating them with the text string "NULL".

I don't think this changeset requires a pre-condition. Worst case you'd re-apply the existing column constraint, once. Liquibase tracks all changsets and ensures they are not executed again.

edited Jul 19 '13 at 18:34

answered Jul 19 '13 at 18:20

Mark O'Connor

76,015
10
139
185

Thanks, but I would not want to do a migration script populating all null elements with a string. – ashes Jul 20 '13 at 15:25
@ashes It's only populated with a string if the column is of string type (MySQL forces you to specify the column type when issuing an MODIFY COLUMN statement). What do you plan to do with the pre-condition? In the event of having NULL value columns, surely you want to specify a default value. This is much better compared to not changing the column type and leaving your schema in an inconsistent state. – Mark O'Connor Jul 21 '13 at 21:44

Liquibase preconditions: How do I check for a column being non-nullable?

3 Answers3

Linked