0

I keep getting this error, I used InnoDB for all tables, menuID, and customerID, are primary keys in their respective tables, and the datatypes appear to be the same. ERROR 1215 (HY000): Cannot add foreign key constraint

Sorry if I am missing something simple, I am new to mySQL.

   create table customers(
customerID int not null auto_increment primary key,
LastName varchar(255) not null,
FirstName varchar(255) not null,
email varchar(255) not null,
password varchar(255),
phone varchar(255),
creditCard varchar(255),
address varchar(255),
time timestamp)
Engine=InnoDB;

 create table menu(
 menuID int not null auto_increment primary key,
 typeID int,
 itemName varchar(255),
 price varchar(255)) 
Engine=InnoDB;

create table orders(
orderID int not null,
customerID int not null,
menuID int not null,
PRIMARY KEY (orderID, customerID, menuID),
FOREIGN KEY (customerID) REFERENCES customers(customerID) on delete set null on update cascade,
foreign key (menuID) references menu(menuID) on delete set null on update cascade )
Engine=InnoDB;
2
  • 1
    Edit your question and add the full error message returned. Commented Mar 29, 2018 at 23:17
  • From this old answer: try SHOW ENGINE INNODB STATUS; for more information. Best guess though it's the 'set null on update' when your columns are not null. Commented Mar 29, 2018 at 23:17

1 Answer 1

1

You can't use ON DELETE SET NULL for a foreign key column that you declared NOT NULL.

See this answer for a long checklist of things to check as possible causes of foreign key errors: MySQL Creating tables with Foreign Keys giving errno: 150

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.