Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2008
    Posts
    22

    Unanswered: Requeriment logic with Composite Primary Keys

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2008
    Posts
    22
    Hello r937

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

    Thanks

  4. #4
    Join Date
    Jan 2008
    Posts
    22
    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
    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 |
    +--------+----+---------+

  5. #5
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  6. #6
    Join Date
    Jan 2008
    Posts
    22
    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

  7. #7
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  8. #8
    Join Date
    Mar 2006
    Posts
    56
    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)

  9. #9
    Join Date
    Jan 2008
    Posts
    22
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •