postgresqlforeign-keysliquibasechangeset

Liquibase erroring when creating foreign key - relation "person" does not exist


Using a liquibase changeset I am receiving an error when attempting to create a foreign key when creating a table. If I run the same query against the postgres db it creates the table just fine.

Here is the changeset

databaseChangeLog:
  - changeSet:
      id: create_address
      author: m
      labels: create
      context: main
      comment: Create address table
      changes:
        - createTable:
            schemaName: public
            tableName: ADDRESS
            columns:
              - column:
                  name: ID
                  type: int
                  autoIncrement: true
                  constraints:
                    primaryKey: true
                    nullable: false
              - column:
                  name: STREET
                  type: varchar(250)
                  constraints:
                    nullable: false
              - column:
                  name: STREET2
                  type: varchar(250)
                  constraints:
                    nullable:true
              - column:
                  name: CITY
                  type: varchar(50)
                  constraints:
                    nullable: false
              - column:
                  name: STATE_PROVINCE
                  type: varchar(50)
                  constraints:
                    nullable: false
              - column:
                  name: COUNTRY
                  type: varchar(50)
                  constraints:
                    nullable: false
              - column:
                  name: POSTAL_CODE
                  type: varchar(30)
                  constraints:
                    nullable: false
              - column:
                  name: ACTIVE
                  type: boolean
                  constraints:
                    nullable: false
                  defaultValue: true
              - column:
                  name: PERSON_ID
                  type: int
                  constraints:
                    nullable: false
                    foreignKeyName: FK_ADDRESS_PERSON
                    references: person(ID)
              - column:
                  name: CREATED
                  type: timestamp
                  defaultValueComputed: CURRENT_TIMESTAMP
              - column:
                  name: MODIFIED
                  type: timestamp
                  defaultValueComputed: CURRENT_TIMESTAMP
              - column:
                  name: RECORD_TYPE
                  type: varchar(50)
                  constraints:
                    nullable: false
              - column:
                  name: NOTE
                  type: varchar(50)

Here is the error message with the sql:

Caused by: liquibase.exception.DatabaseException: ERROR: relation "person" does not exist [Failed SQL: (0) CREATE TABLE public.ADDRESS (ID INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL, STREET VARCHAR(250) NOT NULL, STREET2 VARCHAR(250), CITY VARCHAR(50) NOT NULL, STATE_PROVINCE VARCHAR(50) NOT NULL, COUNTRY VARCHAR(50) NOT NULL, POSTAL_CODE VARCHAR(30) NOT NULL, ACTIVE BOOLEAN DEFAULT TRUE NOT NULL, PERSON_ID INTEGER NOT NULL, CREATED TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW(), MODIFIED TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW(), RECORD_TYPE VARCHAR(50) NOT NULL, NOTE VARCHAR(50), CONSTRAINT "ADDRESS_pkey" PRIMARY KEY (ID), CONSTRAINT FK_ADDRESS_PERSON FOREIGN KEY (PERSON_ID) REFERENCES person(ID))]

and if I run the same sql in pgpadmin it creates the table without issue

CREATE TABLE public.ADDRESS 
    (ID INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL, 
    STREET VARCHAR(250) NOT NULL, 
    STREET2 VARCHAR(250), 
    CITY VARCHAR(50) NOT NULL, 
    STATE_PROVINCE VARCHAR(50) NOT NULL, 
    COUNTRY VARCHAR(50) NOT NULL, 
    POSTAL_CODE VARCHAR(30) NOT NULL, 
    ACTIVE BOOLEAN DEFAULT TRUE NOT NULL, 
    PERSON_ID INTEGER NOT NULL, 
    CREATED TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW(), 
    MODIFIED TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW(), 
    RECORD_TYPE VARCHAR(50) NOT NULL, NOTE VARCHAR(50), 
    CONSTRAINT "ADDRESS_pkey" PRIMARY KEY (ID), 
    CONSTRAINT FK_ADDRESS_PERSON FOREIGN KEY (PERSON_ID) REFERENCES PERSON(ID))


CREATE TABLE

Query returned successfully in 25 msec.

I changed the case of the references table in both liquibase and pgpadmin and got the same results for each.

See above for code and results


Solution

  • Thank you for the responses. If I take out the foreign key constraint the changeset will run. So taking that into account I moved the foreign key constraint creation to it's own changeset after the table is created and that works. It's kind of cludgy but it runs and the constraints are created. I wouldn't say that it is a solution but it s a workaround and will work for what I am currently working.