Nessun risultato. Prova con un altro termine.
Guide
Notizie
Software
Tutorial

Foreign keys: creare relazioni tra tabelle MySQL

Come gestire le relazioni tra i dati di due tabelle diverse in MySQL 4 con le Foreign keys.
Come gestire le relazioni tra i dati di due tabelle diverse in MySQL 4 con le Foreign keys.
Link copiato negli appunti

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.


Ti consigliamo anche