Liquibase: Specify schema when adding foreign key constraint

Question

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>

htshame htshame · Accepted Answer · 2018-03-14T13:51:31

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>

Liquibase: Specify schema when adding foreign key constraint

2 Answers