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.

Wroth

Could You sum up this question in one sentence? – Queenstown

How do I write a precondition to check for a non-nullable constraint being present on a column? – Wroth

Answer 1

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>
  

Supercolumnar

The OP tagged this mySQL. It would be much more helpful to provide that syntax than answering two different questions – Giselle

Added query for mySQL. – Supercolumnar

Answer 2

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>

Reciprocity

Answer 3

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.

Handful

Thanks, but I would not want to do a migration script populating all null elements with a string. – Wroth

@Wroth 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. – Seaden