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
| Name | Description |
|---|---|
| catalogName | Name of the catalog@ v3.0E.g. 'cat' |
| columnDataType | Current data type of the columnE.g. 'int'Required for: informix, mariadb, mssql, mysql |
| columnName | Name of the column to add the constraint toE.g. 'id' |
| constraintName | Created constraint name (if database supports names for NOT NULL constraints)E.g. 'const_name' |
| defaultNullValue | Value to set all currently null values to. If not set, change will fail if null values existE.g. 'A String' |
| schemaName | Name of the schemaE.g. 'public' |
| tableName | 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.E.g. 'person' |
| validate | booleanThis is true if the not null constraint has 'ENABLE VALIDATE' set, or false if the not null constrain has 'ENABLE NOVALIDATE' set. |
<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>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{
"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
}
}]
}
}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
| Database | Notes | Auto Rollback |
|---|---|---|
| DB2/LUW | Supported | Yes |
| DB2/z | Not Supported | Yes |
| Derby | Supported | Yes |
| Firebird | Not Supported | Yes |
| H2 | Supported | Yes |
| HyperSQL | Supported | Yes |
| INGRES | Supported | Yes |
| Informix | Supported | Yes |
| MariaDB | Supported | Yes |
| MySQL | Supported | Yes |
| Oracle | Supported | Yes |
| PostgreSQL | Supported | Yes |
| SQL Server | Supported | Yes |
| SQLite | Not Supported | Yes |
| Sybase | Supported | Yes |
| Sybase Anywhere | Supported | Yes |