CODEWITHSUNDEEP
liquibase
user3960875
user3960875

Reputation: 1005

Can't get liqubase to generate foreign keys with multiple schemas

I have multiple schemas (2) and when generating the tables it is not able to generate FKs between the tables. All FKs are within one schema. When I remove the FKs it generates just fine.

I run it as

mvn clean liquibase:update -Dt1Schema=t1 -Dt2Schema=t2

What I've tried

<changeSet id="0" author="admin">
    <createTable tableName="t1" remarks="t1" schemaName="${t1Schema}">
        <column name="id" type="BIGINT" autoIncrement="true" remarks="id">
            <constraints primaryKey="true" nullable="false"/>
        </column>
    </createTable>

    <createTable tableName="t2" remarks="t2" schemaName="${t1Schema}">
        <column name="id" type="BIGINT" autoIncrement="true" remarks="id">
            <constraints primaryKey="true" nullable="false"/>
        </column>
        <column name="t1_id" type="BIGINT" remarks="t1 id">
            <constraints nullable="true" deferrable="true" foreignKeyName="fk_t1_t2" referencedTableName="${t1Schema}.t1" referencedColumnNames="id"/>
        </column>
    </createTable>
</changeSet>

I get:

ERROR: relation "teeregister.t1" does not exist [Failed SQL: CREATE TABLE teeregister.t2 ...

<changeSet id="0" author="admin">
    <createTable tableName="t1" remarks="t1" schemaName="${t1Schema}">
        <column name="id" type="BIGINT" autoIncrement="true" remarks="id">
            <constraints primaryKey="true" nullable="false"/>
        </column>
    </createTable>

    <createTable tableName="t2" remarks="t2" schemaName="${t1Schema}">
        <column name="id" type="BIGINT" autoIncrement="true" remarks="id">
            <constraints primaryKey="true" nullable="false"/>
        </column>
        <column name="t1_id" type="BIGINT" remarks="t1 id">
            <constraints nullable="true"/>
        </column>
    </createTable>
</changeSet>

<changeSet id="1" author="admin">
    <addForeignKeyConstraint baseColumnNames="t1_id" baseTableName="${t1Schema}.t2" constraintName="fk_t1_t2" referencedColumnNames="id" referencedTableName="${t1Schema}.t1"/>
</changeSet>

I get:

ERROR: relation "teeregister.t2" does not exist [Failed SQL: ALTER TABLE "teeregister.t2" ADD CONSTRAINT ...

pom.xml configuration

<plugin>
    <groupId>org.liquibase</groupId>
    <artifactId>liquibase-maven-plugin</artifactId>
    <version>3.5.1</version>
    <configuration>
        <changeLogFile>src/main/resources/schema1.xml</changeLogFile>
        <changeLogFile>src/main/resources/schema2.xml</changeLogFile>
        <driver>org.postgresql.Driver</driver>
        <url>jdbc:postgresql://localhost:5432/postgres</url>
        <username>postgres</username>
        <password>postgres</password>
    </configuration>
</plugin>

Upvotes: 1

Views: 413

Answers (1)

user3960875
user3960875

Reputation: 1005

All I needed to do is read some documentation...

This works just fine

<addForeignKeyConstraint baseColumnNames="t1_id" baseTableSchemaName="${t1Schema}" baseTableName="t2" constraintName="fk_t1_t2" referencedColumnNames="id" referencedTableSchemaName="${t1Schema}" referencedTableName="t1"/>

Upvotes: 2

Related Questions