Menu
Is it better to use foreign keys in tables or can the same results be achieved with joins?
413
A foreign key is a column or group of columns in one table that contains values that match the primary key in another table. Foreign keys are used to join tables. The following figure shows the primary and foreign keys of the customer and orders tables from the demonstration database.
Yes it will improve the performance of you db if you are checking integrity using foreign key instead of running many queries for checking the record is exist in database in your program. Show activity on this post. For MySQL 5.7, it definitely can speed up queries involving multiple joins amazingly well!
Having active foreign keys on tables improves data quality but hurts performance of insert, update and delete operations. Before those tasks database needs to check if it doesn't violate data integrity. This is a reason why some architects and DBAs give up on foreign keys at all.
Basically, we have only three types of joins: Inner join, Outer join, and Cross join. We use any of these three JOINS to join a table to itself.
Foreign keys are just constraints to enforce referential integrity. You will still need to use JOINs to build your queries.
Foreign keys 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).
94
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
