MYSQL 1215 cannot add foreign constraint
So I don’t know why i cant add the foreign constraint in the table ORDER_LINE, i made sure all the types are correct. Please help, it keeps on giving me the error
Error Code: 1215. Cannot add foreign key constraint
How can i find which table is updated or affected by website action for a large my sql database How can I write an Excel plugin that loads a SQL Server data table? How can I tell what edition of SQL Server runs on the machine? Improving performance on SQL query 900 byte index size limit in character length Dynamic Column SQL Pivot Table
CREATE TABLE IF NOT EXISTS Customer ( Customer_ID int NOT NULL AUTO_INCREMENT, Customer_Name VARCHAR(50) NOT NULL, Customer_Age INT UNIQUE, Customer_Address VARCHAR(255), Customer_City VARCHAR(255), Customer_State VARCHAR(50), Customer_Zip VARCHAR(20), PRIMARY KEY(Customer_ID) ); CREATE TABLE IF NOT EXISTS Sales_order ( Order_ID int AUTO_INCREMENT, Order_date DATE, Customer_ID int, PRIMARY KEY(Order_ID), FOREIGN KEY(Customer_ID) REFERENCES Customer(Customer_ID) ON DELETE CASCADE ); CREATE TABLE IF NOT EXISTS Products ( Product_ID int AUTO_INCREMENT, Product_Description VARCHAR(255), Product_Finish VARCHAR(50), Standard_Price DECIMAL, Product_Line_ID INT, PRIMARY KEY(Product_ID) ); CREATE TABLE IF NOT EXISTS ORDER_LINE ( Order_ID int, Product_ID int, Ordered_Quantity int, PRIMARY KEY(Order_ID, Product_ID), FOREIGN KEY(Order_ID) REFERENCES Sales_order(Order_ID) ON DELETE SET NULL, FOREIGN KEY(Product_ID) REFERENCES Products(Product_ID) );
One Solution collect form web for “MYSQL 1215 cannot add foreign constraint”
This might be because primary key columns are made
NOT NULL, so your
ON DELETE SET NULL is trying to set an invalid value.
EDIT: In fact, I just tested and that does seem to be the issue — it creates the table if I remove either the
SET NULL or the primary key.