Schlüssel

zurück zum IBExpert Glossar

Schlüssel

  1. Primärschlüssel
    Primärschlüsssel zu vorhandener
    Tabelle hinzufügen
  2. Fremdschlüssel
  3. Kandidatenschlüssel
  4. Ersatzschlüssel/Sekundärschlüssel
  5. einfacher Schlüssel
  6. Zusammengesetzer Schlüssel
  7. Unique
  8. Künstliche Schlüssel/Ersatzschlüssel/
    Alias-Schlüssel
  9. Schlüsselverstoß
  10. Referentiale Integrität
  11. Kaskadierende referentiale Integrität

Schlüssel

In dem relationalen Modell werden Schlüssel zur logischen Organisation von Daten verwendet, sodass eine bestimmte Zeile eindeutig identifziert werden kann. ein Schlüssel sollte nicht mit einem Index verwechselt werden. Ein Index ist Teil der physischen Festplattenstruktur einer Tabelle. Er wird verwendet, um den Datenzugriff während einer Abfrage zu beschleunigen. Indizes sind dahingegen nicht Teil ds relationalen Modells.

Firebird/InterBase® generiert automatisch einen Index für Primär- und Fremdschlüsselspalten. Auf Primärschlüsselspalten, erzwingt der Index die eindeutige (unique) Beschränkung, die vom relationalen Modell gefordert wird. Verbindungen zwischen Tabellen entstehen aus Primär- oder Fremdschlüsseln, somit sichert ein Index auf diesen Spalten eine maximale Leistung.

Primärschlüssel

Ein Primärschlüssel ist eine Spalte (= einfacher Schlüssel) oder eine Gruppe von Spalten (= zusammengesetzter Schlüssel), der der eindeutigen Definition eines Datensatzes/Zeile in der Tabelle dient. Ein Primärschlüssel kann immer zum Zeitpunkt der Definierung einer neuen Tabelle für jede Tabellen definiert werden. Wenn Ihre Datenbank nicht in allen Tabellen Primärschlüssel enthält und Sie diese nachträglich hinzufügen müssen, lesen Sie bitte im Kapitel Primärschlüssel zu vorhandenen Tabelen hinzufügen unten nach.

Die relationale Theorie besagt, das jeder Tabelle ein Primärschlüssel zugeordnet sein sollte. Er muss eindeutig sein und kann daher nicht NULL sein. Dadurch entsteht automatisch ein Schutz vor Speicherung von doppelten Werten. Tatsächlich ist es ohne Primärschlüssel unmöglich einen von zwei identischen Datensätzen zu löschen. Jede Tabelle kann nur einen zugeordneten Primärschlüssel haben, obwohl andere Spalten als UNIQUE? oder NOT NULL definiert sein können.

Eine Primärschlüssel ist nichts anderes, als eine eindeutige Beschränkung vervollständigt durch einen System index und die Prüfbeschränkung NOT NULL. Primärschlüssel sind immer der bevorzugte Index des Firebird/InterBase® Optimierers.

Wenn ein Datensatz erzeugt oder geändert wird, prüft Firebird/InterBase® sofort die Gültigkeit des Primärschlüssels. Wenn die Nummer bereist vorhanden ist, erzeugt dies einen Schlüsselverstoß und der Speicherprozess wird sofort storniert. Leider erlaubt Firebird/InterBase® es, Tabellen ohne Primärschlüssel zu erzeugen, was ein Fehler ist. Datentabellen sollten immer verschlüsselt sein.

Vorhandene Primärschlüssel und ihre Systemnamen können Sie auf der Seite Tabelleneditor / Beschränkungen im IBExpert Tabelleneditor einsehen.

