ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

IDENTITY FIELD, Foreign Key, how do you handle?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • IDENTITY FIELD, Foreign Key, how do you handle?

    ***** DISREGARD - I figured it out!!! Happy dance! ********


    The link explained how you have to SELECT the identity field when you INSERT on the header. That makes SO much sense. I couldn't for the life of me figure out how the program would know which Header Record to use.

    I think I'm going to go find some chocolate!


    ****EDIT - I may have found more information here <https://www.ibm.com/docs/en/db2-for-zos/11?topic=programs-identity-columns>

    I don't know where to start with this one.

    I have a "header" and a "detail" SQL table. I need to tie them together by setting up the primary key of the header table as a foreign key on the detail table.

    PHP Code:
    CREATE TABLE &LIB/CYCHDRS
    (CYCLE_COUNT_ID FOR CYCNTID INT NOT NULL GENERATED ALWAYS AS IDENTITY,
    CY_COUNTER_USER FOR CYCNTUSR CHAR(10),
    ...
    more here...
    PRIMARY KEY (CYCLE_COUNT_ID 
    PHP Code:
    CREATE TABLE &LIB/CYCDTLS

    (CYD_DETAIL_KEY FOR CYDKEY INT NOT NULL GENERATED ALWAYS AS IDENTITY,
    CYD_DETAIL_ID FOR CYDID INT NOT NULL,
    CYD_SKID_ID FOR CYDSKID NUMERIC(09NOT NULL,
    ...  
    more fields go herebut not important for this question...
    PRIMARY KEY (CYDKEY) ,
    FOREIGN KEY (CYDIDREFERENCES CYCHDRS
    ON UPDATE NO ACTION
    ON DELETE CASCADE 
    Once I build the HEADER record, I have to insert multiple DETAIL records, but I can't quite wrap my head around how to do it. The program keeps getting an SQL -407, and I think it has to do with CYDKEY or CYDID.

    My header file looks like this:

    ID COUNTER AUDITOR DATE TIME PLANT AREA CLASS
    38 DPMGS1 FEOQUA 06/17/22 13:59:19 22 22WHSE HIBAY

    I think I need my detail file to look something like this:
    CYD_DETAIL_KEY DETAIL ID SKID ID LOCATION PART UOM
    1 38 0 LOC PART EA

    I've read through everything I can find using GOOGLE but I'm obviously missing something!

    Along the same lines if anyone has any suggestions for books/classes/YouTube tutorials that would help me get a grasp on this, please let me know! I'm tired of always asking the questions and not being able to answer them!

    Thank you all.
    Last edited by MelissaG; June 17, 2022, 12:38 PM.

  • #2
    Here's an article I wrote a while back that dealt with a similar situation. Maybe it will be of some help.

    Do you, like Bob Seger, sometimes feel that you are nothing more than a number? Me too. That&#8217;s because to many people, that&#8217;s exactly what we are. And if there&#8217;s one thing that computers are good at, it&#8217;s assigning numbers &#8212; to orders, to accounts, to invoices, to transactions, and of course, to people. Since

    Comment

    Working...
    X