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.

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>