Requeriment logic with Composite Primary Keys

dr_pompeii · #1 (**permalink**) 04-18-10, 15:08

Dear Members

I have the follow table definition

Code:

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

Code:

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

Code:

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>

I want some way, if is necessary edit the table no problem, to get something like this (desired output)

Code:

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

I mean, each Master table has its own control from its childs
I need this solution to apply too for warehouse documentation control, something like

Code:

+------------+------------------+-------+
| series     | warehouse-number | type  |
+------------+------------------+-------+
|          1 |                1 | in    |
|          1 |                2 | out   |
|          1 |                3 | in    |
|          2 |                1 | out   |
|          2 |                2 | out   |
|          2 |                3 | out   |
+------------+------------------+-------+

Thanks in advanced for your guidance

r937 · #2 (**permalink**) 04-18-10, 15:16

yes you can do this in mysql

see MySQL :: MySQL 5.1 Reference Manual :: 3.6.9 Using AUTO_INCREMENT

they have an example there

dr_pompeii · #3 (**permalink**) 04-18-10, 15:23

Hello r937

Thanks for the reply, I will try it, little worry since they use
ENGINE=MyISAM instead of ENGINE=InnoDB;

Thanks

dr_pompeii · #4 (**permalink**) 04-18-10, 15:30

No work

Code:

CREATE TABLE animals (
    grp ENUM('fish','mammal','bird') NOT NULL,
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (id,grp)
) ENGINE=InnoDB;

color orange to avoid

Quote:

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

Code:

INSERT INTO animals (grp,name) VALUES
    ('mammal','dog'),('mammal','cat'),
    ('bird','penguin'),('fish','lax'),('mammal','whale'),
    ('bird','ostrich');

SELECT * FROM animals ORDER BY grp,id;

+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  4 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  5 | whale   |
| bird   |  3 | penguin |
| bird   |  6 | ostrich |
+--------+----+---------+

The sample output has instead

Code:

+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
+--------+----+---------+

it-iss.com · #5 (**permalink**) 04-18-10, 16:19

Hi,

this only appears to work on MyISAM tables and not on InnoDB:

For MyISAM tables you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups.

dr_pompeii · #6 (**permalink**) 04-18-10, 20:39

Hello Ronan

Thanks for the reply, but the huge problem is that my table is
a bridge table, therefore PK/FK and even worst transactional support are mandatory, therefore InnoDB is mandatory

Some guidance, going to be appreciate it

Thanks

it-iss.com · #7 (**permalink**) 04-19-10, 04:28

You can always try writing an ON INSERT trigger so that it calculates the value dynamically based on the algorithm above in the documentation i.e. MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. However, on the downside when deletes occur there may be gaps in the numbering.

Ikviens · #8 (**permalink**) 04-19-10, 07:52

Hello,

If a SELECT statement will do, then consider the following:

Code:

SELECT I1.idArticulo
     , I1.idItem
     , COUNT(I2.idItem) AS groupCount
     , I1.texto
  FROM item I1
 INNER JOIN item I2
    ON I1.iditem >= I2.iditem
   AND I1.idarticulo = I2.idarticulo
 GROUP BY I1.idArticulo, I1.idItem;

+------------+--------+------------+-------+
| idArticulo | idItem | groupCount | texto |
+------------+--------+------------+-------+
|          1 |      1 |          1 | one   | 
|          1 |      2 |          2 | two   | 
|          1 |      3 |          3 | three | 
|          2 |      4 |          1 | one   | 
|          2 |      5 |          2 | two   | 
|          2 |      6 |          3 | three | 
+------------+--------+------------+-------+
6 rows in set (0.00 sec)

dr_pompeii · #9 (**permalink**) 04-20-10, 15:17

Hello Ikviens

Really thanks so much!!!, your approach is really a smart way to handle my requirement in other way

Of course that my initial requirement was has a desired control for each new row insertion, yours is handle or make up the already data inserted

My Best Regards and god bless you

-Manuel

Internet Services

Web Development

Digital Marketing

Consumer Tech