Datenintegrität

In diesem Kapitel wird erläutert, wie Integritäts-Constraints die mit einer Datenbank verknüpften Geschäftsregeln durchsetzen und das Eingeben ungültiger Informationen in Tabellen verhindern.

Einführung in die Datenintegrität

Es ist wichtig, dass die Daten die Datenintegrität aufrechterhalten, d.h. die Einhaltung von Geschäftsregeln, die vom Datenbankadministrator oder Anwendungsentwickler festgelegt werden.

Geschäftsregeln geben Bedingungen und Beziehungen an, die immer wahr sein müssen oder immer falsch sein müssen. Beispiel: Jedes Unternehmen definiert seine eigenen Richtlinien zu Gehältern, Mitarbeiternummern, Bestandsverfolgung usw.

Techniken zur Gewährleistung der Datenintegrität

Beim Entwerfen einer Datenbankanwendung haben Entwickler mehrere Optionen, um die Integrität der in der Datenbank gespeicherten Daten zu gewährleisten.

Zu diesen Optionen zählen die Folgenden:

Siehe:

Vorteile von Integritäts-Constraints

Ein Integritäts-Constraint ist ein Schemaobjekt, das mit SQL erstellt und gelöscht wird. Um die Datenintegrität durchzusetzen, verwenden Sie nach Möglichkeit Integritäts-Constraints oder Assertions.

Vorteile von Integritäts-Constraints und Assertions gegenüber Alternativen zur Durchsetzung der Datenintegrität:

Siehe:

Arten von Integritäts-Constraints

Mit Oracle AI Database können Sie Constraints sowohl auf Tabellen- als auch auf Spaltenebene anwenden, und Assertions, die mehrere Tabellen umfassen.

Ein Constraint, das als Teil der Definition einer Spalte oder eines Attributs angegeben wird, ist eine Inline-Spezifikation. Ein als Teil der Tabellendefinition angegebenes Constraint ist eine Out-of-Line-Spezifikation.

Ein Schlüssel ist die Spalte oder Gruppe von Spalten, die in der Definition bestimmter Typen von Integritäts-Constraints enthalten sind. Schlüssel beschreiben die Beziehungen zwischen den Tabellen und Spalten einer relationalen Datenbank. Einzelne Werte in einem Schlüssel werden als Schlüsselwerte bezeichnet.

Die folgende Tabelle beschreibt die Constraint-Typen. Jede kann entweder inline oder out-of-line angegeben werden, mit Ausnahme von NOT NULL, das inline sein muss.

Tabelle 1 Arten von Integritäts-Constraints

Constraint-Typ Beschreibung Siehe auch
NOT NULL Lässt Einfügungen oder Aktualisierungen von Zeilen zu, die einen Nullwert in einer angegebenen Spalte enthalten. "NOT NULL Integritäts-Constraints"
Eindeutiger Schlüssel Verhindert, das mehrere Zeilen denselben Wert in derselben Spalte oder einer Kombination von Spalten haben. Einige Werte sind jedoch Null. "Eindeutige Constraints"
Primärschlüssel Kombiniert ein Constraint NOT NULL und ein eindeutiges Constraint. Es verhindert, dass mehrere Zeilen denselben Wert in derselben Spalte oder Spaltenkombination haben, und verhindert, dass Werte Null sind. "Primärschlüssel-Constraints"
Fremdschlüssel Gibt eine Spalte als Fremdschlüssel an und stellt eine Beziehung zwischen dem Fremdschlüssel und einem Primärschlüssel oder eindeutigen Schlüssel her, der als referenzierter Schlüssel bezeichnet wird. "FOREIGN KEY Constraints"
Prüfen Erfordert einen Datenbankwert, um eine angegebene Bedingung zu erfüllen. "Constraints prüfen"
REF Gibt die Typen der zulässigen Datenbearbeitung für Werte in einer REF-Spalte an und gibt an, wie sich diese Aktionen auf abhängige Werte auswirken. In einer objektrelationalen Datenbank kapselt ein integrierter Datentyp, der REF genannt wird, eine Referenz auf ein Zeilenobjekt eines angegebenen Objekttyps. Referenzielle Integritäts-Constraints für REF-Spalten stellen sicher, dass ein Zeilenobjekt für die REF vorhanden ist. Oracle AI Database Object-Relational Developer's Guide mit Informationen zu REF-Constraints
Assertions Assertions sind boolesche Ausdrücke, die Unterabfragen in den Datenbanktabellen einer Anwendung enthalten. Assertions haben die Semantik eines Constraints, d.h. die Datenbank hat die Aufgabe, die Wahrheit dieser booleschen Ausdrücke sicherzustellen, während Transaktionen Daten ändern und diese Änderungen festschreiben. SQL-Assertions zum Erlernen von Assertions

