Oracle

LEZIONI - CHIAVI ESTERNE E INTEGRITÀ REFERENZIALE

Constraint FOREIGN KEY

Questo vincolo di integità referenziale consente di stabilire relazioni fra le tabelle. Quanti hanno progettato un database con Microsoft Access, implementato un paio di tabelle e una relazione fra queste, possono riconoscere nelle figure seguenti una semplice relazione.

Figura 1. Descrizione delle tabelle "Padre" e "Figlio"

Figura 2. Relazione tra le tabelle "Padre" e "Figlio"

Nelle figure sono mostrate due tabelle, Padre e Figlio, e una relazione "1 a molti" stabilita fra queste. La relazione creata stabilisce che un oggetto (riga) della tabella Padre può essere collegato a 0 o più oggetti (righe) nella tabella Figlio, mentre un oggetto della tabella Figlio può appartenere solamente ad un oggetto della tabella Padre.

In altre parole questo si traduce in:

• un padre può avere 0 o più figli;
• un figlio appartiene sicuramente ad un solo padre.

Vediamo ora la sintassi da seguire per creare un constraint di chiave esterna (foreign key):

La tabella e le colonne referenziate (osserviamo la clausola REFERENCES) sono quelle con cui viene costruita la relazione (nell'esempio di sopra è il lato 1 della relazione).

Quando viene eliminata una riga dalla tabella Padre non deve ovviamente più esistere una corrispondenza nella tabella Figlio: nel nostro database non può esistere un figlio se è stato eliminato il corrispondente padre (anche se questo può accadere nella vita reale). È vera anche la situazione opposta: può esistere invece un padre senza alcun figlio.

Se cancelliamo un oggetto dalla tabella Padre allora Oracle deve cancellare automaticamente tutti gli oggetti associati nella tabella Figlio. La clausola ON DELETE CASCADE serve appunto a tale scopo.

La clausola ON DELETE SET NULL memorizza invece il valore NULL nella colonna di chiave esterna: ogni qualvolta cancelliamo una riga nella tabella "Padre" Oracle imposta automaticamente il valore NULL nella colonna "IDPadre" della tabella "Figlio", per tutti gli oggetti Figlio collegati al Padre eliminato.

Per esempio riproduciamo lo schema mostrato nelle figure, ma usando Oracle pittosto che Access.

SQL> CREATE TABLE Padre
  2  (
  3     IDPadre NUMBER (6) CONSTRAINT pk_Padre_ID PRIMARY KEY,
  4     Nominativo VARCHAR2 (20) CONSTRAINT nn_Padre_Nom NOT NULL,
  5     DataNascita DATE
  6  );

SQL> CREATE TABLE Figlio
  2  (
  3     IDFiglio NUMBER (6) CONSTRAINT pk_Figlio_ID PRIMARY KEY,
  4     Nome VARCHAR2 (20) CONSTRAINT nn_Figlio_Nome NOT NULL,
  5     DataNascita DATE,
  6     IDPadre NUMBER (6) CONSTRAINT fk_Figlio_IDPadre
  7 REFERENCES Padre (IDPadre)
  8   ON DELETE CASCADE
  9  );

Inseriamo due oggetti nella tabella Padre.

SQL> INSERT INTO Padre VALUES (1, 'Verdaschi Mario', TO_DATE ('01/11/1945', 'DD-MM-YYYY'));
SQL> INSERT INTO Padre VALUES (2, 'Rossi Bruno', TO_DATE ('24/09/1964', 'DD-MM-YYYY'));

Associamo ai due padri inseriti, due figli ciascuno.

SQL> INSERT INTO Figlio VALUES (1, 'Carmelo', TO_DATE ('31/01/1985', 'DD-MM-YYYY'), 1);	
SQL> INSERT INTO Figlio VALUES (2, 'Francesca', TO_DATE ('31/01/1985', 'DD-MM-YYYY'), 1);
SQL> INSERT INTO Figlio VALUES (3, 'Claudio', TO_DATE ('25/07/1983', 'DD-MM-YYYY'), 2);
SQL> INSERT INTO Figlio VALUES (4, 'Luigi', TO_DATE ('27/02/1986', 'DD-MM-YYYY'), 2);

Visualizziamo il contenuto delle tabelle Padre e Figlio.

SQL> SELECT * FROM padre;
SQL> SELECT * FROM figlio;

Abbiamo ora l'esigenza di conoscere i figli di "Verdaschi Mario" che ha IDPadre=1 e di "Rossi Bruno" che ha IDPadre=2.

SQL> SELECT * FROM Figlio WHERE IDPadre = 1;
SQL> SELECT * FROM Figlio WHERE IDPadre = 2;

Cancelliamo ora la riga del padre 'Rossi Bruno'.

SQL> DELETE FROM Padre WHERE IDPadre = 2;

Proviamo a vedere cosa ne è dei figli dell'oggetto 'Rossi Bruno', che abbiamo appena eliminato.

SQL> SELECT * FROM Figlio WHERE IDPadre = 2;