Mehrspaltige Primärschlüssel mit AUTO_INCREMENT

21 Oct 2012 um 12:57 - MySQL/Datenbank

Ich bin Fan von AUTO_INCREMENT, die ich ständig verwende. Standardmäßig wird eine AUTO_INCREMENT-Spalte als Primärschlüssel definiert.

Einerseits ist diese automatische Definition sehr hilfreich, aber das kann auch stören, wenn sie eine andere Spalte als Primärschlüsseö geplant haben. Na ja, dann brauchen sie in diesem Fall AUTO_INCREMENT gar nicht :)

Eine interessante Implementierung von AUTO_INCREMENT gibt es in MySQL bei dem mehrspaltigen Primärschlüssel. Hier kann man auch AUTO_INCREMENT anwenden, man muss aber die Storage-Engine richtig auswählen. Außerdem ist die Parameterreihenfolge zu beachten:

 CREATE TABLE IF NOT EXISTS `TTEST1` (
`ID` int(4) NOT NULL AUTO_INCREMENT,
`PROJECT` int(4) NOT NULL,
PRIMARY KEY (`ID`,`PROJECT`)
) ENGINE=InnoDB;

In diesem Fall wird einfach einen merhspaltigen Primärschlüssel erzeugt. Beim Hinzufügen neuen Datensätze wird den Auto-Wert immer um 1 erhöht. Anders gesagt, es ist schon eine nicht effiziente Definition, weil die Spalte ID allein als Primärschlüssel definiert werden kann.

Wir wollen aber, dass beide Spalten einen Index darstellen, d.h. Auto-Wert wird innerhalb den Werten von Spalte PROJECT erhöht:

 ID    PROJECT
1 1
2 1
3 1
1 2
2 2
3 2
.......

Zuerst muss man die Reihenfolge von Spalten in Primärschlüssel anpassen:

 CREATE TABLE IF NOT EXISTS `TTEST2` (
`ID` int(4) NOT NULL AUTO_INCREMENT,
`PROJECT` int(4) NOT NULL,
PRIMARY KEY (`PROJECT`,
`ID`)
) ENGINE=InnoDB;

Jetzt sollte es schon funktionieren, aber wir bekommen eine Fehlermeldung:

 #1075 - Incorrect table definition; 
there can be only one auto column and it must be defined as a key

Es liegt daran, dass AUTO_INCREMENT in mehrspaltingen Primärschlüsseln nur in MyISAM erlaubt ist (siehe Dokumentation "3.6.9. Using AUTO_INCREMENT"): "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"

 CREATE TABLE IF NOT EXISTS `TTEST2` (
`ID` int(4) NOT NULL AUTO_INCREMENT,
`PROJECT` int(4) NOT NULL,
PRIMARY KEY (`PROJECT`,
`ID`)
) ENGINE=MyISAM;

Mit MyISAM und den richtigen Parameter-Reihenfolge (`PROJECT`,`ID`) funktioniert es einwandfrei :)

Leider kann man dann nicht mehr AUTO-Wert verschieben Abfrage ALTER TABLE TTEST2 AUTO_INCREMENT=10 wird zwar problemlos ausgeführt, aber der nächste Wert wird sowieso als MAX(auto_increment_column) + 1 WHERE prefix=given-prefix berechnet.

Kommentare

Bisland keine Kommentare - sei der erste!


© 2008 Anton Pavlushko - Webentwicklung, Internetmarketing und Suchmaschinenoptimierung (SEO)

0.0058319568634033 sec