MariaDB: Autoincrement bei zusammengesetzten Primärschlüsseln

Letztens musste ich eine MariaDB-Tabelle vom älteren MyIsam– in das heute geläufige InnoDB-Engine-Format übertragen. Allerdings wurde die Umsetzung mit einer Fehlermeldung quittiert:

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

Vereinfacht war der Aufbau der MyIsam-Tabelle wie folgt:

CREATE TABLE foo_table (
  foo_group tinyint(3) unsigned NOT NULL,
  foo_id int(10) unsigned NOT NULL AUTO_INCREMENT,
  foo_description varchar(255) NOT NULL,
  PRIMARY KEY(foo_group, foo_id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

Der Primary Key der Tabelle foo_table umfasste als Composite Key zwei Spalten: foo_group und foo_id, wobei für foo_id ein auto_increment definiert war.

Eine Abfrage verdeutlicht dies:

MariaDB [foo_db]> select foo_group, foo_id from foo_table;
+-----------+--------+
| foo_group | foo_id |
+-----------+--------+
|         1 |      1 |
|         1 |      2 |
|         1 |      3 |
|         2 |      1 |
|         2 |      2 |
|         3 |      1 |
|         3 |      2 |
|         3 |      3 |
+-----------+--------+
MariaDB [foo_db]>

Der Auto-Increment für foo_id erfolgt demnach nicht global, sondern nur für die jeweilige Gruppe, die durch foo_group definiert ist. Diese Funktionalität ist in InnoDb nicht direkt vorhanden, lässt sich aber mit einem Trigger nachbilden.

Bei einer Konvertierung nach InnoDB muss auch der Auto-Increment entfernt werden, indem foo_id ohne auto_increment neu definiert wird:

ALTER TABLE foo_table
CHANGE foo_id foo_id int(10) unsigned NOT NULL AFTER foo_group,
ENGINE='InnoDB';

Danach kann ein Trigger eingerichtet werden, der bei einem Insert für pro_id den bislang höchsten Wert der entsprechenden Gruppe um 1 erhöht, oder falls noch kein Wert für die Gruppe vorhanden eine 1 zuweist:

DELIMITER $$

CREATE TRIGGER foo_bi BEFORE INSERT ON foo_table
FOR EACH ROW BEGIN
  SET NEW.foo_id = (
    SELECT IFNULL(MAX(foo_id), 0) + 1
    FROM foo_table
    WHERE foo_group = NEW.foo_group
  );
END $$

DELIMITER ;

Beim Einfügen eines Datensatzes über das Datenbank-Tool Adminer gab es allerdings noch ein kleines Problem. Wenn ich das Eingabefeld foo_id überspringe, sendet Adminer einen leeren String, was zu einer Fehlermeldung führt:

Incorrect integer value: '' for column `test1`.`foo_table`.`foo_id` at row 1

Hier hilft ein Default-Wert 0 für foo_id:

ALTER TABLE foo_table
CHANGE foo_id foo_id int(10) unsigned NOT NULL DEFAULT 0 AFTER foo_group;

Der Trigger überschreibt die 0 dann mit dem korrekten Auto-Increment-Wert.

Update:

Jens T. hat mich in den Kommentaren darauf aufmerksam gemacht, dass ein nachträglicher Wechsel von foo_group so nicht möglich ist. Hierzu muss zusätzlich ein entsprechender Trigger für die Updates erstellt werden:

DELIMITER $$

CREATE TRIGGER foo_bu BEFORE UPDATE ON foo_table
FOR EACH ROW BEGIN
  SET NEW.foo_id = (
    SELECT IFNULL(MAX(foo_id), 0) + 1
    FROM foo_table
    WHERE foo_group = NEW.foo_group
  );
END $$

DELIMITER ;