Generating and Using Sequences - MySQL - page 465

See Recipe 13.11 for application of these techniques to situations in which you must
insert rows into multiple tables that each contain an AUTO_INCREMENT column.
13.11. Using Auto-Increment Values to Associate Tables
Problem
You use sequence values from one table as keys in a second table so that you can associate
rows in the two tables with each other. But the associations aren't being set up properly.
Solution
You're probably not inserting rows 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.
Discussion
Be careful with an AUTO_INCREMENT value used as an ID value in a master table if you
also store the value in detail table rows for the purpose of linking the detail rows to the
proper master table row. Suppose that an invoice table lists invoice information for
customer orders, and an inv_item table lists the individual items associated with each
invoice. Here, invoice is the master table and inv_item is the detail table. To uniquely
identify each order, include an AUTO_INCREMENT column inv_id in the invoice table.
You'd also store the appropriate invoice number in each inv_item table row so that 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 , # quantity
description VARCHAR ( 40 ) # description
);
For this kind of table relationship, it's typical to insert a row into the master table first
(to generate the AUTO_INCREMENT value that identifies the row), and then insert the detail
rows using LAST_INSERT_ID() to obtain the master row ID. If a customer buys a ham‐
mer, three boxes of nails, and (in anticipation of finger-bashing with the hammer) a