什么时候引用完整性不合适？
我了解需要具有引用完整性，以限制条目中的特定值或可能阻止它们在删除请求时被删除。但是，我不清楚是否有一个有效的用例可以排除这种机制的始终使用。
我想这会分为几个子问题：
- 什么时候引用完整性不合适？
- 字段包含外键列表的多个和/或可能不完整的子集是否合适？
- 通常，这应该是模式结构设计决策还是界面设计决策？ （或者可能两者都没有或两者兼而有之）
有什么想法？
I understand the need to have referential integrity for limiting specific values on entry or possibly preventing them from removal upon a request of deletion. However, I am unclear as to a valid use case which would exclude this mechanism from always being used.
I guess this would fall into several sub-questions:
- When is referential integrity not appropriate?
- Is it appropriate to have fields containing multiple and/or possibly incomplete subsets of a foreign key's list?
- Typically, should this be a schema structure design decision or an interface design decision? (Or possibly neither or both)
Thoughts?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
什么时候引用完整性不合适？
如果通常不在数据仓库中使用引用完整性，其中数据是事务数据库的只读副本。另一个不需要 RI 的例子是当您想要记录包含行 ID 的信息时；维护只读日志表的引用完整性是对数据库开销的浪费。
字段包含外键列表的多个和/或可能不完整的子集是否合适？
有时，您更关心捕获数据而不是数据质量。想象一下，您正在聚合来自不同系统的大量数据，而每个系统本身都存在数据质量问题。有时，您追求更好的数据质量，并且将所有内容都集中在一个地方，即使密钥损坏等也是迈向真正数据质量的起点。这并不理想，但它确实发生了，因为好处可能超过权衡。
通常，这应该是模式结构设计决策还是界面设计决策？ （或者可能两者都不是或两者兼而有之）
有关系统开发的一切都以信息安全为中心，其中的一个关键要素是数据完整性。数据库结构应该尽可能倾向于强制执行这些事情，但是您通常不处理现代数据库系统。有时，您的数据源是带有过时应用程序的老式 AS400。有时您必须构建一个提供数据完整性的数据和业务层。
只是我的想法。
When is referential integrity not appropriate?
Referential intergrity if typically not used on Data Warehouses where the data is a read only copy of a transactional datbase. Another example of when you'd not need RI is when you want to log information which includes row ids; maintaining referential integrity for a read-only log table is a waste of database overhead.
Is it appropriate to have fields containing multiple and/or possibly incomplete subsets of a foreign key's list?
Sometimes you care more about capturing data than data quality. Imagine you are aggregating a large amount of data from disparate systems which each in their own right suffer from data quality issues. Sometimes you are after the greater good of data quality and having everything in one place even with broken keys etc. represents a starting point for moving towards true data quality. It's not ideal, but it does happen as the beenfits could outweigh the tradeoffs.
Typically, should this be a schema structure design decision or an interface design decision? (Or possibly neither or both)
Everything about systems development is centered around information security, and a key element of that is data integrity. The database structure should lean towards enforcing these things when possible, however you often are not dealing with modern database systems. Sometimes your data source is an old school AS400 with long-antiquated apps. Sometimes you have to build a data and business layer which provide for data integrity.
Just my thoughts.
我听说过的唯一情况是，如果您要将大量数据加载到数据库中；在这种情况下，只要您确定数据有效，关闭引用完整性可能是有意义的。加载/迁移完成后，应重新打开引用完整性。
关于将数据验证规则放入编程代码与数据库中存在争议，我认为这取决于软件的用例。如果单个应用程序是通往数据库的唯一路径，您可以将验证放入程序本身，并且可能没问题。但是，如果多个不同的程序同时使用数据库（例如您的应用程序和您朋友的应用程序），您将需要数据库中的业务规则，以便您的数据始终有效。
通过“验证规则”，我正在谈论诸如“购物车中的商品>”之类的规则。 0'。您可能需要也可能不需要验证规则。但我认为主键/外键总是很重要（或者您稍后可能会发现您希望拥有它们）。我认为如果你想在某个时候进行复制，它们是必需的。
The only case I have heard of is if you are going to load a vast amount of data into your database; in that case, it may make sense to turn referential integrity off, as long as you know for certain that the data is valid. Once your loading/migration is complete, referential integrity should be turned back on.
There are arguments about putting data validation rules in programming code vs. the database, and I think it depends on the use cases of your software. If a single application is the only path to the database, you could put validation into the program itself and probably be alright. But if several different programs are using the database at the same time (e.g. your application and your friend's application), you'll want business rules in the database so that your data is always valid.
By 'validation rules', I am talking about rules such as 'items in cart > 0'. You may or may not want validation rules. But I think that primary/foreign keys are always important (or you could find later on that you wish you had them). I think they are required if you want to do replication at some point.
什么时候引用完整性不合适？
有时当您复制批次时
批量记录，或恢复
来自某种备份的数据，它是
方便暂时关闭
参考约束
完整性。
字段包含外键列表的多个和/或可能不完整的子集是否合适？
以这种方式复制数据是这样的
反对这个概念
正常化。有
这样做的优点和缺点
方法。
通常，这应该是模式结构设计决策还是界面设计决策？ （或者可能两者都不或两者兼而有之）
我认为这是一种架构设计
决定。想想最好的办法
建立关系模型来解决你的问题
条款。按照它的方式使用数据库
When is referential integrity not appropriate?
Sometimes when you are copying lots
of records in bulk, or restoring
data from some sort of backup, it is
convenient to temporarily turn off
the constraints of referential
integrity.
Is it appropriate to have fields containing multiple and/or possibly incomplete subsets of a foreign key's list?
Duplicating data in this way goes
against the concept of
normalization. There are are
advantages and disadvantages to this
approach.
Typically, should this be a schema structure design decision or an interface design decision? (Or possibly neither or both)
I would consider it a schema design
decision. Think about the best way
to model your problem in relational
terms. Use the database in the way it
was intended.
如果引用完整性不以性能、可扩展性和/或其他功能为代价，那么它总是合适的。
在某些应用程序中，引用完整性可能会被用来换取比数据质量更重要的东西。
Referential integrity would always be appropriate if it didn't come at the cost of performance, scalability, and/or other features.
In some applications, referential integrity may be traded for something more important than the quality of the data.