Using AUTO_INCREMENT Valuesto Relate Tables

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

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

11.16.3 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, # quantity
 description VARCHAR(40) # description
);

For these kinds of table relationships, it's typical to insert a record into the master table first (to generate the AUTO_INCREMENT value that identifies the record), then insert the detail records and refer to LAST_INSERT_ID( ) to obtain the master record ID. For example, if a customer buys a hammer, three boxes of nails, and (in anticipation of finger-bashing with the hammer) a dozen bandages, the records pertaining to the order can be inserted into the two tables like so:

INSERT INTO invoice (inv_id,date)
 VALUES(NULL,CURDATE( ));
INSERT INTO inv_item (inv_id,qty,description)
 VALUES(LAST_INSERT_ID( ),1,'hammer');
INSERT INTO inv_item (inv_id,qty,description)
 VALUES(LAST_INSERT_ID( ),3,'nails, box');
INSERT INTO inv_item (inv_id,qty,description)
 VALUES(LAST_INSERT_ID( ),12,'bandage');

The first INSERT adds a record to the invoice master table and generates a new AUTO_INCREMENT value for its inv_id column. The following INSERT statements each add a record to the inv_item detail table, using LAST_INSERT_ID( ) to get the invoice number. This associates the detail records with the proper master record.

What if you need to process multiple invoices? There's a right way and a wrong way to enter the information. The right way is to insert all the information for the first invoice, then proceed to the next. The wrong way is to add all the master records into the invoice table, then add all the detail records to the inv_item table. If you do that, all the detail records in the inv_item table will contain the AUTO_INCREMENT value from the most recently entered invoice record. Thus, all will appear to be part of the same invoice, and records in the two tables won't have the proper associations.

If the detail table contains its own AUTO_INCREMENT column, you must be even more careful about how you add records to the tables. Suppose you want to number the rows in the inv_item table sequentially for each order. The way to do that is to create a multiple-column AUTO_INCREMENT index that generates a separate sequence for the items in each invoice. (Recipe 11.14 discusses this type of index.) Create the inv_item table as follows, using a PRIMARY KEY that combines the inv_id column with an AUTO_INCREMENT column, seq:

CREATE TABLE inv_item
(
 inv_id INT UNSIGNED NOT NULL, # invoice ID (from invoice table)
 seq INT UNSIGNED NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (inv_id, seq),
 qty INT, # quantity
 description VARCHAR(40) # description
);

The inv_id column allows each inv_item row to be associated with the proper invoice table record, just as with the original table structure. In addition, the index causes the seq values for the items in each invoice to be numbered sequentially starting at 1. However, now that both tables contain an AUTO_INCREMENT column, you cannot enter information for an invoice the same way as before. To see why it doesn't work, try it:

INSERT INTO invoice (inv_id,date)
 VALUES(NULL,CURDATE( ));
INSERT INTO inv_item (inv_id,qty,description)
 VALUES(LAST_INSERT_ID( ),1,'hammer');
INSERT INTO inv_item (inv_id,qty,description)
 VALUES(LAST_INSERT_ID( ),3,'nails, box');
INSERT INTO inv_item (inv_id,qty,description)
 VALUES(LAST_INSERT_ID( ),12,'bandage');

These queries are the same as before, but now behave somewhat differently due to the change in the inv_item table structure. The INSERT into the invoice table works properly. So does the first INSERT into the inv_item table; LAST_INSERT_ID( ) returns the inv_id value from the master record in the invoice table. However, this INSERT also generates its own AUTO_INCREMENT value (for the seq column), which changes the value of LAST_INSERT_ID( ) and causes the master record inv_id value to be "lost." The result is that subsequent inserts into the inv_item store the preceding record's seq value into the inv_id column. This causes the second and following records to have incorrect inv_id values.

These are several ways to avoid this difficulty. One involves using a different INSERT syntax to add the detail records; others save the master record AUTO_INCREMENT value in a variable for later use:

• Insert multiple detail records at a time.

  One solution to the problem is to add detail records using MySQL's INSERT syntax that allows multiple rows to be inserted with a single statement. That way you can apply the LAST_INSERT_ID( ) value from the master record to all the detail records:

  INSERT INTO invoice (inv_id,date)
   VALUES(NULL,CURDATE( ));
  INSERT INTO inv_item (inv_id,qty,description) VALUES
   (LAST_INSERT_ID( ),1,'hammer'),
   (LAST_INSERT_ID( ),3,'nails, box'),
   (LAST_INSERT_ID( ),12,'bandage');

• Use a SQL variable.

  Another method is to save the master record AUTO_INCREMENT value in a SQL variable for use when inserting the detail records:

  INSERT INTO invoice (inv_id,date)
   VALUES(NULL,CURDATE( ));
  SET @inv_id = LAST_INSERT_ID( );
  INSERT INTO inv_item (inv_id,qty,description)
   VALUES(@inv_id,1,'hammer');
  INSERT INTO inv_item (inv_id,qty,description)
   VALUES(@inv_id,3,'nails, box');
  INSERT INTO inv_item (inv_id,qty,description)
   VALUES(@inv_id,12,'bandage');

• Use an API variable.

  A third method is similar to the second, but applies only from within an API. Insert the master record, then save the AUTO_INCREMENT value into an API variable for use when inserting detail records. For example, in Perl, you can access the AUTO_INCREMENT using the mysql_insertid attribute, so the invoice-entry procedure looks something like this:

  $dbh->do ("INSERT INTO invoice (inv_id,date) VALUES(NULL,CURDATE( ))");
  $inv_id = $dbh->{mysql_insertid};
  $sth = $dbh->prepare ("INSERT INTO inv_item (inv_id,qty,description)
   VALUES(?,?,?)");
  $sth->execute ($inv_id, 1, "hammer");
  $sth->execute ($inv_id, 3, "nails, box");
  $sth->execute ($inv_id, 12, "bandage");