Mysql JOIN with or without Foreign key?

Asked

Viewed 609 times

6

What’s the difference between relating 2 tables using and not using a Foreign key?

With regard to performance, it is recommended to use Foreign key to make the relationship?

In which situation would be indispensable and in which situation it would be unnecessary to use a Foreign key to make the relationship?

mysql join foreign-key

  • A situation that differs both, in a quick "response", the foreign key ties the tables, so usually one can not have data if the "link" with another table (or even the same table), we can say that is a "mandatory" link. As soon as I have time, I’ll try to formulate a response.

1 answer

3


What is the difference between relating 2 tables using and not using a foreign key?

R: the difference is that when you don’t use foreign key to relate its tables to the restriction of the relationship if it exists, it will have to be controlled by its application, this can be a major disadvantage as the integrity check of your tables will be decentralized differently from when the database server controls the integrity of relationships.

With regard to performance, it is recommended to use foreign key to make the relationship?

R: When we set up a foreign key what the database server actually does is create a trigger internal, so you can check the referential integrity of each inserted, updated or deleted row. This results in overhead in the execution of batch operations for example where many changes are processed by the server in an operation, imagine that your application will make an import of movement data from a supermarket where thousands of sales happen in one day; in such situations the use of foreign keys greatly affect performance, what many programmers often do is disable the foreign key before costly operations, example mysql:

SET foreign_key_checks = 0;

In which situation would it be indispensable and in which situation it would be unnecessary to use a foreign key to make the relationship?

R: This depends a lot on the business rule of its application but in general are indispensable when the existence of the entity of one table depends on another and this has great importance in the system and is also accessed by different locations in the application; requiring a centralization. Now tables where foreign keys are dispensable may be for entities of secondary registers that do not have great importance in the application, but it depends a lot.

Reference: https://dev.mysql.com/tech-resources/articles/mysql-enforcing-foreign-keys.html

Browser other questions tagged mysql join foreign-key

You are not signed in. Login or sign up in order to post.