The error is coming from the payment table. It says that the insert values for PAY_ID_U are incorrect. What is the problem? The pieces of code below are a portion of the insert values for both tables.
INSERT INTO PAYMENT VALUES('0100','00100','DEBIT','33456A','2021-01-20',1856.54);
INSERT INTO PAYMENT VALUES('0110','00110','CREDIT','11223E','2020-02-02',56.78);
INSERT INTO PAYMENT VALUES('0120','00120','NONE','55334Z','2020-12-22',88.99);
INSERT INTO INVOICE VALUES('0100','00100','TARGET','2019-01-08',100.00,'OPEN');
INSERT INTO INVOICE VALUES('0110','00110','MORTGAGE','2021-01-20',1856.96,'PAID');
INSERT INTO INVOICE VALUES('0120','00120','VERSACE','2020-08-20',985.97,'UNPAID'); 1 Answer
Db2 is behaving correctly here, it is saying there is no such invoice key as '00100' (for the first insert into payment). The reason is your invoice.inv_id is CHAR(5), but when you insert into invoice you specify only four characters in the values statements for the invoice key, missing the leading zero!
By the way, it is good practice to always explicitly name the insert column names.
So change your insert statements as follows, notice the key length is 5 characters:
INSERT INTO INVOICE (inv_id, inv_id_u, inv_desc, inv_date, inv_amt, inv_status) VALUES('00100','00100','TARGET','2019-01-08',100.00,'OPEN');
INSERT INTO INVOICE (inv_id, inv_id_u, inv_desc, inv_date, inv_amt, inv_status) VALUES('00110','00110','MORTGAGE','2021-01-20',1856.96,'PAID');
INSERT INTO INVOICE (inv_id, inv_id_u, inv_desc, inv_date, inv_amt, inv_status) VALUES('00120','00120','VERSACE','2020-08-20',985.97,'UNPAID'); 
