Generating and Using Sequences - MySQL - page 466

dozen bandages, the rows 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 row to the invoice master table and generates a new AUTO_IN
CREMENT value for its inv_id column. The following INSERT statements each add a row
to the inv_item detail table, using LAST_INSERT_ID() to get the invoice number. This
associates the detail rows with the proper master row.
What if you have multiple invoices to process? 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 rows into the in
voice table, then add all the detail rows to the inv_item table. If you do that, all the new
detail rows in the inv_item table have the AUTO_INCREMENT value from the most recently
entered invoice row. Thus, all items appear to be part of that invoice, and rows in the
two tables don'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 rows to the tables. Suppose that you want each row in the
inv_item table to have a unique identifier. To do that, create the inv_item table as
follows with an AUTO_INCREMENT column named item_id :
CREATE TABLE inv_item
(
inv_id INT UNSIGNED NOT NULL , # invoice ID ( from invoice table )
item_id INT UNSIGNED NOT NULL AUTO_INCREMENT , # item ID
PRIMARY KEY ( item_id ),
qty INT , # quantity
description VARCHAR ( 40 ) # description
);
The inv_id column enables each inv_item row to be associated with the proper in
voice table row, just as with the original table structure. In addition, item_id uniquely
identifies each item row. However, now that both tables contain an AUTO_INCREMENT
column, you cannot enter information for an invoice the same way as before. If you
execute the INSERT statements shown previously, they now produce a different result
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 row in the invoice table. However, this INSERT
also generates its own AUTO_INCREMENT value (for the item_id column), which changes