/* \i 'H:\\Linguaggi_1\\PostgreSQL\\Prove3_IntegriRefere.sql' */


/* INSERISCE VALORI CON VINCOLI DI INTEGRITA` REFERENZIALE. */

DROP DATABASE IF EXISTS prove3; /* Cancellazione della versione precedente del db */

CREATE DATABASE prove3; /* Creazione del db */

/* Connessione al db */
\c prove3


\qecho ''
/* Creazione della prima tabella (principale) */
CREATE TABLE TP (
    NomeTP VARCHAR(20) PRIMARY KEY
);

/* Inserimenti nella tabella principale */
INSERT INTO TP VALUES ('CS');
INSERT INTO TP VALUES ('NL');
INSERT INTO TP VALUES ('DF');
INSERT INTO TP VALUES ('NA');
INSERT INTO TP VALUES ('YY');
INSERT INTO TP VALUES ('XX');

\qecho ''
SELECT *
FROM TP
ORDER BY NomeTP;


/* Creazione della seconda tabella, vincolata alla prima, che definisce i vincoli mediante la sintassi specifica per un unico attributo. */
CREATE TABLE TV (
    InteTV INTEGER PRIMARY KEY,
    Casca VARCHAR(20) NOT NULL REFERENCES TP(NomeTP) ON UPDATE CASCADE ON DELETE CASCADE,
    Nulla VARCHAR(20) /* deve essere opzionale */ REFERENCES TP(NomeTP) ON UPDATE SET NULL ON DELETE SET NULL,
    Defau VARCHAR(20) NOT NULL DEFAULT 'VD' REFERENCES TP(NomeTP) ON UPDATE SET DEFAULT ON DELETE SET DEFAULT,
    NoActio VARCHAR(20) NOT NULL REFERENCES TP(NomeTP) ON UPDATE NO ACTION ON DELETE NO ACTION
);

/* Inserimenti nella seconda tabella */
INSERT INTO TV VALUES (10, 'CS', 'YY', 'YY', 'YY');
INSERT INTO TV VALUES (20, 'YY', 'NL', 'YY', 'YY');
INSERT INTO TV VALUES (30, 'YY', 'YY', 'DF', 'YY');
INSERT INTO TV VALUES (40, 'YY', 'YY', 'YY', 'NA');
INSERT INTO TV VALUES (50, 'CS', 'YY', 'YY', 'YY');
INSERT INTO TV VALUES (60, 'NE', 'YY', 'YY', 'NA'); /* ERRORE: L'inserimento e` rifiutato;
infatti, TV.Casca viola il vincolo di chiave esterna perche' 'NE' non compare in TP.NomeTp. */

\qecho ''
SELECT *
FROM TV
ORDER BY InteTV;


/* Creazione di una terza tabella, vincolata alla seconda */
CREATE TABLE TV2 (
    CharTV2 CHAR(2) PRIMARY KEY,
    InteTv INTEGER,
    FOREIGN KEY (InteTv) REFERENCES TV(InteTv) ON DELETE CASCADE ON UPDATE CASCADE
);

/* Inserimenti nella terza tabella */
INSERT INTO TV2 VALUES ('AA', 10);
INSERT INTO TV2 VALUES ('BB', 10);
INSERT INTO TV2 VALUES ('CC', 20);

\qecho ''
SELECT *
FROM TV2
ORDER BY InteTV;

/* Aggiornamenti nella prima tabella */
UPDATE TP SET NomeTP = 'CS2' WHERE NomeTP = 'CS';

UPDATE TP SET NomeTP = 'NL2' WHERE NomeTP = 'NL';

UPDATE TP SET NomeTP = 'DF2' WHERE NomeTP = 'DF'; /* ERRORE: L'aggiornamento e` rifiutato;
infatti, TV.Defau viola il vincolo di chiave esterna perche' il suo valore di default 'VD' non compare in TP */

UPDATE TP SET NomeTP = 'NA2' WHERE NomeTP = 'NA'; /* ERRORE: L'aggiornamento e` rifiutato;
infatti, il valore 'NA' compare in TV2.NoActio e quindi 'NA' in TP non puo` essere aggiornato con 'NA2'. */

\qecho ''
\qecho 'TV dopo l''aggiornamento in TP: da CS a CS2, da NL a NL2, da DF a DF2, da NA a NA2'
SELECT *
FROM TV
ORDER BY InteTV;

DELETE FROM TV WHERE NoActio = 'NA';

UPDATE TP SET NomeTP = 'NA2' WHERE NomeTP = 'NA';  /* Adesso l'aggiornamento e` effettuato;
infatti, il valore 'NA' non compare piu` in TV2 e quindi 'NA' in TP puo` essere aggiornato con 'NA2'. */

\qecho ''
\qecho 'TV dopo l''aggiornamento in TP da NA a NA2'
SELECT *
FROM TV
ORDER BY InteTV;


DELETE FROM TP WHERE NomeTP = 'CS2';

\qecho 'TV dopo la cancellazione di CS2 in TP:'
SELECT *
FROM TV
ORDER BY InteTV;

\qecho 'TV2 dopo la cancellazione di CS2 in TP:'
SELECT *
FROM TV2
ORDER BY InteTV;


/* Creazione della quarta tabella (principale). */
CREATE TABLE TP4 (
    IdeTP4 INTEGER PRIMARY KEY,
    CC1 CHAR(2) UNIQUE,
    CC2_1 INTEGER,
    CC2_2 CHAR(2),
    UNIQUE (CC2_1, CC2_2)
);

/* Creazione della quinta tabella, vincolata alla quarta , che definisce i vincoli mediante la sintassi generale. */
CREATE TABLE TV5 (
    InteTV5 INTEGER,
    CC1 CHAR(2),
    CC2_1 INTEGER,
    CC2_2 CHAR(2),
    PRIMARY KEY(InteTV5),
    FOREIGN KEY (CC1) REFERENCES TP4(CC1),
    FOREIGN KEY (CC2_1, CC2_2) REFERENCES TP4(CC2_1, CC2_2)
);