Change: ‘addForeignKeyConstraint’
Adds a foreign key constraint to an existing column
Available Attributes
| Name | Description |
|---|---|
| baseColumnNames | Name of column(s) to place the foreign key constraint on. Comma-separate if multipleE.g. 'person_id' |
| baseTableCatalogName | Catalog name of the base table@ v3.0E.g. 'cat' |
| baseTableName | Name of the table containing the column to constraintE.g. 'address' |
| baseTableSchemaName | Schema name of the base tableE.g. 'public' |
| constraintName | Name of the new foreign key constraintE.g. 'fk_address_person' |
| deferrable | booleanIs the foreign key deferrableSupported by: oracle, postgresql, sqlite |
| initiallyDeferred | booleanIs the foreign key initially deferredSupported by: oracle, postgresql, sqlite |
| onDelete | ON DELETE functionality. Possible values: 'CASCADE', 'SET NULL', 'SET DEFAULT', 'RESTRICT', 'NO ACTION'Supported by: asany, db2, db2z, derby, firebird, h2, hsqldb, informix, ingres, mariadb, mssql, mysql, oracle, postgresql@ v2.0E.g. 'CASCADE' |
| onUpdate | ON UPDATE functionality. Possible values: 'CASCADE', 'SET NULL', 'SET DEFAULT', 'RESTRICT', 'NO ACTION'E.g. 'RESTRICT' |
| referencedColumnNames | Column(s) the foreign key points to. Comma-separate if multipleE.g. 'id' |
| referencedTableCatalogName | Schema name of the referenced table@ v3.0E.g. 'cat' |
| referencedTableName | Name of the table the foreign key points toE.g. 'person' |
| referencedTableSchemaName | Schema name of the referenced table@ v3.0E.g. 'public' |
| referencesUniqueColumn | boolean |
| validate | booleanShall be true if the foreign key should 'ENABLE VALIDATE' set, or false if the foreign key should 'ENABLE NOVALIDATE' set.@ v3.6Default: true |
<changeSet author="liquibase-docs" id="addForeignKeyConstraint-example">
<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
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",
"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
| Database | Notes | Auto Rollback |
|---|---|---|
| DB2/LUW | Supported | Yes |
| DB2/z | Supported | Yes |
| Derby | Supported | Yes |
| Firebird | 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 |