Foriegn keys best practice?
Posted: Thu Nov 27, 2008 4:38 pm
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:
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.
Cheers,
Alex
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, nameIf 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.
What is your experience/opinion on this matter?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.
Cheers,
Alex