• Primary Key
  • Erstellen eines Primary Key
  • Löschen eines Primary Key
  
  
• Foreign Key
• CHECK
  • Löschen eines CHECK Key
  
  
• UNIQUE
  • Erstellen
  • Löschen
  
  
• NOT NULL
• Constraintangabe bei Tabellenerstellung
• CONSTRAINT Aktivierung
• CONSTRAINT Deaktivierung
• Übungen 08

Constraints

Primary Key

Erstellen eines Primary Key

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

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

Löschen eines Primary Key

ALTER TABLE tabellenname DROP CONSTRAINT Constraintname;

ALTER TABLE employees DROP CONSTRAINT empl_pk;

Foreign Key

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

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)
;

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 (abt) nicht gibt, so führt dies zu einer Verletzung des Foreign Key Constraint und es erfolgt folgende

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

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

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 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.

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

delete from dept where deptno = 30;

select * from emp where deptno = 30;

CHECK

ALTER TABLE tabellenname 
  ADD CONSTRAINT Constraintname CHECK (bedingung)
;

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

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

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

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.

Löschen eines CHECK Key

ALTER  TABLE tabellenname DROP CONSTRAINT Constraintname;

ALTER  TABLE employees DROP CONSTRAINT empl_CK1;

UNIQUE

Erstellen

Bei UNIQUE wird automatisch ein INDEX erstellt.

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

alter table dept add constraint u1 unique (dname);

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

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

Löschen

ALTER TABLE tabellenname DROP CONSTRAINT Constraintname;

ALTER TABLE employees DROP CONSTRAINT empl_UQ1;

NOT NULL

ALTER TABLE tabellenname MODIFY spalte NOT NULL;

alter table dept modify dname not null;

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

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

Stefan Hietel dama.go GmbH, Robert Warnke http://rowa.giso.de