Auto Increment Composite Key InnoDB

innodb, mysql

Solution

You can use this BEFORE INSERT trigger to substitute zero id-values -

CREATE TRIGGER trigger1
  BEFORE INSERT
  ON table1
  FOR EACH ROW
BEGIN

  SET @id1 = NULL;

  IF NEW.id1 = 0 THEN
    SELECT COALESCE(MAX(id1) + 1, 1) INTO @id1 FROM table1;
    SET NEW.id1 = @id1;
  END IF;

  IF NEW.id2 = 0 THEN

    IF @id1 IS NOT NULL THEN
      SET NEW.id2 = 1;
    ELSE
      SELECT COALESCE(MAX(id2) + 1, 1) INTO @id2 FROM table1 WHERE id1 = NEW.id1;
      SET NEW.id2 = @id2;
    END IF;

  END IF;

END

Then insert zero-values (id1 or id2) to generate new values -

INSERT INTO table7 VALUES(0, 0, '1');
INSERT INTO table7 VALUES(0, 0, '2');
INSERT INTO table7 VALUES(1, 0, '3');
INSERT INTO table7 VALUES(1, 0, '4');
INSERT INTO table7 VALUES(1, 0, '5');
...

Problem

I want to create a MyISAM like behavior for an InnoDB MySQL table. I want to have a composite primary key: PRIMARY KEY(id1, id2) Where id1 auto increments based on the value of id2. What's the best way to accomplish this with InnoDB? ``` +----------+-------------+--------------+ | id1 | id2 | other_column | +----------+-------------+--------------+ | 1 | 1 | Foo | | 1 | 2 | Bar | | 1 | 3 | Bam | | 2 | 1 | Baz | | 2 | 2 | Zam | | 3 | 1 | Zoo | +----------+-------------+--------------+ ```

Original source