Siehe:

NOT NULL Integritäts-Constraints

Ein NOT NULL-Constraint erfordert, dass eine Spalte einer Tabelle keine Nullwerte enthält. Ein Null ist das Fehlen eines Wertes. Standardmäßig sind für alle Spalten in einer Tabelle Nullwerte zulässig.

NOT NULL-Constraints sind für Spalten vorgesehen, denen keine Werte fehlen dürfen. Beispiel: Für die Tabelle hr.employees ist ein Wert in der Spalte email erforderlich. Wenn Sie versuchen, einen Mitarbeiterdatensatz ohne E-Mail-Adresse einzufügen, wird ein Fehler ausgegeben:

SQL> INSERT INTO hr.employees (employee_id, last_name) values (999, 'Smith');
.
.
.
ERROR at line 1:
ORA-01400: cannot insert NULL into ("HR"."EMPLOYEES"."EMAIL")

Sie können eine Spalte mit einem Constraint NOT NULL nur hinzufügen, wenn die Tabelle keine Zeilen enthält oder wenn Sie einen Standardwert angeben.

Siehe:

Eindeutige Constraints

Ein Eindeutiger Schlüssel-Constraint erfordert, dass jeder Wert in einer Spalte oder Gruppe von Spalten eindeutig ist. Keine Zeilen einer Tabelle dürfen doppelte Werte in einer einzelnen Spalte (dem eindeutigen Schlüssel) oder Spaltengruppe (dem zusammengesetzten eindeutigen Schlüssel) mit einem eindeutigen Schlüssel-Constraint aufweisen.

Hinweis: Der Begriff Schlüssel bezieht sich nur auf die im Integritäts-Constraint definierten Spalten. Da die Datenbank ein eindeutiges Constraint erzwingt, indem ein Index implizit in den Schlüsselspalten erstellt oder wiederverwendet wird, wird der Begriff eindeutiger Schlüssel manchmal fälschlicherweise als Synonym für eindeutiges Schlüssel-Constraint oder eindeutigen Index verwendet.

Eindeutige Schlüssel-Constraints eignen sich für jede Spalte, in der doppelte Werte nicht zulässig sind. Eindeutige Constraints unterscheiden sich von Primärschlüssel-Constraints, deren Zweck es ist, jede Tabellenzeile eindeutig zu identifizieren, und enthalten in der Regel Werte, die keine andere Bedeutung haben als eindeutig zu sein. Beispiele für eindeutige Schlüssel:

Wie in Beispiel 4-1 gezeigt, ist ein eindeutiges Schlüssel-Constraint für die Spalte email der Tabelle hr.employees vorhanden. Der relevante Teil der Erklärung lautet wie folgt:

CREATE TABLE employees    ( ...
    , email          VARCHAR2(25)
        CONSTRAINT   emp_email_nn  NOT NULL ...
    , CONSTRAINT     emp_email_uk  UNIQUE (email) ... );

Mit dem Constraint emp_email_uk wird sichergestellt, dass nicht zwei Mitarbeiter dieselbe E-Mail-Adresse haben, wie im folgenden Beispiel dargestellt:

SQL> SELECT employee_id, last_name, email FROM employees WHERE email = 'PFAY';

EMPLOYEE_ID LAST_NAME                 EMAIL
----------- ------------------------- -------------------------
        202 Fay                       PFAY

SQL> INSERT INTO employees (employee_id, last_name, email, hire_date, job_id)
  1  VALUES (999,'Fay','PFAY',SYSDATE,'ST_CLERK');
.
.
.
ERROR at line 1:
ORA-00001: unique constraint (HR.EMP_EMAIL_UK) violated

Wenn kein NOT NULL-Constraint definiert ist, erfüllt ein NULL-Wert immer ein eindeutiges Schlüssel-Constraint. Daher sind Spalten mit eindeutigen Schlüssel-Constraints und NOT NULL-Constraints typisch. Diese Kombination zwingt den Benutzer, Werte in den eindeutigen Schlüssel einzugeben, und eliminiert die Möglichkeit, dass neue Zeilendaten mit vorhandenen Zeilendaten in Konflikt stehen.

Hinweis: Aufgrund des Suchmechanismus für eindeutige Schlüssel-Constraints für mehrere Spalten können Sie keine identischen Werte in den Nicht-Null-Spalten eines zusammengesetzten eindeutigen Schlüssel-Constraints mit teilweise Null verwenden.

Beispiel 7-1: Eindeutiger Constraint

SQL> SELECT employee_id, last_name, email FROM employees WHERE email = 'PFAY';

