Kap 6.7 Integritätsbedingungen in SQL Arten von Integritätsbedingungen • Schlüssel und Eindeutigkeit • Fremdschlüssel, referentielle Integrität • Typen von Attributen, aber niedrige Semantik, z.B. Tel# = Zimmer# ?? • Bereiche von Attributwerten R kr S kr sei s ∈ S, dann muß sein oder s.kr = null s.kr ∈ πkr R 1 Diese Bedingung heißt referentielle Integrität und wird vom DBS automatisch überwacht. πkr S ⊆ πkr R =: KR Änderungs-Bedingungen: 1. insert into S values s 2. ändere s.kr zu s.kr' 3. verändern von r.kr 4. löschen von r mit r.kr s.kr ∈ πkr R s.kr' ∈ πkr R ¬∃ s∈S: s.kr = r.kr ¬∃ s∈S: s.kr = r.kr Hinweis: durch Schlüssel-Fremdschlüssel Beziehungen entstehen automatisch Integritätsbedingungen! 2 Schlüssel-Vereinbarung create table R ( kr: integer primary key …) Fremdschlüssel-Vereinbarung create table S ( ... m: integer references R ) m bezieht sich automatisch auf primary key von R 3 Beispiel create table Student ( Matr# integer primary key, Name varchar (25), Semester tinyint ) create table prüft ( Prof varchar (25), Matr# integer references Student, Fach: varchar (17) ) 4 nach Kemper/Eickler: Kaskadierung create table S (..., α integer references R on update cascade); S S R α κ κ1 κ1 κ2 κ2 update R set κ=κ’1 where κ=κ1; R α κ‘1 κ κ‘1 κ2 κ2 vorher nachher create table S (...,α integer references R on delete cascade); S R α κ1 κ κ1 κ2 κ2 S R α κ2 delete from R where κ=κ1; κ κ2 nachher vorher 5 create table S (..., α integer references R on update set null); S R α κ1 κ1 κ2 κ2 R S κ update R set κ=κ’1 where κ=κ1; α κ --κ2 κ‘1 vorher κ2 nachher create table S (...,α integer references R on delete set null); S R α κ1 κ κ1 κ2 α κ2 S delete from R where κ=κ1; R α --κ2 κ κ2 nachher vorher 6 delete from Professoren where Name = ‚Sokrates’; Logik Sokrates Schopenhauer M äeutik Ethik R ussel Theophrastos Erkenntnistheorie W issenschaftstheorie C arnap Bioethik create table Vorlesungen ( ..., gelesenVon integer references Professoren on delete cascade); create table hören ( ..., VorlNr integer references Vorlesungen on delete cascade); 7 create table Studenten ( MatrNr integer primary key, Name varchar(30) not null, Semester integer check Semester between 1 and 13), create table Professoren ( PersNr integer primary key, Name varchar(30) not null, Rang character(2) check (Rang in (`C2´,`C3´,`C4´)), Raum integer unique ); 8 create table Assistenten ( PersNr integer primary key, Name varchar(30) not null, Fachgebiet varchar(30), Boss integer, foreign key (Boss) references Professoren on delete set null); create table Vorlesungen ( VorlNr integer primary key, Titel varchar(30), SWS integer, gelesen Von integer references Professoren on delete set null); 9 create table hören ( MatrNr integer references Studenten on delete cascade, VorlNr integer references Vorlesungen on delete cascade, primary key (MatrNr, VorlNr)); create table voraussetzen ( Vorgänger integer references Vorlesungen on delete cascade, Nachfolger integer references Vorlesungen on delete cascade, primary key (Vorgänger, Nachfolger)); 10 create table prüfen ( MatrNr integer references Studenten on delete cascade, VorlNr integer references Vorlesungen, PersNr integer references Professoren on delete set null, Note numeric (2,1) check (Note between 0.7 and 5.0), primary key (MatrNr, VorlNr)); 11 create table prüfen ( MatrNr integer references Studenten on delete cascade, VorlNr integer references Vorlesungen, PersNr integer references Professoren on delete set null, Note numeric (2,1) check (Note between 0.7 and 5.0), primary key (MatrNr, VorlNr)); constraint VorherHören check (exists( select * from hören h where h.VorlNr = prüfen.VorlNr and h.Matr.Nr = prüfen.MatrNr)) ); 12