CODEWITHSUNDEEP
mysqlforeign-keys
J.Potenza
J.Potenza

Reputation: 1

mysql error 1215(hy000) cannot add foreign key constraint

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;

Upvotes: 0

Views: 91

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562310

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

Upvotes: 1

Related Questions