Constraints

Primary Key

Erstellen eines Primary Key

Jede Tabelle besitzt eine Spalte oder zumindest eine Spaltenkombination, die eindeutig ist. Das bedeutet, mit dieser Spaltenkombination ist es möglich, jeden Datensatz der Tabelle eindeutig von den anderen Datensätzen abzugrenzen. Diese Spalte bzw. Spaltenkombination wird als Primärschlüssel bezeichnet und darf nicht doppelt innerhalb einer Tabelle auftauchen. Der Primärschlüssel darf nicht NULL sein. Bei einem zusammengesetzten Primärschlüssel darf keine der am Primärschlüssel beteiligten Spalten NULL-Werte beinhalten.

Im Zuge des Erstellens eines Primärschlüssels wird automatisch von Oracle auf diese Spalte bzw. Spaltenkombination ein eindeutiger Index erstellt.

alter table emp add constraint PK1 primary key (empno);

In der Tabelle Mitarbeiter (emp) wird ein neuer Primärschlüssel erstellt, der die Spalte Mitarbeiternummer (empno) beinhaltet. Dies ist natürlich nur dann möglich, wenn es noch keinen Primärschlüssel in dieser Tabelle gibt

Löschen eines Primary Key

ALTER TABLE tabellenname DROP CONSTRAINT Constraintname;

ALTER TABLE employees DROP CONSTRAINT empl_pk;

Foreign Key

Der Foreign Key Constraint ist ein Constraint unter SQL, dessen Aufgabe darin besteht, die referentielle Integrität von zwei Tabellen sicherzustellen.

ALTER TABLE tabellenname 
  ADD CONSTRAINT constraintname 
  FOREIGN KEY (spalte1, spalte2 ...) 
  REFERENCES tabellenname(spalte1, spalte2 ...)
;

Nehmen wir hierfür das Beispiel der beiden Tabellen Mitarbeiter (emp) und Abteilung (dept). Die beiden Tabellen stehen in einer 1:n Beziehung zueinander. Eine Abteilung besteht aus mehreren Mitarbeitern bzw. kann aus mehreren Mitarbeitern bestehen. Ein Mitarbeiter wiederum ist in genau einer Abteilung.

Die Verbindung der beiden Tabellen wird über die in beiden Tabellen vorkommende Spalte Abteilungsnummer (deptno) hergestellt. Ein Mitarbeiter, der in einer Abteilung arbeitet, die es gar nicht gibt, macht keinen Sinn. Daher dürfen in der Tabelle Mitarbeiter nur Abteilungsnummern stehen, die auch in der Tabelle Abteilung auftauchen.

Weiterhin können nur Abteilungen gelöscht werden, in denen keine Mitarbeiter mehr arbeiten. Wäre dies nämlich möglich, hätten wir wieder das Problem, dass es Mitarbeiter gibt, die in Abteilungen arbeiten, die es gar nicht mehr gibt. Oracle bietet dennoch eine Löschmöglichkeit für die Tabelle Abteilung. Beim kaskadierenden Löschen werden alle Mitarbeiter, die in der gelöschten Abteilung arbeiteten, mitgelöscht. Die eben beschriebenen Merkmale der referentiellen Integrität werden erst durchgesetzt, nachdem ein Foreign Key Constraint erstellt wurde.

alter table emp add constraint FK1 
  foreign key (deptno) references dept(deptno)
;

Ein Foreign Key Constraint wird immer in der Tabelle der n-Seite erstellt und referenziert auf die entsprechende Spalte der 1-Seite. Die Spalte, auf die referenziert wird, muss als UNIQUE und NOT NULL definiert sein. Ein Foreign Key Constraint kann auch eine Spaltenkombination beinhalten. Hierbei werden die in den Klammern eingeschlossenen Spalten durch ein Komma getrennt!

Wenn Sie diesen Foreign Key Constraint erstellt haben, so wird die referentielle Integrität zwischen den beiden Tabellen gewahrt bzw. erzwungen. Versuchen Sie, in die Tabelle Mitarbeiter (emp) einen Datensatz hinzuzufügen, der eine Abteilungsnummer besitzt, die es in der Tabelle Abteilung (dept) nicht gibt, so führt dies zu einer Verletzung des Foreign Key Constraint.

insert into emp (empno,ename,deptno) values(9999,'Mueller',50);
ORA-02291: 
Verstoß gegen Integritätsregel (SCOTT.FK1). 
Übergeordn. Schlüssel nicht gefunden

Wenn Sie versuchen, die Abteilung 30 zu löschen, wird auch hierbei die referentielle Integrität verletzt.

delete from dept where deptno=30;
ORA-02292: 
Verstoß gegen Integritätsregel (SCOTT.FK1). 
Untergeordneter Datensatz gefunden.

Hinweis: Beschreibungen zu den ORA-Fehlern können Sie mit dem Programm oerr erhalten:

host
oerr ora 02291
02291, 00000,"integrity constraint (%s.%s) violated - parent key not found"
// *Cause: A foreign key value has no matching primary key value.
// *Action: Delete the foreign key or add a matching primary key.
exit

Wenn Sie versuchen, die Abteilung 40 zu löschen, wird hierbei die referentielle Integrität nicht verletzt, da es keine Mitarbeiter gibt, die zur Abteilung 40 gehören.

Wenn Sie versuchen, die Abteilung 30 in Abteilung 45 umzubenennen, wird auch hierbei die referentielle Integrität verletzt.

update dept set deptno=45 where deptno=30;
ORA-02292: 
Verstoß gegen Integritätsregel (SCOTT.FK1). 
Untergeordneter Datensatz gefunden.

Zum Abschluss der Foreign Key Constraint wollen wir uns die Möglichkeit des kaskadierenden Löschens anschauen. Hierfür löschen wir den Constraint FK1 und erstellen diesen mit der Option ON DELETE CASCADE neu.

alter table emp drop constraint fk1;
alter table emp add 
  constraint fk1 
  foreign key (deptno) 
  references dept(deptno) ON delete cascade
;

Zuerst wird der Foreign Key Constraint FK1 gelöscht und anschließend mit der Option ON DELETE CASCADE neu erstellt. Sehen wir uns jetzt an, was im Falle des Löschens einer Abteilung passiert:

delete from dept where deptno = 30;

Eine Zeile wurde gelöscht. Trotz referentieller Integrität wurde eine Zeile (deptno = 30) aus der Tabelle Abteilung gelöscht. Des weiteren wurden alle Mitarbeier, die zur Abteilung 30 gehörten, ebenfalls gelöscht. Hiervon können Sie sich gern durch folgende Abfrage überzeugen.

select * from emp where deptno = 30;

CHECK

Mit einem Check-Constraint wird festgelegt, welche Bedingung bzw. Bedingungen jeder Datensatz der Tabelle erfüllen muss. Es kann je Tabelle mehrere Check-Constraints geben, die dann natürlich alle eingehalten werden müssen. Check-Constraints dienen zur Wahrung der Integrität der Daten und erweitern die durch Datentypen zur Verfügung gestellten Integritätsmöglichkeiten.

ALTER TABLE tabellenname 
  ADD CONSTRAINT Constraintname CHECK (bedingung)
;

Die Anwendung eines Check-Constraint wollen wir am Beispiel der Tabelle Mitarbeiter (emp) verdeutlichen. Wir nehmen an, dass die Spalte sal nicht größer als 100000 sein darf.

alter table emp add constraint ck1 check (sal < 100000);

Zur Tabelle emp wird ein Check-Constraint namens ck1 hinzugefügt. Dieser Check-Constraint überprüft, ob das Gehalt (sal) kleiner als 100000 ist. Ist dies nicht der Fall, erscheint folgende

insert into emp (empno,ename,sal) values(9999,'Mueller',110000);
FEHLER in Zeile 1: 
ORA-01438: 
Wert größer als angegebene Stellenzahl für diese Spalte zulässt

Unterabfragen sind in Check-Constraints nicht möglich:

alter table emp add constraint ck2 check 
  (sal<(select max(sal) from emp))
