96

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?

Macha
  • 14,366
  • 14
  • 57
  • 69

4 Answers4

124

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.

Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • 1
    The slowdown is minimal, because you usually make FK on indexed fields, which makes finding the relevant values a snap. – Seb Apr 16 '09 at 17:37
  • 5
    That's why I wrote "a little" :) Actually, of you delete lots of rows, the underlying `JOIN` may be much less efficient that two DELETE's using FULL TABLE SCAN's – Quassnoi Apr 16 '09 at 17:39
  • can I create a new employee with a `dept_id` which is absent in `department(id)` – Nishant Aug 03 '14 at 10:13
  • @Nishant no you can't – Quassnoi Aug 03 '14 at 10:20
  • 2
    Sorry for the down vote. It was accidental, but it is not allowing me to change my vote. – Wondercricket Mar 20 '15 at 21:24
  • 10
    @Wondercricket: I don't even know what to do now, it's gray area in the site rules. I think you buy me a beer when you're in Moscow and we're even. – Quassnoi Mar 21 '15 at 16:46
  • May you please tell me what happens when I use `ON` **`UPDATE`** `CASCADE` ? – stack Jul 05 '16 at 14:42
  • 1
    @stack: any update you do to the referenced key cascades to the referencing table. – Quassnoi Jul 05 '16 at 15:50
  • FKs certainly can improve query efficiency, since the DBMS knows there's a 1:many join with the referenced table & column. – philipxy Feb 05 '17 at 00:26
  • FKs make some wrong *updates* fail. FKs cannot "make some wrong queries fail". Constraints don't need to be known to query, and when they hold, some expressions always return the same result as other queries that wouldn't otherwise. So with sufficient qualification & sloppiness, FKs "make" some "wrong" queries *succeed*--FKs that now hold but didn't make some previously wrong queries correct--declaring FKs that held but weren't declared confirms that some otherwise apparently wrong queries are correct. – philipxy Jun 14 '20 at 08:10
34

The main benefits of using real foreign keys are ensuring data integrity, and being able to set up cascading actions on related items when something is modified or deleted.

For example, imagine you're programming a forum. You have a "topics" table with primary key topics.topic_id, and you have a "posts" table where posts are attached to topics with the column posts.topic_id, which is a foreign key to the topics table.

This foreign key relationship ensures that every post is attached to a valid topic. If the only topic you have has ID #1, it's impossible for there to exist a post in the database attached to topic #2. The database ensures this.

For the cascading benefit, you can set it up so that if a topic is deleted from the topic table, the database automatically deletes all the posts in the posts table that were attached to this topic. This is nice because it removes a step that you have to remember to do manually, which can get quite complex when you have many tables linked together. With foreign keys all the relationships can be cleaned up automatically.

Chad Birch
  • 73,098
  • 23
  • 151
  • 149
11

1.FOREIGN KEYS just ensure your data are consistent.

2. If we apply on delete cascade to the foreign key definition,referencing row will delete automatically when parent row will delete.

3. If we apply on Update Cascade to the foreign key definition,Child row will update automatically when parent row will update.

Query: ALTER TABLE child ADD FOREIGN KEY(parent_id) REFERENCES parent(id) ON UPDATE CASCADE ON DELETE CASCADE ;

  1. you can not delete direct parent table , first delete foreign key from child table than delete parent table.
Gaurav Kumar
  • 175
  • 2
  • 4
  • 7
    Finally realized what confused me about foreign key examples. As a father of four, I'm not used to the **child keeping track of the parent**. In another forty years or so this might no longer seem backwards. – Bob Stein Sep 24 '17 at 20:20
  • 1
    Thanks for giving your example tables names like "child" and "parent"... it's actually rather helpful and I wish the official documentation did it! – mike rodent Dec 04 '17 at 19:48
7

The main advantage is that you can limit which values you can enter in the table; if you try to enter a value that doesn't exist in the referenced table, you won't be able to do it.

Also, if you update or delete the value in the referenced table, you can set it to automatically update the value or delete in cascade any row containing that value.

It's indeed a great feature leveraging your code.

Seb
  • 24,920
  • 5
  • 67
  • 85