MySQL foreign key ratio to mysql_insert_id for table binding

Question

This is for some kind of proof of concept design, to make everything work, but do not want to have the full code. For my database, I tried to get the true foreign key relationship using innoDB but could not get it.

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

This is terrible? Everything seems to work fine, and I can attach and bind IDs as needed. What are the benefits of using foreign keys my method gives me (besides updating / removing cascading)?

Answer 1

To create a relation (master-> detail), you must always supply the keys yourself using mysql_insert_id , natural keys, or the key created by your applications. FOREIGN KEY will not do what works for you.

What does FOREIGN KEY do

• Helping you ensure the consistency / integrity of your data (therefore, a "detail" entry does not indicate an invalid parent)
• Performs deletion or modification of master records for master records (ON DELETE ..., ON UPDATE ...).
• It also creates an index in your "detail" -table for "master_id" -row, if it does not already exist (well, you can also do this without FOREIGN KEY )
• There is also some kind of documentary purpose, for example, an ERM tool can reorganize the relationship model from your schema (well, this point is a little long)

The cost of adding a FOREIGN KEY operator is low compared to its benefits.