I've been reading up on foreign keys and such for postgres and I noticed that it allows a cascading update for foreign keys.
我一直在为postgres阅读外键之类的东西，我注意到它允许对外键进行级联更新。
Well, my question is, when would you need to update the primary key of a row?
我的问题是，什么时候需要更新一行的主键?
Apparently this guy needs to http://www.oreillynet.com/onlamp/blog/2004/10/hey_sql_fans_check_out_foreign.html but I'm not quite understanding how it could ever be useful.
显然这个人需要http://www.oreillynet.com/onlamp/2004/10/hey_sql_fans_check_out_foreignn.html，但我不太理解它是如何有用的。
Edit: I see for natural primary keys, how this could be used. But what about technical primary keys? Ones that have no meaning and are almost always auto generated on insert?
编辑:我看到了自然的主键，如何使用它。但是技术的主要关键是什么呢?那些毫无意义并且几乎总是自动生成的插入?
7 个解决方案
#1
2
Well... we have a lot of primary keys that are defined as a human readable code. Terrible idea, but not much choice in the matter.
嗯…我们有许多主键被定义为人类可读的代码。这主意糟透了，但在这个问题上没有多少选择。
It is very very handy to be able to fix that PK, and all dependent records, when someone realizes it is misspelled, or the meaning has changed.
当有人意识到它被拼错了，或者意思改变了的时候，修复那个PK和所有依赖记录是非常方便的。
#2
1
You would need to do it if you chose your primary key as a natural key instead of a surrogate key, and then later found out that the user changed their surname, or that they wrote their SSN incorrectly on the application form.
如果您选择主键作为自然键而不是代理键，然后发现用户改变了姓，或者他们在申请表上写错了SSN，那么您需要这样做。
Moral of story: don't use natural keys as primary keys.
故事的寓意:不要把自然键作为主键。
#3
1
I had to change my PK several times, when exposing my PK to a third party system. From time to time they called us asking to change the PKs, to fit the records in their database (from time to time due to tech problem, the synchronization between there two systems - fails).
当我将我的PK暴露给第三方系统时，我不得不多次改变我的PK。他们不时地给我们打电话，要求我们更改PKs，以适应他们数据库中的记录(由于技术问题，两个系统之间的同步不时失败)。
After several times we just stopped exposing the PK and add a new column.
几次之后，我们不再公开PK并添加一个新的列。
#4
1
For a synthetic, meaningless primary key like an autoincrementing column there should (with a few exceptions) never be any reason to update the PK value. If the PK is a user-visible value you might have to update it (which is one of the many arguments in favour of synthetic keys). An example of this situation is an insurance policy number. In some cases the year is a part of the number, and may tick over on every renewal. In some data models the record is just updated in situ.
对于一个合成的、没有意义的主键，比如一个自动递增的列，应该永远不会有任何理由更新PK值。如果PK是用户可见的值，您可能需要更新它(这是支持合成键的众多参数之一)。这种情况的一个例子是保险保单编号。在某些情况下，年份是数字的一部分，并且可能在每次更新时显示出来。在一些数据模型中，记录只是在原地更新。
Where this happens you would be better off to use a synthetic key, so that other items are not dependent on the visible number.
如果出现这种情况，最好使用合成键，这样其他项就不会依赖于可见的数字。
One possible scenario where you would need to update a synthetic key is if you were merging two or more application databases together. In this case you may need to shift keys en masse to avoid collissions with the keys of records from the other source.
需要更新合成键的一个可能场景是，如果将两个或多个应用程序数据库合并在一起。在这种情况下，您可能需要整体地移动键，以避免与来自另一个源的记录的键发生冲突。
#5
1
You may get in this situation if you use natural primary key.
如果使用自然主键，您可能会遇到这种情况。
Here's one very fresh example: in Croatia government changed tax identification numbers for both companies and individuals. New law was introduced with January 1st 2010.
这里有一个非常新鲜的例子:在克罗地亚，*改变了公司和个人的税务识别号码。新法律于2010年1月1日出台。
Last year, I was consultant in several projects where companies were changing natural key (old tax number) to surrogate key in existing applications. Natural key seemed logical selection to original designers of those apps because it was defined by law. And then it changed.
去年，我曾在几个项目中担任顾问，在这些项目中，公司正在将自然密钥(旧税号)更改为现有应用程序中的代理密钥。对于这些应用的原始设计者来说，自然键似乎是合理的选择，因为它是由法律定义的。然后它发生了变化。
#6
1
For autogenerated keys, one example that I came across here (can't remember question) is if you need to merge two database tables together. In this case, you'll likely have duplicates unless your keys happened to be offset enough.
对于自动生成的密钥，我在这里遇到的一个例子(不记得问题)是如果您需要合并两个数据库表。在这种情况下，您可能会有重复，除非您的键恰好被抵消了。
#7
0
You might need to do this if using a natural primary key (one that has an actual meaning in the problem domain). If the meaning changed, then you'd need to cascade the change.
如果使用自然主键(在问题域中具有实际意义)，您可能需要这样做。如果意思改变了，那么你就需要把变化串联起来。
I supposed a bad example of this would be a database of buildings on a school campus, with the building name as a primary key (don't do this at home). If the building is renamed to bribe honor a new donor, then the key would need to change.
我认为一个坏的例子就是学校校园里的建筑数据库，把建筑名称作为主键(不要在家里这样做)。如果建筑物被重新命名以贿赂新的捐赠者，那么钥匙就需要改变。
#1
2
Well... we have a lot of primary keys that are defined as a human readable code. Terrible idea, but not much choice in the matter.
嗯…我们有许多主键被定义为人类可读的代码。这主意糟透了，但在这个问题上没有多少选择。
It is very very handy to be able to fix that PK, and all dependent records, when someone realizes it is misspelled, or the meaning has changed.
当有人意识到它被拼错了，或者意思改变了的时候，修复那个PK和所有依赖记录是非常方便的。
#2
1
You would need to do it if you chose your primary key as a natural key instead of a surrogate key, and then later found out that the user changed their surname, or that they wrote their SSN incorrectly on the application form.
如果您选择主键作为自然键而不是代理键，然后发现用户改变了姓，或者他们在申请表上写错了SSN，那么您需要这样做。
Moral of story: don't use natural keys as primary keys.
故事的寓意:不要把自然键作为主键。
#3
1
I had to change my PK several times, when exposing my PK to a third party system. From time to time they called us asking to change the PKs, to fit the records in their database (from time to time due to tech problem, the synchronization between there two systems - fails).
当我将我的PK暴露给第三方系统时，我不得不多次改变我的PK。他们不时地给我们打电话，要求我们更改PKs，以适应他们数据库中的记录(由于技术问题，两个系统之间的同步不时失败)。
After several times we just stopped exposing the PK and add a new column.
几次之后，我们不再公开PK并添加一个新的列。
#4
1
For a synthetic, meaningless primary key like an autoincrementing column there should (with a few exceptions) never be any reason to update the PK value. If the PK is a user-visible value you might have to update it (which is one of the many arguments in favour of synthetic keys). An example of this situation is an insurance policy number. In some cases the year is a part of the number, and may tick over on every renewal. In some data models the record is just updated in situ.
对于一个合成的、没有意义的主键，比如一个自动递增的列，应该永远不会有任何理由更新PK值。如果PK是用户可见的值，您可能需要更新它(这是支持合成键的众多参数之一)。这种情况的一个例子是保险保单编号。在某些情况下，年份是数字的一部分，并且可能在每次更新时显示出来。在一些数据模型中，记录只是在原地更新。
Where this happens you would be better off to use a synthetic key, so that other items are not dependent on the visible number.
如果出现这种情况，最好使用合成键，这样其他项就不会依赖于可见的数字。
One possible scenario where you would need to update a synthetic key is if you were merging two or more application databases together. In this case you may need to shift keys en masse to avoid collissions with the keys of records from the other source.
需要更新合成键的一个可能场景是，如果将两个或多个应用程序数据库合并在一起。在这种情况下，您可能需要整体地移动键，以避免与来自另一个源的记录的键发生冲突。
#5
1
You may get in this situation if you use natural primary key.
如果使用自然主键，您可能会遇到这种情况。
Here's one very fresh example: in Croatia government changed tax identification numbers for both companies and individuals. New law was introduced with January 1st 2010.
这里有一个非常新鲜的例子:在克罗地亚，*改变了公司和个人的税务识别号码。新法律于2010年1月1日出台。
Last year, I was consultant in several projects where companies were changing natural key (old tax number) to surrogate key in existing applications. Natural key seemed logical selection to original designers of those apps because it was defined by law. And then it changed.
去年，我曾在几个项目中担任顾问，在这些项目中，公司正在将自然密钥(旧税号)更改为现有应用程序中的代理密钥。对于这些应用的原始设计者来说，自然键似乎是合理的选择，因为它是由法律定义的。然后它发生了变化。
#6
1
For autogenerated keys, one example that I came across here (can't remember question) is if you need to merge two database tables together. In this case, you'll likely have duplicates unless your keys happened to be offset enough.
对于自动生成的密钥，我在这里遇到的一个例子(不记得问题)是如果您需要合并两个数据库表。在这种情况下，您可能会有重复，除非您的键恰好被抵消了。
#7
0
You might need to do this if using a natural primary key (one that has an actual meaning in the problem domain). If the meaning changed, then you'd need to cascade the change.
如果使用自然主键(在问题域中具有实际意义)，您可能需要这样做。如果意思改变了，那么你就需要把变化串联起来。
I supposed a bad example of this would be a database of buildings on a school campus, with the building name as a primary key (don't do this at home). If the building is renamed to bribe honor a new donor, then the key would need to change.
我认为一个坏的例子就是学校校园里的建筑数据库，把建筑名称作为主键(不要在家里这样做)。如果建筑物被重新命名以贿赂新的捐赠者，那么钥匙就需要改变。