EMPLOYEE_ID LAST_NAME                 EMAIL
----------- ------------------------- -------------------------
        202 Fay                       PFAY

SQL> INSERT INTO employees (employee_id, last_name, email, hire_date, job_id)
  1  VALUES (999,'Fay','PFAY',SYSDATE,'ST_CLERK');
.
.
.
ERROR at line 1:
ORA-00001: unique constraint (HR.EMP_EMAIL_UK) violated

Siehe:

PRIMARY KEY Constraints

In einem Primärschlüssel-Constraint identifizieren die Werte in der Gruppe einer oder mehrerer Spalten, die dem Constraint unterliegen, die Zeile eindeutig. Jede Tabelle kann über einen Primärschlüssel verfügen, der die Zeile tatsächlich benennt und sicherstellt, dass keine doppelten Zeilen vorhanden sind.

Ein Primärschlüssel kann natürlich oder ein Surrogat sein. Ein natürlicher Schlüssel ist eine aussagekräftige ID, die aus vorhandenen Attributen in einer Tabelle besteht. Beispiel: Ein natürlicher Schlüssel kann eine Postleitzahl in einer Lookup-Tabelle sein.

Im Gegensatz dazu ist ein Surrogatschlüssel eine vom System generierte inkrementelle ID, die die Eindeutigkeit innerhalb einer Tabelle gewährleistet. In der Regel generiert eine Sequenz oder UUID Surrogatschlüssel.

Die Implementierung des Primärschlüssel-Constraints in Oracle AI Database stellt sicher, dass die folgenden Anweisungen wahr sind:

Eine typische Situation, in der ein Primärschlüssel angefordert wird, ist die numerische ID für einen Mitarbeiter. Jeder Mitarbeiter muss über eine eindeutige ID verfügen. Ein Mitarbeiter darf nur durch eine Zeile in der Tabelle employees beschrieben werden.

Das Beispiel unter Eindeutige Constraints gibt an, dass ein vorhandener Mitarbeiter die Personal-ID 202 hat, wobei die Personal-ID der Primärschlüssel ist. Das folgende Beispiel zeigt einen Versuch, einen Mitarbeiter mit derselben Personalnummer und einen Mitarbeiter ohne Personalnummer hinzuzufügen:

SQL> INSERT INTO employees (employee_id, last_name, email, hire_date, job_id)
  1  VALUES (202,'Chan','JCHAN',SYSDATE,'ST_CLERK');
.
.
.
ERROR at line 1:
ORA-00001: unique constraint (HR.EMP_EMP_ID_PK) violated

SQL> INSERT INTO employees (last_name) VALUES ('Chan');
.
.
.
ERROR at line 1:
ORA-01400: cannot insert NULL into ("HR"."EMPLOYEES"."EMPLOYEE_ID")

Die Datenbank erzwingt Primärschlüssel-Constraints mit einem Index. Normalerweise erstellt ein für eine Spalte erstelltes Primärschlüssel-Constraint implizit einen eindeutigen Index und ein NOT NULL-Constraint. Beachten Sie die folgenden Ausnahmen für diese Regel:

Standardmäßig ist der Name des implizit erstellten Index der Name des Primärschlüssel-Constraints. Sie können auch einen benutzerdefinierten Namen für einen Index angeben. Sie können Speicheroptionen für den Index angeben, indem Sie die ENABLE-Klausel in die CREATE TABLE- oder ALTER TABLE-Anweisung aufnehmen, mit der das Constraint erstellt wird.

Siehe auch: Oracle AI Database – Erste Schritte mit Oracle AI Database Development und Oracle AI Database Development Guide, um zu erfahren, wie Primärschlüssel-Constraints zu einer Tabelle hinzugefügt werden

Fremdschlüssel-Constraints

Wenn zwei Tabellen eine oder mehrere gemeinsame Spalten enthalten, kann Oracle AI Database die Beziehung zwischen den beiden Tabellen über ein Fremdschlüssel-Constraint erzwingen, das auch als referentielles Integritäts-Constraint bezeichnet wird.

Für ein Fremdschlüssel-Constraint muss für jeden Wert in der Spalte, in der das Constraint definiert ist, der Wert in der anderen angegebenen anderen Tabelle und Spalte übereinstimmen. Ein Beispiel für eine referenzielle Integritätsregel ist, dass ein Mitarbeiter nur für eine vorhandene Abteilung arbeiten kann.

In der folgenden Tabelle sind Begriffe aufgeführt, die mit referenziellen Integritäts-Constraints verknüpft sind.

Tabelle 2: Bedingungen für referenzielle Integritäts-Constraints

Begriff Definition
Fremdschlüssel

