MySQL Cookbook, 2nd Edition

Using AUTO_INCREMENT Values to Relate Tables

Problem

You’re using sequence values from one table as keys in a second table so that you can relate rows in the two tables to 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 AUTO_INCREMENT values that are used to generate ID values in a master table if you also store those values in detail table rows for the purpose of linking the detail rows to the proper master table row. This kind of situation is quite common. Suppose that you have an invoice table listing invoice information for customer orders, and an inv_item table listing 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, the invoice table could contain an AUTO_INCREMENT column inv_id. 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 ...