Autoincrement on secondary key not working
Posted by:
Dag Nygren
Date: April 30, 2005 02:56AM
Hi,
For a long time I have had a problem with the autoincrement function on a secondary key, but now I finally just have to get it working.
The scenario is a orders database that has a separate header table for an order and then rows of items.
The problem is that the row numbers on the items keep counting from the last inserted row, even on a new order where they should start from 1 according to the manual.
The definition of the table is:
CREATE TABLE processitem (
pr_code varchar(20) NOT NULL default '',
pr_description text,
pr_ordered_no decimal(8,2) default NULL,
pr_delivered_no decimal(8,2) default NULL,
pr_selling_price decimal(8,2) default NULL,
pr_process_no int(10) NOT NULL default '0',
pr_invoice int(10) default NULL,
pr_row smallint(3) NOT NULL auto_increment,
pr_deliverynote int(10) default NULL,
pr_deliverytime date default NULL,
PRIMARY KEY (pr_process_no,pr_row),
KEY k_pr_process_no (pr_process_no),
KEY Key_pr_row (pr_row)
) TYPE=MyISAM PACK_KEYS=1;
and pr_row is the counter I expected to be reset at every new pr_process_no.
Now it just keeps counting up on every new insert, even with a new pr_process_no.
What is wrong with the definition?
Is this a case of not having the combined-key defined during the first time of using the database?
My MySQL version is 4.0.17, but it has been woring like this since I defined it around version 3.x
Any advice appreciated
Dag Nygren