Die Spalte oder Gruppe von Spalten, die in der Definition des Constraints enthalten sind und einen referenzierten Schlüssel referenzieren. Beispiel: Die Spalte department_id in employees ist ein Fremdschlüssel, der die Spalte department_id in departments referenziert.

Fremdschlüssel können als mehrere Spalten definiert werden. Ein zusammengesetzter Fremdschlüssel muss jedoch einen zusammengesetzten Primär- oder eindeutigen Schlüssel mit derselben Anzahl von Spalten und denselben Datentypen referenzieren.

Der Wert von Fremdschlüsseln kann entweder mit dem referenzierten Primär- oder eindeutigen Schlüsselwert übereinstimmen oder Null sein. Wenn eine Spalte eines zusammengesetzten Fremdschlüssels Null ist, müssen die Nicht-Null-Teile des Schlüssels keinem entsprechenden Teil eines übergeordneten Schlüssels entsprechen.
Referenzierter Code Der eindeutige Schlüssel oder Primärschlüssel der Tabelle, die von einem Fremdschlüssel referenziert wird. Beispiel: Die Spalte department_id in departments ist der referenzierte Schlüssel für die Spalte department_id in employees.
Abhängige oder untergeordnete Tabelle Die Tabelle, die den Fremdschlüssel enthält. Diese Tabelle hängt von den Werten ab, die im referenzierten eindeutigen oder Primärschlüssel vorhanden sind. Beispiel: Die Tabelle employees ist ein untergeordnetes Element von departments.
Referenzierte oder übergeordnete Tabelle Die Tabelle, die vom Fremdschlüssel der untergeordneten Tabelle referenziert wird. Der referenzierte Schlüssel dieser Tabelle bestimmt, ob bestimmte Einfügungen oder Aktualisierungen in der untergeordneten Tabelle zulässig sind. Beispiel: Die Tabelle departments ist ein übergeordnetes Element von employees.

Abbildung 7-1 zeigt einen Fremdschlüssel in der Spalte employees.department_id. Dadurch wird sichergestellt, dass jeder Wert in dieser Spalte mit einem Wert in der Spalte departments.department_id übereinstimmen muss. Daher können keine fehlerhaften Abteilungsnummern in der Spalte employees.department_id vorhanden sein.

Abbildung 1: Referenzielle Integritäts-Constraints

Beschreibung der Abbildung cncpt056.gif

Integritäts-Constraints mit Selbstreferenz

Ein selbstreferenzielles Integritäts-Constraint ist ein Fremdschlüssel, der einen übergeordneten Schlüssel in derselben Tabelle referenziert.

In der folgenden Abbildung stellt ein selbstreferenzielles Constraint sicher, dass jeder Wert in der Spalte employees.manager_id einem vorhandenen Wert in der Spalte employees.employee_id entspricht. Beispiel: Der Manager für Mitarbeiter 102 muss in der Tabelle employees vorhanden sein. Mit diesem Constraint wird die Möglichkeit fehlerhafter Personalnummern in der Spalte manager_id eliminiert.

Abbildung 2: Referenzielle Constraints für einzelne Tabellen

Beschreibung der Abbildung cncpt057.gif

Nullwerte und Fremdschlüssel

Das relationale Modell lässt zu, dass der Wert von Fremdschlüsseln entweder mit dem referenzierten Primär- oder eindeutigen Schlüsselwert übereinstimmt oder Null ist. Beispiel: Eine Zeile in hr.employees gibt möglicherweise keine Abteilungs-ID an.

Wenn eine Spalte eines zusammengesetzten Fremdschlüssels Null ist, müssen die Nicht-Null-Teile des Schlüssels keinem entsprechenden Teil eines übergeordneten Schlüssels entsprechen. Beispiel: Eine Tabelle reservations kann einen zusammengesetzten Fremdschlüssel in den Spalten table_id und date enthalten, aber table_id ist null.

Übergeordnete Schlüsseländerungen und Fremdschlüssel

Die Beziehung zwischen Fremdschlüssel und übergeordnetem Schlüssel hat Auswirkungen auf das Löschen übergeordneter Schlüssel. Beispiel: Wenn ein Benutzer versucht, den Datensatz für diese Abteilung zu löschen, was geschieht mit den Datensätzen für Mitarbeiter in dieser Abteilung?

Wenn ein übergeordneter Schlüssel geändert wird, können referenzielle Integritäts-Constraints die folgenden Aktionen angeben, die für abhängige Zeilen in einer untergeordneten Tabelle ausgeführt werden sollen:

In Tabelle 7-3 werden die DML-Anweisungen beschrieben, die durch die verschiedenen referenziellen Aktionen für die Schlüsselwerte in der übergeordneten Tabelle und die Fremdschlüsselwerte in der untergeordneten Tabelle zulässig sind.

