Re: SQL Identity column -- MIDRANGE-L

Hi

Many of us - perhaps we "veterans" - have used what might be called "natural" keys - something like a vendor# in Charles' example here. Then a product table would include the vendor#. When referential integrity was first introduced on iSeries or AS/400 - whatever generation of this system - we might referential constraints that tied parent and child tables (physical files at the time). We might have included a vendor# in both, but, as in Charles' example, to get the information about the vendor along with product info, we joined them.

As I think about this, there is a what I might call directionality assumed - we start with the header and go down to the detail - parent then child. What gets more complex, perhaps, is when we want to know vendor information for a given product. But we have views to help us with that, right?

*Regards*

*Vern Hamberg*

<cid:part1.hDqdAKND.8QSF78vb@centurylink.net>

On 2/11/2025 9:14 AM, Charles Wilt wrote:

The idea would be that the child table wouldn't have a vendor number
column...

Instead it'd have a FK column with the parent's identity value.

If you need the vendor# with the child data, you just join back to the
parent.

Charles

On Tue, Feb 11, 2025 at 7:25 AM DEnglander--- via MIDRANGE-L <
midrange-l@xxxxxxxxxxxxxxxxxx> wrote:

Thank you.

That is what I thought.

The reason I asked is this: I have a table that I would like to have
function as the Parent. I want to possibly have other tables have
referential integrity with it. If I have an Identity number as the key [as
opposed to a Vendor Number for example], then how would I set something
like: A Child table's Vendor Number column must have a value that exists
in the Parent's Vendor Number column. Unless I'm missing something, it's
not as easy using Identity columns only, since you first have to get the
Identity column of the Vendor whose associated row you want to write in
the Child table. Sounds like extra steps to me, but maybe it's worth it?

Thanks again,

Doug

"CONFIDENTIALITY NOTICE: This e-mail transmission (and/or the attachments
accompanying it) contain confidential information belonging to the sender.
The information is intended only for the use of the intended recipient. If
you are not the intended recipient, you are hereby notified that any
disclosure, copying, distribution or the taking of any action in reliance
on the contents of the information is strictly prohibited. Any
unauthorized interception of this transmission is illegal under the law.
If you have received this transmission in error, please promptly notify the
sender by reply e-mail, and then destroy all copies of the transmission."
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email:MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:https://lists.midrange.com/mailman/listinfo/midrange-l
or email:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
athttps://archive.midrange.com/midrange-l.

Please contactsupport@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.