If you go to this part on “foreign key relationships” in Introduction to Databases and MySQL:
you will see these SQL statements:
insert into orders set FK_cust_no=10; insert into orders set FK_cust_no=11;
The problem with this is that it will trigger the foreign key constraint – how do you drop this?
If you go to W3Schools.com’s SQL Foreign Key Constraint, you will see these statements:
ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
So if you wanted to drop the foreign key constraint you would type:
ALTER TABLE Orders ADD FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
the problem is you probably didn’t name your foreign key constraint and it was generated automatically by MySQL (maybe InnoDB was active?)
so what to do? Type in:
SHOW CREATE TABLE orders;
where the table you where you want to drop the foreign key constraint is, and you’ll get this message:
Aha! So the name of the foreign key constraint is actuall
orders_ibfk_1
(who knew?)
Drop the foreign key constraint by typing:
ALTER TABLE orders
DROP FOREIGN KEY orders_ibfk_1;
You can then proceed to typing in these statements:
insert into orders set FK_cust_no=10; insert into orders set FK_cust_no=11;
Not getting any errors? Good!
What if you now try to put back the foreign key constraint using:
ALTER TABLE orders ADD CONSTRAINT orders_ibfk_1 FOREIGN KEY(FK_cust_no) REFERENCES customer(cust_no);
You can’t! You’ll get another error (actually, more of the same …) :
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint
What to do?
Delete the rows that you added:
DELETE FROM orders WHERE order_no>11;
After deleting those rows type:
ALTER table orders ADD FOREIGN KEY(FK_cust_no) REFERENCES customer(cust_no);
you should find the foreign key constraint back if you type:
SHOW CREATE table orders;
Happy MySQL-ing! 
