Nei database relazionali professionali sono disponibili le cosiddette
Foreign keys che consentono di legare dati di due tabelle
diverse tra di loro permettendo di mantenere l'integrità
delle tabelle del database implicate: in pratica si impedisce di
cancellare dei dati di una tabella che possiedono una relazione per cui
invaliderebbero dei dati in un'altra.
Pensiamo ad esempio ad un database formato da una tabella editori ed una
libri collegate tra di loro con relazione uno a molti. Cancellando un
record della tabella editori ci troveremmo con alcuni record della
tabella libri a puntare ad editori non più presenti nel database
danneggiando l'integrità del database. Questo può portare teoricamente
ad estrarre meno dati di quelli previsti con una query.
MySQL permette di utilizzare questa funzione per mezzo dello storage
engine InnoDB.
InnoDB storage engine
L'engine InnoDB fornisce, oltre alle Foreign keys, un sistema di
transazioni con rollback e possibilità di recupero a seguito di
un crash, un sistema di locking ben funzionante, prestazioni elevate con
tabelle con una grande mole di dati, la possibilità di creare tabelle di
ogni dimensione anche con filesystem con limiti nella dimensione dei
file.
Dalla versione 4.0 di MySQL questo storage engine è attivo di default
quindi se avete a disposizione una versione superiore sul vostro spazio
(ad oggi la MySQL AB rilascia come versione consigliata la 4.1)
probabilmente potrete creare delle tabelle di questo tipo. Per le
versioni precedenti è invece necessario compilare il supporto specifico.
Non è necessario che tutte le tabelle del database siano di tipo InnoDB,
basta che lo siano le tabelle che dovranno fare uso delle funzioni
garantite solo da questo storage engine.
Per creare una tabella di tipo InnoDB è sufficiente specificarlo nella
definizione della stessa in questo modo:
CREATE TABLE tabella (campoa INT, campob CHAR (20)) TYPE=InnoDB;
Foreign keys
Le Foreign keys sono implementabili se:
- entrambe le tabelle siano di tipo InnoDB
- entrambe le tabelle abbiano un indice in cui i campi interessati siano
indicati come primo campo - non siano inclusi campi di tipo BLOB o TEXT nelle chiavi usate
Vediamo qualche esempio che invaliderebbe l'uso delle Foreign keys.
Esempi non permessi
Una delle due tabelle non è InnoDB
In presenza di due tabelle così definite non è possibile creare una relazione esterna:
CREATE TABLE tabella_a (campoa INT, campob CHAR (20)) TYPE=InnoDB; CREATE TABLE tabella_b (campoa INT, campob CHAR (20)) TYPE=MyISAM;
Per risolvere il problema è necessario che anche tabella_b sia definito come InnoDB.
Non sono presenti due indici con i campi interessati come primo campo
In presenza di due tabelle così definite non è possibile creare una relazione esterna:
CREATE TABLE tabella_a (campoa INT auto_increment, campob CHAR (20), PRIMARY KEY campo (campoa) ) TYPE=InnoDB; CREATE TABLE tabella_b (campoa_taba INT, campob CHAR (20)) TYPE=InnoDB;
Per risolvere il problema è necessario aggiungere a tabella_b ad esempio un indice tipo KEY campo (campoa_taba).
Sono presenti tabelle text in un indice
In presenza di due tabelle così definite non è possibile creare una relazione esterna:
CREATE TABLE tabella_a (campoa INT auto_increment, campob CHAR (20), PRIMARY KEY campo (campoa) ) TYPE=InnoDB; CREATE TABLE tabella_b (campoa_taba INT, campob TEXT, KEY campo (campob, campoa_taba)) TYPE=InnoDB;
Per risolvere il problema è necessario eliminare campob dall'indice di tabella_b.
Come funzionano le Foreign key
Le Foreign key aiutano l'utente a stabilire i comportamenti che il database assumerà nel momento in cui si tenta di eliminare o modificare un record di una tabella padre legato ancora ad uno o più record nella tabella figlio. Per fissare le idee, nell'esempio del database dei libri la tabella editore agirebbe come tabella padre, mentre la tabella libro agirebbe come tabella figlia.
È stabilita una relazione uno a molti, ovvero per un editore abbiamo più libri. Ma in più possiamo definire quello che viene chiamato un "vincolo di partecipazione". La domanda che ci poniamo è: se cancelliamo un editore, ha senso tenere tutti i suoi libri nel database? Non creeremmo delle istanze orfane? Insomma le chiavi esterne ci offrono la possibilità di gestire eventuali rischi di perdita di consistenza del database.
La tabella padre e la tabella figlia possono anche essere la stessa nel caso in cui si generi una relazione interna alla tabella (caso ad esempio di categorie comprendenti categorie figlie).
Possiamo definire in teoria cinque azioni diverse da far attivare in caso di cancellazione o modifica:
- CASCADE
- SET NULL
- NO ACTION o RESTRICT
- SET DEFAULT
Come osserviamo nel proseguio dell'articolo, in pratica queste azioni si riducono sostanzialmente a tre.
Esempi di azioni sulle tabelle
Vediamo di chiarire queste possibilità con l'aiuto del database di esempio descritto ad inizio articolo. Immaginiamo che ci sia un legame esterno tra le due tabelle per mezzo dei campi editore.id e libro.editore, in questo caso è stato specificato NO ACTION, nel codice, ma in seguito immaginiamo di sostituirlo con quelli presenti sotto.
CREATE TABLE editore ( id INT auto_increment, nome VARCHAR (200), PRIMARY KEY id (id)) TYPE=InnoDB; INSERT INTO editore (id, nome) VALUES (1, 'Mondadori'), (2, 'Feltrinelli'), (3, 'Apogeo'), (4, 'Tecniche nuove');
CREATE TABLE libro (
id INT auto_increment,
titolo VARCHAR (200),
editore INT NOT NULL,
PRIMARY KEY id (id),
INDEX editore_key (editore),
FOREIGN KEY (editore) REFERENCES editore(id)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
TYPE=InnoDB;
INSERT INTO libro (titolo, editore)
VALUES ('Di noi tre', 1), ('Due di due', 1),
('Il diario di Bolivia', 2), ('Il tropico del cancro', 2),
('HTML 4 tutto e oltre', 3), ('MySQL guida completa', 3),
('Imparare PHP in 24 ore', 4), ('Comunità sul web', 4);
CASCADE
In questo caso la cancellazione o modifica di un record nella tabella padre genererà la cancellazione o la modifica dei record collegati nella tabella figlia. Se cancelliamo un editore cancelleremo tutti i libri collegati, se modifichiamo un editore cambiando l'id ad esempio sostituiremo il valore del campo editore anche nella tabella libri.
Questa query
DELETE FROM editore WHERE nome = 'Apogeo';
implicitamente eseguirà la seguente
DELETE FROM libro WHERE editore = 3;
e quindi la tabella libro risulterà così:
id | titolo | editore 1 | Di noi tre | 1 2 | Due di due | 1 3 | Il diario di Bolivia | 2 4 | Il tropico del cancro | 2 7 | Imparare PHP in 24 ore | 4 8 | Comunità sul web | 4
SET NULL
Questa azione è attivabile solo se il campo interessato della tabella figlia non è impostato a NOT NULL. Come si può intuire in caso di eliminazione o modifica di un record nella tabella padre i record collegati della tabella figlia verranno modificati impostando il campo NULL.
Questa query
DELETE FROM editore WHERE nome = 'Apogeo';
modificherà la tabella come segue:
id | titolo | editore 1 | Di noi tre | 1 2 | Due di due | 1 3 | Il diario di Bolivia | 2 4 | Il tropico del cancro | 2 5 | HTML 4 tutto e oltre | 3 6 | MySQL guida completa | 3 7 | Imparare PHP in 24 ore | NULL 8 | Comunità sul web | NULL
NO ACTION o RESTRICT
In questo caso si potrebbe pensare che il record della tabella padre venga cancellato o modificato senza che cambi niente. Queste due azioni invece impediscono direttamente la modifica o la cancellazione dei record della tabella padre. Praticamente specificare queste due azioni equivale a non inserire alcuna azione.
SET DEFAULT
Questa azione benché supportata dallo standard SQL genera un errore sulla definizione della tabella quindi non è utilizzabile, al momento, con MySQL.
