You’re using sequence values from one table as keys in second table so that you can relate records in the two tables properly. But the associations aren’t being set up properly.
You’re probably not inserting records in the proper order, or you’re losing track of the sequence values. Change the insertion order, or save the sequence values so that you can refer to them when you need them.
Be careful with
AUTO_INCREMENT values that are
used to generate ID values in a master table if you also store those
values in detail table records to link the detail records to the
proper master table record. This kind of situation is quite common.
Suppose you have an
invoice table listing invoice
information for customer orders, and an
table listing the individual items associated with each invoice.
invoice is the master table and
inv_item is the detail table. To uniquely identify
each order, the
invoice table could contain an
You’d also store the appropriate invoice number in
inv_item table record so you can tell which
invoice it goes with. The tables might look something like this:
CREATE TABLE invoice ( inv_id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (inv_id), date DATE NOT NULL # ... other columns could go here # ... (customer ID, shipping address, etc.) ); CREATE TABLE inv_item ( inv_id INT UNSIGNED NOT NULL, # invoice ID (from invoice table) INDEX (inv_id), qty INT, ...