Tabelle 3: Zulässige DML-Anweisungen: Keine Aktion aktualisieren und löschen

DML-Anweisung Ausgestellt für übergeordnete Tabelle Ausgestellt für untergeordnete Tabelle
INSERT Immer OK, wenn der übergeordnete Schlüsselwert eindeutig ist OK nur, wenn der Fremdschlüsselwert im übergeordneten Schlüssel vorhanden oder teilweise oder vollständig Null ist
UPDATE NO ACTION Zulässig, wenn die Anweisung keine Zeilen in der untergeordneten Tabelle ohne einen referenzierten übergeordneten Schlüsselwert lässt Zulässig, wenn der neue Fremdschlüsselwert immer noch einen referenzierten Schlüsselwert referenziert
DELETE NO ACTION Zulässig, wenn keine Zeilen in der untergeordneten Tabelle den übergeordneten Schlüsselwert referenzieren Immer OK
DELETE CASCADE Immer OK Immer OK
DELETE SET NULL Immer OK Immer OK

Hinweis: Andere referenzielle Aktionen, die von FOREIGN KEY-Integritäts-Constraints von Oracle Database nicht unterstützt werden, können mit Datenbanktriggern durchgesetzt werden. Siehe "Überblick über Trigger".

Siehe auch: Oracle AI Database SQL Language Reference, um mehr über die ON DELETE-Klausel zu erfahren

Indizes und Fremdschlüssel

In der Regel sollten Fremdschlüssel indiziert werden. Die einzige Ausnahme ist, wenn der übereinstimmende eindeutige Schlüssel oder Primärschlüssel nie aktualisiert oder gelöscht wird.

Die Indexierung der Fremdschlüssel in untergeordneten Tabellen bietet folgende Vorteile:

Siehe:

Constraints prüfen

Ein Check-Constraint für eine Spalte oder eine Gruppe von Spalten erfordert, dass eine angegebene Bedingung für jede Zeile wahr oder unbekannt ist.

Wenn DML dazu führt, dass die Bedingung des Constraints als "false" ausgewertet wird, wird die SQL-Anweisung zurückgesetzt. Der Hauptvorteil von Check Constraints ist die Möglichkeit, sehr spezifische Integritätsregeln durchzusetzen. Beispiel: Sie können Check Constraints verwenden, um die folgenden Regeln in der Tabelle hr.employees durchzusetzen:

Das folgende Beispiel erstellt ein Höchstgehalts-Constraint für employees und zeigt, was geschieht, wenn eine Anweisung versucht, eine Zeile einzufügen, die ein Gehalt enthält, das den Höchstwert überschreitet:

SQL> ALTER TABLE employees ADD CONSTRAINT max_emp_sal CHECK (salary < 10001);
SQL> INSERT INTO employees (employee_id,last_name,email,hire_date,job_id,salary)
  1  VALUES (999,'Green','BGREEN',SYSDATE,'ST_CLERK',20000);
.
.
.
ERROR at line 1:
ORA-02290: check constraint (HR.MAX_EMP_SAL) violated

Eine einzelne Spalte kann mehrere Prüfungs-Constraints aufweisen, die auf die Spalte in ihrer Definition verweisen. Beispiel: Die Spalte salary könnte einen Constraint enthalten, der Werte über 10000 verhindert, und einen separaten Constraint, der Werte unter 500 verhindert.

Wenn mehrere Prüfungs-Constraints für eine Spalte vorhanden sind, müssen sie so konzipiert sein, dass ihre Zwecke nicht in Konflikt stehen. Es kann keine Reihenfolge der Bewertung der Bedingungen angenommen werden. Die Datenbank prüft nicht, ob Prüfbedingungen sich gegenseitig ausschließen.

Siehe auch: Oracle AI Database SQL Language Reference, um Informationen zu Einschränkungen für Check Constraints zu erhalten

Vorab überprüfbare CHECK-Constraints

Ein Check Constraint, das als PRECHECK markiert ist, kann außerhalb der Datenbank geprüft werden.

Beim Erstellen oder Ändern der Tabelle wird ein Check Constraint auf den Status PRECHECK gesetzt. Der Status PRECHECK ergänzt den Status ENABLE und VALIDATE eines Constraints und eines Constraints. Sie können alle drei Status gleichzeitig aktiv haben.

