CODEWITHSUNDEEP
postgresqlliquibase
John P
John P

Reputation: 1580

Liquibase: Specify schema when adding foreign key constraint

I'm using liquibase 3.5.5 with postgresql. All my tables are being created in a specified schema, however when liquibase tried to generate the DDL, it is appending "public." in front of the referencesTableName. I've tried making the table name "identity.users", but it just tries to create public."identity.users".

For example, the below

<createTable tableName="users" schemaName="identity">
            <column name="id" type="bigint" defaultValueSequenceNext="seq_users">
                <constraints primaryKey="true" primaryKeyName="pk_users"/>
            </column>
</createTable>
<createTable tableName="external_identities" schemaName="identity">
            <column name="id" type="bigint" defaultValueSequenceNext="seq_external_identities">
                <constraints primaryKey="true" primaryKeyName="pk_external_identities"/>
            </column>
            <column name="user_id" type="bigint">
                <constraints foreignKeyName="fk_external_identities_users" referencedTableName="users" referencedColumnNames="id" nullable="false"/>
            </column>
</createTable>

Produces: 

 CREATE TABLE identity.external_identities (id BIGINT DEFAULT nextval('identity.seq_external_identities') NOT NULL, user_id BIGINT NOT NULL, CONSTRAINT fk_external_identities_users FOREIGN KEY (user_id) REFERENCES public.users(id))

The docs don't help much, and the XSD file does not list schema as an option:

<!-- Attributes for constraints -->
    <xsd:attributeGroup name="constraintsAttributes">
        <xsd:attribute name="nullable" type="booleanExp" />
        <xsd:attribute name="primaryKey" type="booleanExp" />
        <xsd:attribute name="primaryKeyName" type="xsd:string" />
        <xsd:attribute name="primaryKeyTablespace" type="xsd:string" />
        <xsd:attribute name="unique" type="booleanExp" />
        <xsd:attribute name="uniqueConstraintName" type="xsd:string" />
        <xsd:attribute name="references" type="xsd:string" />
        <xsd:attribute name="referencedTableName" type="xsd:string"/>
        <xsd:attribute name="referencedColumnNames" type="xsd:string"/>
        <xsd:attribute name="foreignKeyName" type="xsd:string" />
        <xsd:attribute name="deleteCascade" type="booleanExp" />
        <xsd:attribute name="deferrable" type="booleanExp" />
        <xsd:attribute name="initiallyDeferred" type="booleanExp" />
        <xsd:attribute name="checkConstraint" type="xsd:string" />
    </xsd:attributeGroup>

Upvotes: 3

Views: 5617

Answers (3)

user1660326
user1660326

Reputation: 204

Support for specifying the referenced schema of a foreign key defined in the constraints element was added in version 3.5 of Liquibase. In version 3.5 of the dbchangelog XSD you can find the new referencedTableSchemaName attribute on the constraints element.

Liquibase will not recognize this new attribute if your changelog XML file still imports an older XSD version, so make sure that the databaseChangeLog root element references version 3.5 or newer of the XSD file:

    <databaseChangeLog
            xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
            xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
            xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
             http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.5.xsd">
        <!-- ... -->
    </databaseChangeLog>

Then update the constraints element of your column definition with a value for the referencedTableSchemaName attribute:

    <column name="user_id" type="bigint">
        <constraints
            foreignKeyName="fk_external_identities_users"
            referencedTableSchemaName="identity"
            referencedTableName="users"
            referencedColumnNames="id"
            nullable="false"/>
    </column>

Upvotes: -1

vsavkov
vsavkov

Reputation: 9

Had to use changeSet with sql like: ALTER TABLE schema.table ADD CONSTRAINT constraint FOREIGN KEY (fk_field) REFERENCES schema.ref_table (field)

Upvotes: 1

htshame
htshame

Reputation: 7330

Try using separate changeset for adding foreign key constratint. It'll be addForeignKeyConstraint. It has schema attributes.

ChangeSet will look like this:

<changeSet author="liquibase-docs" id="addForeignKeyConstraint-example">
    <addForeignKeyConstraint
            baseColumnNames="user_id"
            baseTableName="external_identities"
            constraintName="fk_external_identities_users"
            referencedColumnNames="id"
            referencedTableName="users"
            baseTableSchemaName="identity"
            referencedTableSchemaName="identity"/>
</changeSet>

Upvotes: 2

Related Questions