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: ‘addForeignKeyConstraint’

Adds a foreign key constraint to an existing column

Available Attributes

NameDescriptionRequired ForSupportsSince
baseColumnNamesName of column(s) to place the foreign key constraint on. Comma-separate if multipleallall
baseTableCatalogNameall3.0
baseTableNameName of the table containing the column to constrainallall
baseTableSchemaNameall
constraintNameName of the new foreign key constraintallall
deferrableIs the foreign key deferrableoracle, sqlite, postgresql
initiallyDeferredIs the foreign key initially deferredoracle, sqlite, postgresql
onDeleteON DELETE functionality. Possible values: 'CASCADE', 'SET NULL', 'SET DEFAULT', 'RESTRICT', 'NO ACTION'firebird, oracle, hsqldb, db2z, h2, informix, mariadb, postgresql, ingres, db2, asany, derby, mysql, mssql
onUpdateON UPDATE functionality. Possible values: 'CASCADE', 'SET NULL', 'SET DEFAULT', 'RESTRICT', 'NO ACTION'all
referencedColumnNamesColumn(s) the foreign key points to. Comma-separate if multipleallall
referencedTableCatalogNameall3.0
referencedTableNameName of the table the foreign key points toallall
referencedTableSchemaNameall
referencesUniqueColumnall
validateThis is true if the foreign key has 'ENABLE VALIDATE' set, or false if the foreign key has 'ENABLE NOVALIDATE' set.all
<changeSet author="liquibase-docs"
        id="addForeignKeyConstraint-example"
        objectQuotingStrategy="LEGACY">
    <addForeignKeyConstraint baseColumnNames="person_id"
            baseTableCatalogName="cat"
            baseTableName="address"
            baseTableSchemaName="public"
            constraintName="fk_address_person"
            deferrable="true"
            initiallyDeferred="true"
            onDelete="CASCADE"
            onUpdate="RESTRICT"
            referencedColumnNames="id"
            referencedTableCatalogName="cat"
            referencedTableName="person"
            referencedTableSchemaName="public"
            validate="true"/>
</changeSet>
changeSet:
  id: addForeignKeyConstraint-example
  author: liquibase-docs
  objectQuotingStrategy: LEGACY
  changes:
  - addForeignKeyConstraint:
      baseColumnNames: person_id
      baseTableCatalogName: cat
      baseTableName: address
      baseTableSchemaName: public
      constraintName: fk_address_person
      deferrable: true
      initiallyDeferred: true
      onDelete: CASCADE
      onUpdate: RESTRICT
      referencedColumnNames: id
      referencedTableCatalogName: cat
      referencedTableName: person
      referencedTableSchemaName: public
      validate: true
{
  "changeSet": {
    "id": "addForeignKeyConstraint-example",
    "author": "liquibase-docs",
    "objectQuotingStrategy": "LEGACY",
    "changes": [
      {
        "addForeignKeyConstraint": {
          "baseColumnNames": "person_id",
          "baseTableCatalogName": "cat",
          "baseTableName": "address",
          "baseTableSchemaName": "public",
          "constraintName": "fk_address_person",
          "deferrable": true,
          "initiallyDeferred": true,
          "onDelete": "CASCADE",
          "onUpdate": "RESTRICT",
          "referencedColumnNames": "id",
          "referencedTableCatalogName": "cat",
          "referencedTableName": "person",
          "referencedTableSchemaName": "public",
          "validate": true
        }
      }]
    
  }
}

SQL Generated From Above Sample (MySQL)

ALTER TABLE cat.address ADD CONSTRAINT fk_address_person FOREIGN KEY (person_id) REFERENCES cat.person (id) ON UPDATE RESTRICT ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;

Database Support

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