Wenn Sie das Check Constraint mit JSON ausdrücken, können Sie mit Oracle AI Database ein JSON-Schema exportieren und JSON-Daten mit einem JSON-Schemavalidator im Anwendungsclient validieren. Wenn ein Constraint auf den Status PRECHECK gesetzt ist, bedeutet dies, dass das Constraint ein äquivalentes JSON-Schema aufweist, das die Semantik des Constraints beibehält. Die Entwickler von Clientanwendungen können das Constraint in der Clientanwendung vorab validieren. Die Datenbank prüft das Constraint, und es wird ein Fehler generiert, wenn das Constraint nicht im JSON-Schema ausgedrückt werden kann.

Verwandte Themen

Assertions

Assertions sind boolesche Ausdrücke, die Unterabfragen in den Datenbanktabellen einer Anwendung enthalten.

Assertions haben die Semantik eines Constraints, d.h. die Datenbank hat die Aufgabe, die Wahrheit dieser booleschen Ausdrücke sicherzustellen, während Transaktionen Daten ändern und diese Änderungen festschreiben.

Die folgende SQL-Anweisung erstellt eine Assertion mit dem Namen NO_TRAINERS_IN_BOSTON, um zu fordern, dass in den in Boston ansässigen Abteilungen keine Trainer beschäftigt sind. Die oberste Ebene NOT EXISTS stellt einen booleschen Ausdruck dar, der verlangt, dass die Ergebnismenge des Joins zwischen den Tabellen EMP und DEPT leer bleiben soll.

CREATE ASSERTION no_trainers_in_boston CHECK
(NOT EXISTS
   (SELECT 'trainer in Boston'
    FROM   emp e, dept d
    WHERE  e.deptno = d.deptno
      AND  e.job = 'TRAINER'
      AND  d.loc = 'BOSTON'));

Assertions sind Objekte auf Schemaebene. Dies bedeutet, dass die Objektberechtigung ASSERTION REFERENCES für den Zugriff auf diese Tabellen erforderlich ist, wenn Tabellen anderer Schemas vom booleschen Ausdruck referenziert werden. Dies unterscheidet sich nicht von einem Fremdschlüssel, der sich über zwei Schemas erstreckt. Das heißt, eine REFERENCES-Objektberechtigung muss vorhanden sein, um erfolgreich einen Fremdschlüssel zu erstellen, der auf einen Primärschlüssel einer Tabelle in einem anderen Schema "zeigt".

Assertions sind generische Datenintegritäts-Constraints. Sie können verwendet werden, um CHECK-, PRIMARY KEY-, UNIQUE- und FOREIGN KEY-Constraints zu ersetzen.

Nehmen wir beispielsweise die folgenden Constraints für die EMP-Tabelle an:

Diese Constraints können mit folgenden Assertions implementiert werden:

CREATE ASSERTION commission_not_greater_than_salary CHECK
(NOT EXISTS
  (SELECT ''
   FROM   emp e
   WHERE  e.comm > e.sal));
CREATE ASSERTION emp_empno_pk CHECK
(NOT EXISTS
  (SELECT ''
   FROM   emp e1, emp e2
   WHERE  e1.empno = e2.empno
     AND  e1.rowid != e2.rowid));
CREATE ASSERTION emp_ename_deptno_uk CHECK
(NOT EXISTS
  (SELECT ''
   FROM   emp e1, emp e2
   WHERE  e1.ename = e2.ename
     AND  e1.deptno = e2.deptno
     AND  e1.rowid != e2.rowid));
CREATE ASSERTION emp_fk_dept CHECK
(NOT EXISTS
  (SELECT ''
   FROM   emp e
   WHERE  NOT EXISTS
     (SELECT ''
      FROM   dept d
      WHERE  d.deptno = e.deptno)));

Es wird jedoch stark empfohlen, immer Constraints zu verwenden, wann immer dies möglich ist, und Assertions nur zu verwenden, wenn Sie keinen Constraint verwenden können.

Verwandte Themen

Integritäts-Constraints und Assertions

Im Rahmen der Constraint- oder Assertion-Definition können Sie angeben, wie und wann Oracle AI Database das Constraint oder die Assertion erzwingen soll. Dadurch wird der Constraint- oder Assertion-Status bestimmt.

Prüft auf geänderte und vorhandene Daten

Mit der Datenbank können Sie angeben, ob ein Constraint oder eine Assertion für vorhandene Daten oder zukünftige Daten gilt. Wenn ein Constraint oder eine Assertion aktiviert ist, prüft die Datenbank neue Daten bei der Eingabe oder Aktualisierung. Daten, die nicht dem Constraint oder der Assertion entsprechen, können nicht in die Datenbank eingegeben werden.

Beispiel: Wenn Sie ein NOT NULL-Constraint für employees.department_id aktivieren, wird sichergestellt, dass jede zukünftige Zeile eine Abteilungs-ID aufweist. Wenn ein Constraint deaktiviert ist, kann die Tabelle Zeilen enthalten, die das Constraint verletzen.

