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, mssql, h2, mysqlall
columnNameName of the column to add the constraint toallall
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
<changeSet author="liquibase-docs" id="addNotNullConstraint-example">
    <addNotNullConstraint catalogName="cat"
            columnDataType="int"
            columnName="id"
            defaultNullValue="A String"
            schemaName="public"
            tableName="person"/>
</changeSet>
changeSet:
  id: addNotNullConstraint-example
  author: liquibase-docs
  changes:
  - addNotNullConstraint:
      catalogName: cat
      columnDataType: int
      columnName: id
      defaultNullValue: A String
      schemaName: public
      tableName: person
{
  "changeSet": {
    "id": "addNotNullConstraint-example",
    "author": "liquibase-docs",
    "changes": [
      {
        "addNotNullConstraint": {
          "catalogName": "cat",
          "columnDataType": "int",
          "columnName": "id",
          "defaultNullValue": "A String",
          "schemaName": "public",
          "tableName": "person"
        }
      }]
    
  }
}

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
DB2SupportedYes
DerbySupportedYes
FirebirdNot SupportedYes
H2SupportedYes
HyperSQLSupportedYes
InformixSupportedYes
MySQLSupportedYes
OracleSupportedYes
PostgreSQLSupportedYes
SQL ServerSupportedYes
SQLiteNot SupportedYes
SybaseSupportedYes
Sybase AnywhereSupportedYes

Missing, incomplete or wrong information? Please send a pull request