MySQL外键关系vs mysql_insert_id来关联表 [英] MySQL foreign key relations vs mysql_insert_id to relate tables

问题描述

这是一个概念证明的草案，使事情工作，但不希望有完全废话的代码。对于我的数据库，我试图得到真正的外键关系使用innoDB，但无法得到它。

不用外键，我决定只是拉mysql_insert_id （）插入后，将其保存为变量，然后将该变量放入相关的表中。

这太可怕了吗？一切似乎都运作良好，我可以根据需要连接和关联ID。有什么好处，使用外键给我通过我的方法（除了更新/删除级联）？

解决方案

- > detail），你必须自己提供密钥，或者使用 mysql_insert_id ，由你的应用程序生成的自然键或密钥。 FOREIGN KEY 不会使 适合您。

code> FOREIGN KEY 是
$ b

• 帮助您执行数据的关系/完整性所以细节记录不会指向无效的父项）


• 处理主记录的删除或键更改（ON DELETE ...，ON UPDATE ...）。 >
  
• 这也是在你的详细信息表中创建一个索引 - master_id-row的表，如果它还不存在（好吧，你也可以做到这一点，没有 FOREIGN KEY ）


• 还有一些记录目的，例如ERM工具可以从您的模式中重新构建关系模型（好的，这一点很长）

添加 FOREIGN KEY 约束语句的成本比较小对其好处。

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

解决方案

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.

这篇关于MySQL外键关系vs mysql_insert_id来关联表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！