All geek questions in one place

Liquibase prerequisites: how to verify that a column is not NULL?

I have a db upgrade script to remove a non-zero restriction for a column. I want to perform a preliminary check and call ALTER TABLE only if it is not equal to zero.

The .xml script wizard is progressive in which I continue to add scripts and all the work is done every time. After the first run of my script change table, I do not want it to run again.

Could not find a predefined condition for this and could not write sqlcheck.

+4
source share
2 answers

Can be done using 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>
+8
source

Revision of my answer. Liquibase supports adding a non-null constraint as follows:

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

This automatically processes the nullable columns, in my example filling them with the text string "NULL".

I do not think this set of changes requires a precondition. In the worst case scenario, you reapply the existing column constraint. Liquibase tracks all changes and ensures that they are not retried.

+1
source

Source: https://habr.com/ru/post/1492296/

More articles:


All Articles