Cannot Create Tables in Postgres SQL-CodePudding

I am trying to create tables in my postgres database using a .sql file and psql

Here is the command that I am using to load the file

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

After entering my password, I am met with the following error messages

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

Here is the .sql file

/* 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)
);

Why can't the fields user_id and plant_id be referenced? I tried moving those create table scripts to the top of the file but no success.

CodePudding user response：

You have several issues with your .sql file that's why it is not working while trying to create tables and their reference in PostgreSQL, Here is the full file with correction-

/* 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 plantdisease(
  plant_disease_id SERIAL PRIMARY KEY,
  plant_id int, 
  disease_id int,
  CONSTRAINT fk_plant
    FOREIGN KEY(plant_id)
      REFERENCES plant(plant_id),
  
  CONSTRAINT fk_disease
    FOREIGN KEY(disease_id)
      REFERENCES disease(disease_id)
);

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

CREATE TABLE plantpest(
  plant_pest_id SERIAL PRIMARY KEY,
  plant_id int, 
  pest_id int,
  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,
  user_id int,
  soil_id int,
  light_level varchar,
  CONSTRAINT fk_user
    FOREIGN KEY(user_id)
      REFERENCES sproutshareuser(user_id),
  CONSTRAINT fk_soil
    FOREIGN KEY(soil_id)
      REFERENCES soil(soil_id)
);

CREATE TABLE userplant(
  user_plant_id SERIAL PRIMARY KEY,
  user_id int,
  plant_id int,
  garden_id int, 
  plant_disease_id int, 
  plant_pest_id int,
  plant_qty int,
  planting_date date,
  plant_difficulty int,
  plant_quality int,
  
  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)
);

See Reference: