>>> I'm not sure where the idea that primary keys must be stable comes from.
>>> There's nothing necessarily wrong with updating a primary key. All a
>>> primary key does is uniquely identify a row in a table. If that id changes
>>> over time, that's fine, as long as the primary key columns continue to
>>> uniquely identify each row in the table.
Firstly trying to update such a key you'll immediately bump into the practical
reasons why it doesn't work well. You have to update every record everywhere
in the database that references that key which represents a lot of potential
work.
But secondly, what happens to applications that have read that value into
their local state and then try to operate on it? When they return to perform a
second operation and use the primary key to specify the record they wish to
operate on they'll find it gone.
In short, you have to update every instance of the key, not only in the
database, but in every application and even in every other representation in
the real world. That could include changing people's bookmarks, notes in PDAs,
even paper reports sitting on people's desks -- a tall order for an SQL query.
In short primary keys that aren't static just aren't very useful.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com