Liquibase® version 3.8.1 is now available! Get it for free.
2018 XML Announcement
News Liquibase 3.8.1 Released
06 Nov 2019

- and/or -

All Previous Posts >>

Change: ‘addNotNullConstraint’

Adds a not-null constraint to an existing table. If a defaultNullValue attribute is passed, all null values for the column will be updated to the passed value before the constraint is applied.

Available Attributes

NameDescriptionRequired ForSupportsSince
catalogNameName of the catalogall3.0
columnDataTypeCurrent data type of the columninformix, mariadb, mysql, mssqlall
columnNameName of the column to add the constraint toallall
constraintNameCreated constraint name (if database supports names for NOT NULL constraints)all
defaultNullValueValue to set all currently null values to. If not set, change will fail if null values existall
schemaNameName of the schemaall
tableNameAdds a not-null constraint to an existing table. If a defaultNullValue attribute is passed, all null values for the column will be updated to the passed value before the constraint is applied.allall
validateThis is true if the not null constraint has 'ENABLE VALIDATE' set, or false if the not null constrain has 'ENABLE NOVALIDATE' set.all
<changeSet author="liquibase-docs"
        id="addNotNullConstraint-example"
        objectQuotingStrategy="LEGACY">
    <addNotNullConstraint catalogName="cat"
            columnDataType="int"
            columnName="id"
            constraintName="const_name"
            defaultNullValue="A String"
            schemaName="public"
            tableName="person"
            validate="true"/>
</changeSet>
changeSet:
  id: addNotNullConstraint-example
  author: liquibase-docs
  objectQuotingStrategy: LEGACY
  changes:
  - addNotNullConstraint:
      catalogName: cat
      columnDataType: int
      columnName: id
      constraintName: const_name
      defaultNullValue: A String
      schemaName: public
      tableName: person
      validate: true
{
  "changeSet": {
    "id": "addNotNullConstraint-example",
    "author": "liquibase-docs",
    "objectQuotingStrategy": "LEGACY",
    "changes": [
      {
        "addNotNullConstraint": {
          "catalogName": "cat",
          "columnDataType": "int",
          "columnName": "id",
          "constraintName": "const_name",
          "defaultNullValue": "A String",
          "schemaName": "public",
          "tableName": "person",
          "validate": true
        }
      }]
    
  }
}

SQL Generated From Above Sample (MySQL)

UPDATE cat.person SET id = 'A String' WHERE id IS NULL;

ALTER TABLE cat.person MODIFY id INT NOT NULL;

Database Support

DatabaseNotesAuto Rollback
DB2Not SupportedYes
DB2SupportedYes
DerbySupportedYes
FirebirdNot SupportedYes
H2SupportedYes
HyperSQLSupportedYes
INGRESSupportedYes
InformixSupportedYes
MariaDBSupportedYes
MySQLSupportedYes
OracleSupportedYes
PostgreSQLSupportedYes
SQL ServerSupportedYes
SQLiteNot SupportedYes
SybaseSupportedYes
Sybase AnywhereSupportedYes