55

I was wondering,

What will be my motivation to use constraint as foreign key in MySQL, as I am sure that I can rule the types that are added?

Does it improve performance?

Daniel Vassallo
  • 337,827
  • 72
  • 505
  • 443
Dejell
  • 13,947
  • 40
  • 146
  • 229
  • 2
    similar question has been answered, see http://stackoverflow.com/questions/1876013/ there are a lot of discussions :D – Andy Lin Aug 08 '10 at 10:38

2 Answers2

77

Foreign keys enforce referential integrity. These constraints guarantee that a row in a table order_details with a field order_id referencing an orders table will never have an order_id value that doesn't exist in the orders table.

Foreign keys aren't required to have a working relational database (in fact MySQL's default storage engine doesn't support FKs), but they are definitely essential to avoid broken relationships and orphan rows (ie. referential integrity). The ability to enforce referential integrity at the database level is required for the C in ACID to stand.

As for your concerns regarding performance, in general there's a performance cost, but will probably be negligible. I suggest putting in all your foreign key constraints, and only experiment without them if you have real performance issues that you cannot solve otherwise.

Daniel Vassallo
  • 337,827
  • 72
  • 505
  • 443
  • 4
    Is there any good reasons why NOT to use foreign key constraints? I have worked on various php frameworks and have never needed to use them. I feel like the application should be the one handling and preventing the orphans. Could some one enlighten me? – Angel S. Moreno Sep 30 '13 at 20:58
  • 3
    @AngelS.Moreno: It's a bit like asking "Is there any reason not to use a garbage collector?". Just that in this case, you won't gain performance by managing this kind of DB garbage yourself. Quite the contrary. Issuing all queries required to keep FK relations consistent is not only extremely error prone, it is also extremely inefficient. – thesaint Dec 01 '13 at 09:18
  • 11
    The only good reason not to use FK is when you don't need consistency. – thesaint Dec 01 '13 at 09:23
  • 6
    Yes, there ARE good reasons for not using FK constraints. The most obvious one in my mind is that you can end up locking several rows at once which can have unintended consequences. We've inadvertently done this here and we often find our database slow and totally frozen up for certain records. Please read this article: https://www.percona.com/blog/2010/10/25/impact-of-the-sort-buffer-size-in-mysql/ – John Hunt May 01 '18 at 13:30
  • 1
    Another one is difficulty if you ever want to rename the table https://stackoverflow.com/questions/6384778/renaming-an-innodb-table-without-updating-foreign-key-references-to-it – Adam Millerchip Apr 22 '20 at 09:42
0

One reason is that a set of tables with foreign key constraints cannot be sharded into multiple databases.

Ben Wheeler
  • 6,788
  • 2
  • 45
  • 55