Es ist ratsam, den Primärschlüssel so kurz wie möglich zu halten, um den benötigen Speicherplatz zu minimieren und die Leistung zu erhöhen. IBExpert empfiehlt die Verwendung der ID Nummer eines Autoinkrementierungsgenerators als internen Primärschlüssel für alle Tabellen. Zum Beispiel ein einfacher BIGINT Datentyp Generator, der nicht von irgendwelchen Daten beeinflusst wird. Diese müssen nicht für den Benutzer sichtbar sein, da sie lediglich als Werkzeug dienen, um die Arbeit mit der Datenbank effizienter zu machen und die Datenintegrität zu erhöhen. Ein Generator kann als Quelle für alle Primärschlüssel in einer Datenbank dienen, da die Nummern nicht fortlaufend, sondern nur eindeutig sein müssen. Bei jeder Eingabe eines Datensatzes erzeugt der Generator automatisch eine ID Nummer, unabhängig vom Tabellennamen, beispielsweise new customer_id = 1, new order_id = 2, new orderline_id = 3, new orderline_id = 4, new customer_id = 5, etc. Ein weiterer Vorteil eines solchen einzelnen Autoinkrementiersgenerators als Primärschlüssel ist, dass die Datenbank perfekt vorbereitet ist für Replikationen; zwei oder mehr Server können verbunden werden und deren Daten einfach ausgetauscht werden, da Prmärschlüssel einfach auf beiden Servern definiert werden können, z.B. der Generator von Server 1 startet bei dem Wert 1000000000 und Server 2 bei 2000000000, dadurch werden Konflikte vermieden.

Obwohl diese Methode in Wirklichkeit leider nur selten verwendet wird, sollte man sie doch nutzen. Jeder Primärschlüssel wird immer nur einmal in einer Datenbank auftauchen, was in einer oo (objektorientierten) Framework mit sovielen umherfließenden Objekten sehr wichtig sein kann. Sie und die Objekte brauchen eine eindeutige Identifizierung, um dem System zu sagen, was sich hinter der Nummer verbirgt, ein Produkt, eine Bestellung, etc.

Seit der Firebird Version 1.5 ist die Verwendung der Subklausel USING INDEX am Ende eine Primär-, Unique- oder Fremdschlüsseldefinition erlaubt. Weiteres finden Sie im Kapitel Firebird 2.0 Language Reference Upate chapter, USING INDEX subclause.

Zusammengesetzte Schlüssel werden nicht empfohlen, da diese immer die Leistung verlangsamen und die Sequenz der betroffenen Felder immer in allen referenzierten Tabellen identsich sein muss.

Primärschlüsssel zu vorhandener Tabelle hinzufügen

Dieser Artikel wurde von Melvin Cox geschrieben. Hier wird eine Methode vorgestellt, Primärschlüssel in vorhandenen Tabellen mit IBExpert zu definieren:

Hier ist eine brauchbare Arbeitsumgebung für diejenigen von uns, die nicht eine Ewigkeit damit verbringen möchten, Daten zu exportieren, diverse Tabellen zu löschen und neu zu erzeugen und anschließend Daten in diese Tabellen zurück zu importieren. Mit einer Firebird 1.5 Datenbank (Dialekt 1) erzeugt ODBC export von einer Microsoft Access Datenbank, habe ich erfolgreich Primärschlüssel in Tabellen definiert, indem ich folgende Schritte gemacht habe:

  1. Rufen Sie die Tabelle in das IBExpert Interfacefenster Tabelleneditor (per Doppelklick auf die entsprechenden Tabelle im DB Explorer oder verwenden Sie [Strg + O]). Die Seite Felder sollte aktiviert sein.
  1. Doppelklicken Sie auf die, zu dem Feld gehörenden NICHT NULL Box, das Sie als Primärschlüssel definieren möchten. Dadurch wird ein Feld (Feldname) ändern Dialogfenster geöffnet.
  2. Aktivieren Sie die Option NOT NULL und wählen Sie eine vorhandene Domäne oder erzeugen Sie eine.
  1. Klicken Sie auf OK und dann, nachdem Sie das von IBExpert erzeugte Skript überprüft haben auf Commit. Das Feld ist jetzt auf NOT NULL gesetzt.
  2. Rufen Sie den SQL Editor auf: Nützliches / SQL Editor (oder [F12]).
  3. Geben Sie folgenden Befehl ein:
