MySQL foreign key relations vs mysql_insert_id to relate tables

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)?

Lucianaluciano

If I understand you correctly you're doing something like mysql_query('INSERT INTO master...'); $master_id = mysqsl_insert_id(); mysql_query("INSERT INTO detail (master_id, ...) values ('$master_id', ...)");. Then no, it's not horrible it's the only thing you can do to create the relation. The only other way is using "natural keys" or keys generated by your application but then the only difference is that you don't get the id with mysql_insert_id but rather from the user. What exactly do you mean by foreign keys? – Councillor

I'm trying to compare using mysql_insert_id to actually baking the relations right into the database with: ADD CONSTRAINT 'mytable' FOREIGN KEY ('my_id') REFERENCES 'othertable' ('other_id') ON DELETE CASCADE ON UPDATE CASCADE; – Lucianaluciano

Not letting me edit my comment, but to confirm your assumption: yes, that's exactly what I'm doing. – Lucianaluciano

Thank you for the clarification, I have now added an answer - I hope it answers your question. – Councillor

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.

Councillor