Page 1 of 1

Foriegn keys best practice?

Posted: Thu Nov 27, 2008 4:38 pm
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

Re: Foriegn keys best practice?

Posted: Thu Nov 27, 2008 5:37 pm
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.

Re: Foriegn keys best practice?

Posted: Thu Nov 27, 2008 6:03 pm
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.

Re: Foriegn keys best practice?

Posted: Fri Nov 28, 2008 9:16 am
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.

Re: Foriegn keys best practice?

Posted: Fri Nov 28, 2008 9:24 am
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.

Re: Foriegn keys best practice?

Posted: Fri Nov 28, 2008 3:07 pm
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.

Re: Foriegn keys best practice?

Posted: Fri Nov 28, 2008 7:46 pm
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.