ALTER TABLE table_name ADD PRIMARY KEY (field_name);
Zum Beispiel, um einen Primärschlüssel in der Tabelle EVENTS zu definieren, geben Sie folgendes ein:
ALTER TABLE events ADD PRIMARY KEY (event_id);
  1. Klicken Sie auf den Schaltknopf Ausführen oder [F9].
  2. Schließen Sie den SQL Editor. Dadurch wird der Dialog There is active transaction geöffnet. Wählen Sie Commit.
  3. Schließen Sie den Tabelleneditor.
  4. Öffnen Sie den Tabelleneditor erneut [Strg + O]. Der neu definierte Primärschlüssel ist jetzt sichtbar.

zurück zum Seitenanfang

Fremdschlüssel

Ein Fremdschlüssel setzt sich zusammen aus ein oder mehreren Spalten, die auf einen Primärschlüssel referenzieren. Referenzieren bedeutet hier, dass wenn ein Wert in einem Fremdschlüssel eingegeben wird, prüft Firebird/InterBase®, ob der Wert auch im referenzierten Primärschlüssel vorhanden ist. Dies wird zur Wahrung der Domänenintegrität verwendet.

Ein Fremdschlüssel ist unerlässlich für die Definition von Beziehungen (Referenzen) in einer Datenbank. Er kann im IBExpert Tabelleneditor (wird vom DB Explorer gestartet) auf der Seite Beschränkungen definiert werden.

Fremdschlüssel werden hauptsächlich für sogenannte Referenztabellen verwendet. In einer Tabelle gespeichert, z.B. EMPLOYEE muss bestimmt werden zu welcher Abteilung jeder Mitarbeiter gehört. Mögliche Eingaben für Abteilungsnummer für jeden EMPLOYEE-Datensatz sind in der Tabelle DEPARTMENT enthalten. Da die Tabelle EMPLOYEE auf die Tabelle DEPT_NO als Primärschlüssel für die Tabelle DEPARTMENT referenziert, gibt es eine Fremdschlüsselbeziehung zwischen der Tabelle EMPLOYEE und der Tabelle DEPARTMENT. Fremdschlüsselbeziehungen werden automatische in Firebird/InterBase® überprüft, so können Datensätze mit nicht vorhandenen Abteilungsnummer nicht gespeichert werden.

Wenn eine Primärschlüssel:Fremdschlüsselbeziehung zu einer einzelnen Zeile in einer anderen Tabelle führt, ist das bekannt, weil eine virtuelle Zeile erzuegt wird. Die Spalten in dieser zweiten Tabelle bieten eine zusätzliche Beschreibung für den Primärschlüssel der ersten Tabelle. Dies nent man auch eine 1:1 Beziehung.

Ein Fremdschlüssel kann auch auf sich selbst verweisen. Firebird ermöglicht die Referenzierung rekursiver Daten und repräsentiert diese sogar auf diese Weise in einer Baumstruktur.

Fremdschlüssel und Ihre Systemnamen können in IBEXpert auf der Seite Tabelleneditor / Beschränkungen eingesehen werden.

Seit der Firebird Version 1.5 ist die Verwendung der Subklausel USING INDEX am Ende eine Primär-, Unique- oder Fremdschlüsseldefinition erlaubt. Weiteres finden Sie im Kapitel Firebird 2.0 Language Reference Upate chapter, USING INDEX subclause.

Ein Primärschlüssel muss nicht auf einen Fremdschlüssel referenzieren. Ein Unique-Index ist jedoch nicht ausreichend; es muss eine Unique-Beschränkung defineirt werden (diese Definition veursacht die automatische Erzeugung des Unique-Indexes).

Wenn ein Fremdschlüssel definiert wird, ist es notwendig Aktualisierungs- und Löschregeln festzulegen. Weiters hierzu finden Sie unter Referentiale Integrität und Kaskadierende referentiale Integrität.

SQL syntax: 

 ALTER TABLE MASTER 
 ADD CONSTRAINT UNQ_MASTER UNIQUE (FIELD_FOR_FK);

