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
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.– Guilherme Nascimento