Basics of Foreign Keys in MySQL?

mysql foreign-keys
Macha picture Macha · Apr 16, 2009 · Viewed 63.3k times · Source

Is there any good explanation of how to use MySQL's foreign key construct?

I don't quite get it from the MySQL docs themselves. Up until now I've been handling things like foreign keys with joins and programming code.

And the second part of the question, are there any improvements to be made by using MySQL's inbuilt foreign keys?

Answer

Quassnoi picture Quassnoi · Apr 16, 2009

FOREIGN KEYS just ensure your data are consistent.

They do not improve queries in sense of efficiency, they just make some wrong queries fail.

If you have a relationship like this:

CREATE TABLE department (id INT NOT NULL)
CREATE TABLE employee (id INT NOT NULL, dept_id INT NOT NULL, FOREIGN KEY (dept_id) REFERENCES department(id))

, then you cannot delete a department if it has some employee's.

If you supply ON DELETE CASCADE to the FOREIGN KEY definition, the referencing rows will be deleted automatically along with the referenced ones.

As a constraint, FOREIGN KEY actually slows down the queries a little.

Extra checking needs to be performed when deleting from a referenced table or inserting into a referencing one.

Related questions

Add Foreign Key to existing table

I want to add a Foreign Key to a table called "katalog". ALTER TABLE katalog ADD CONSTRAINT `fk_katalog_sprache` FOREIGN KEY (`Sprache`) REFERENCES `Sprache` (`ID`) ON DELETE SET NULL ON UPDATE SET NULL; When I try to do this, …

Read More
How to truncate a foreign key constrained table?

Why doesn't a TRUNCATE on mygroup work? Even though I have ON DELETE CASCADE SET I get: ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (mytest.instance, CONSTRAINT instance_ibfk_1 FOREIGN KEY (GroupID) REFERENCES mytest.mygroup (ID)) …

Read More
MySQL Error 1215: Cannot add foreign key constraint

I am trying to forward engineer my new schema onto my db server, but I can't figure out why I am getting this error. I've tried to search for the answer here, but everything I've found has said to either …

Read More