CREATE TABLE professor (ID Integer,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Office VARCHAR(50),
PRIMARY KEY (ID));
CREATE TABLE student (ID Integer,
FirstName VARCHAR(50),
LastName VARCHAR(50),
PRIMARY KEY (ID));
CREATE TABLE lecture (ID Integer,
Title VARCHAR(50),
Credits Integer,
Room VARCHAR(50),
PRIMARY KEY (ID));
CREATE TABLE attends (Student Integer,
Lecture Integer,
FOREIGN KEY (Student) REFERENCES student(ID),
FOREIGN KEY (Lecture) REFERENCES lecture(ID),
PRIMARY KEY (Student, Lecture));
CREATE TABLE teaches (Professor Integer,
Lecture Integer,
FOREIGN KEY (Professor) REFERENCES professor(ID),
FOREIGN KEY (Lecture) REFERENCES lecture(ID),
PRIMARY KEY (Professor, Lecture));
INSERT INTO professor (ID, FirstName, LastName, Office)
VALUES (1, 'Julia', 'Maier', 'G12.42');
INSERT INTO lecture (ID, Title, Credits, Room)
VALUES (1, 'Datenbanken', 6, 'HS10');
INSERT INTO student (ID, FirstName, LastName)
VALUES (1, 'Max', 'Mustermann');
INSERT INTO teaches (Professor, Lecture)
VALUES (1, 1);
INSERT INTO attends (Student, Lecture)
VALUES (1, 1);
-- Contraint Violation (Default NO ACTION)
DELETE FROM lecture WHERE ID = 1;
DELETE FROM professor WHERE ID = 1;
DELETE FROM student WHERE ID = 1;
-- No Contraint Violation (Default NO ACTION)
DELETE FROM attends WHERE Student = 1;
DELETE FROM student WHERE ID = 1;
-- Change attends table to CASCADE (CAREFUL!)
ALTER TABLE attends DROP CONSTRAINT attends_student_fkey;
ALTER TABLE attends ADD CONSTRAINT attends_student_fkey
FOREIGN KEY (Student) REFERENCES student(ID)
ON DELETE CASCADE ON UPDATE CASCADE;
-- Update/DELETE (CASCADE)
UPDATE student SET ID=2 WHERE FirstName = 'Max' and LastName='Mustermann';
DELETE FROM student WHERE ID = 2;