Esercitazione MySQL - Foreign Key

C:\.....\bin>mysql -u root -p
Enter password:******
Welcome to the MySQL monitor....
....
....
....
mysql> create database scuola;
Query OK, 1 row affected (0.00 sec)

mysql> use scuola;
Database changed

mysql> create table classe (idc integer not null auto_increment primary key, ann
o integer not null, sezione char(1) not null, descrizione varchar(25) not null);

Query OK, 0 rows affected (0.22 sec)

mysql> insert into classe (anno,sezione,descrizione) values (1,'a','informatica');
Query OK, 1 row affected (0.03 sec)

mysql> insert into classe (anno,sezione,descrizione) values (1,'b','informatica');
Query OK, 1 row affected (0.02 sec)

mysql> insert into classe (anno,sezione,descrizione) values (1,'c','informatica');
Query OK, 1 row affected (0.01 sec)

mysql> insert into classe (anno,sezione,descrizione) values (1,'a','chimica');
Query OK, 1 row affected (0.02 sec)

mysql> insert into classe (anno,sezione,descrizione) values (1,'b','chimica');
Query OK, 1 row affected (0.03 sec)

mysql> insert into classe (anno,sezione,descrizione) values (1,'c','chimica');
Query OK, 1 row affected (0.03 sec)

mysql> select * from classe;
+-----+------+---------+-------------+
| idc | anno | sezione | descrizione |
+-----+------+---------+-------------+
|   1 |    1 | a       | informatica |
|   2 |    1 | b       | informatica |
|   3 |    1 | c       | informatica |
|   4 |    1 | a       | chimica     |
|   5 |    1 | b       | chimica     |
|   6 |    1 | c       | chimica     |
+-----+------+---------+-------------+
6 rows in set (0.00 sec)

mysql> create table studente (idce integer, foreign key(idce) references classe(idc) on delete cascade, ids integer not null auto_increment primary key, nome varchar(25) not null, cognome varchar(25) not null, citta varchar(25) not null);
Query OK, 0 rows affected (0.19 sec)

mysql> insert into studente (idce,nome,cognome,citta) values (1,'giacomo','leopardi','recanati');
Query OK, 1 row affected (0.02 sec)

mysql> insert into studente (idce,nome,cognome,citta) values (1,'mario','rossi','milano');
Query OK, 1 row affected (0.02 sec)

mysql> insert into studente (idce,nome,cognome,citta) values (1,'giovanni','brambilla','bologna');
Query OK, 1 row affected (0.02 sec)

mysql> insert into studente (idce,nome,cognome,citta) values (4,'massimo','bianchi','milano');
Query OK, 1 row affected (0.02 sec)

mysql> insert into studente (idce,nome,cognome,citta) values (4,'gianluca','basso','pavia');
Query OK, 1 row affected (0.03 sec)

mysql> insert into studente (idce,nome,cognome,citta) values (4,'davide','mallemaro','torino');
Query OK, 1 row affected (0.03 sec)

mysql> insert into studente (idce,nome,cognome,citta) values (2,'giuseppe','astri','bolzano');
Query OK, 1 row affected (0.02 sec)

mysql> insert into studente (idce,nome,cognome,citta) values (3,'luca','bonfanti','aosta');
Query OK, 1 row affected (0.04 sec)

mysql> insert into studente (idce,nome,cognome,citta) values (5,'susanna','castaldi','napoli');
Query OK, 1 row affected (0.04 sec)

mysql> insert into studente (idce,nome,cognome,citta) values (6,'maria','soprano','taranto');
Query OK, 1 row affected (0.04 sec)

mysql> select * from studente;
+------+-----+----------+-----------+----------+
| idce | ids | nome     | cognome   | citta    |
+------+-----+----------+-----------+----------+
|    1 |   1 | giacomo  | leopardi  | recanati |
|    1 |   2 | mario    | rossi     | milano   |
|    1 |   3 | giovanni | brambilla | bologna  |
|    4 |   4 | massimo  | bianchi   | milano   |
|    4 |   5 | gianluca | basso     | pavia    |
|    4 |   6 | davide   | mallemaro | torino   |
|    2 |   7 | giuseppe | astri     | bolzano  |
|    3 |   8 | luca     | bonfanti  | aosta    |
|    5 |   9 | susanna  | castaldi  | napoli   |
|    6 |  10 | maria    | soprano   | taranto  |
+------+-----+----------+-----------+----------+
10 rows in set (0.00 sec)

