Recipe 11.13. Using AUTO_INCREMENT Values to Relate Tables
ProblemYou'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. SolutionYou'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. DiscussionBe 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. (Section 11.11 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.
|
- The Effects of an Enterprise Resource Planning System (ERP) Implementation on Job Characteristics – A Study using the Hackman and Oldham Job Characteristics Model
- Context Management of ERP Processes in Virtual Communities
- Data Mining for Business Process Reengineering
- Healthcare Information: From Administrative to Practice Databases
- A Hybrid Clustering Technique to Improve Patient Data Quality