Re: Using long names

My opinion only but the identity key should always be the primary key. The
surrogate key (Customer Number) should be unique key.

Every table has an identity key as the primary that uniquely definitions
that record so if you have a header/detail situation, the primary key of
the header goes into each detail record.

On Fri, Jul 26, 2019 at 4:24 AM Rob Berendt <rob@xxxxxxxxx> wrote:

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
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com


-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Joe
Pluta
Sent: Thursday, July 25, 2019 4:17 PM
To: rpg400-l@xxxxxxxxxxxxxxxxxx
Subject: Re: Using long names

CAUTION: This email originated from outside of the organization. Do not
click links or open attachments unless you recognize the sender and know
the content is safe.


On 7/25/2019 2:24 PM, Rob Berendt wrote:

<snip>
It's similar to the folks who use a unique, system-generated number as

the actual key for every file. This in theory is freaking awesome, because
you can change customer number and every related record will change with
it. But it takes a bit more work whenever you're trying to join files
together in queries. So often the customer number is attached to a file
somewhere and it doesn't get fixed.

</snip>

Which situation are you talking about?

Situation one: CCUST is not the key to RCM (Customer Master). Some

hidden uniquely generated column is. The referential constraint between
ECH (order header) and RCM would not be CCUST but would be this other key.

Situation two: CCUST remains the key to RCM. However they really start

using referential constraints from the database and not just code them in
themselves.

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.

Generated keys means you don't.

--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a
message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or
change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com
--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com