MySQL Forums
Forum List  »  Newbie

CANNOT ADD FOREIGN KEY CONSTRAIN
Posted by: Latifah Lewis
Date: December 05, 2021 08:49PM

I am new to learning SQL and I have a project that just doesnt make sense to me. Now when I type in the following code down below down, I get hit with the message "Cannot add foreign key constraint". The whole idea of adding foreign keys into SQL code is super confusing. HELP!!!

CREATE TABLE Coffeeshop ( Shop_id INT UNSIGNED NOT NULL,Shop_Name VARCHAR(50) NULL,
Shop_City VARCHAR(50) NOT NULL, Shop_State CHAR(2) NOT NULL, PRIMARY KEY (Shop_id) );

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

CREATE TABLE employee (Employee_Id INT UNSIGNED NOT NULL AUTO_INCREMENT, Employee_Shop_Id INT,
Employee_FName VARCHAR(30) NOT NULL, Employee_LName VARCHAR(30) NOT NULL,
Employee_Hire_Date DATE NOT NULL, Employee_Job_Title VARCHAR(30) NOT NULL,
PRIMARY KEY (Employee_Id));

ALTER TABLE Employee ADD INDEX checks (Employee_Shop_Id), ADD CONSTRAINT checks FOREIGN KEY (Employee_Shop_Id)
REFERENCES Coffee (Employee_Shop_Id)

CREATE TABLE Coffee (coffee_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
shop_id INT UNSIGNED NOT NULL,supplier_id INT NOT NULL,coffee_name VARCHAR(30) NOT NULL,
price_per_pound NUMERIC (5,2) NOT NULL, PRIMARY KEY (coffee_id) );

Options: ReplyQuote


Subject
Written By
Posted
CANNOT ADD FOREIGN KEY CONSTRAIN
December 05, 2021 08:49PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.