Come usare i vincoli chiave stranieri mysql

Il vincolo di chiave esterna viene utilizzato per fare una relazione tra due tabelle nel database MySQL. È una caratteristica molto importante di MySQL impostare diversi tipi di restrizioni sulle tabelle correlate. Per definire qualsiasi vincolo di chiave esterna per qualsiasi tabella è necessario utilizzare la chiave primaria di un'altra tabella. La chiave primaria è una chiave univoca per una tabella per identificare la riga particolare nella tabella e quando questa chiave primaria viene utilizzata in un'altra tabella per fare una relazione one-to-one o one-to-many o molti a molti è chiamato chiave straniera. Le caratteristiche delle chiavi straniere e il modo in cui queste chiavi possono essere utilizzate nelle tabelle MySQL sono mostrate in questo articolo.

Caratteristiche dei vincoli chiave stranieri:

Di seguito sono spiegate alcune importanti caratteristiche del vincolo di chiave straniera.

• 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 principale per fare riferimento alla chiave esterna.
• Qualsiasi colonna di indice o più colonne può essere indicata come una chiave esterna solo per la tabella innodb.
• RIFERIMENTI I privilegi o almeno uno dei privilegi delle dichiarazioni di selezione, inserire, aggiornare ed eliminare.
• Una chiave straniera può essere creata in due modi. Uno utilizzando l'istruzione Crea e un altro utilizzando l'istruzione Alter.

Prerequisito:

Prima di creare un vincolo di chiave esterna, è necessario creare un database e una tabella principale con la chiave primaria. Supponiamo che il nome del database sia 'biblioteca'E contiene due tabelle principali denominate'libri' E 'mutuatario'. Fare una connessione con MySQL Server utilizzando il mysql client ed esegui le seguenti istruzioni SQL per creare il database e le tabelle.

Crea libreria di database;
Usa la libreria;
Crea libri da tavolo (
id int non null auto_increment,
Titolo Varchar (50) non null,
autore varchar (50) non null,
editore varchar (50) non null,
Chiave primaria (ID)
) Motore = innodB;
Crea mutuatari di tabella (
id varchar (50) non null,
Nome varchar (50) non null,
Indirizzo Varchar (50) non null,
Email Varchar (50) non NULL,
Chiave primaria (ID)
) Motore = innodB;

Definire il vincolo di chiave estera usando la dichiarazione Crea

Crea una tabella chiamata 'book_borrow_info'Con vincoli chiave estere eseguendo la seguente dichiarazione. Qui, il Book_id Il campo è un Chiave straniera per questa tabella e ogni valore di questo campo deve esistere nel id campo di libri tavolo. libri è la tabella dei genitori e book_borrow_info è il tavolo dei bambini. Qui sono anche impostate due restrizioni con la chiave estera. Questi sono Elimina Cascade E Aggiorna Cascade. Ciò significa che se una chiave primaria rimuoverà o si aggiornerà dalla tabella principale, i record corrispondenti relativi alla tabella dei figli relativi alla chiave esterna verranno rimossi o la chiave esterna verrà aggiornata.

Crea table book_borrow_info (
Borrow_id Varchar (50),
Book_id int,
Data di Borrow_Date non null,
return_date data non null,
status varchar (15) non null,
INDICE par_ind (book_id),
Chiave primaria (Borrow_id, Borrow_Date),
Foreign Key (Book_id) Riferimenti Libri (ID)
Su Elimina Cascade
Su aggiornamento cascade
) 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 La tabella 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.

Inserisci i valori dei libri
(Null, "Kill a Mockingbird", "Harper Lee", "Grand Central Publishing"),
(NULL, "Cento anni di solitudine", "Garcia Marquez", "Lutfi Ozkok"),
(Null, "un passaggio in India", "Forster, E.M.',' BBC Hulton Picture Library '),
(Null, "Invisible Man", "Ralph Ellison", "Encyclopædia Britannica, Inc.');
Inserisci i valori Book_Borrow_info
("123490", 1, "2020-02-15", "2020-02-25", "restituito"),
("157643", 2, "2020-03-31", "2020-03-10", "in sospeso"),
("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 della chiave esterna della tabella figlio che non esiste nel campo chiave primario della tabella principale, MySQL genererà un errore. La seguente istruzione SQL genererà un errore perché la tabella principale, libri non contiene alcun valore ID 10.

Inserisci i valori Book_Borrow_info
("195684", 10, "2020-04-15", "2020-04-30", "restituito");

Dopo aver eseguito la seguente istruzione ELETE, quando il quarto record verrà rimosso dal libri tabella quindi i record correlati dal book_borrow_info La tabella verrà rimossa automaticamente per il vincolo della chiave esterna.

Elimina dai libri in cui id = 4;
Seleziona * dai libri;
Seleziona * da book_borrow_info;

Definire il vincolo di chiave estera usando la dichiarazione alter

All'inizio, inserisci alcuni record in mutuatari La tabella e questa tabella verranno definite come tabella genitore nella prossima Altera dichiarazione.

Inserire i valori dei mutuatari
('123490', 'Patrick Wood', '34 West Street Lancaster LA14 9ZH ',' [email protected] '),
('157643', 'Ezra Martin', '10 The Grove Birmingham B98 1eu ',' [email protected] '),
('174562', 'John Innes Archie', '55 Main Road Liverpool L2 3od ',' [email protected] '),
;.com ');

Eseguire quanto segue Altera Dichiarazione per impostare un altro vincolo a chiave esterna per book_borrow_info tavolo con cui fare la relazione con mutuatari tavolo. Qui, Borrow_id è definito come una chiave estera per book_borrow_info tavolo.

Alter table book_borrow_info aggiungi vincolo fk_borrower
Chiave straniera (Borrow_id) Riferimenti Mutuars (ID) su Elimina Cascade su Aggiornamento Limite;

Ora, inserisci un record in book_borrow_info con valido Borrow_id valore che esiste in id campo di mutuatari tavolo. 157643 Il valore esiste nella tabella dei mutuatari e la seguente dichiarazione di insert verrà eseguita correttamente.

Inserisci i valori Book_Borrow_info
("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.

Inserisci i valori Book_Borrow_info
("195680", 1, "2020-04-15", "2020-04-30", "restituito");

Conclusione:

La definizione corretta dei vincoli chiave estere è un compito molto importante per la creazione di un database relazionale e gestire i dati tra le tabelle in modo appropriato. Conoscere gli usi dei vincoli chiave stranieri è molto essenziale per i progettisti di database. Spero che questo articolo aiuti i nuovi progettisti di database a comprendere il concetto di vincoli chiave stranieri e ad applicarli correttamente nelle loro attività.