-1

What are the pros and cons of creating table relationships in a MySQL database using queries (JOINS) as opposed to doing it with DDL using Foreign key and referential integrity constraints? I have received a database that has not relationships (No FK) on its tables to identify relationships among tables. The relationships are being created on JOINS when data is being queried.

The Georgia
  • 1,005
  • 7
  • 23
  • 59

1 Answers1

0

The main reason for using foreign keys is that data is always consistent in the database. This is "independent" from joins - when you use foreign keys you still need to use joins.

In MySQL it also has a nice side effect: if you use foreign keys, you have to define indexes which might speed up queries.

But with foreign keys you can make sure, that the row which you are referring must exist in the database. See https://en.wikipedia.org/wiki/Foreign_key.

MrTux
  • 32,350
  • 30
  • 109
  • 146
  • Thanks MrTux for your prompt response. I know that you still use JOINS on tables with FKs. I also know that defining tables with FK keeps data integrity. I guess what i was trying to get was...Is it a No No designing a database without using foreign keys to define table relationships or it does not matter that much? Besides data integrity, what are the major disadvantages of designing a database this way i.e, no FKs? Thanks. – The Georgia Sep 02 '14 at 11:43
  • I won't design any DB w/o FK any more. Only disadvantage is that inserting into the child table might be a bit slower since the existance of the key in the parent table has to be done. But I think it's neglectible compared to a "clean" and consistent DB. FKs are only supported for InnoDB tables in MySQL, that could also be a reason why lots of DBs don't use FK, because those are just MyISAM (I don't recommend that either - InnoDB has row based locking and supports transactions). – MrTux Sep 02 '14 at 12:15
  • Thanks MrTux. Exactly the kind of response i was looking for. – The Georgia Sep 02 '14 at 12:23