Liquibase® version 3.8.2 is now available! Get it for free.
2018 XML Announcement
News 5 Ways to Fix a Bad Database Change in Liquibase
04 Dec 2019

- and/or -

All Previous Posts >>

Change: ‘addUniqueConstraint’

Adds a unique constrant to an existing column or set of columns.

Available Attributes

NameDescriptionRequired ForSupportsSince
catalogNameName of the catalogall3.0
clusteredmssql
columnNamesName of the column(s) to create the unique constraint on. Comma separated if multipleallall
constraintNameName of the unique constraintall
deferrableTrue if this constraint is deferrable, False otherwiseall
disabledTrue if this constraint is disabled, False otherwiseall
forIndexCatalogNameall
forIndexNameoracle
forIndexSchemaNameall
initiallyDeferredTrue if this constraint is initially deferred, False otherwiseall
schemaNameName of the schemaall
tableNameName of the table to create the unique constraint onallall
tablespace'Tablespace' to create the index in. Corresponds to file group in mssqlall
validateThis is true if the unique constraint has 'ENABLE VALIDATE' set, or false if the foreign key has 'ENABLE NOVALIDATE' set.all
<changeSet author="liquibase-docs"
        id="addUniqueConstraint-example"
        objectQuotingStrategy="LEGACY">
    <addUniqueConstraint catalogName="cat"
            clustered="true"
            columnNames="id, name"
            constraintName="const_name"
            deferrable="true"
            disabled="true"
            forIndexName="A String"
            initiallyDeferred="true"
            schemaName="public"
            tableName="person"
            tablespace="A String"
            validate="true"/>
</changeSet>
changeSet:
  id: addUniqueConstraint-example
  author: liquibase-docs
  objectQuotingStrategy: LEGACY
  changes:
  - addUniqueConstraint:
      catalogName: cat
      clustered: true
      columnNames: id, name
      constraintName: const_name
      deferrable: true
      disabled: true
      forIndexName: A String
      initiallyDeferred: true
      schemaName: public
      tableName: person
      tablespace: A String
      validate: true
{
  "changeSet": {
    "id": "addUniqueConstraint-example",
    "author": "liquibase-docs",
    "objectQuotingStrategy": "LEGACY",
    "changes": [
      {
        "addUniqueConstraint": {
          "catalogName": "cat",
          "clustered": true,
          "columnNames": "id, name",
          "constraintName": "const_name",
          "deferrable": true,
          "disabled": true,
          "forIndexName": "A String",
          "initiallyDeferred": true,
          "schemaName": "public",
          "tableName": "person",
          "tablespace": "A String",
          "validate": true
        }
      }]
    
  }
}

SQL Generated From Above Sample (MySQL)

ALTER TABLE cat.person ADD CONSTRAINT const_name UNIQUE CLUSTERED (id,
 name) USING INDEX `A String`;

Database Support

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