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
Web API error: "Cannot insert the value NULL into column 'Id' How to get the stored procedure name from a trigger when an update operation was performed? Design SQL Server database correctly Too nested loops sql, ran out internal resources Stored procedure to insert values into dynamic table T-SQL extra column in GROUP BY. How to include in results but exclude from grouping
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.