RE: Using long names

Can you example this?
<snip>
Generated keys mean that you can change the primary key *without* requiring database I/O to all of the hundreds (thousands, millions) of records. Even with referential constraints, someone still has to change all those records.
</snip>

Let's say I have a table called RCM. And in that table I have a column called CCUST. This is the column normally visible to the users. CCUST is defined with the Primary key constraint. I also have a generated column called RCMKey. RCMKey is defined with a Unique Constraint.
Let's say I have another table called ECH. And that table has a column called HCUST. Now let's say I build a referential constraint which says that if the customer key is not null that it must exist in RCM. I assume that ECH has RCMKey and not CCUST.

I think I have it now. I just discovered that the parent table being referenced does not need to be referenced by it's primary key constraint. It can be referenced by a different unique constraint. This was the part I had to wrap my head around.

The trick is not to show the users the unique generated key. Otherwise they'll claim there's some sort of SOX requirement that there be no gaps in the numbering, etc. You know, all the crap they threw at you in the first place when they wanted the ability to change CCUST.

Rob Berendt