DB2: Attempting to reference Auto Generated Fields within Intersection Tables

Question

I am working on a project using DB2 in attempt to create a small inventory system. Currently my team and I are looking for answers to a roadblock we have encountered where we are creating tables with auto generated fields. I have included a sample create statement to give you an understanding of what we are working with:

"CREATE TABLE INVENTORY ( PRODUCT_ID INT NOT NULL Generated always as identity (start with 1 increment by 1 minvalue 1 no maxvalue no cycle no cache order), PRODUCT_NAME CHAR(100) NOT NULL, DESCRIPTION CHAR(100), UNIT_PRICE DECIMAL(6 , 2), QUANTITY INTEGER ) DATA CAPTURE NONE;

The issue we are coming across is when we insert test data into our database (no errors when we run the ddl; test data has no errors when we insert it) is that we can view the values of an auto generated field in the main table, but NOT in an intersection table where that value is used as a foreign key. An example of an intersection table that we are using in relation to our INVENTORY table is below:

CREATE TABLE RECEIVABLES ( ITEM_LINE INT NOT NULL Generated always as identity (start with 10 increment by 1 minvalue 10 no maxvalue no cycle no cache order),
PAYMENT_TYPE CHAR(15), REMARKS CHAR(70), RECEIVED_QUANTITY INTEGER, VENDOR_ID CHAR(4) NOT NULL, PRODUCT_ID INT, SHIPMENT_ID INT ) DATA CAPTURE NONE;

INSERT INTO RECEIVEABLES VALUES (DEFAULT,'DISCOVER','Success',9,'1000',DEFAULT,DEFAULT);

When we view the data, we are not seeing any NULL values; we simply aren't seeing ANY values for any of the foreign key fields in the intersection table (in this case, PRODUCT_ID and SHIPMENT_ID).

Is there a particular method to inserting data into an intersection table that will allow the values of the Primary keys to be represented in the intersection table as well?

Thank you very much for your time. I will be actively responding to any questions you may have

Charles Charles · Accepted Answer · 2015-04-24T18:46:46

It takes more than naming a column the same to make it a foreign key. In fact, the name doesn't matter. What matters is defining a FK constraint over it:

CREATE TABLE RECEIVABLES ( 
  ITEM_LINE INT NOT NULL Generated always as identity 
   (start with 10 
    increment by 1 
    minvalue 10 
    no maxvalue 
    no cycle 
    no cache order),
  PAYMENT_TYPE CHAR(15), 
  REMARKS CHAR(70), 
  RECEIVED_QUANTITY INTEGER, 
  VENDOR_ID CHAR(4) NOT NULL, 
  PRODUCT_ID INT, 
  SHIPMENT_ID INT, 
  CONSTRAINT inv_fk FOREIGN KEY (product_id) 
     REFERENCES inventory (product_id),
  CONSTRAINT shp_fk FOREIGN KEY (shipment_id) 
     REFERENCES shimpments (shipment_id)
) DATA CAPTURE NONE;

I would have expected to see null values in original table after your insert; since neither product_id nor shimpment_id in recieables is defined with NOT NULL. Running the same insert over the table I've posted you still give you NULL in the FK columns.

The generated ID is only generated in the primary table. You have to specify the value when inserting into into RECEIVABLES.

Now let's say you've just insert a row into INVENTORY and you want to insert a row referencing it into RECEIVABLES. Before the insert into RECEIVABLES, you have to ask the DB what the ID it generated for the row in INVENTORY.

The IDENTITY_VAL_LOCAL() function is one such way to do so...

DB2: Attempting to reference Auto Generated Fields within Intersection Tables

1 Answers