-- PUE-Zettel 4, Aufgabe 1.1
CREATE TABLE Konto (
KontoID INTEGER PRIMARY KEY,
Name TEXT ,
EMail VARCHAR(50) NOT NULL UNIQUE, -- Bindestrich mit `E-Mail`
Punkte INTEGER ,
CHECK(Punkte BETWEEN 0 AND 999)
);
CREATE TABLE Eintrag (
PostID INTEGER PRIMARY KEY,
Autor INTEGER ,
Datum DATE ,
Anonym BOOLEAN ,
FOREIGN KEY (Autor) REFERENCES Konto(KontoID) ON DELETE SET NULL
);
CREATE TABLE Kommentar (
KontoID INTEGER ,
Zeit TIMESTAMP ,
Text TEXT ,
PostID INTEGER ,
PRIMARY KEY (KontoID, Zeit, PostID), -- Zusammengesetzter PK
FOREIGN KEY (KontoID) REFERENCES Konto(KontoID) ON DELETE CASCADE,
FOREIGN KEY (PostID) REFERENCES Eintrag(PostID) ON DELETE CASCADE,
CONSTRAINT Text_Length CHECK(LENGTH(Text) <= 1024) -- Benennung (optional)
);
-- PUE-Zettel 4, Aufgabe 1.1 (Einträge)
INSERT INTO Konto VALUES
(1000, "Viola F.", "viola@mail.com", 30),
(1001, "Natan S.", "natan@mail.com", 10),
(1002, "Oscar H.", "oscar@mail.com", 30),
(1003, "Klara W.", "klara@mail.com", 70);
INSERT INTO Eintrag VALUES
(10, 1002, "2005-11-13", 1),
(11, 1000, "2005-11-17", 0),
(12, 1001, "2005-11-21", 0),
(13, 1002, "2005-11-25", 1);
INSERT INTO Kommentar VALUES
(1000, "2005-11-13 11:45:00.000", "A?", 10),
(1001, "2005-11-13 12:15:00.000", "A!", 10),
(1002, "2005-11-17 11:15:00.000", "B?", 11),
(1003, "2005-11-25 12:45:00.000", "C!", 13);
-- PRAGMA foreign_keys=OFF;
DELETE FROM Konto Where KontoID = 1000;
SELECT * FROM Konto;
SELECT * FROM Eintrag;
SELECT * FROM Kommentar;
Telegram: DB developers channel