Make column non-nullable in liquibase script

In my liquibase script I made a mistake in the first change set, one of the columns (OWNER) in my Primary Key is nullable as I forgot to set nullable to false for it, as shown below

  <changeSet author="sharakab" id="ALERT_WF:CREATE">
        <createTable catalogName="${rec_owner}" tableName="ALERT_WF" tablespace="${table_space_rec_data}">
            <column name="ALERT_ID" type="VARCHAR2(150 CHAR)">
                <constraints nullable="false" />
            </column>
            <column name="STATUS" type="VARCHAR2(30 CHAR)">
                <constraints nullable="false" />
            </column>
            <column name="STATUS_CHANGE_DATE" type="TIMESTAMP(6)">
                <constraints nullable="false" />
            </column>
            <column name="OWNER" type="VARCHAR2(200 CHAR)"/>
            <column name="VERSION" type="NUMBER(2,0)" defaultValue='1'>
                <constraints nullable="false" />
            </column>
            <column name="LAST_ACTIONED_BY" type="VARCHAR2(200 CHAR)"/> 
            <column name="REVIEWER_GRP_DISP_ID" type="VARCHAR2(20 CHAR)">
                <constraints nullable="false" />
            </column>
            <column name="REVIEWER_GRP_NAME" type="VARCHAR2(50 CHAR)"/>
            <column name="RECORD_CREATION_DATE" type="TIMESTAMP(6)"/>
        </createTable>

        <addPrimaryKey catalogName="${rec_owner}" columnNames="ALERT_ID,STATUS,VERSION,STATUS_CHANGE_DATE,OWNER,REVIEWER_GRP_DISP_ID" constraintName="PK_ALERT_WF"
                       tableName="ALERT_WF" tablespace="${table_space_rec_index}"/>
    </changeSet>

when I mvn clean install my springboot app, I get the below error:

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'liquibase' defined in class path resource [org/springframework/boot/autoconfigure/liquibase/LiquibaseAutoConfiguration$LiquibaseConfiguration.class]: Invocation of init method failed; nested exception is liquibas
e.exception.MigrationFailedException: Migration failed for change set classpath:/sql/tables/ALERT_WF.xml::ALERT_WF:CREATE::sharakab:
     Reason: liquibase.exception.DatabaseException: Column "OWNER" must not be nullable; SQL statement:
ALTER TABLE PUBLIC.ALERT_WF ADD CONSTRAINT PK_ALERT_WF PRIMARY KEY (ALERT_ID, STATUS, VERSION, STATUS_CHANGE_DATE, OWNER, REVIEWER_GRP_DISP_ID) [90023-200] [Failed SQL: (90023) ALTER TABLE PUBLIC.ALERT_WF ADD CONSTRAINT PK_ALERT_WF PRIMARY KEY (ALERT_ID, STATUS, VERSION, STATUS_CHANGE_DATE, OWNER, R
EVIEWER_GRP_DISP_ID)]

I tried adding below chnageset but it doesn’t work and still give then same error:

A few change sets down the line I am trying to set nullable to false

<changeSet id="ALERT_WF::add_non_null_constraint" author="sharakab">
        <addNotNullConstraint catalogName="${rec_owner}" columnDataType="VARCHAR2(200 CHAR)" columnName="OWNER" tableName="ALERT_WF" />         
</changeSet>

Please can someone tell me how to correct my mistake here.

Answer

Add this change set. Make sure you change changeSet id.

<changeSet id="ALERT_WF::add_non_null_constraint" author="sharakab">
    <addPrimaryKey tableName="ALERT_WF" columnNames="OWNER"/>
</changeSet>

Advertisement

Answer