Foriegn keys best practice?

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Foriegn keys best practice?

Post by alex.barylski »

I have never used foriegn keys before however I am now considering it...

I will always have MySQL installed (as it's a hosted service -- I have full control) and I believe it supports FKeys.

As I understand, when I have tables like:

Code: Select all

records
pkid, first_name, last_name, age
 
records_groups:
pkid, fkid, name
fkid in records_groups would map to pkid of records. groups is a list of custom groups which might associate with a record.

If I were to delete record from records then any associated records in recordss_groups would be removed as well.

Up until now, I have always handled this intergriy check in the model. A model facade would essentially provide the API for both tables using their CRUD provided API.

While this is easy enough to implement and maintain...I think it would proablby be more fail safe if I just relied on FKeys...

Do both tables need to be FKEy supported? In MySQL case I believe the Inno tables are what support FKeys.
http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html wrote:Both tables must be InnoDB tables and they must not be TEMPORARY tables.
What is your experience/opinion on this matter?

Cheers,
Alex
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: Foriegn keys best practice?

Post by pickle »

Foreign keys are always a good practice. If nothing else, it stops coding errors or unhandled cases from buggering up your data integrity.

I've never used foreign keys with MySQL, only Postgres. In Postgres, deleting a row could only be done if there were no rows that mapped to it via a foreign key constraint. Deleting a row did not cause a cascading delete.

A foreign key constraint is a relationship between 2 rows in 2 tables. I can't imagine it being possible that only 1 of the tables needs to support foreign keys.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
crazycoders
Forum Contributor
Posts: 260
Joined: Tue Oct 28, 2008 7:48 am
Location: Montreal, Qc, Canada

Re: Foriegn keys best practice?

Post by crazycoders »

just note that the default table handler for MySQL DOESNT support foreign keys and transactions. Only InnoDB table handler does that. So make sure you install the support for InnoDB too, i know that on Windows, you have to configure it when creating the database server, i don't know about linux.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Foriegn keys best practice?

Post by josh »

PCSpectra wrote:If I were to delete record from records then any associated records in recordss_groups would be removed as well.
That's if you use a cascading delete, there's other tools at your disposal as well.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Foriegn keys best practice?

Post by Eran »

From my experience of using foreign keys in mysql, they hinder the development process. They are good practice to employ in a production environment, but you lose much control on manual changes to the database as you must now conform always to the constrains the FK impose. Since schema is evolving and changing during development, this leads (from my experience) to a lot of frustration.
crazycoders
Forum Contributor
Posts: 260
Joined: Tue Oct 28, 2008 7:48 am
Location: Montreal, Qc, Canada

Re: Foriegn keys best practice?

Post by crazycoders »

Agreed to pytrin

While developping, unless you have rock solid schema that will not change, don't use FKs until you are done. It creates a whole lot of problems when debugging... BUT, remember to put in your Fk's before doing the unit testing. Thats where you want your FKs to works and find potential errors not seen when developping.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Foriegn keys best practice?

Post by josh »

pytrin wrote: this leads (from my experience) to a lot of frustration.
While I'd ween towards agreeing with you, they could also be viewed as a benefit. With FKs there's a minor inconvenience to pay, but you can't accidently delete a table forgetting that another table depends on it. It forces you to consider your dependencies, which forces you to think. In the majority of systems that might not be desired though, it depends.
Post Reply