Auto-Increment-Spalten in Datenbanken
Werbung
Auto-Increment-Spalten in Datenbanken Holger Jakobs – [email protected], [email protected] 2012-06-25 Inhaltsverzeichnis 1 Oracle 2 2 PostgreSQL 3 3 MS Access 2000 4 Jede Tabelle sollte einen Primärschlüssel (primary key) haben, der ein Tupel eindeutig identifiziert. Für manche Dinge bietet sich ein solcher Schlüssel geradezu an, z. B. die ISBN1 (International Standard Book Number) für Buchtitel, ISSN2 (International Standard Serial Number) für Periodika oder die EAN3 (European Article Number) für Artikel des Handels. Manche Datenbank-Frontends, z. B. OpenOffice.org Base, verweigern die Änderung von Daten bei Tabellen ohne Primärschlüssel. Bei anderen Dingen bietet sich nicht unmittelbar ein solcher Schlüssel an, so dass man oft einen sogenannten „künstlichen Schlüssel“ vergibt. Dieser kann aus einer laufenden Nummer bestehen. Damit man diese nicht selbst mühsam hochzählen muss, kann man dies der Datenbank überlassen. Leider gibt der SQL-Standard diesbezüglich nichts her, so dass die einzelnen Datenbanksysteme hier proprietäre Lösungen bieten – wenn überhaupt. Natürlich könnte man auf die Idee kommen, eine select-Abfrage von max(nr) zu machen und selbst 1 zu addieren, um diesen Wert anschließend beim Einfügen des neuen Tupels zu verwenden. Dies hat den Charme, dass die neue Nummer bereits in einer Eingabemaske angezeigt werden kann, allerdings mit einem erheblichen Nachteil – denn es kann passieren, dass parallel laufende Transaktionen das Gleiche tun. Nur beim Einfügen des ersten Tupels wird es also gut gehen; bei allen anderen ist der Primärschlüssel dann schon vorhanden. Dann könnte man auf die Idee kommen, das Tupel unmittelbar nach dem Abfragen der höchsten Nummer einzufügen und sofort zum Ändern zu selektieren (select . . . for update. Aber auch hier gibt es eine „race condition“4 , denn auch dies können mehrere 1) 2) 3) 4) http://www.isbn.org http://www.issn.org http://www.ean-int.org siehe http://de.wikipedia.org/wiki/Race_Condition 1 1 ORACLE Transaktionen parallel versuchen. Die Wahrscheinlichkeit eines Fehlers ist hier gering, aber genau das ist das Problem: Es tritt erst beim Kunden auf, nicht beim Test. Weiterer Nachteil ist, dass das Tupel bei Abbruch der Eingabe wieder gelöscht werden muss, während es bei Verwendung von Auto-Increment nur erzeugt wird, wenn die Eingabe auch abgeschickt wird. Alle Lösungsansätze mit einem select max(nr) from tabelle oder ähnlich muss man als schlicht falsch zurückweisen. Daher bieten die Datenbanksysteme auch richtige Lösungen hierfür, was im Folgenden beispielhaft beschrieben wird. 1 Oracle Bei Oracle gibt es Sequenzen, die mittels create sequence erzeugt werden können. Allerdings ist es nicht möglich, durch einfache Angabe eines „Datentyps“ eine Sequenz zu erzeugen und zu benutzen. Um die jeweils nächste Zahl aus der Sequenz zu verwenden, kann man einen entsprechenden Aufruf manuell beim insert mit angeben, aber das wäre ja nicht automatisch und kann leicht vergessen werden. Besser ist es daher, einen Trigger zu generieren, der automatisch den gewünschten Wert einfügt. Mit einer einfachen default-Angabe klappt es bei Oracle leider nicht. create create nr name ); sequence mitarb_nr_seq; table mitarb ( integer primary key, varchar(20) -- Beispiel-Eingabe für Benutzung der Sequenz ohne Trigger insert into mitarb values (mitarb_nr_seq.nextval, 'Fritz Meier'); -- Trigger-Erzeugung zum automatischen Ergänzen des Wertes -- im neuen Tupel create trigger mitarb_nr_trig before insert on mitarb for each row when (new.nr is null) begin select mitarb_nr_seq.nextval into :new.nr from dual; end; / -- Slash ist nötig, um den Fehler "success with compilation error" -- zu vermeiden Die Prüfung auf null ist notwendig, denn es könnte ja sein, dass explizit ein Wert für das Attribut nr angegeben wurde, der dann nicht durch eine Zahl aus der Sequenz über- 2 2 POSTGRESQL schrieben werden soll. Möchte man das Einfügen expliziter Werte verhindern, kann man die when-Klausel weglassen. Bei Verwendung einer Sequenz benötigt man also drei Datenbank-Objekte: Sequenz, Tabelle und Trigger. Möchte man wissen, welcher Wert eingefügt wurde, so kann man den zuletzt eingefügten Wert ermitteln mit select sequenzname.currval from dual;, oder im Programm die Abfrage select sequenzname.nextval from dual; ausführen und den gelieferten Wert im anschließenden insert-Statement benutzen. 2 PostgreSQL In PostgreSQL gibt es 2 Möglichkeiten, das Gewünschte zu erreichen. Möchte man einen ab 1 zählenden Zähler haben, der immer um 1 erhöht wird, so genügt die Angabe serial als „Datentyp“. Es wird automatisch eine Sequenz erzeugt, die beim Löschen des Attributs aus der Datenbank gelöscht wird. Möchte man die Sequenz über die Lebensdauer des Attributs erhalten, dann muss man sie separat mittels create sequence sequenzname erzeugen. So erzeugte Sequenzen müssen bei Bedarf mit drop sequence sequenzname gelöscht werden. Beispiel für ein einfaches, automatisch hochzählendes Attribut als Primärschlüssel: create table mitarb ( nr serial primary key, name varchar(20) ); Auch bei spezielleren Wünschen, d. h. anderem Startwert oder anderer Schrittweite muss man mittels create sequence eine Sequenz erzeugen und den jeweils nächsten Wert aus dieser Sequenz als default-Wert für das gewünschte Attribut angeben. Beispiel: create create nr name ); sequence mitarb_nr_seq; table mitarb ( integer default nextval('mitarb_nr_seq') primary key, varchar(20) Natürlich kann man den Namen der Sequenz völlig frei wählen; der oben gezeigte Name entspricht dem von der Datenbank automatisch generierten bei Verwendung von serial. Bei Verwendung einer Sequenz benötigt man also zwei Datenbank-Objekte: Sequenz und Tabelle (mit default-Wert). Falls man erwartet, dass im Laufe der Zeit ein integer-Zähler nicht ausreicht, also überlaufen könnte, bietet sich der Typ bigserial an, der einen 64-Bit-Ganzzahltyp verwendet. Ob man die Spalte eindeutig macht oder zum Primärschlüssel erhebt, ist einem selbst überlassen, d. h. möchte man es tun, muss man die Klauseln unique bzw. primary key hinzufügen. 3 3 MS ACCESS 2000 Falls man anderen Benutzern das Recht zum Einfügen nicht direkt an der Tabelle, sondern nur über eine Sicht (View5 ) erlaubt hat, ist es notwendig, den Benutzern auch das UPDATE-Recht an der Sequenz ausdrücklich zu geben: grant update on sequenzname to benutzer/gruppe Möchte man wissen, welcher Wert eingefügt wurde, so kann man den zuletzt eingefügten Wert ermitteln mit select currval(’sequenzname’);. Alternativ kann man sich im Programm einen Wert besorgen mittels select nextval(’sequenzname’); und diesen beim anschließenden insert verwenden. 3 MS Access 2000 Bei MS Access kann man einen automatisch hochzählenden Datentyp auswählen. Der Felddatentyp heißt dort „Auto-Wert“. In einem create table-Statement verwendet man den „Datentypen“ autoincrement, wodurch tatsächlich ein langer Ganzzahlwert mit automatischem Hochzählen eingerichtet wird. Eine separate Sequenz als eigenes Datenbankobjekt wird bei Access nicht erzeugt. Daher kann man auch nicht mehrere verschiedene Tabellen haben, die sich aus derselben Quelle eindeutiger Nummern bedienen. Bei Verwendung einer Sequenz benötigt man also nur ein Datenbank-Objekt: Tabelle (mit autoincrement-Angabe). Möchte man wissen, welcher Wert eingefügt wurde, so kann man nach dem Einfügen des Tupels die Abfrage select @@identity; (ohne Angabe einer Tabelle) verwenden. $Id: auto_increment.tex,v bef207c043da 2008/11/24 10:46:08 bibjah $ 5) siehe auch Dokument „Views in SQL“ 4
