At 10:41 AM 14/12/2003 +0700, you wrote:

>Helen Borrie wrote:
> >>Some of my foreign keys are referencing unique columns, not PK, and
> >>these columns sometime change values (though infrequently). All of my PK
> >>usually don't change their value, but many times it's more convenient to
> >>refer to a UC instead of a PK.
> >
> > Then you are falling into the trap creating of non-atomic dependencies.
>
>I have a table like this (GUID is CHAR(16) CHARACTER SET OCTETS):
>
>create table countries (
> id GUID,
> alpha2 CHAR(2) NOT NULL, UNIQUE(alpha2)
> name VARCHAR(50)
>);
>
>create table addresses (
> id GUID,
> line1 VARCHAR(64),
> line2 VARCHAR(64),
> city VARCHAR(32),
> province VARCHAR(32),
> countrycode CHAR(2) REFERENCES countries(alpha2)
>);
>
>I use GUID for PK's because this will be a distributed database thingy
>(each user using his/her own database can add records to tables and we
>will allow users to synchronize/pull changes from one anoother).
>
>It's so much more convenient to refer to 'countries(alpha2)' instead of
>to 'countries(id)' (it's much shorter, and it carries a meaning, which
>makes the table easier to read when browsing). But since it is, as you
>say, a non-atomic relationship, if you designed the 'addresses', would
>you still refer to 'countries(id)'? Would you never use anything other
>than PK as references in FK relationships?

Do I answer "Yes, negative" or "No, affirmative"?

1. If a PK has logically to cross database boundaries, then something like
a GUID is very elegant. Two generator fields are nearly as elegant.

2. I used to use the IATA (alpha2) country code as the PK on my country
table always, not least because I was working in the travel industry at one
point and had access to InterBase tables that were industry-maintained.

That was then and now is now. Country codes are now much more dynamic than
they used to be; and there are two standards, not one. So I maintain my
country tables with a generator and hold both IATA and CCCIT country codes,
a "standard", a status flag for each to indicate whether it is obsolete
and, if possible, an implementation date. I have case-insensitive indexed
proxy columns for both, but not unique, because a) country codes get
recycled and b) there is a high degree of overlap between the codes for the
two standards. I pull both the atomic PK and the requested standard of
country code into applications; and I don't delete country records at all,
since History needs to access the obsolete ones.

I don't put cascading ref. integrity on country codes, or any other control
relationships that are subject to external change. Tax codes is another
one that gets this treatment.

My golden rule these days is - never depend on a PK that a human might
change. I don't put unique constraints on numbers created by external
systems: I use triggers to generate exceptions if something isn't unique
when it ought to be.
if (exists(select blah from aliasme where uniqueval=new.myval) then
BOOM!) Maybe I still would put an unique index on preprinted document
numbers, if a system required it, but I haven't seen any of these for years.

IMO, unique constraints are a waste of space. They have their uses for
half-baked automated database development tools, I suppose, that generate
schemata from the data model. (I take a very dim view of those, too, in
case you were in doubt!!)

If I need a foreign key, then I need an atomic PK to link it
to. Thin. Untouched by human hand.

Oh dear, a sermon, it must be Sunday!

/heLen