如何确定数据库表关系是否值得强制执行引用完整性？
我有一个应用程序，其中大多数数据库表与另一个表都有很强的关系。 目前我正在使用外键强制引用完整性，但我想知道这是否真的是最好的方法。 主表中的数据可以由业务用户从管理界面删除，这意味着必须执行级联删除（或编写多个删除语句），但我不确定我是否真的想删除所有其他数据同一时间。 这可能是很多数据，“可能”在以后有用（也许是报告？）。 然而，除非与主表存在关系，否则辅助表中的数据对于应用程序本身基本上是无用的。
I have an application where the majority of the database tables have a strong relationship to one other table. Currently I am enforcing referential integrity with foreign keys, but I'm wondering if this is really the best approach. Data in the primary table can be deleted from an admin interface by business users, which means having to do a cascading delete, (or writing several delete statements), but I'm not sure if I really want to remove all that other data at the same time. It could be a lot of data that *might* be useful at a later date (reporting maybe?). However, the data in the secondary tables is basically useless to the application itself unless the relationship exists with the primary table.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
如果可以的话，我总是保留数据。 由于您已经有了外键，因此您拥有一些针对完整性违规的内置保护。
如果您的用户想要“删除”记录，从而将其隐藏在应用程序中，请考虑“虚拟删除”策略 - 将记录标记为非活动状态，而不是从数据库中物理删除它。
至于实现，根据您的数据库，添加相当于表的布尔/位逻辑的内容。 默认情况下，所有行都被分配 true/1； “删除”被标记为 false/0。
Given the option, I always keep data around. And since you already have foreign keys in place, you have some built-in protection from integrity violations.
If what your users want is to "delete" a record, therefore hiding it from the application, consider the "virtual delete" strategy -- mark a record as inactive, instead of physically removing it from the database.
As for implementation, depending on your db, add whatever equates to boolean/bit logic for your table. All rows get assigned true/1 by default; "deletes" are marked as false/0.
您可以使用外键和关系来强制引用完整性，而无需使用级联删除。 我很少使用级联删除，因为我总是发现拥有数据并妥善管理/归档它通常比删除它更好。
只需编写您自己的删除逻辑来支持您自己的业务规则。
逻辑删除也非常有效，我广泛使用它们。
You can use foreign keys and relationships to enforce referential integrity without having to use cascading deletes. I seldom use cascading deletes as I've always found it's often better to have the data and manage/archive it well than it is to delete it.
Just write your own delete logic to support your own business rules.
Logical deletions work excellently as well and I use them extensively.
您不想删除某些数据 - 您可能最终会得到恶意数据，您不知道它最初属于哪里。 要么全有，要么全无。
软删除，即在每一行上都有一个位字段来确定记录是否被“删除”，这是一种可行的方法。 这样，您只需在 API 中检查记录是否已删除 == true，并将其对应用程序隐藏即可。
您保留数据，但没有人可以通过应用程序检索它。
You don't want to delete some of the data - you'll likely end up with rogue data, that you have no idea where it belonged in the first place. It's either all or nothing.
Soft delete, i.e. having a bit field on every row that determins if the record is "deleted" or not is the way to go. That way, you simply check if the record is deleted == true in the API, and hide it from the application.
You keep the data, but no one can retrieve it through the application.
我想说的是，通常使用外键约束 - 这可以长期“保护”您的数据库设计以及数据完整性本身。 还存在明确说明设计师决定的限制。
我已经看到在非常大的数据库上放弃了约束 - 如果您比较性能并且存在显着的外键开销，这将是不使用它们的原因之一。
I would say use foreign key constraints as a rule - this "safeguards" your DB design long-term, as well as data integrity itself. Constraints are there also to explicitly state a designer's decision.
I've seen constraints ditched on extremely large databases - that would be one reason not to use them, if you compare the performance and there is a significant foreign key overhead.
我会使用逻辑/软删除。 这基本上意味着向相关表中添加一列（可能是位列
Deleted），这会将特定行标记为已删除。也就是说，“已删除”数据就是：已删除。 因此，从逻辑上讲，它不能用于报告和类似的内容。 为了克服这个问题，我还引入了
Hidden列来隐藏某些行，保留其逻辑含义。I'd use logical/soft delete. This basically means adding one more column (possibly bit column
Deleted) to the table in question, which would mark a particular row as deleted.That said, "deleted" data is just that: deleted. Thus it cannot logically be used in reporting and similar stuff. In order to overcome this, I'd also introduce
Hiddencolumn to hide certain rows retaining their logical meaning.切勿进行物理删除。 您可以添加一个 BOOL 标志 IsDeleted 来指示该记录已被删除。 当您想要“删除”记录时，只需将标志设置为 True 即可。
Never do physical deletes. You can add a BOOL flag IsDeleted to indicate the record is deleted. When you want to "Delete" a record, simply set the flag to True.