MySQL Forums
Forum List  »  Newbie

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

Options: ReplyQuote


Subject
Written By
Posted
Autoincrement on secondary key not working
April 30, 2005 02:56AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.