如何创建可移动外键？
基本上情况是这样的（简化的伪代码）：
我有 2 个表人和国家。
人员表：
KEY INT ID, STRING NAME, STRING COUNTRY
国家/地区表：
KEY INT ID, STRING COUNTRY_NAME
客户端可以控制国家/地区表中的内容，因此他可以在列表中添加和删除县。创建人员时，该国家/地区值会显示在下拉列表中。创建人员后，国家/地区字符串值将插入人员行的国家/地区列中。
对我来说，该 Person 应该具有对国家/地区的外键引用，但由于客户端可以控制国家/地区表中显示的内容，因此它们被保留为单独的表，因为您不能只删除使用的国家/地区，（参照完整性等）。这是我的一位同事提出的在这种情况下不使用外键的论点，但我觉得这个问题应该有更好的解决方案，那么，我的同事是对的还是有更好的解决方案？
客户端可以添加和删除国家/地区表中的值，但如果从国家/地区表中删除某个值，则使用该值的创建者应保留其值。
Basically the situation is like this (simplified, pseudo code):
I have 2 TABLES person and country.
Person table:
KEY INT ID, STRING NAME, STRING COUNTRY
Country table:
KEY INT ID, STRING COUNTRY_NAME
The client has control over what the contents are in the Country Table, so he can add and remove counties to the list. This country values appear in a drop down list when creating a person. When the person is created the country strings value is inserted in the country column of the Person row.
To me, it makes sense that that Person should have a foreign key reference to country, but because the client has control over what appears in the Country table, they are kept as separate tables, because you can't just remove used countries, (referential integrity and all). This is an argument a colleague of mine made to not use foreign keys in this case, but I feel like there should be a better solution for this problem, So, is my colleague right or is there a better solution?
The client can add and remove values in the country table but if a value is removed from the country table, created person who used that value should keep their value.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
创建一个 isactive 列，历史记录的数据将显示最初选择的县（或其更新值，见下文），并且可以调整下拉列表以仅显示活动国家/地区。在任何情况下都不应删除外键约束。这就是数据完整性问题的根源。
将想法设置为空通常也是一个不好的想法。您不想丢失有关该人所在国家/地区的数据。
如果该国家/地区可能会发生变化（而不是添加新国家/地区），那么您可以使用级联更新，但在这种情况下最好使用代理键（这不应该改变）。然后，如果名称发生更改，它会在各处反映出来，而无需更新数百万条子记录。
Create an isactive column, the data for historical records will then show the county chosen orginally (or it's updated value, see below) and the drop down list can be adjusted to only show the active countries. Under no circumstances should you remove the foreign key constraint. That is a recipe for data integrity issues.
The setting to null idea is generally a bad one as well. You do not want to lose data about the country the person is in.
If the country might change (as opposed to new countries being added), then you can use cascade update, but it is better in this case to use a surrogate key (which should not change). Then if the name is changed, it is reflected everywhere without having to update millions of child records.
您可以作为
FOREIGN KEY的选项指定删除引用表的记录时要执行的操作：ON DELETE ([RESTRICT | CASCADE | SET NULL | NO ACTION]) （这些是 MySQL 中提供的选项） 。一般来说，您应该使用外键。例如，如果有人引用一个国家，为什么可以删除它？如果没有外键，您需要手动检查引用。
在您的示例中，我还将使用国家/地区的 ID 字段作为参考。您可以更改国家/地区的名称，所有引用该 ID 的人都将获得此信息（当然，您需要加入）。
此更新的另一个解决方案是为原始外键指定一个
ON UPDATE CASCADE选项，从而在更改名称时更新相应人员的所有Country字段一个国家的。You can specify as an option to the
FOREIGN KEYwhat to to when a record of the referenced table is deleted:ON DELETE ([RESTRICT | CASCADE | SET NULL | NO ACTION])(These are the option available in MySQL).In general you should use foreign keys. For example, why should it be possible to delete a country if persons refer to it? Wihtout a foreign key you need to manually check for references.
In you example I wouls also use the ID field of the country as a refernce. You can rhen change the name of a country and all persons which reference the ID will get this information (Sure, you will need a join).
Another solution for this update would be to specify an
ON UPDATE CASCADEoption to the original foreign key, resulting in an update of allCountryfields of the corresponding persons when changing the name of a country.数据库已经被设计为处理仍然由外键引用的记录的删除。
您仍然应该使用外键，并指定您希望它在
删除时的行为方式。如果您希望允许删除仍然引用的记录（国家/地区），请使用如果您希望外键防止引用的国家/地区消失，请忽略
删除 code> 子句并尝试删除仍引用的国家/地区将失败。这通常是期望的行为；尝试将数据库置于无效状态应该失败。无论关联记录的外键设置为
null，还是阻止引用记录的删除，外键仍然会强制执行并维护引用完整性。这就是他们的工作，而且他们非常擅长。Database are already designed to handle removal of records still referenced by foreign keys.
You should still use a foreign key, and specify how you want it to behave with
on delete. If you want to allow records (countries) still referenced (by persons) to be removed, useIf you want the foreign key to prevent referenced countries from disappearing, leave off the
on deleteclause and attempts to delete countries that are still referenced will fail. This is typically desired behaviour; attempts to put the database into an invalid state should fail.Whether the associated record's foriegn key is set to
null, or the referenced record's deletion is prevented, the foreign key still enforces and maintains referential integrity. That's their job, and they're pretty good at it.