CANNOT ADD FOREIGN KEY CONSTRAIN
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) );