更新：当 MySQL 中没有引用完整性功能时，如何在 MySQL 中实现外键概念？
我的问题是关于 MySQL 数据库中的引用完整性概念。由于我们的 DBA 的某种限制，我们不允许使用 MySQL 的引用完整性功能，所以我的问题是“当 MySQL 中没有引用完整性功能时，我们如何在 MySQL 中实现外键概念？”
谢谢。
My question is regarding referential integrity concept in MySQL Database. Due to some kind of restrictions from our DBA we are not allowed to use referential integrity features of MySQL and so my question is "How can we implement Foreign Key concept in MySQL when we do not have referential integrity features in MySQL ?"
Thanks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
我假设您正在使用 MyISAM 表？你几乎只能自己做检查。
因此，如果 tableB 依赖于 tableA，那么在从 tableA 中删除之前，必须先从 tableB 中删除（或执行其他更新）。通过插入，您将在主表中创建记录，然后在从属表中创建记录。
这很麻烦，而且我发现保留 MyISAM 表的唯一原因是全文索引。如果我更多地了解您的数据库限制，我可能会提出其他建议。
ETA：
如果他限制你使用 InnoDB 表，我想知道你的 DBA。无论如何，如果他出于安全原因不允许您创建和使用 Innodb 表，那么他实际上不太可能让您使用存储过程或触发器。因此，您实际上将不得不编写一个应用程序来执行 CRUD 操作一些脚本语言。
因此，您需要考虑针对每种具体情况对这些操作的限制。也许最简单的方法是映射您的数据库架构，就好像它确实强制了引用完整性一样。包括由于任何表的更改而可能发生的操作（如果有）的详细信息。操作后遗症选项有RESTRICT、SET NULL 和CASCADE。
一旦您知道数据库应该如何响应，您就可以对查询进行相应的编程。
因此，如果员工有地址，并且删除员工时地址应该消失：
Innodb 版本（其中地址具有员工的外键，以及
ON DELETE CASCADE操作）MyISAM 版本：
我希望这会让事情变得有点更清晰。
I assume you are using MyISAM tables? You're pretty much stuck with doing the checks yourself.
So if tableB depends on tableA, then before you delete from tableA you have to delete from tableB (or perform other update) first. With inserts you'd create the record in the main table and then create the record in the dependant table.
It's cumbersome and the only reason I've ever found for keeping a MyISAM table was FULLTEXT indexing. If I knew more about your db restrictions I could possibly make other suggestions.
ETA:
If he's restricting you from using InnoDB tables, I'd wonder about your DBA. In any event, if he won't let you create and use Innodb tables for security reasons, it's really unlikely he'll let you use stored procedures or triggers. So you'll essentially be stuck writing an application to do your CRUD operations in some scripting language.
Therefore you need to think about what the restrictions on these operations have to be for each specific case. Probably the easiest way is to map out your db schema as though it did enforce referential integrity. Include details on what, if any, actions may happen due to a change in any table. Operation sequelae options are RESTRICT, SET NULL and CASCADE.
Once you know how your db ought to respond, you can programs you queries accordingly.
So if Employees have Addresses, and addresses should disappear when an Employee is deleted:
Innodb version (where Addresses has foreign key for Employees, and
ON DELETE CASCADEaction)MyISAM version:
I hope this makes things a bit clearer.
这意味着所有开发人员都必须专门了解应该存在的关系，并且在执行插入或更新时从父表向下到所有子表工作，在执行删除时从子表向上到父表工作。当然，问题是并不是每个开发人员都会意识到所有涉及约束的表都没有设置。如果您有多层关系，则需要一直到链的底部才能进行删除。
如果你使用 ORM，我想你可以在那里定义关系？不确定从未使用过，但我认为你可以。无论如何都值得研究一下。
如果您不使用 ORM 并且无法定义引用完整性，至少将关系存储在表中的某个位置，以便开发人员可以查找哪些表会受到影响。
如果您无法具体定义引用完整性，另一种方法是通过触发器强制执行。
编辑以扩展触发器：
如果您在父表上创建替代触发器（这些在我的 sql 中可用吗？），您可以通过在执行实际删除之前指定要删除的表来模仿级联删除的行为（这就是我们在在我们进行级联删除之前）。您还可以指定在更新中主键发生更改时要更新的表（希望您的设计有一个不会更改的键，但如果您使用任何自然键则不会）。任何子表上的插入触发器都会查看父表中是否存在该键字段值，如果不存在则拒绝它。
What this means is that all developers must know specifially the relationships that should be there and work from the parent table down through all the child tables when doing inserts or updates and work from the child tables up through the parent tables when doing deletes. Of course the problem is that not every developer will be aware of all the tables involved in constraints aren't set. And if you have layers of relationships, you need to go allthe way to the bottom of the chain to do a delete.
If you use an ORM, I think you can define relationships there? Not sure never used one but I think you can. Worth looking into at any rate.
If you aren't using an ORM and you can't define referential integrity, at least store the relationships in a tables somewhere so the developers can look up what tables would be affected.
Another approach if you can't define referential integrity specfically would be to enforce it through triggers.
edited to expand on triggers:
If you create an instead of trigger (are these available in my sql?) on the parent table you can mimic the behavior of a cascading delete by specifying the tables to delete from before doing the actual delete (this how we used to do it in the old days before we had cascading deletes). You can also specify the tables to update if the primary key changes in an update (hopefully your design has a key that won't change, but not if you used any natural keys). An insert trigger on any child tables would see if the key field value existed in the parent table and then reject it if it did not.
我曾经使用 myIsam 表。
因此，这意味着您需要手动完成这项工作。
例如，在删除某些父行之前，您需要执行一些选择。
这是可以做到的，但如果没有引用完整性就不一样了。但有效。
I used to work with myIsam table.
So, this means you need to do manualy the job.
You will need to do some SELECTs before delete some parent row for example.
It's possible to do, but without referencial integrity isn't the same. But works.
如果可以使用存储过程来使“选择”更容易。
另一个好的方法是将数据库模式隐藏在模型后面，并通过其接口使用它。
If might use stored procedures to make 'selects' easier.
Another good apporach is to hide the database schema behind a model, and use it through it's interface.
在这种情况下，DBA 经常听到的一个论点是“如果使用 RI，那么卸载/加载以进行重组对我们来说就更难了”。
这本身当然是正确的，但问题在于，“更难”通常被认为意味着“太难了，以至于我们 DBA 几乎不可能完成我们的工作”。
那就是公牛。
One argument often heard from DBA's in this context is that "if RI is used, then unloading/loading to reorganise is harder for us to do".
This is of course true, per se, but the vilain is that the 'harder' is usually suggested to mean 'so much harder that it becomes nigh impossible for us DBAs to do our jobs'.
And THAT is bullocks.
有一些持久性框架可以为您做到这一点。为了在尽可能多的数据库上工作，这些框架不依赖于 FK 等数据库特定功能，而是自行实现。 KODO 就是一个例子。
There are persistence frameworks that will do it for you. In order to work on as many DBs as possible those frameworks don't rely on DB specific features like FKs and implement it them self. KODO is one example.