CODEWITHSUNDEEP
mysqlsqlforeign-keyssqlfiddle
VerizonCorporateOverlords
VerizonCorporateOverlords

Reputation: 1

SQL Fiddle Giving "cannot add foreign key constraint" error

I am trying to figure out what is wrong with my syntax here. I feel like I have matched the examples I have found over the internet... but something must be amiss. Here is the code I am using. I keep getting the error code "cannot add Foreign Key constraint" when trying to run the build the schema.

CREATE TABLE Employe (
  employee_id INT PRIMARY KEY, 
  first_name VARCHAR(30),
  last_name VARCHAR(30),
  hire_date DATE,
  job_title VARCHAR(30),
  shop_id INT,
  FOREIGN KEY (shop_id) REFERENCES Coffee_Shop(shop_id)
);

CREATE TABLE Coffee_Shop (
 shop_id INT PRIMARY KEY, 
 shop_name VARCHAR(50),
 state CHAR(2),
);

 CREATE TABLE Coffee (
  coffee_id INT PRIMARY KEY,
  shop_id INT,
  supplier_id INT,
  coffee_name VARCHAR(30),
  price_per_pound NUMERIC(5,2),
  FOREIGN KEY (shop_id) REFERENCES Coffee_Shop(shop_id),
  FOREIGN KEY (supplier_id) REFERENCES Supplier(supplier_id)
);
CREATE TABLE Supplier (
 supplier_id INT PRIMARY KEY,
 company_name VARCHAR(50),
 country VARCHAR(30),
 sales_contact_name VARCHAR(60),
 email VARCHAR(50) NOT NULL,
);

I just need to figure out what part of my foreign key is incorrectly defined. I know it normally results from an improperly defined primary key, but cannot seem to find where I messed up.

Thanks for your help

Upvotes: 0

Views: 106

Answers (1)

Zak
Zak

Reputation: 7515

You are referencing a KEY that is not set up yet. SQL is linear, so the code gets executed line-by-line and cannot reference future lines (in most cases)

Create your tables in this order:

CREATE TABLE Coffee_Shop (
 shop_id INT PRIMARY KEY, 
 shop_name VARCHAR(50),
 state CHAR(2),
);

CREATE TABLE Supplier (
 supplier_id INT PRIMARY KEY,
 company_name VARCHAR(50),
 country VARCHAR(30),
 sales_contact_name VARCHAR(60),
 email VARCHAR(50) NOT NULL,
);

CREATE TABLE Employe (
  employee_id INT PRIMARY KEY, 
  first_name VARCHAR(30),
  last_name VARCHAR(30),
  hire_date DATE,
  job_title VARCHAR(30),
  shop_id INT,
  FOREIGN KEY (shop_id) REFERENCES Coffee_Shop(shop_id)
);

 CREATE TABLE Coffee (
  coffee_id INT PRIMARY KEY,
  shop_id INT,
  supplier_id INT,
  coffee_name VARCHAR(30),
  price_per_pound NUMERIC(5,2),
  FOREIGN KEY (shop_id) REFERENCES Coffee_Shop(shop_id),
  FOREIGN KEY (supplier_id) REFERENCES Supplier(supplier_id)
);

Upvotes: 1

Related Questions