Fremdschlüsselnamen sind auf 32 Zeichen seit InterBase® 6 and Firebird 1.5 begrenzt; InterBase® 7 erlaubt 64 Zeichen. IBExpert hingegen empfiehlt den Tabelennamen auf 14 Zeichen zu begrenzen, sodass der Fremdchlüsselname beide refernezierende Tabellenamen enthalten kann: Präfix FK plus zwei Separatoren plus beide Tabellennamen, z.B. FK_Table1_Table2.

Bitte beachten Sie jedoch, dass dies keine Auflage von Firebird/InterBase® ist, sondern eine reine IBExpert Empfehlung ist, um klare und logische Namensbedingungen für Fremdschlüssel zu erreichen.

Hinweis: wenn Daten bereits in eine Tabelle eingegeben wurden, der nachträglich ein Fremdschlüssel zugewiesen wurde, wird dies von Firebird/InterBase® nicht zugelassen, da es gegen die Prinzipien der referentialen Integrität verstößt. Es ist jedoch möglich, alte Daten zu filtern und zu löschen (wo keine Referenz zu einem Primäätschlüssel definiert wurde mit Hilfe einer SELECT-Anweisung und anschließendem Commit. Es ist hierfür wichtig in IBExpert, die Datenbankverbindung anschließend zu lösen und erneut zu verbinden.

Neu seit Firebird 2.0: Bei der Erzeugung von Fremdschlüsselbeschränkungen ist kein exklusiver Zugriff mehr erforderlich - Jetzt ist es möglich, eine Fremdschlüsselbeschränkung zu erzeugen, ohne das eine exklusive Datensperre für die ganze Datenbank notwendig ist.

zurück zum Seitenanfang

Kandidatenschlüssel

Jede Spalte oder Gruppe, die eindeutig einen Datensatz identifizieren kann kommt als Primärschlüssel in Betracht. Es ist immer NICHT NULL (d.h. darf nicht undefineirt bleiben) und eindeutig.

Ersatzschlüssel/Sekundärschlüssel

Zusätzlich zu Primärschlüssel ist es manchmal von Nutzen, Ersatzschlüssel oder Sekundärschlüssel zu definieren, zum Beispiel, in einer Projekttabelle, wo, zusätzlich zu dem Primärschlüssel ID-Feld, Sie sicherstellen möchten, dass jeder Projektname nur ein Mal verwendet wird.

zurück zum Seitenanfang

Einfacher Schlüssel

Ein einfacher Schlüssel aus nur einer Spalte zusammengestellt, d.h. eine einzelne Spalte wird als Primärschlüssel einer Tabelle ausgewiesen.

zurück zum Seitenanfang

Zusammengesetzter Schlüssel

Ein zusammengesetzter Schlüssel besteht aus zwei oder mehr Spalten, die zusammen als Primärschlüssel einer Tabelle ausgewiesen werden. Mehrfachspalten-Primärschlüssel können nur als Tabellenlevel-Beschränkungen definiert werden:

Einzelspalten Primärschlüssel können entweder in der Spalte oder im Tabellenlevel (nicht in beiden) definiert werden. Zum Beispil besagt der folgende Code, dass der Primärschlüssel der Tabelle aus drei Spalten besteht, JOB_CODE, JOB_GRADE und JOB_COUNTRY. Keiner dieser drei Spalten muss zwingend eindeutig sein, aber ihr kombinierter Wert muss eindeutig sein (und NOT NULL). 

 CREATE TABLE
 COLUMN_defs ...
 PRIMARY KEY (JOB_CODE,JOB_GRADE,JOB_COUNTRY);

Leider haben solche Schlüssel zwei große Nachteile: erstens verlangsamen Sie die Datenbankleistung erheblich, da Firebird/InterBase® alle Inhalte aller zugeordneten Spalten eines solchen zusammnegestezten Schlüssels überprüfen muss; zweitens muss die Sequenz der entsprechenden Felder in allen referenzierten Tabellen identisch sein.

Grundsätzlich sollten zusammengesetze Schlüssel vermieden werden! Die Verwendung von internen ID-Schlüsseln (sogenannte artificial keys) als Primärschlüssel sollte für jede Tabelle vorgezogen werden.

zurück zum Seitenanfang

Unique (eindeutig)

Unique-Felder sind eindeutig, unmissverständlich, einmalig, einzigartig (d.h. es sind keine doppelten Informationen in einem Datensatz eines Uniqe-Feldes erlaubt). Solche Felder müssen daher auch NICHT NULL sein.

Unique-Feldern wird eine eindeutiger Index zugeordnet. Jedes Unique-Feld ist ein Kandidaten-/Sekundärschlüssel.

zurück zum Seitenanfang

Künstliche Schlüssel/Ersatzschlüssel/Alias-Schlüssel

Ein künstlicher oder Ersatz- oder Alias-Schlüssel wird vom Datenbankdesigner/entwickler erzeugt, wennes keinen Kandidatenschlüssel gibt, d.h. kein logisches, einfaches Feld, dass als Primärschlüssel dienen könnte. Ein künstlicher Schlüssel ist eine kurze ID-Nummerdie zur eindeutigen Identifizierung eines Datensatzes verwendet wird.

So eine interne Primärschhlüssel-ID wird für alle Tabellen empfohlen. Sie sollten immer für den Benutzer unsichtbar sein, um jedem potentiellen externen Einfluss bezüglich ihrer Erscheinung und Zusammensetzung vorzubeugen.

Es sit immer ratsam, den Primärschlüssel so kurz, wie möglich zu halten, um den benötigten Speicherplatz zu minimieren und die Leistung zu erhöhen. Daher sollten künstliche Schlüssel ebenfalls so kurz, wie möglich sein. Eine ideale Lösung für die Erzeugung eines künstlichen Schlüssels ist, eine autoimkrementierende Generatoren-ID-Nummer zu verwenden.

IBExpert empfiehlt, diese Lösung als internen Primärschlüssel für alle Tabellen zu verwenden.

Für gewöhnlich ist ein solcher künstlicher/Ersatz-/Alias-Schlüssel nur ein autoinkrememtierendes Integer-Feld, sodass jeder Datensatz seinen eigenen eindeutigen Integer Idenitifizierer hat. Zum Beispiel: 

 CREATE TABLE CUSTOMERS (
    CUSTOMER_ID INTEGER NOT NULL,
    FIRST_NAME VARCHAR(20),
    MIDDLE.NAME VARCHAR(20),
    LAST_NAME VARCHAR(20);
 ...);

In diesem Fall ist CUSTOMER_ID thder künstliche oder Ersatzschlüssel.

zurück zum Seitenanfang

Schlüsselverstoß

Wenn ein Datensatz erzeugt oder geändert wird, überprüft Firebird/InterBase® sofort die Gültigkeit der Primärschlüssel. Wenn die Nummer bereits vorhanden ist, oder das Feld leer gelassen wurde, ist das ein Schlüsselverstoß und die Speicherungsprozess wird sofort storniert.

Firebird/InterBase® sendet sofort eine Fehelrmeldung entsprechend des Verstoßes gegen eine Unique oder Primärschlüssel-Beschränkung.

zurück zum Seitenanfang

Referentiale Integrität

Die Beziehung zwischen einem Fremdschlüssel und seiner referenzierten Primärschlüssel ist der Mechanismus zur Erhaltug der Datenbeständigkeit und Integrität. Referentiale Integrität stellt die Datenintegrität zwischen Tabellen her, die durch Fremdschlüssel verbunden sind. Ein Fremdschlüssel ist eine oder mehrere Spalten die auf einen Prmärschlüssel referenzieren, d.h. wenn ein Wert in den Fremdschlüssel eingegeben wird, überprüft Firebird/InterBase®, ob dieser Wert auch im referenzierten Priärschlüssel vorhanden ist und sichert so die referentiale Integrität.

Referentiale Integrität kann in folgenden drei Fällen auftreten:

  1. In der Master-Tabelle wird ein Datensatz gelöscht. Zum Beispiel könnte die Löschung eines Kunden, für den es noch Aufträge gibt, zur Anforderung von Datensätzen führen ohne gültige Kundennummer. Dies könnte Analysen und Listen verfälschen, da die internen Beziehungen nicht mehr erscheinen. Die Vorbeugung gegen Datensatzlöschung in Master-Tabellen, nennt man unerlaubte Löschung. Die Weiterleitung der Löschungen an alle Detail-Tabellen nennt man kaskadierende Löschung.
  2. Der Primärschlüssel wird in der Master-Tabelle geändert. Zum Beispiel wird an eine Kunden eine neue Kundennummer vergeben, sodass alle zugehörigen Aufträge des Kunden auch der neuen Kundennumemr zugeordnet werden. Dies nennt man kaskadierende Aktualisierung.
  3. Ein neuer Datensatz wird erzeugt und der Fremschlüssel ist nicht in der Master-Tabelle vorhanden. Zum Beispiel wird ein Auftrag mit einer Kundennummer noch nicht in der Master-TAbelle zugeordnet. Eine mögliche Lösung könnte die automatische Erzeugung eines neuen Kunden sein. Dies nennt man kaskadierende Eingabe.

Referentiale Integrität wird nativ in Firebird/InterBase® unterstützt, d.h. alle auf Fremdschlüsseln basierenden Beziehungen werden automatisch während der Datenänderung einbezogen. Seit InterBase® 5, unterstützt InterBase® deklarative referential Integrität mit kaskadierenden Löschungen und Aktualisierungen. In älteren Versionen könnte dies mit Triggern erreicht werden.

zurück zum Seitenanfang

Kaskadierende referentiale Integrität

Seit InterBase® 5/Firebird wird auch die kaskadierende referentiale Integrität unterstützt.

Wenn eine Fremdschlüsselbeziehung definiert wird kann der Benutzer definieren, welche Aktion Änderungen oder Löschungen seines referenzierten Primärschlüssels folgen soll. ON UPDATE definiert, was passiert, wenn der Primärschlüssel sich ändert und ON DELETE definiert die Aktion die einer Löschung eines referenzierten Primärschlüssels folgen soll. In beiden Fällen stehen folgende Optionen zur Verfügung:

  1. NO ACTION: ruft eine Exception (Ausnahme) auf, wenn keine Beziehung irgendwo in einer anderen Tabelle vorhanden ist:
  1. CASCADE: Die Fremdschlüsselspalte wird auf den Wert des neuen Primärschlüssels gesetzt. Eine sehr nützlich Funktion, wenn aktualisiert wird, da alle referenzierten Fremdschlüssel automatisch aktualisiert werden. Beim Löschen, löscht die CASCADE-Option auch die Fremdschlüsselzeile, wenn der Primärschlüssel gelöscht wird. Sein Sie bitte sehr vorichtig beim Einsatz von CASCADE ON DELETE; wenn Sie einen Kunden löschen, löschen Sie seine Aufträge, seine Bestellpositionen, seine Adresse, alles wofür eine Schlüsselbeziehung definiert wurde. Es ist sicherer, eine Prozedur zu schreiben, die sicher stellt, dass nur die notwendigen Datensätze in der richtigen Reihenfolge gelöscht werden.
  2. SET NULL: Wenn ein Fremdchlüsselwert NULL sein darf, wenn ein Primärschlüsselwert gelöscht wird, werden die relevanten Fremdschlüsselfelder, die auf diesen Primärschlüselwert referenzieren auch auf NULL gesetzt.
  3. SET DEFAULT: Die Fremdschlüsselspalte wrid auf ihren Standardwert gesetzt, wenn ein Primärschlüsselfeld gelöscht wird.

Siehe auch:
englischsprachig:
Firebird Null Guide: Keys and uniques indices
Declarative referential integrity versus triggers

zurück zum Seitenanfang | zurück zum IBExpert Glossar