PostgreSQL表创建失败：外键引用字段不存在问题求助

我使用psql工具执行SQL文件在PostgreSQL中创建表，执行命令为：

psql -U postgres -d dbname -a -f path\to\file\createPostgresSchema.sql

输入密码后系统返回多条错误：

psql:path\to\file\createPostgresSchema.sql:106: ERROR: column "user_id" referenced in foreign key constraint does not exist

CREATE TABLE plantdisease(
plant_disease_id SERIAL PRIMARY KEY,

CONSTRAINT fk_plant
FOREIGN KEY(plant_id)
REFERENCES plant(plant_id),

CONSTRAINT fk_disease
FOREIGN KEY(disease_id)
REFERENCES disease(disease_id)
);
psql:path\to\file\createPostgresSchema.sql:118: ERROR: column "plant_id" referenced in foreign key constraint does not exist

CREATE TABLE plantpest(
plant_pest_id SERIAL PRIMARY KEY,

CONSTRAINT fk_plant
FOREIGN KEY(plant_id)
REFERENCES plant(plant_id),

CONSTRAINT fk_pest
FOREIGN KEY(pest_id)
REFERENCES pest(pest_id)
);
psql:path\to\file\createPostgresSchema.sql:130: ERROR: column "plant_id" referenced in foreign key constraint does not exist
CREATE TABLE garden(
garden_id SERIAL PRIMARY KEY,

CONSTRAINT fk_user
FOREIGN KEY(user_id)
REFERENCES sproutshareuser(user_id),

light_level varchar,

CONSTRAINT fk_soil
FOREIGN KEY(soil_id)
REFERENCES soil(soil_id)
);
psql:path\to\file\createPostgresSchema.sql:143: ERROR: column "user_id" referenced in foreign key constraint does not exist

以下是SQL文件内容：

/* Remove Tables if they exist */
DROP TABLE IF EXISTS sproutshareuser;
DROP TABLE IF EXISTS plant;
DROP TABLE IF EXISTS userplant;
DROP TABLE IF EXISTS plantdisease;
DROP TABLE IF EXISTS plantpest;
DROP TABLE IF EXISTS soil;
DROP TABLE IF EXISTS disease;
DROP TABLE IF EXISTS pest;
DROP TABLE IF EXISTS garden;


CREATE TYPE soil_type AS ENUM (
  'sandy',
  'silt',
  'clay',
  'loamy'
);

CREATE TYPE nutrient_level AS ENUM (
  'depleted',
  'deficient',
  'adequate',
  'sufficient',
  'surplus'
);

CREATE TYPE ph_level AS ENUM (
  'basic',
  'neutral',
  'Acidic'
);

CREATE TYPE threat_level AS ENUM (
  'No_Threat',
  'Partial_Threat',
  'Threatened'
);

CREATE TABLE sproutshareuser(
  user_id SERIAL PRIMARY KEY,
  first_name varchar,
  last_name varchar,
  email_address varchar,
  language varchar,
  zip_code int
);

CREATE TABLE plant(
  plant_id SERIAL PRIMARY KEY,
  common_name varchar,
  latin_name varchar,
  light_level varchar,
  min_temp int,
  max_temp int,
  rec_temp int,
  hardiness_zone varchar,
  soil_type soil_type,
  image varchar
);


CREATE TABLE soil(
  soil_id SERIAL PRIMARY KEY,
  soil_type soil_type,
  ph_level ph_level,
  nitrogen_level nutrient_level,
  phosp_level nutrient_level,
  potas_level nutrient_level
);

CREATE TABLE disease(
  disease_id SERIAL PRIMARY KEY,
  disease_name varchar,
  threat_level threat_level,
  care_tips varchar
);

CREATE TABLE pest(
  pest_id SERIAL PRIMARY KEY,
  pest_name varchar,
  threat_level threat_level,
  care_tips varchar
);
CREATE TABLE userplant(
  user_plant_id SERIAL PRIMARY KEY,
  
  CONSTRAINT fk_user FOREIGN KEY(user_id) REFERENCES sproutshareuser(user_id),
  
  CONSTRAINT fk_plant FOREIGN KEY(plant_id) REFERENCES plant(plant_id),
  
  CONSTRAINT fk_garden FOREIGN KEY(garden_id) REFERENCES garden(garden_id),

  CONSTRAINT fk_disease
    FOREIGN KEY(plant_disease_id)
      REFERENCES plantdisease(plant_disease_id),
  
  CONSTRAINT fk_pest
    FOREIGN KEY(plant_pest_id)
      REFERENCES plantpest(plant_pest_id),

  plant_qty int,
  planting_date date,
  plant_difficulty int,
  plant_quality int
);

CREATE TABLE plantdisease(
  plant_disease_id SERIAL PRIMARY KEY,
  
  CONSTRAINT fk_plant
    FOREIGN KEY(plant_id)
      REFERENCES plant(plant_id),
  
  CONSTRAINT fk_disease
    FOREIGN KEY(disease_id)
      REFERENCES disease(disease_id)
);

