(Error)Sql Code -530, Error the Insert or Update Value of Foreign Key Pay$Id$U Is Invalid
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...
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');
Maya is a hardware enthusiast who tests and reviews smart home devices, smartphones, wearables, and audio gear. She focuses on practical consumer value and build quality.
