Composite Primary Keys - Control auto_increment

[Torque]

Hello

I have the follow table definition
( I need work mandatory with ENGINE=InnoDB due transactions control)

CREATE TABLE item ( 
idItem bigint(20) unsigned NOT NULL auto_increment, 
idArticulo bigint(20) unsigned NOT NULL , 
texto varchar(40), 
PRIMARY KEY (idItem,idArticulo) 
)ENGINE=InnoDB;

consider idArticulo like the PK from a Master table
Therefore Articulo (Master Table, Father) and Item (Sub Master Table, child)

Here to fill with simple data

insert into item(idArticulo,texto)values(1,'one'); 
insert into item(idArticulo,texto)values(1,'two'); 
insert into item(idArticulo,texto)values(1,'three'); 

insert into item(idArticulo,texto)values(2,'one'); 
insert into item(idArticulo,texto)values(2,'two'); 
insert into item(idArticulo,texto)values(2,'three');

If I do the follow query, I get

mysql> select idarticulo,iditem,texto from item order by iditem,idarticulo;
+------------+--------+-------+
| idarticulo | iditem | texto |
+------------+--------+-------+
|          1 |      1 | one   |
|          1 |      2 | two   |
|          1 |      3 | three |
|          2 |      4 | one   |
|          2 |      5 | two   |
|          2 |      6 | three |
+------------+--------+-------+
6 rows in set (0.00 sec)

mysql>

Realize yourself that iditem is 1,2,3,4,5,6


I want some way, I dont care if is necessary edit the table, to get something like this (desired output) when I insert my items (child table)

+------------+--------+-------+
| idarticulo | iditem | texto |
+------------+--------+-------+
|          1 |      1 | one   |
|          1 |      2 | two   |
|          1 |      3 | three |
|          2 |      1 | one   |
|          2 |      2 | two   |
|          2 |      3 | three |
+------------+--------+-------+

Realize yourself that iditem is 1,2,3 and 1,2,3  according for each idarticulo

I mean, each Master table (for each PK) has
its own control from its childs (for its FK)

Please I need a way to work in this, I would have 3 buildings/local in different places on my city working in real time, I need strict control for this generation for the fields, I assume that MySQL latest versions perhaps would handle this situation


Thanx

[UserName]

change the idItem column so that it is not auto_increment

instead, your application code will handle assigning incremental numbers

this requires that you use START TRANSACTION and COMMIT around two queries -- the first to obtain MAX(idItem) for the given idArticulo, and the second to insert using max+1

since you are already using innodb for transaction control, you should have no trouble implementing this.

[Torque]

Thanks for the reply

change the idItem column so that it is not auto_increment

instead, your application code will handle assigning incremental numbers

this requires that you use START TRANSACTION and COMMIT around two queries -- the first to obtain MAX(idItem) for the given idArticulo, and the second to insert using max+1

Has sense, even I think I could merge both in one sql statement

Since you are already using innodb for transaction control, you should have no trouble implementing this

I assume that Mysql should handle perfectly the concurrence on multiple insertions in the same time, right?, the possibility is low, but I want to avoid the e problem about while the select max and insertion +1 sentences are happening for 2,3 or more users in the same time, I would avoid get a wonderful exception due the duplicity in the PK

How I could reinforce this control?

Thanks in advanced

[UserName]

Originally Posted by Torque

I want to avoid the e problem about while the select max and insertion +1 sentences are happening for 2,3 or more users in the same time, I would avoid get a wonderful exception due the duplicity in the PK

How I could reinforce this control?

Thanks in advanced

like i said, with a transaction -- by wrapping START TRANSACTION and COMMIT around the two query statements (select and then insert)