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
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.