MySQL 外键：我应该设置它吗？
我需要为这种情况设置外键吗？ 我在数据库设计方面很弱，尤其是在mysql方面。我可以知道如果我想为它们设置外键，我应该为它们设置什么？万一人员删除...所有对people_id的引用都会一起删除，当表太多时是否可以设置？ 感谢回复
Do i need to setting the foreign key for this situation ?
i'm weak in database design, especially in mysql.. may i know if i want to setting foreign keys for them, what should i setting for them ? in case if the people delete... all referral to people_id will delete together, is it possible to set while the table is too many ?
Thx for reply
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
是的。外键约束强制引用完整性，这是确保数据可靠和高质量的关键原则。否则，您的 people_address 表可能会引用 people 表中不存在的 people_id 值，并且该值将成为孤立值。外键约束可以防止这种情况发生。
所以，就这么做吧。确实没有充分的理由不这样做。
在 people_email 表上定义如下外键：
ALTER TABLE people_email ADD CONSTRAINT FOREIGN KEY (people_id) REFERENCES people (id) ON DELETE CASCADE;
这意味着您无法在 people_email 中输入该表中的 people_id 不存在于 people 中的记录。此外，如果您删除 people 中的父行，则 people_email 中引用它的行也会自动删除。
我个人更喜欢手动删除子表中的所有行，而不是使用级联删除。这是一些额外的应用程序开发工作，但它让我感觉更安全，并且还允许我对锁定进行一些控制并确保查询尽可能高效。
Yes. Foreign key constraints enforce referential integrity, a key tenet of ensuring that your data is reliable and of high quality. Otherwise, your people_address table could reference a people_id value that doesn't exist in the people table, and would be an orphan. A foreign key constraint would prevent that from happening.
So, just do it. There's really no good reason not to.
Define foreign keys such as the following on the people_email table:
ALTER TABLE people_email ADD CONSTRAINT FOREIGN KEY (people_id) REFERENCES people (id) ON DELETE CASCADE;
This will mean that you cannot enter a record in people_email where the people_id in that table does not exist in people. Also, if you delete the parent row in people, the rows referencing it in people_email with get automatically deleted.
I personally prefer to manually delete all the rows from the child tables and not use cascade deletes though. It's a bit of extra app dev work, but it makes me feel safer and also allows me some control over locking and ensuring that queries are as efficient as possible.