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?

--
dave