Is it better to use foreign keys in tables or can the same results be achieved with joins?
+13
A:
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).
Daniel Vassallo
2010-06-01 05:26:12
I just want to point out that although FK are cool, it is rather poor design to *rely* on the DB for the application logic. Your application may break without updates to your database(multiply by installations). May as well minimize it.
sims
2010-06-01 06:24:10
@sims: I don't consider "referential integrity" as application logic. The ability to enforce referential integrity at the database level is required for the C in [ACID](http://en.wikipedia.org/wiki/ACID) to stand.
Daniel Vassallo
2010-06-01 06:32:08
@sims: I agree with Daniel, but would like to extend his reasoning. IMO RI is part of business logic (or 'data logic') it has to be enforced somewhere (ONCE, don't repeat yourself). Since the DB is the central/final data authority, the data must be valid there, hence his point invoking ACID is correct!
lexu
2010-06-01 06:43:49
DRY ACID... yummy... Not using FKs doesn't cause a problem. Abstract the database access. You are better off doing it this way if you have to cater for databases that do not support FK or SP or views or whatever. I'm all for data integrity and tidy DBs. But I also favour portability and maintainability.
sims
2010-06-02 01:39:09
@sims: abstracting the DB access assumes you can enforce the db will only be accessed by that abstraction or interface. In my experience this is not enforceable. "data want's to be free / code craves to have errors". Best to delegate RI to the DB, bake it into the ERD. DRY ACID, yum indeed :-)
lexu
2010-06-02 04:48:09
+7
A:
They don't do the same thing!
- A foreign key enforces data integrity, making sure the data confirms to some rules when it is added to the DB.
A join is used when you extract/query data from the DB by giving rules how to select the data.
Joins work if there are FK or not.
- FK's work if you extract data with or without joins.
CONCLUSION: FK and JOIN don't allow you to achieve the same goal!
lexu
2010-06-01 05:27:42