MySQL Cookbook

Using AUTO_INCREMENT Valuesto Relate Tables

Problem

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.

Solution

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.

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 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 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 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, ...