MySQL foreign key relations vs mysql_insert_id to

Question

This is for a sort of proof of concept draft to get things working, but don't want to have completely crap code. For my database, I tried to get true foreign key relations going using innoDB, but couldn't get it.

Instead of using foreign keys, I decided to just pull mysql_insert_id() after inserts, saving it as a variable, then putting that variable into the related table.

Is this horrible? Everything seems to work well, and I'm able to connect and relate ID's as needed. What benefits would using foreign keys give me over my method (besides updates/deletes cascading)?

Answer 1

To create a relation (master->detail), you have to always supply the keys by yourself, either using mysql_insert_id, natural keys or key generated by your applications. The FOREIGN KEY is not going to make that work for you.

What FOREIGN KEY does is

• Helping you enforce the relationship/the integrity of your data (so the "detail" record does not point to an invalid parent)
• Handles deletion or key alterations of master records (ON DELETE ..., ON UPDATE ...).
• It's also creating an index in your "detail"-table for the "master_id"-row if it doesn't exist yet (okay, you could also do that without FOREIGN KEY)
• Has also some kind of documenting purpose for example an ERM-tool could reengineer the relationship model from your schema (okay, this point is a slight long shot)

The cost of adding the FOREIGN KEY constraint statement is small compared to its benefits.