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.
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.
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 (inv_id), qty INT, # quantity description VARCHAR(40) # description );
For these kinds of table relationships, 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. 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 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_INCREMENT
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 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 and then proceed to
the next. The wrong way is to add all the master rows into the
invoice
table and then add all the
detail rows to the inv_item
table.
If you do that, all the detail rows in the
inv_item
table will contain the
AUTO_INCREMENT
value from the most
recently entered invoice
row. Thus,
all will appear to be part of the same invoice, and rows 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 rows to the tables. Suppose that 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. (Using an AUTO_INCREMENT Column to Create Multiple
Sequences 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 enables
each inv_item
row to be associated
with the proper invoice
table row,
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 statements 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 row 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 row inv_id
value
to be “lost.” The result is that subsequent inserts into
the inv_item
store the preceding
row’s seq
value into the inv_id
column. This causes the second and
following rows to have incorrect inv_id
values.
To avoid this difficulty, save the sequence value generated by the insert into the master table and use the saved value for the inserts into the detail table. To save the value, you can use a user-defined variable in SQL or a variable maintained by your program.
Save the master row AUTO_INCREMENT
value in a user-defined
variable for use when inserting the detail rows:
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'),
This method is similar to the previous one, but applies
only from within an API. Insert the master row, and then save
the AUTO_INCREMENT
value into
an API variable for use when inserting detail rows. For example,
in Ruby, you can access the AUTO_INCREMENT
using the database
handle insert_id
attribute,
so the invoice-entry procedure looks something like
this:
dbh.do("INSERT INTO invoice (inv_id,date) VALUES(NULL,CURDATE())") inv_id = dbh.func(:insert_id) 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")
3.146.35.72