Sie können Constraints und Assertions auf einen der folgenden Validierungsmodi einstellen:

Das Verhalten von VALIDATE und NOVALIDATE hängt immer davon ab, ob das Constraint oder die Assertion aktiviert oder deaktiviert ist. In der folgenden Tabelle werden die Beziehungen zusammengefasst.

Tabelle 4: Prüfung von geänderten und vorhandenen Daten

Geänderte Daten Vorhandene Daten Übersicht
ENABLE VALIDATE Vorhandene und zukünftige Daten müssen dem Constraint oder der Assertion entsprechen. Ein Versuch, ein neues Constraint oder eine neue Assertion auf eine aufgefüllte Tabelle anzuwenden, führt zu einem Fehler, wenn vorhandene Zeilen das Constraint oder die Assertion verletzen.
ENABLE NOVALIDATE Die Datenbank prüft das Constraint oder die Assertion, muss jedoch nicht für alle Zeilen wahr sein. Daher können vorhandene Zeilen das Constraint oder die Assertion verletzen, neue oder geänderte Zeilen müssen jedoch den Regeln entsprechen. Dieser Modus wird häufig in Data Warehouses verwendet, die vorhandene Daten enthalten, deren Integrität bereits geprüft wurde.
DISABLE VALIDATE Die Datenbank deaktiviert das Constraint, löscht den Index und verhindert die Änderung der eingeschränkten Spalten. Eine Assertion kann sich nicht in diesem Status befinden.
DISABLE NOVALIDATE Der Constraint oder die Assertion wird nicht geprüft und ist nicht unbedingt wahr.

Siehe auch: Oracle AI Database SQL Language Reference, um Informationen zu Constraint-Status zu erhalten

Wenn die Datenbank Constraints und Assertions auf Gültigkeit prüft

Jeder Constraint und jede Assertion ist entweder nicht verzögerbar (Standard) oder verzögerbar. Dieser Status bestimmt, wann Oracle AI Database das Constraint oder die Assertion auf Gültigkeit prüft.

Die folgende Grafik zeigt die Optionen für verzögerbare Constraints und Assertions.

Abbildung 3: Optionen für verzögerbare Constraints und Assertions

Beschreibung der Abbildung cncpt313.gif

Nicht verzögerbare Einschränkungen und Assertions

In einem nondeferrable-Constraint verzögert Oracle AI Database die Gültigkeitsprüfung des Constraints oder der Assertion niemals auf das Ende der Transaktion. Stattdessen prüft die Datenbank das Constraint oder die Assertion am Ende jeder Anweisung. Wenn das Constraint oder die Assertion verletzt wird, wird die Anweisung zurückgerollt.

Beispiel: Für die Spalte employees.last_name ist ein nicht verzögerbares NOT NULL-Constraint vorhanden. Wenn eine Session versucht, eine Zeile ohne Nachnamen einzufügen, setzt die Datenbank die Anweisung sofort zurück, da das Constraint NOT NULL verletzt wird. Es wird keine Zeile eingefügt.

Verzögerbare Constraints und Assertions

Mit einem deferrable Constraint oder einer Assertion kann eine Transaktion die SET CONSTRAINT-Klausel verwenden, um die Prüfung dieses Constraints oder dieser Assertion zu verzögern, bis eine COMMIT-Anweisung abgesetzt wird. Wenn Sie Änderungen an der Datenbank vornehmen, die das Constraint oder die Assertion verletzen könnten, können Sie mit dieser Einstellung das Constraint oder die Assertion effektiv deaktivieren, bis alle Änderungen abgeschlossen sind.

Sie können das Standardverhalten festlegen, wenn die Datenbank das verzögerbare Constraint oder die verzögerte Assertion prüft. Sie können eines der folgenden Attribute angeben:

Angenommen, ein verzögerbares NOT NULL-Constraint für employees.last_name ist auf INITIALLY DEFERRED gesetzt. Ein Benutzer erstellt eine Transaktion mit 100 INSERT-Anweisungen, von denen einige Nullwerte für last_name aufweisen. Wenn der Benutzer versucht, festzuschreiben, führt die Datenbank ein Rollback aller 100 Anweisungen durch. Wenn dieses Constraint jedoch auf INITIALLY IMMEDIATE gesetzt wurde, würde die Datenbank die Transaktion nicht zurücksetzen.