mysql> select * from classe;
+-----+------+---------+-------------+
| idc | anno | sezione | descrizione |
+-----+------+---------+-------------+
|   1 |    1 | a       | informatica |
|   2 |    1 | b       | informatica |
|   3 |    1 | c       | informatica |
|   4 |    1 | a       | chimica     |
|   5 |    1 | b       | chimica     |
|   6 |    1 | c       | chimica     |
+-----+------+---------+-------------+
6 rows in set (0.00 sec)

mysql> delete from classe where idc=1;
QUERY OK, 1 row affected (0.02 sec)

mysql> select * from classe;
+-----+------+---------+-------------+
| idc | anno | sezione | descrizione |
+-----+------+---------+-------------+
|     |      |         |             |
|   2 |    1 | b       | informatica |
|   3 |    1 | c       | informatica |
|   4 |    1 | a       | chimica     |
|   5 |    1 | b       | chimica     |
|   6 |    1 | c       | chimica     |
+-----+------+---------+-------------+
5 rows in set (0.00 sec)

mysql> select * from studente;
+------+-----+----------+-----------+----------+
| idce | ids | nome     | cognome   | citta    |
+------+-----+----------+-----------+----------+
|      |     |          |           |          |
|    4 |   4 | massimo  | bianchi   | milano   |
|    4 |   5 | gianluca | basso     | pavia    |
|    4 |   6 | davide   | mallemaro | torino   |
|    2 |   7 | giuseppe | astri     | bolzano  |
|    3 |   8 | luca     | bonfanti  | aosta    |
|    5 |   9 | susanna  | castaldi  | napoli   |
|    6 |  10 | maria    | soprano   | taranto  |
+------+-----+----------+-----------+----------+
10 rows in set (0.00 sec)

I vincoli di integrità referenziale di propagazione consentono di definire le operazioni eseguite da SQL Server quando un utente tenta di eliminare o aggiornare una chiave alla quale fa riferimento una chiave esterna.

Le clausole REFERENCES delle istruzioni CREATE TABLE e ALTER TABLE supportano le clausole ON DELETE e ON UPDATE. Le operazioni di propagazione possono anche essere definite utilizzando la finestra di dialogo Relazioni chiavi esterne:

NO ACTION è il valore predefinito se non viene specificata ON DELETE o ON UPDATE.

ON DELETE NO ACTION: Specifica che se si tenta di eliminare una riga contenente una chiave a cui fanno riferimento chiavi esterne in righe esistenti in altre tabelle, verrà generato un errore e verrà eseguito il rollback dell'istruzione DELETE.

ON UPDATE NO ACTION: Specifica che se si tenta di aggiornare un valore di chiave in una riga e alla chiave fanno riferimento chiavi esterne in righe esistenti in altre tabelle, verrà generato un errore e verrà eseguito il rollback dell'istruzione UPDATE.

Con CASCADE, SET NULL e SET DEFAULT, le eliminazioni o gli aggiornamenti di valori di chiave possono influire sulle tabelle con relazioni di chiave esterna tracciabili alla tabella in cui è stata eseguita la modifica. Se le operazioni referenziali di propagazione sono state definite anche nelle tabelle di destinazione, tali operazioni saranno inoltre valide per le righe eliminate o aggiornate. 

ON DELETE CASCADE: Specifica che se si tenta di eliminare una riga contenente una chiave a cui fanno riferimento chiavi esterne in righe esistenti in altre tabelle, verranno inoltre eliminate tutte le righe contenenti tali chiavi esterne.

ON UPDATE CASCADE: Specifica che se si tenta di aggiornare un valore di chiave in una riga e a tale valore fanno riferimento chiavi esterne in righe esistenti in altre tabelle, tutti i valori che compongono la chiave esterna verranno anch'essi aggiornati al nuovo valore specificato per la chiave.

Fonte: Esercizio pratico in laboratorio e "https://technet.microsoft.com/it-it/library/ms186973(v=sql.105).aspx"