CREATE TABLE plantpest(
  plant_pest_id SERIAL PRIMARY KEY,

  CONSTRAINT fk_plant
    FOREIGN KEY(plant_id)
      REFERENCES plant(plant_id),

  CONSTRAINT fk_pest
    FOREIGN KEY(pest_id)
      REFERENCES pest(pest_id)
);
CREATE TABLE garden(
  garden_id SERIAL PRIMARY KEY,

  CONSTRAINT fk_user
    FOREIGN KEY(user_id)
      REFERENCES sproutshareuser(user_id),
  
  light_level varchar,

  CONSTRAINT fk_soil
    FOREIGN KEY(soil_id)
      REFERENCES soil(soil_id)
);

我曾尝试将相关表的创建脚本移至文件顶部，但问题仍未解决。请问为何user_id和plant_id字段无法被正常引用？

这些错误的核心原因很明确：你在定义外键约束时，引用的字段并没有在当前表中提前声明，同时还存在表创建顺序颠倒的问题。

具体问题分析 #

1. 字段缺失：
   • 比如plantdisease表中，你用FOREIGN KEY(plant_id)关联plant(plant_id)，但plantdisease只定义了plant_disease_id，完全没有plant_id和disease_id这两个字段，数据库自然找不到它们。
   • 同理，garden表的外键引用了user_id，但表结构里没声明这个字段；userplant表的外键引用了user_id、plant_id等，这些字段也都没在表内定义。
2. 创建顺序错误：
   userplant表创建时引用了garden、plantdisease、plantpest，但这些表是在userplant之后创建的，即使字段都正确，也会因为依赖表不存在而报错。

修正后的完整SQL脚本 #

/* Remove Tables if they exist */
DROP TABLE IF EXISTS userplant;
DROP TABLE IF EXISTS plantdisease;
DROP TABLE IF EXISTS plantpest;
DROP TABLE IF EXISTS garden;
DROP TABLE IF EXISTS sproutshareuser;
DROP TABLE IF EXISTS plant;
DROP TABLE IF EXISTS soil;
DROP TABLE IF EXISTS disease;
DROP TABLE IF EXISTS pest;


CREATE TYPE soil_type AS ENUM (
  'sandy',
  'silt',
  'clay',
  'loamy'
);

CREATE TYPE nutrient_level AS ENUM (
  'depleted',
  'deficient',
  'adequate',
  'sufficient',
  'surplus'
);

CREATE TYPE ph_level AS ENUM (
  'basic',
  'neutral',
  'Acidic'
);

CREATE TYPE threat_level AS ENUM (
  'No_Threat',
  'Partial_Threat',
  'Threatened'
);

CREATE TABLE sproutshareuser(
  user_id SERIAL PRIMARY KEY,
  first_name varchar,
  last_name varchar,
  email_address varchar,
  language varchar,
  zip_code int
);

CREATE TABLE plant(
  plant_id SERIAL PRIMARY KEY,
  common_name varchar,
  latin_name varchar,
  light_level varchar,
  min_temp int,
  max_temp int,
  rec_temp int,
  hardiness_zone varchar,
  soil_type soil_type,
  image varchar
);


CREATE TABLE soil(
  soil_id SERIAL PRIMARY KEY,
  soil_type soil_type,
  ph_level ph_level,
  nitrogen_level nutrient_level,
  phosp_level nutrient_level,
  potas_level nutrient_level
);

CREATE TABLE disease(
  disease_id SERIAL PRIMARY KEY,
  disease_name varchar,
  threat_level threat_level,
  care_tips varchar
);

CREATE TABLE pest(
  pest_id SERIAL PRIMARY KEY,
  pest_name varchar,
  threat_level threat_level,
  care_tips varchar
);

CREATE TABLE garden(
  garden_id SERIAL PRIMARY KEY,
  user_id INT REFERENCES sproutshareuser(user_id), -- 先声明字段再关联外键
  light_level varchar,
  soil_id INT REFERENCES soil(soil_id)
);

CREATE TABLE plantdisease(
  plant_disease_id SERIAL PRIMARY KEY,
  plant_id INT REFERENCES plant(plant_id),
  disease_id INT REFERENCES disease(disease_id)
);

CREATE TABLE plantpest(
  plant_pest_id SERIAL PRIMARY KEY,
  plant_id INT REFERENCES plant(plant_id),
  pest_id INT REFERENCES pest(pest_id)
);

CREATE TABLE userplant(
  user_plant_id SERIAL PRIMARY KEY,
  user_id INT REFERENCES sproutshareuser(user_id),
  plant_id INT REFERENCES plant(plant_id),
  garden_id INT REFERENCES garden(garden_id),
  plant_disease_id INT REFERENCES plantdisease(plant_disease_id),
  plant_pest_id INT REFERENCES plantpest(plant_pest_id),
  plant_qty int,
  planting_date date,
  plant_difficulty int,
  plant_quality int
);

修复要点 #

1. 先声明字段再关联外键：所有外键要引用的字段，必须先在当前表中定义（比如garden表先加user_id INT，再关联外键）。
2. 调整表创建顺序：先创建被依赖的基础表（如sproutshareuser、plant），再创建中间关联表（如garden、plantdisease），最后创建依赖这些中间表的userplant。
3. 简化外键写法：可以在声明字段时直接关联外键，这种方式更简洁，也避免了单独定义约束时的字段遗漏问题。

内容的提问来源于stack exchange，提问作者Gnome-Improvement713