什么时候引用完整性不合适?

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:

我想这可能会分成几个子问题:

1. When is referential integrity not appropriate?
2. 什么时候引用完整性不合适?
3. Is it appropriate to have fields containing multiple and/or possibly incomplete subsets of a foreign key's list?
4. 是否应该使用包含多个和/或可能不完整的外键列表子集的字段?
5. Typically, should this be a schema structure design decision or an interface design decision? (Or possibly neither or both)
6. 通常，这是架构结构设计决策还是接口设计决策?(或者两者都不可能)

Thoughts?

想法吗?

5 个解决方案

#1

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.

如果数据仓库的数据是事务数据库的只读副本，则通常不使用引用互格。另一个不需要RI的例子是，当您想要记录包含行id的信息时;为只读日志表维护引用完整性是对数据库开销的浪费。

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.

系统开发的所有内容都围绕着信息安全展开，其中的一个关键元素就是数据完整性。数据库结构应该在可能的情况下加强这些功能，但是您通常不会处理现代数据库系统。有时你的数据源是一所老旧的AS400学校，里面有过时的应用程序。有时，您必须构建一个数据和业务层，以提供数据完整性。

Just my thoughts.

只是我的想法。

#2

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.

通过“验证规则”，我指的是规则，比如“cart > 0中的项目”。您可能需要验证规则，也可能不需要。但是我认为主键/外键总是很重要的(或者稍后您会发现您希望拥有它们)。我认为如果你想在某个时刻进行复制，它们是必需的。

#3

1. 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.

   有时，当您批量复制大量记录，或者从某种备份中恢复数据时，临时关闭引用完整性的约束是很方便的。

2. 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.

   以这种方式复制数据违背了规范化的概念。这种方法有优点也有缺点。

3. 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.

   我认为这是一个模式设计决策。考虑用关系术语建模问题的最佳方式。以预期的方式使用数据库。

#4

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.

在某些应用程序中，引用完整性可能被用来交换比数据质量更重要的东西。

#5

1. Never, though a few people in the NoSQL, the multi-value, and oo-db realms will feel differently. Don't listen to them, they're wrong.
2. 尽管NoSQL、多值和oo-db领域中的少数人会有不同的感觉，但这是绝对不会发生的。别听他们的，他们错了。
3. Yes. For example, if a vehicle is identified uniquely as (lotid,vin) then lotid is a foreign key to the lot table. If you want to find all pictures for a lot you can join the vehicle_pictures table right to the lot table, by using a subset of the vehicle_pictures key (lotid in (lotid,vin)). Or, am I not understanding you?
4. 是的。例如，如果一个车辆被唯一地标识为(lotid,vin)，那么lotid就是lot表的外键。如果您想要查找大量的所有图片，可以使用车载_pictures键(lotid in (lotid,vin))的一个子集，将车载_pictures表直接连接到lot表。或者，我不理解你吗?
5. Schema, interface comes second. If the schema is bad, having a nice interface is not a long term goal.
6. 模式,接口是第二。如果模式不好，那么拥有一个良好的接口并不是一个长期的目标。