I saw this comment:
我看到这个评论：
[applications] with the most data-related problems were those using natural keys.
与数据相关的问题最多的[应用程序]是那些使用自然键的应用程序。
Source: Surrogate vs. natural/business keys
来源：代理与自然/商业密钥
I want more supporting evidence of this, as the comment left much to imagination.
我想要更多支持这方面的证据，因为评论留下了很多想象力。
It suggests that practice of using natural keys creates data-related problems, but does not specify what goes wrong... does data get corrupt? out of sync? becomes erroneous, lost, damaged? hard to query?
它表明使用自然键的做法会产生与数据相关的问题，但没有说明出现了什么问题......数据是否会损坏？不同步？变得错误，丢失，受损？难以查询？
What are the data problems that happen when database is designed with natural keys opposed to using surrogate keys? How can those type of problems can be prevented when using surrogate keys?
使用与使用代理键相反的自然键设计数据库时会发生什么数据问题？使用代理键时，如何防止这类问题？
1 个解决方案
#1
1
The main issue with natural keys is how it affects related tables. If you change the value of the key, then you must correct every row in every table that references the original value.
自然键的主要问题是它如何影响相关表。如果更改键的值，则必须更正每个引用原始值的表中的每一行。
For example, suppose you have a Zip Code or Postal Code table. Quite often, these are designed where the Postal Code also serves as the natural key. Now suppose the Post Office changes a particular Postal Code (92680 becomes 92780). When you change the key in the Postal Code table, you must then go to every table that references that Postal Code, and update it there as well. So every row in the customer address, vendor address, etc... that has 92680 in the Postal Code has to be changed to 92780.
例如，假设您有邮政编码或邮政编码表。通常，这些都是在邮政编码也作为自然密钥的地方设计的。现在假设邮局改变了特定的邮政编码（92680变为92780）。当您更改邮政编码表中的密钥时，您必须转到引用该邮政编码的每个表，并在那里更新它。因此，邮政编码中包含92680的客户地址，供应商地址等的每一行都必须更改为92780。
Obviously, if the related tables are not remediated, you can start to have big problems. Let's say you charge insurance premiums based upon postal code. Imagine the issues you could have if these are not fixed in the premium table.
显然，如果相关表没有得到修复，你可能会遇到大问题。假设您根据邮政编码收取保险费。想象一下，如果高级表中没有修复这些问题，您可能遇到的问题。
Using Surrogate keys eliminates this problem altogether. You simply change the postal code in the Postal Code table. The surrogate key is not changed. And since the related tables store the surrogate key, you don't have to change anything else.
使用Surrogate键完全消除了这个问题。您只需更改邮政编码表中的邮政编码即可。代理键不会改变。由于相关表存储了代理键，因此您无需更改任何其他内容。
#1
1
The main issue with natural keys is how it affects related tables. If you change the value of the key, then you must correct every row in every table that references the original value.
自然键的主要问题是它如何影响相关表。如果更改键的值，则必须更正每个引用原始值的表中的每一行。
For example, suppose you have a Zip Code or Postal Code table. Quite often, these are designed where the Postal Code also serves as the natural key. Now suppose the Post Office changes a particular Postal Code (92680 becomes 92780). When you change the key in the Postal Code table, you must then go to every table that references that Postal Code, and update it there as well. So every row in the customer address, vendor address, etc... that has 92680 in the Postal Code has to be changed to 92780.
例如，假设您有邮政编码或邮政编码表。通常，这些都是在邮政编码也作为自然密钥的地方设计的。现在假设邮局改变了特定的邮政编码（92680变为92780）。当您更改邮政编码表中的密钥时，您必须转到引用该邮政编码的每个表，并在那里更新它。因此，邮政编码中包含92680的客户地址，供应商地址等的每一行都必须更改为92780。
Obviously, if the related tables are not remediated, you can start to have big problems. Let's say you charge insurance premiums based upon postal code. Imagine the issues you could have if these are not fixed in the premium table.
显然，如果相关表没有得到修复，你可能会遇到大问题。假设您根据邮政编码收取保险费。想象一下，如果高级表中没有修复这些问题，您可能遇到的问题。
Using Surrogate keys eliminates this problem altogether. You simply change the postal code in the Postal Code table. The surrogate key is not changed. And since the related tables store the surrogate key, you don't have to change anything else.
使用Surrogate键完全消除了这个问题。您只需更改邮政编码表中的邮政编码即可。代理键不会改变。由于相关表存储了代理键，因此您无需更改任何其他内容。