Composite Primary Key and Auto_Increment

Question

I'm trying to do the composite key with one of them auto incrementing, but when I try to enter a new row it just continue the sequential.

Here's the example of what happens:

Item_1 | Item_2
1     |   1    
1     |   2
2     |   3
2     |   4
2     |   5

Here's the example of what I want:

Item_1 | Item_2
1     |   1    
1     |   2
2     |   1
2     |   2
2     |   3

I create the table this way:

CREATE TABLE IF NOT EXISTS `usuarios` (
  `cod_user` int(11) NOT NULL AUTO_INCREMENT,
  `cod_user_emp` int(11) NOT NULL,
  PRIMARY KEY (`cod_user`,`cod_user_emp`),
  UNIQUE KEY `user` (`user`),
  KEY `cod_user` (`cod_user`)
  );

Edit

I resolved the problem doing a server sided php validation.

$result = $db->query("SELECT * FROM usuarios WHERE cod_user_emp=\"$emp\" ORDER BY cod_user DESC LIMIT 1");
while($row=$result->fetch_array()){
   $cod2 = $row['cod_user']+1;
}

See this post: http://stackoverflow.com/questions/5416548/mysql-two-column-primary-key-with-auto-increment — sgeddes, Mar 26 '13 at 12:32

John Woo · Accepted Answer · 2013-03-26T12:43:46.863

2

Remove that AUTO_INCREMENT column,

CREATE TABLE IF NOT EXISTS `usuarios` 
(
  `cod_user` int(11) NOT NULL,
  `cod_user_emp` int(11) NOT NULL,
  PRIMARY KEY (`cod_user`,`cod_user_emp`) -- <<== this is enough
);

And can create a Stored Procedure that increments Item_2 for every Item_1.

DELIMITER $$
CREATE PROCEDURE InsertRecord(IN ItemA INT)
BEGIN
    SET @max_id =  (
                    SELECT COALESCE(MAX(Item_2), 0) + 1
                    FROM TableName
                    WHERE   Item_1 = ItemA
                    );
    INSERT INTO tableName(Item_1, Item_2)
    VALUES(ItemA, @max_id)
END $$
DELIMITER ;

and call it like this,

CALL InsertRecord(2);

edited Mar 26 '13 at 12:43

answered Mar 26 '13 at 12:34

John Woo

258,903
69
498
492

I would do it with a trigger – Michiel van Vaardegem Mar 26 '13 at 12:37
@MichielvanVaardegem I rather not to. – John Woo Mar 26 '13 at 12:38
1

Why not? In my opinion you are much saver with a trigger, than you are with this procedure. Also, your insert stays the same – Michiel van Vaardegem Mar 26 '13 at 12:43
yep typo due to cop-paste. I'm not fun of using triggers though, you can post trigger as an answer `:D` – John Woo Mar 26 '13 at 12:44
The trigger example is also posted in the other thread mentioned in the first comments – Michiel van Vaardegem Mar 26 '13 at 12:45
I'd like to see the trigger example, could anyone post it please? – Heron Vieira Mar 26 '13 at 13:01
@HeronVieira see [here](http://stackoverflow.com/questions/18120088/defining-composite-key-with-auto-increment-in-mysql) for an example – DanMan Apr 04 '14 at 08:11

Composite Primary Key and Auto_Increment

Edit

1 Answers1

Linked