Caratteristiche dei vincoli di chiave esterna:
Di seguito sono illustrate alcune importanti caratteristiche del vincolo di chiave esterna.
- Il tipo di dati della chiave esterna utilizzata nella tabella figlio deve essere lo stesso con il tipo di dati della chiave primaria utilizzata nella tabella padre per fare riferimento alla chiave esterna.
- È possibile fare riferimento a qualsiasi colonna di indice o più colonne come chiave esterna solo per la tabella InnoDB.
- Per creare una chiave esterna sono necessari i privilegi di riferimento o almeno uno dei privilegi delle istruzioni SELECT, INSERT, UPDATE e DELETE.
- Una chiave esterna può essere creata in due modi. Uno utilizzando l'istruzione CREATE e un altro utilizzando l'istruzione ALTER.
Prerequisito:
Prima di creare un vincolo di chiave esterna, è necessario creare un database e una tabella padre con la chiave primaria. Supponiamo che il nome del database sia ' biblioteca ' e contiene due tabelle padre denominate ' libri ' e ' mutuatario '. Effettuare una connessione con il server MySQL utilizzando il mysql client ed eseguire le seguenti istruzioni SQL per creare il database e le tabelle.
CREARE BANCA DATI biblioteca;
UTILIZZO biblioteca;
CREARE TAVOLO libri(
ID INT NON NULLO INCREMENTO AUTOMATICO ,
titolo varchar (cinquanta) NON NULLO ,
autore varchar (cinquanta) NON NULLO ,
editore varchar (cinquanta) NON NULLO ,
CHIAVE PRIMARIA (ID)
) MOTORE = INNODB ;
CREARE TAVOLO mutuatari(
ID VARCHAR (cinquanta) NON NULLO ,
nome varchar (cinquanta) NON NULLO ,
indirizzo varchar (cinquanta) NON NULLO ,
e-mail varchar (cinquanta) NON NULLO ,
CHIAVE PRIMARIA (ID)
) MOTORE = INNODB ;
Definire il vincolo di chiave esterna utilizzando l'istruzione CREATE
Crea una tabella denominata ' book_borrow_info ' con vincoli di chiave esterna eseguendo la seguente istruzione. qui, il id_libro il campo è un chiave esterna per questa tabella e ogni valore di questo campo deve esistere nel ID campo di libri tavolo. libri è la tabella genitore e book_borrow_info è la tabella figlio. Anche qui vengono impostate due restrizioni con la chiave esterna. Questi sono ELIMINA CASCATA e AGGIORNAMENTO CASCATA . Ciò significa che se una chiave primaria verrà rimossa o aggiornata dalla tabella padre, i record corrispondenti relativi alla tabella figlio relativi alla chiave esterna verranno rimossi o la chiave esterna verrà aggiornata.
CREARE TAVOLO book_borrow_info(
id_prestito VARCHAR (cinquanta),
id_libro INT ,
prestito_data DATA NON NULLO ,
data di ritorno DATA NON NULLO ,
stato VARCHAR (quindici) NON NULLO ,
INDICE par_ind(id_libro),
CHIAVE PRIMARIA (id_prestito,prestito_data),
CHIAVE ESTERA (id_libro) RIFERIMENTI libri(ID)
SU ELIMINA CASCATA
SU AGGIORNARE CASCATA
) MOTORE = INNODB ;
Ora, esegui le seguenti istruzioni SQL per inserire alcuni record in entrambe le tabelle. La prima istruzione INSERT inserirà quattro record in libri tavolo. I quattro valori di ID campo di libri table sarà 1, 2, 3 e 4 per l'attributo di incremento automatico. La seconda istruzione INSERT inserirà quattro record in book_borrow_info basato sul ID valore di libri tavolo.
INSERIRE IN libri VALORI
( NULLO , 'Uccidere un Mockingbird', 'Harper Lee', 'Grand Central Publishing'),
( NULLO , 'Cent'anni di solitudine', 'Garcia Marquez', 'Lutfi Ozkok'),
( NULLO , 'Un passaggio in India', 'Forster, E.M.', 'Libreria di immagini BBC Hulton'),
( NULLO , 'Uomo invisibile', 'Ralph Ellison', 'Enciclopedia Britannica, Inc.');
INSERIRE IN book_borrow_info VALORI
('123490', 1, '2020-02-15', '2020-02-25', 'Restituito'),
('157643', 2, '2020-03-31', '2020-03-10', 'In attesa di'),
('174562', 4, '2020-04-04', '2020-04-24', 'Preso in prestito'),
('146788', 3, '2020-04-10', '2020-01-20', 'Preso in prestito');
Se si tenta di inserire un valore nel campo chiave esterna della tabella figlio che non esiste nel campo chiave primaria della tabella padre, MySQL genererà un errore. La seguente istruzione SQL genererà un errore perché la tabella padre, libri non contiene alcun valore ID 10 .
INSERIRE IN book_borrow_info VALORI('195684', 10, '2020-04-15', '2020-04-30', 'Restituito');
Dopo aver eseguito la seguente istruzione DELETE, quando il quarto record verrà rimosso dal libri tabella quindi i relativi record dal book_borrow_info tabella verrà rimossa automaticamente per il vincolo di chiave esterna.
ELIMINA A PARTIRE DAL libri DOVE ID= 4;SELEZIONARE * a partire dal libri;
SELEZIONARE * a partire dal book_borrow_info;
Definire il vincolo di chiave esterna utilizzando l'istruzione ALTER
All'inizio, inserisci alcuni record in mutuatari tabella e questa tabella sarà definita come tabella padre nella prossima ETÀ dichiarazione.
INSERIRE IN mutuatari VALORI('123490', 'Patrick Wood', '34 West Street LANCASTER LA14 9ZH', '[e-mail protetta]'),
('157643', 'Ezra Martin', '10 The Grove BIRMINGHAM B98 1EU', '[e-mail protetta]'),
('174562', 'John Innes Archie', '55 Strada principale LIVERPOOL L2 3OD', '[e-mail protetta]'),
('146788', 'Federico Hanson', '85 Highfield Road SHREWSBURY SY46 3ME', '[e-mail protetta]');
Esegui quanto segue ETÀ istruzione per impostare un altro vincolo di chiave esterna per book_borrow_info tavolo con cui fare il rapporto mutuatari tavolo. Qui, id_prestito è definita come una chiave esterna per book_borrow_info tavolo.
ALTER TABLE book_borrow_info AGGIUNGI VINCOLO fk_borrowerCHIAVE ESTERA(id_prestito)REFERENZE mutuatari(ID)IN ELIMINA CASCATA IN AGGIORNAMENTO LIMITA;
Ora inserisci un record in book_borrow_info con valido id_prestito valore che esiste in ID campo di mutuatari tavolo. 157643 value esiste nella tabella dei mutuatari e la seguente istruzione INSERT verrà eseguita correttamente.
INSERIRE IN book_borrow_info VALORI('157643', 1, '2020-03-10', '2020-03-20', 'Restituito');
La seguente istruzione INSERT genererà un messaggio di errore perché il valore id 195680 non esiste nella tabella dei mutuatari.
INSERIRE IN book_borrow_info VALORI('195680', 1, '2020-04-15', '2020-04-30', 'Restituito');
Conclusione:
Definire correttamente i vincoli di chiave esterna è un compito molto importante per creare un database relazionale e gestire i dati tra le tabelle in modo appropriato. Conoscere gli usi dei vincoli di chiave esterna è molto essenziale per i progettisti di database. Spero che questo articolo aiuti i nuovi progettisti di database a comprendere il concetto di vincoli di chiave esterna e ad applicarli correttamente nelle loro attività.