Inserting Data into Parent and Child Tables
You must insert each record into the parent table before inserting the corresponding records in the child table. To find the ROWID of the record in the parent table, select the %ID column and use a WHERE clause to specify the newly added record. For example:
INSERT INTO Sample.Invoice (Buyer,InvoiceDate) VALUES ('Fred',CURRENT_TIMESTAMP)
SELECT %ID FROM Sample.Invoice WHERE Buyer = 'Fred' // returns 1
INSERT INTO Sample.LineItem (Cparent,ProductSKU,UnitPrice) VALUES (1,'45-A7',99.95)
INSERT INTO Sample.LineItem (Cparent,ProductSKU,UnitPrice) VALUES (1,'22-A1',0.75)
Attempting to insert a child record for which no corresponding parent record ID exists generates an SQLCODE -104 error with a %msg Child table 'Sample.LineItem' references non-existent row in parent table.
During an INSERT operation on a child table, a shared lock is acquired on the corresponding row in the parent table. This row is locked while inserting the child table row. The lock is then released (it is not held until the end of the transaction). This ensures that the referenced parent row is not changed during the insert operation.
