addNotNullConstraint

The addNotNullConstraint Change Type adds a NOT NULL constraint to an existing table.

Uses

You can typically use the addNotNullConstraint Change Type when you want to apply a NOT NULL constraint value to a specific table instead of having nullable columns in that table. The addNotNullConstraint Change Type enforces a column to always contain a value and not to accept NULL values so that you cannot insert or update a record without adding a value to the column containing this constraint.

If a defaultNullValue attribute is passed, all null values for the column will be updated to the passed value before the constraint is applied. If null values exist in your database, and if you don’t not use a defaultNullValue attribute, the change will fail.

Running the `addNotNullConstraint` Change Type

To run this Change Type, follow these steps:

Add the Change Type to your changeset, as shown in the examples on this page.
Specify any required attributes. Use the table on this page to see which ones your database requires.
Deploy your changeset by running the update command:

liquibase update

Available attributes

Name	Description	Required for	Supports
`catalogName`	Name of the catalog		all
`columnDataType`	Data type of the column. To help make scripts database-independent, Liquibase automatically converts the following generic data types to the correct database implementation: `BIGINT`, `BLOB`, `BOOLEAN`, `CHAR`, `CLOB`, `CURRENCY`, `DATE`, `DATETIME`, `DECIMAL`, `DOUBLE`, `FLOAT`, `INT`, `MEDIUMINT`, `NCHAR`, `NUMBER`, `NVARCHAR`, `SMALLINT`, `TIME`, `TIMESTAMP`, `TINYINT`, `UUID`, `VARCHAR`, `XML`. Also, specifying a `java.sql.Types.` type is converted to the correct type as well. For example: `java.sql.Types.TIMESTAMP` `java.sql.Types.VARCHAR(255)` For Snowflake, Liquibase also implements `BINARY`, `TIME`, and `TIMESTAMP_NTZ`. For Databricks, Liquibase also implements `ARRAY<INT>` and `ARRAY<STRING>`. Note:* To specify a complex Databricks type like `ARRAY<STRING>` in an XML changelog, you must specify the escape sequences `<` and `>` rather than `<` and `>`. See `createTable`. For more information, see Liquibase Data Type Handling.	informix, mariadb, mssql, mysql	all
`columnName`	Name of the column for which to add the constraint.	all	all
`constraintName`	Name of the constraint to add (if database supports names for `NOT NULL` constraints)		--
`defaultNullValue`	The value for which you can set all values that currently equal null in a column. If null values exist and if `defaultNullValue` is not set, the change will fail.		all
`schemaName`	Name of the schema		all
`tableName`	Name of the table to add a `NOT NULL` constraint to. If a `defaultNullValue` attribute is passed, all null values for the column will be updated to the passed value before the constraint is applied.	all	all
`validate`	Set to `true` if the constraint has `ENABLE VALIDATE` set, or `false` if the constraint has `ENABLE NOVALIDATE` set.		all

Examples

--liquibase formatted sql

--changeset liquibase-docs:addNotNullConstraint-example
UPDATE cat.person SET id = 'INT' WHERE id IS NULL; 
ALTER TABLE cat.person MODIFY id INT NOT NULL;

databaseChangeLog:
-  changeSet:
    id:  addNotNullConstraint-example
    author:  liquibase-docs
    changes:
    -  addNotNullConstraint:
        catalogName:  cat
        columnDataType:  int
        columnName:  id
        constraintName:  const_name
        defaultNullValue:  A String
        schemaName:  public
        tableName:  person
        validate:  true

{
    "databaseChangeLog": [
        {
            "changeSet": {
                "id": "addNotNullConstraint-example",
                "author": "liquibase-docs",
                "changes": [
                    {
                        "addNotNullConstraint": {
                            "catalogName": "cat",
                            "columnDataType": "int",
                            "columnName": "id",
                            "constraintName": "const_name",
                            "defaultNullValue": "A String",
                            "schemaName": "public",
                            "tableName": "person",
                            "validate": true
                        }
                    }
                ]
            }
        }
    ]
}

<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
    xmlns:pro="http://www.liquibase.org/xml/ns/pro"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd
        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">

    <changeSet author="liquibase-docs"  id="addNotNullConstraint-example">
        <addNotNullConstraint catalogName="cat"
            columnDataType="int"
            columnName="id"
            constraintName="const_name"
            defaultNullValue="A String"
            schemaName="public"
            tableName="person"
            validate="true"/>
    </changeSet>

</databaseChangeLog>

Database support