MySQL notes on Foreign Key Contraints

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:

screenshot of what comes out when you type in "show create table orders;"

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! 🙂

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s