DROP TABLE Elokuva
;

DROP TABLE Lajityyppi
;

CREATE TABLE Lajityyppi (
LajiID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
Lajinimi VARCHAR(100) NOT NULL
)
;

CREATE TABLE Elokuva (
ElokuvaID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
Elokuvanimi VARCHAR(100) NOT NULL,
Lajityyppi_LajiID INTEGER NOT NULL
)
;


-- Koska SQLite ei toteuta viite-eheyksiä toteutetaan ne itse
-- triggereiden avulla
CREATE TRIGGER fki_elokuva_lajityyppi_id
BEFORE INSERT ON Elokuva
FOR EACH ROW BEGIN 
  SELECT CASE
     WHEN ((SELECT LajiID FROM Lajityyppi WHERE LajiID = NEW.Lajityyppi_LajiID) IS NULL)
     THEN RAISE(ABORT, 'INSERT on table "Elokuva" violates foreign key constraint "fki_elokuva_lajityyppi_id"')
  END;
END;

CREATE TRIGGER fku_elokuva_lajityyppi_id
BEFORE UPDATE ON Elokuva
FOR EACH ROW BEGIN 
  SELECT CASE
     WHEN ((SELECT LajiID FROM Lajityyppi WHERE LajiID = NEW.Lajityyppi_LajiID) IS NULL)
     THEN RAISE(ABORT, 'UPDATE on table "Elokuva" violates foreign key constraint "fku_elokuva_lajityyppi_id"')
  END;
END;

CREATE TRIGGER fkd_lajityyppi_elokuva_id
BEFORE DELETE ON Lajityyppi
FOR EACH ROW BEGIN 
  SELECT CASE
     WHEN ((SELECT Lajityyppi_LajiID FROM Elokuva WHERE Lajityyppi_LajiID = OLD.LajiID) IS NOT NULL)
     THEN RAISE(ABORT, 'DELETE on table "Lajityyppi" violates foreign key constraint "fkd_lajityyppi_elokuva_id"')
  END;
END;


INSERT INTO Lajityyppi (LajiID, Lajinimi) VALUES (1, 'Draama' );
INSERT INTO Lajityyppi (LajiID, Lajinimi) VALUES (2, 'Toiminta' );
INSERT INTO Lajityyppi (LajiID, Lajinimi) VALUES (3, 'Komedia' );

INSERT INTO Elokuva (ElokuvaID, Elokuvanimi, Lajityyppi_LajiID) VALUES (1, 'Da Vinci Code', 1);
INSERT INTO Elokuva (ElokuvaID, Elokuvanimi, Lajityyppi_LajiID) VALUES (2, 'Terminator', 2);
INSERT INTO Elokuva (ElokuvaID, Elokuvanimi, Lajityyppi_LajiID) VALUES (3, 'Dead Poets Society', 1);
INSERT INTO Elokuva (ElokuvaID, Elokuvanimi, Lajityyppi_LajiID) VALUES (4, 'Forrest Gump', 1);
INSERT INTO Elokuva (ElokuvaID, Elokuvanimi, Lajityyppi_LajiID) VALUES (5, 'Monty Python and the Holy Grail', 3);
INSERT INTO Elokuva (ElokuvaID, Elokuvanimi, Lajityyppi_LajiID) VALUES (6, 'Siivoton Juttu', 3);
INSERT INTO Elokuva (ElokuvaID, Elokuvanimi, Lajityyppi_LajiID) VALUES (7, 'Volver - Paluu', 1);