Mysql Auto-Increment Composite

Question

I am trying to get the following to occur with my InnoDB using auto-increment

How do I set this up? I tried setting both as the PK but that didn't work like it seems to in other DB engines.

Google was of no help or perhaps I couldn't get the right search terms.

This question does not show any research effort. It is important to **do your homework**. Tell us what you found and ***why*** it didn't meet your needs. This demonstrates that you've taken the time to try to help yourself, it saves us from reiterating obvious answers, and most of all it helps you get a more specific and relevant answer. [FAQ](http://stackoverflow.com/questions/how-to-ask). — Kermit, Apr 01 '13 at 22:18
What I found was people using different DB engines and someone trying to do the opposite of what I wanted to do. Didn't help too much: http://stackoverflow.com/questions/3804845/auto-increment-usage-in-composite-key — Rujikin, Apr 01 '13 at 22:22
4 days, that helped with searching. Found this: http://stackoverflow.com/questions/5416548/mysql-two-column-primary-key-with-auto-increment# — Rujikin, Apr 01 '13 at 22:31

score 2 · Accepted Answer · answered Apr 01 '13 at 22:44

One way to do it is to use a trigger.

Your table DDL might look like this

CREATE TABLE `composite` (
  `id` INT(11) UNSIGNED NOT NULL,
  `ai` INT(11) UNSIGNED NOT NULL,
  PRIMARY KEY (`id`,`ai`)
);

And a trigger

DELIMITER $$

CREATE TRIGGER tg_comp_insert
BEFORE INSERT ON composite
FOR EACH ROW 
BEGIN
    SET NEW.ai = (SELECT IFNULL(MAX(ai), 0) + 1 FROM composite WHERE id = NEW.id);
END $$

DELIMITER ;

Then use it like this

INSERT INTO composite (id, ai) VALUES(1,0);
INSERT INTO composite (id, ai) VALUES(1,0);

The result will be

+----+----+
| id | ai |
+----+----+
|  1 |  1 |
|  1 |  2 |
+----+----+

Mysql Auto-Increment Composite

1 Answers1