;
FEHLER in Zeile 1:
ORA-02251: 
Unterabfrage hier nicht zulässig

Hierdurch sollte erreicht werden, dass kein Gehalt eingefügt werden darf, welches größer als das bisher höchste Gehalt ist.

Durch einen Check-Constraint wird die gewünschte Einschränkung gewährleistet. Dies gilt selbstverständlich auch für etwaige Updates.

update emp set sal = 110000;
FEHLER in Zeile 1: 
ORA-01438: 
Wert größer als angegebene Stellenzahl für diese Spalte zulässt.

Das Gehalt aller Mitarbeiter soll auf 110000 erhöht werden. Dieser Wert ist größer als 100000 und verstößt demnach gegen den Check-Constraint.

Löschen eines CHECK KEY

ALTER  TABLE tabellenname DROP CONSTRAINT Constraintname;

ALTER  TABLE employees DROP CONSTRAINT empl_CK1;

UNIQUE

Erstellen

Durch einen Unique-Constraint können Sie sicherstellen, dass Werte in bestimmten Spalten bzw. Spaltenkombinationen eindeutig sind und bleiben. NULL-Werte sind hierbei in den beteiligten Spalten erlaubt.

Bei UNIQUE wird automatisch ein INDEX erstellt.

ALTER TABLE tabellenname 
  ADD CONSTRAINT Constraintname 
  UNIQUE (spalte1,spalte2...)
;

alter table dept add constraint u1 unique (dname);

Es wird ein Unique-Constraint über die Spalte dname erstellt. Es ist nun nicht mehr möglich, in der Tabelle dept zwei mal den gleichen Abteilungsnamen anzugeben. Wird dies versucht, erscheint ein entsprechender Fehler:

insert into dept (deptno, dname) values(50,'RESEARCH');
ORA-00001: 
Verstoß gegen Eindeutigkeit, Regel (SCOTT.U1)

Durch die INSERT-Anweisung wird versucht, in die Tabelle Abteilung eine Abteilungsnamen hinzuzufügen, den es bereits gibt. Dies wird durch den UNIQUE-Constraint verhindert.

update dept set dname='RESEARCH' where deptno=10;
ORA-00001: 
Verstoß gegen Eindeutigkeit, Regel (SCOTT.U1)

Durch die UPDATE-Anweisung wird versucht, in die Tabelle Abteilung einen Abteilungsnamen auf einen anderen Abteilungsnamen zu ändern, den es bereits gibt. Dies wird durch den Unique-Constraint verhindert.

Löschen

ALTER TABLE tabellenname DROP CONSTRAINT Constraintname;

ALTER TABLE employees DROP CONSTRAINT empl_UQ1;

NOT NULL

Durch den NOT NULL Constraint wird verhindert, dass in den entsprechenden Spalten NULL-Werte enthalten sein dürfen. Nur möglich, wenn keine bestehenden Daten gegen NOT NULL verstossen.

ALTER TABLE tabellenname MODIFY spalte NOT NULL;

alter table dept modify dname not null;

Durch den NOT NULL Constraint wird erreicht, dass in der Spalte dname etwas eingetragen werden muss. Wird gegen diese Regel verstoßen, erscheint eine

insert into dept (deptno) values (60);
ORA-01400: 
Einfügen von NULL in ("SCOTT"."DEPT"."DNAME") nicht möglich.

Durch die Insert-Anweisung wird versucht, einen Datensatz ohne Abteilungsname hinzuzufügen

Constraintangabe bei Tabellenerstellung

create table OfficialCar
  (
   CarNr int constraint PK1 PRIMARY KEY,
   CarName varchar2(50) constraint  nn NOT NULL,
   VIN int constraint UQ1 UNIQUE,
   color varchar2(20) constraint ck1 check (color in('Black','White')),
   deptno int constraint fk1 references scott.dept(deptno)
  )
;

CONSTRAINT Aktivierung

alter table OfficialCar enable constraint uq1;

CONSTRAINT Deaktivierung

alter table OfficialCar disable constraint uq1;

Übungen 08

Übungen siehe Seite .