Liquibase Change Types dropNotNullConstraint

Not Null constraint Make sure that the column should not be null and must have some value or provide some default value. but sometimes the record has to be null. Liquibase provides dropNotNullConstraint to drop an existing notNullConstraint.

Add Not Null Constraint after Table Creation

<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" xmlns:pro="http://www.liquibase.org/xml/ns/pro" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-latest.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd">
	<changeSet author="wesome" id="1692599548266-1">
		<createTable catalogName="appledb" tableName="apple">
			<column autoIncrement="true" name="apple_id" type="BIGINT">
				<constraints nullable="false" primaryKey="true" />
			</column>
			<column name="apple_name" type="VARCHAR(255)" />
			<column defaultValueComputed="CURRENT_TIMESTAMP" name="current_date_time" type="datetime" />
		</createTable>
	</changeSet>
	<changeSet author="wesome" id="1692701200879-2">
		<insert catalogName="appledb" tableName="apple">
			<column name="current_date_time" valueDate="now()" />
		</insert>
	</changeSet>
	<changeSet author="wesome" id="1692701200879-3">
		<addNotNullConstraint catalogName="appleDb" columnDataType="VARCHAR(255)" columnName="apple_name" defaultNullValue="Default Fuji" constraintName="apple_name_not_null" schemaName="appleDb" tableName="apple" validate="true" />
	</changeSet>
</databaseChangeLog>

liquibase.properties (update changeLog File file format with SQL, XML, YAML, JSON as per changelog.mysql.)

changeLogFile=changelog.mysql.xml
liquibase.command.url:jdbc:mysql://localhost:3306/appleDb?createDatabaseIfNotExist=true&autoReconnect=true&useSSL=false&allowPublicKeyRetrieval=true
liquibase.command.username : root
liquibase.command.password : rootroot

Run command liquibase update

A record was inserted with a null value in apple_name column, which also has been updated by the default value provided in the addNotNullConstraint tag. The same can be verified by running the query

SELECT * FROM appleDb.apple; 

Drop notNullConstraint

<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" xmlns:pro="http://www.liquibase.org/xml/ns/pro" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-latest.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd">
	<changeSet author="wesome" id="1692701200879-4">
		<dropNotNullConstraint catalogName="appledb" columnDataType="VARCHAR(255)" columnName="apple_name" constraintName="apple_name_not_null" schemaName="appledb" tableName="apple" />
	</changeSet>
	<changeSet author="wesome" id="1692701200879-5">
		<insert catalogName="appledb" tableName="apple">
			<column name="current_date_time" valueDate="now()" />
		</insert>
	</changeSet>
</databaseChangeLog>

liquibase.properties (update changeLog File file format with SQL, XML, YAML, JSON as per changelog.mysql.)

changeLogFile=changelog.mysql.xml
liquibase.command.url:jdbc:mysql://localhost:3306/appleDb?createDatabaseIfNotExist=true&autoReconnect=true&useSSL=false&allowPublicKeyRetrieval=true
liquibase.command.username : root
liquibase.command.password : rootroot

Run command liquibase update

A record was inserted with a null value in apple_name column, which also has been updated by the default value provided in the addNotNullConstraint tag.
after dropNotNullConstraint null values are allowed, The same can be verified by running the query

SELECT * FROM appleDb.apple;