Wie oft habe ich schon nach der Syntax gesucht um nachträglich einen Constraint auf eine Tabelle zu legen. Google ist ja da immer eine gute Hilfe, aber in deutsch gab es bisher wenig.
Das ändert sich nun, wir beschätigen uns mit dem Thema Constraints hier nun.
Nachträglich einen Not Null Constraints auf eine Tabelle legen:
ALTER TABLE emp MODIFY (ename VARCHAR2(10) CONSTRAINT emp_ename_nn NOT NULL);
Constraint wieder löschen:
ALTER TABLE emp DROP CONSTRAINT emp_ename_nn;
Nachträglich einen Primärschlüssel anlegen (Achtung es darf keiner bisher existieren):
ALTER TABLE emp ADD CONSTRAINT pk_emp PRIMARY KEY(empno);
Constraint wieder löschen:
ALTER TABLE emp DROP CONSTRAINT pk_emp;
Nachträglich einen Unique Index anlegen (davon dürfen Sie mehrere auf der gleichen Tabelle besitzen)
Hier erlauben wir pro Tag nur einen Mitarbeiter mit gleichem Nachname einzustellen.
ALTER TABLE emp ADD CONSTRAINT emp_hiredate_ename_uk UNIQUE KEY(hiredate,ename);
Constraint wieder löschen:
ALTER TABLE emp DROP CONSTRAINT emp_hiredate_ename_uk;
Foreign Key Constraint anlegen. (Dieser hat einen Bezug zu einer lokalen Spalte der Tabelle und eine Verbindung zu einer Spalte einer anderen Tabelle)
ALTER TABLE emp ADD CONSTRAINT fk_deptno FOREIGN KEY (deptno)
REFERENCES dept(deptno);
Der Foreign Key Constraint hat aber noch eine weitere nette Option: On Delete Cascade
Wenn hier ein Hauptdatensatz (bei uns z.B. die Abteilung) gelöscht wird, werden die Kinddatensätze (hier die Mitarbeiter der Abteilung) auch rekursiv gelöscht
ALTER TABLE emp ADD CONSTRAINT fk_deptno FOREIGN KEY (deptno)
REFERENCES dept(deptno) ON DELETE CASCADE;
Alternativ kann auch nur die abhängige Spalte auf NULL gesetzt werden
ALTER TABLE emp ADD CONSTRAINT fk_deptno FOREIGN KEY (deptno)
REFERENCES dept(deptno) ON DELETE SET NULL;
Constraint wieder löschen:
ALTER TABLE emp DROP CONSTRAINT fk_deptno;
Sonderfälle
Constraints können auch verzögert geprüft werden. Das ist dann sinvoll, wenn im Datenmodell mit Constraints Änderungen durchgeführt werden sollen, die gegen die Regeln des Constraint verstossen würden.
Durch die verzögerte Prüfung erfolgt dies erst beim Commit und nicht schon während der Transaktion.
ALTER TABLE emp ADD CONSTRAINT fk_deptno FOREIGN KEY (deptno)
REFERENCES dept(deptno) DEFERRABLE INITIALLY IMMEDIATE;
Bei der Option DEFERRABLE ENABLE NOVALIDATE wird bei einem Unique Index ein Non-Unique Index angelegt.
Sollte ein Index auf den benötigten Spalten bereits vorhanden sein, wird dieser verwendet.
Danach kann die verzögerte Prüfung in der Session aktiviert werden:
ALTER SESSION SET CONSTRAINTS = DEFERRED;
oder wieder zurück auf Default:
ALTER SESSION SET CONSTRAINTS = IMMEDIATE;
Mit einem ALTER TABLE kann der Zustand der verzögerten Constraints verändert werden:
ENABLE VALIDATE entspricht dem ENABLE. Der Constraint wird für alle Zeilen geprüftENABLE NOVALIDATE hier werden nur neue Zeilen geprüft, bereits existierende Zeilen können gegen den Constraint verstossen:
Oracle muss hier einen Non-Unique Index anlegen, weil ja evtl Doubletten bereits vorhanden sind.DISABLE NOVALIDATE entspricht dem Zustand DISABLE. Der Constraint wird nicht geprüft und damit kann es Daten geben, die gegen die Constraint Regel verstossen.
Bei Unique Constraints wird der dazugehörige Index gelöschtDISABLE VALIDATE bedeutet, dass der Constraint nicht geprüft wird und verbietet Änderungen an den betroffenen Spalten. Sinnvoll bei einem Exchange Parition Vorgng im DWH.
Bei Unique Constraints wird der dazugehörige Index gelöscht