MySQL foreign keys - always a good idea?

Question

Are MySQL foreign keys always a good idea? I mean if the field I'm using is required for every row and it references another table, should I 100% of the time use a foreign key? It seems to me like I would, and it seems like I'd always want to setup ON DELETE CASCADE as well to maintain data integrity.

canon · Accepted Answer · 2011-03-10 14:35:35Z

1

If you have a relationship then it's a good idea to setup your foreign keys. Foreign keys are sort of a key component of relational databases. However, you don't necessarily need cascading deletes. Simply by implementing your foreign keys you'll maintain data integrity. Any delete will fail when conflicting with your foreign key constraints.

answered Mar 10, 2011 at 14:35

canon

41.9k10 gold badges77 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ben Over a year ago

Any delete will fail when conflicting with your foreign key constraints. - what do you mean by this? Can you give me an example of when it will fail?

canon Over a year ago

Table a has a column b_id which references the id column in table b. You will experience a failure if you try to delete a record from b if it's referenced in a, i.e.: it won't get deleted and you'll get an error message. To be honest, I'm not sure exactly what mysql's behavior will be in this case... but it should fail.

Erkan Haspulat · Accepted Answer · 2011-03-10 14:38:47Z

Foreign keys are the relations of a Relational Database. You should use them whenever you can in an OLTP system.

Collectives™ on Stack Overflow

MySQL foreign keys - always a good idea?

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related