Wenn ein Constraint eine Aktion auslöst, betrachtet die Datenbank diese Aktion als Teil der Anweisung, die sie verursacht hat, unabhängig davon, ob das Constraint verzögert oder sofort ausgeführt wird. Beispiel: Wenn Sie eine Zeile in departments löschen, werden alle Zeilen in employees gelöscht, die auf die gelöschte Abteilungszeile verweisen. In diesem Fall wird das Löschen aus employees als Teil der DELETE-Anweisung betrachtet, die für departments ausgeführt wird.

Siehe auch: Oracle AI Database SQL Language Reference für Informationen zu Constraint-Attributen und deren Standardwerte

Beispiele für Constraint- und Assertion-Prüfung

In den folgenden Beispielen wird veranschaulicht, wann Oracle AI Database Constraints und Assertions prüft.

Folgendes wird angenommen:

Beispiel: Einfügen eines Wertes in eine Fremdschlüsselspalte, wenn kein übergeordneter Schlüsselwert vorhanden ist

Dieses Beispiel betrifft das Einfügen der ersten Zeile in die Tabelle employees. Derzeit sind keine Zeilen vorhanden. Wie kann also eine Zeile eingegeben werden, wenn der Wert in der Spalte manager_id keinen vorhandenen Wert in der Spalte employee_id referenzieren kann?

Einige Möglichkeiten sind:

Standardwerte werden als Teil einer INSERT-Anweisung aufgenommen, bevor die Anweisung geparst wird. Daher unterliegen die Default-Spaltenwerte der gesamten Integritäts-Constraint-Prüfung.

Beispiel: Aktualisierung aller Fremdschlüssel- und übergeordneten Schlüsselwerte

In diesem Beispiel hängt ein selbstreferenzielles Constraint die Einträge in der Spalte manager_id von employees von den Werten der Spalte employee_id ab.

Die Firma wurde verkauft. Aufgrund dieses Verkaufs müssen alle Personalnummern so aktualisiert werden, dass sie den aktuellen Wert plus 5000 ergeben, um sie mit den Personalnummern des neuen Unternehmens abzustimmen. Wie in der folgenden Grafik dargestellt, sind einige Mitarbeiter auch Manager:

Abbildung 4: Tabelle "Mitarbeiter" vor Aktualisierungen

Beschreibung der Abbildung cncpt058.gif

Da es sich bei den Managern auch um Personalnummern handelt, müssen die Managernummern ebenfalls um 5000 erhöht werden. Sie können die folgende SQL-Anweisung ausführen, um die Werte zu aktualisieren:

UPDATE employees SET employee_id = employee_id + 5000,
  manager_id = manager_id + 5000;

Obwohl ein Constraint definiert ist, um zu prüfen, ob jeder manager_id-Wert mit einem employee_id-Wert übereinstimmt, ist die vorherige Anweisung gültig, da die Datenbank Constraints nach Abschluss der Anweisung effektiv prüft. Abbildung 7-5 zeigt, dass die Datenbank die Aktionen der gesamten SQL-Anweisung ausführt, bevor Constraints geprüft werden.

Abbildung 5: Constraint-Prüfung

Beschreibung der Abbildung cncpt059.gif

Die Beispiele in diesem Abschnitt veranschaulichen das Constraint-Prüfverfahren während der Anweisungen INSERT und UPDATE. Die Datenbank verwendet jedoch dasselbe Verfahren für alle Typen von DML-Anweisungen. Die Datenbank verwendet denselben Mechanismus für alle Arten von Constraints, nicht nur für selbstreferenzielle Constraints. Hinweis: Vorgänge in einer Ansicht oder einem Synonym unterliegen den Integritäts-Constraints, die in den Basistabellen definiert sind.

Beispiel: Jeder Manager muss einen Sachbearbeiter in derselben Abteilung haben

In diesem Beispiel erstellen Sie eine Assertion, um sicherzustellen, dass jeder Manager über einen Sachbearbeiter in derselben Abteilung verfügt.

In diesem Beispiel müssen Sie zuerst den Sachbearbeiter und dann den Manager einfügen. Wenn Sie wie bei Constraints mehrere Zeileneinfügevorgänge ausführen, werden Assertions nach der Ausführung jeder INSERT-Anweisung validiert. Sie können diese Assertion auf DEFERRABLE setzen, sodass Sie zuerst den Manager und dann den Sachbearbeiter einfügen können. Die Validierung erfolgt anstelle jeder INSERT-Anweisung in der Datei COMMIT.

CREATE ASSERTION manager_has_clerk check
(NOT EXISTS
  (SELECT 'manager without clerk'
   FROM   employees m
   WHERE  m.job_id = 'ST_MAN'
     AND  NOT EXISTS
            (SELECT 'a clerk in same department'
             FROM   employees c
             WHERE  c.job_id = 'ST_CLERK'
               AND  c.department_id = m.department_id)));