使用Liquibase创建外键时报错：relation 'person'不存在

使用Liquibase变更集创建包含外键的ADDRESS表时，报错提示relation "person" does not exist，但将生成的SQL语句直接在PostgreSQL的pgAdmin中执行却能成功创建表。已尝试调整引用表的大小写，问题依旧。

变更集内容 #

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)

报错信息及生成的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))]

pgAdmin中执行成功的SQL #

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

解决方案 #

原因分析 #

PostgreSQL对标识符的大小写规则是：未用双引号包裹的标识符会自动转为小写处理；如果表名是通过带双引号的SQL创建（或默认创建为大写），则引用时必须用双引号指定准确的大小写，否则会找不到对应表。

Liquibase生成的SQL中外键引用的是小写的person(ID)，而实际数据库中存在的是大写的PERSON表，导致查找失败；而直接执行的SQL中明确写了大写的PERSON(ID)，PostgreSQL能正确匹配到表。

修复步骤 #

1. 确保PERSON表已提前创建：确认创建PERSON表的变更集在create_address变更集之前执行，保证Liquibase执行当前变更集时，PERSON表已经存在于数据库中。

2. 修改Liquibase变更集中的外键引用：
   将PERSON_ID列的约束部分，替换为明确指定引用表的大小写和schema的写法，避免Liquibase自动转换大小写：

   - column:
       name: PERSON_ID
       type: int
       constraints:
         nullable: false
         foreignKeyName: FK_ADDRESS_PERSON
         referencedSchemaName: public
         referencedTableName: "PERSON"
         referencedColumnNames: ID
   

   或者使用带双引号的references值：

   references: public."PERSON"(ID)
   

   这样Liquibase生成的SQL会包含带双引号的表名，匹配数据库中实际存在的PERSON表。

内容的提问来源于stack exchange，提问作者moff2