On 05/30/07 11:01, John D. Burger wrote:
Even ISO country codes are not guaranteed to be stable
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.
And any archived data (for example, transaction detail that you must
keep for 7 years but don't still want in your database, since it
doubles your backup/restore times) will still have the old codes.
"Static" data needs to be static.
Yes, and then there is the question of what such a recycled code
actually =means= as a foreign key.
For example, CS used to be the code for Czechoslovakia, then it was for
Serbia and Montenegro, now it is in "transition" before being deleted.
Czechoslovakia no longer has a code, since it no longer exists, as far
as ISO is concerned. What do you want to do with your biography
database for 19th century Slavic poets, which indicate that some people
were born in Czechoslovakia. Did those people move (briefly) to Serbia
and Montenegro? Or did their birthplace change to NULL? If you want to
give them a code, you have to find out what part of Czechoslovakia they
actually lived in, and what country that region's now in. Do you really
want some external agency forcing you to muck with you data like this?
In situations like this (a toll road schedule) we add beginning and
expiring timestamps, and the expiring timestamp is part of the
natural PK.
Anyway, regardless of one's feelings along these lines, I thought many
might be implicitly assuming that all of these standards guarantee such
stability, and I wanted to disabuse folks of that.
It's very interesting and useful to know.
--
Ron Johnson, Jr.
Jefferson LA USA
Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings