basics mysql foreign key constraint with examples
Questo tutorial spiega le basi di MySQL FOREIGN KEY Constraint come la sua sintassi, come aggiungerlo, dichiararlo, rilasciarlo e modificarlo con esempi:
In termini molto semplici, la FOREIGN KEY viene utilizzata per collegare due o più tabelle in MySQL.
Le tabelle MySQL devono essere collegate per interrogare e aggiornare vari tipi di dati in diversi momenti. Quindi, è imperativo avere un punto di collegamento tra 2 tabelle.
In questo tutorial, discuteremo i diversi usi delle chiavi esterne e come possono essere dichiarate e modificate e quali vincoli ha sulla struttura complessiva della tabella.
Cosa imparerai:
CHIAVE ESTERA MySQL
Sintassi:
CONSTRAINT {constraintName} FOREIGN KEY (referringColumnName) REFERENCES {referredTable}({referredColumn}) ON UPDATE {reference-option} ON DELETE {reference-option}
Sopra è la sintassi utilizzata quando si specifica FOREIGN KEY su una tabella durante la creazione della tabella o con Istruzione ALTER TABLE.
Comprendiamo i diversi componenti della sintassi:
- constrantName: Questo è il nome simbolico che vogliamo definire per il vincolo FK specificato. Se viene saltato, il motore MySQL assegna automaticamente un nome al vincolo FK.
- referringColumnName: Questa è la colonna che farebbe riferimento ai valori in un'altra tabella come specificato dalla colonna nella tabella di riferimento.
- Tabella referenziata / tabella padre: Si riferisce al nome della tabella da cui farebbero riferimento i valori.
- Colonna referenziata: Il nome della colonna nella tabella di riferimento.
- Opzione di riferimento: Queste sono le azioni che entrano in scena quando viene eseguita un'azione di aggiornamento o eliminazione sulla tabella che contiene il vincolo di chiave esterna. Sia UPDATE che DELETE possono avere le stesse o diverse opzioni di riferimento.
Apprenderemo le diverse azioni di integrità referenziale più avanti in questo tutorial.
Vediamo un esempio di riferimento a FOREIGN KEY utilizzando l'esempio Employee / Department. Creeremo una tabella Department con colonne: departmentId (int & PRIMARY KEY) e departmentName (varchar).
CREATE TABLE department(departmentId INT PRIMARY KEY NOT NULL, departmentName VARCHAR(100));
Crea una tabella Employee con colonne come di seguito:
Colonna | genere |
---|---|
id | INT (chiave primaria) |
nome | VARCHAR |
dept_id | INT (chiave esterna) a cui si fa riferimento dalla tabella di reparto |
indirizzo | VARCHAR |
età | INT |
dob | DATA |
CREATE TABLE employee(id INT PRIMARY KEY NOT NULL, name VARCHAR(100), address VARCHAR(100), age INT, dob DATE, deptId INT, CONSTRAINT depIdFk FOREIGN KEY (deptId) REFERENCES department(departmentId) ON UPDATE CASCADE ON DELETE CASCADE);
Come puoi vedere, nella tabella Employee sopra, abbiamo dichiarato la colonna deptId di tipo Int e definito FOREIGN KEY dalla tabella Department sulla colonna departmentId.
Ciò significa essenzialmente che la colonna deptId nella tabella Employee può contenere solo valori che si trovano nella tabella Department.
Proviamo a inserire dati in queste tabelle e vediamo come funziona FOREIGN KEY CONSTRAINT.
- Creare prima un record nella tabella Reparto e aggiungere un record nella tabella Impiegato facendo riferimento all'ID del record che è stato aggiunto alla tabella Reparto.
INSERT INTO department VALUES (1, 'ENGINEERING') --------- INSERT INTO EMPLOYEE VALUES (1, 'AMIT KUMAR', 'MUMBAI', 32, '1988-02-12',1);
Vedrai che entrambe le istruzioni verranno eseguite senza errori.
- Ora fai riferimento a un valore per departmentId che non è esistente.
Per esempio, nell'istruzione di query seguente, stiamo creando un dipendente con un ID dipartimento -10 non esistente
INSERT INTO EMPLOYEE VALUES (1, 'DARREN JOHNSON', 'CHICAGO', 32, '1988-02-12',10);
- In questo scenario, riceveremo un errore come di seguito:
Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`my_sql_foreign_key`.`employee`, CONSTRAINT `depIdFk` FOREIGN KEY (`deptId`) REFERENCES `department` (`departmentId`) ON DELETE CASCADE ON UPDATE CASCADE)
Quindi, in generale, quando vengono definiti i riferimenti CHIAVE ESTERI, è importante assicurarsi che la tabella a cui si fa riferimento abbia i dati prima di essere referenziata.
Azioni di integrità referenziale
Proviamo prima a capire cos'è esattamente l'integrità referenziale.
L'integrità referenziale aiuta a mantenere i dati in uno stato pulito e coerente in cui sono presenti tabelle correlate tra loro con una relazione CHIAVE ESTERA.
In poche parole, l'integrità referenziale si riferisce all'azione che ci aspettiamo dal motore di database di intraprendere, quando si verifica un AGGIORNAMENTO o ELIMINAZIONE nella tabella di riferimento che contiene la CHIAVE ESTERA.
Per esempio, nel nostro esempio Dipendente / Reparto, supponiamo di modificare l'ID reparto per una determinata riga nel DB. Quindi tutte le righe di riferimento nella tabella Employee sarebbero interessate. Possiamo definire diversi tipi di scenari di integrità referenziale che potrebbero essere applicati in questi casi.
Nota: L'integrità referenziale viene definita durante l'impostazione / dichiarazione di FOREIGN KEY come parte dei comandi / sezioni ON DELETE e ON UPDATE.
Fare riferimento a una query di esempio qui (per l'esempio Dipendente / Dipartimento):
CREATE TABLE department(departmentId INT PRIMARY KEY NOT NULL, departmentName VARCHAR(100)); CREATE TABLE employee(id INT PRIMARY KEY NOT NULL, name VARCHAR(100), address VARCHAR(100), age INT, dob DATE, deptId INT, CONSTRAINT depIdFk FOREIGN KEY (deptId) REFERENCES department(departmentId) ON UPDATE {ref-integrity-action} ON DELETE {ref integrity action});
Inserisci alcuni dati in queste tabelle come di seguito:
INSERT INTO department VALUES (1, 'ENGINEERING'), (2,'ACCOUNTING'), (3, 'MARKETING'), (4, 'HR'), (5, 'LEGAL'); INSERT INTO EMPLOYEE VALUES (1, 'AMIT KUMAR', 'MUMBAI', 32,'1988-02-12',1), (2, 'RYAN HILMAN', 'SEATTLE',43, '1977-03-15',1), (3, 'KAVITA SINGH', 'DELHI', 42, '1978-02-18',4), (4, 'DAVID BECKHAM', 'LONDON', 40, '1980-07-13',3), (5, 'PRITI KUMARI', 'DELHI', 35, '1985-12-11',2), (6, 'FRANK BALDING', 'NEW YORK', 35, '1985-08-25',5)
Esistono 4 azioni di riferimento supportate da MySQL. Proviamo a capirli tutti.
# 1) CASCATA
Questa è una delle azioni di integrità referenziale più comunemente utilizzate. L'impostazione DELETE e UPDATE su CASCADE applicherebbe le modifiche apportate alla tabella di riferimento nella tabella di riferimento, ad esempio nell'esempio Dipendente / Dipartimento. Supponiamo che qualcuno elimini una riga nella tabella Department che deve dire department_name = ACCOUNTING, quindi verranno eliminate anche tutte le righe nella tabella Employee con department_id come quella della tabella Accounting.
Comprendiamo questo con un esempio:
SELECT * FROM employee;
id | nome | indirizzo | età | dob | deptId |
---|---|---|---|---|---|
1 | AMIT KUMAR | MUMBAI | 32 | 1988-02-12 | 1 |
Due | RYAN HILMAN | SEATTLE | 43 | 1977-03-15 | 1 |
3 | KAVITA SINGH | DELHI | 42 | 1978-02-18 | 4 |
4 | DAVID BECKHAM | LONDRA | 40 | 1980-07-13 | 3 |
5 | PRITI KUMARI | DELHI | 35 | 1985-12-11 | Due |
6 | FRANK BALDING | NEW YORK | 35 | 1985-08-25 | 5 |
Elimina il record dalla tabella Department dove departmentName = 'ACCOUNTING'
DELETE from DEPARTMENT WHERE departmentName='ACCOUNTING';
Ora, poiché si tratta di un'azione referenziale CASCADE, ci aspetteremmo che anche tutte le righe con departmentID = 2 (che è per il dipartimento 'ACCOUNTING') vengano eliminate. Eseguiamo di nuovo una query SELECT sulla tabella Employee.
SELECT * FROM employee;
id | nome | indirizzo | età | dob | deptId |
---|---|---|---|---|---|
1 | AMIT KUMAR | MUMBAI | 32 | 1988-02-12 | 1 |
Due | RYAN HILMAN | SEATTLE | 43 | 1977-03-15 | 1 |
3 | KAVITA SINGH | DELHI | 42 | 1978-02-18 | 4 |
4 | DAVID BECKHAM | LONDRA | 40 | 1980-07-13 | 3 |
6 | FRANK BALDING | NEW YORK | 35 | 1985-08-25 | 5 |
Come puoi vedere sopra, a causa dell'integrità referenziale di CASCADE, le righe nella tabella Employee che si riferivano alla colonna eliminata come FOREIGN KEY avranno quelle righe eliminate.
# 2) RESTRIZIONI / NESSUNA AZIONE
La modalità RESTRICT o NO ACTION non consentirà alcuna operazione UPDATE o DELETE sulla tabella con colonne a cui si fa riferimento come FOREIGN KEY in alcune tabelle.
La modalità NO ACTION può essere applicata semplicemente omettendo le clausole ON UPDATE e ON DELETE dalla dichiarazione della tabella.
Proviamo lo stesso esempio e in questo caso salta semplicemente l'azione ON UPDATE e ON DELETE Integrità referenziale.
Ora, quando proviamo a eliminare qualsiasi voce nella tabella di riferimento, otterremmo un errore poiché abbiamo impostato l'azione referenziale su RESTRICT
DELETE FROM department WHERE departmentName='ACCOUNTING';
Vedrai un errore simile a quello riportato di seguito se provi a eseguire il comando DELETE sopra.
Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`my_sql_foreign_key`.`employee`, CONSTRAINT `depIdFk` FOREIGN KEY (`deptId`) REFERENCES `department` (`departmentId`))
# 3) SET NULL
Con SET NULL, qualsiasi UPDATE o DELETE nella tabella di riferimento causerebbe l'aggiornamento di un valore NULL rispetto al valore di colonna contrassegnato come FOREIGN KEY nella tabella di riferimento.
Con questa azione di integrità referenziale, la definizione della tabella Employee diventerebbe la seguente:
CREATE TABLE employee(id INT PRIMARY KEY NOT NULL, name VARCHAR(100), address VARCHAR(100), age INT, dob DATE, deptId INT, CONSTRAINT depIdFk FOREIGN KEY (deptId) REFERENCES department(departmentId) ON DELETE SET NULL);
Elimina una riga nella tabella di riferimento come mostrato di seguito:
DELETE FROM department WHERE departmentName='ACCOUNTING';
Ora, in questo caso, il valore di riferimento nella tabella Employee sarebbe impostato su NULL. Eseguire una query SELECT sulla tabella Employee per vedere i risultati.
SELECT * FROM employee;
id | nome | indirizzo | età | dob | deptId |
---|---|---|---|---|---|
1 | AMIT KUMAR | MUMBAI | 32 | 1988-02-12 | 1 |
Due | RYAN HILMAN | SEATTLE | 43 | 1977-03-15 | 1 |
3 | KAVITA SINGH | DELHI | 42 | 1978-02-18 | 4 |
4 | DAVID BECKHAM | LONDRA | 40 | 1980-07-13 | 3 |
5 | PRITI KUMARI | DELHI | 35 | 1985-12-11 | NULLO |
6 | FRANK BALDING | NEW YORK | 35 | 1985-08-25 | 5 |
# 4) SET DEFAULT
La modalità SET DEFAULT quando specificata comporterebbe la sostituzione del valore predefinito per la colonna (come specificato durante la dichiarazione della colonna), nel caso in cui si faccia riferimento a qualsiasi DELETES nella tabella.
Nota - Come da Documentazione MySQL , l'opzione SET DEFAULT è supportata da MySQL Parser ma non dai motori DB come InnoDB. Questo potrebbe essere supportato in futuro.
Tuttavia, per supportare tale comportamento, è possibile considerare l'utilizzo di SET NULL e definire un trigger sulla tabella che potrebbe impostare un valore predefinito.
Aggiungere un vincolo FOREIGN KEY utilizzando l'istruzione ALTER TABLE
Molte volte può capitare di voler aggiungere un vincolo FOREIGN KEY a una tabella esistente che non lo possiede.
Supponiamo che nell'esempio Employee and Department, abbiamo creato una tabella Employee senza alcun vincolo FOREIGN KEY e successivamente vogliamo introdurre il vincolo. Ciò può essere ottenuto utilizzando il comando ALTER TABLE.
Proviamo a capirlo con un esempio.
Supponiamo di avere una tabella Employee con la definizione seguente per il comando CREATE.
CREATE TABLE employee(id INT PRIMARY KEY NOT NULL, name VARCHAR(100), address VARCHAR(100), age INT, dob DATE, deptId INT);
Qui abbiamo una colonna deptId ma nessun vincolo FOREIGN KEY. In questo caso, anche senza avere una tabella Reparto, possiamo specificare qualsiasi valore durante l'inserimento dei record.
Ora, supponiamo in seguito di avere una tabella Department separata e di volerci collegare departmentId come FOREIGN KEY alla tabella Employee.
ALTER TABLE employee ADD CONSTRAINT depIdFk FOREIGN KEY (deptId) REFERENCES department(departmentId) ON UPDATE CASCADE ON DELETE CASCADE;
E se questa tabella avesse dati esistenti? Possiamo ALTER tabella e aggiungere il vincolo FOREIGN KEY?
La risposta è sì - possiamo con la condizione che i valori esistenti nella colonna a cui verrà fatto riferimento da un'altra tabella debbano avere quei valori esistenti nella tabella genitore stessa.
Crea una tabella Employee senza vincolo FOREIGN KEY, aggiungi alcuni dati e prova ad aggiungere un vincolo FOREIGN KEY utilizzando il comando ALTER.
CREATE TABLE employee(id INT PRIMARY KEY NOT NULL, name VARCHAR(100), address VARCHAR(100), age INT, dob DATE, deptId INT);
INSERT INTO EMPLOYEE VALUES (1, 'DARREN JOHNSON', 'CHICAGO', 32, '1988-02-12',1); INSERT INTO EMPLOYEE VALUES (2, 'ANITA SHERWIN', 'COLUMBIA', 32, '1988-02-12',10);
Crea una tabella Department e aggiungi FOREIGN KEY nel campo 'deptId' nella tabella Employee come mostrato di seguito:
CREATE TABLE department(departmentId INT PRIMARY KEY NOT NULL DEFAULT 1000, departmentName VARCHAR(100));
A questo punto, se proviamo ad aggiungere il vincolo FOREIGN KEY,
ALTER TABLE employee ADD CONSTRAINT depIdFk FOREIGN KEY (deptId) REFERENCES department(departmentId) ON UPDATE CASCADE ON DELETE CASCADE;
Quindi otterremo un errore, poiché la tabella Employee contiene alcuni dati ma il vincolo di integrità referenziale non può essere soddisfatto poiché la tabella Department non ha ancora dati.
Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`my_sql_foreign_key`.`#sql-63_87`, CONSTRAINT `depIdFk` FOREIGN KEY (`deptId`) REFERENCES `department` (`departmentId`) ON DELETE CASCADE ON UPDATE CASCADE)
Per avere il vincolo FOREIGN KEY, dobbiamo prima aggiungere i dati alla tabella Department. Inseriamo i record richiesti nella tabella Department.
INSERT INTO department VALUES (1, 'ENGINEERING'),(10,'ACCOUNTING');
Aggiungere nuovamente il vincolo FOREIGN KEY eseguendo la stessa istruzione ALTER TABLE. Noterai che questa volta, il comando ha esito positivo e la tabella Employee viene aggiornata correttamente per avere deptId come FOREIGN KEY dalla tabella Department.
Eliminazione di un vincolo CHIAVE ESTERA
Simile all'aggiunta di un vincolo FOREIGN KEY, è anche possibile eliminare / eliminare un vincolo FOREIGN KEY esistente da una tabella.
Ciò può essere ottenuto utilizzando il comando ALTER TABLE.
Sintassi:
ALTER TABLE {childTable} DROP FOREIGN KEY {foreign key constraint name};
Qui 'childTable' è il nome della tabella che ha definito il vincolo FOREIGN KEY, mentre il 'nome del vincolo di chiave esterna' è il nome / simbolo utilizzato per definire la FOREIGN KEY.
Vediamo un esempio utilizzando la tabella Dipendente / Dipartimento. Per eliminare un vincolo denominato 'depIdFk' dalla tabella Employee, utilizza il comando seguente:
ALTER TABLE employee DROP FOREIGN KEY depIdFk;
Domande frequenti
D # 1) Come posso modificare le chiavi esterne in MySQL?
Risposta: FOREGIN KEY può essere aggiunto / rimosso utilizzando il comando ALTER TABLE.
Per modificare o aggiungere una nuova CHIAVE ESTERA, è possibile utilizzare il comando ALTER e definire la CHIAVE ESTERA e la colonna della tabella di riferimento a cui fare riferimento dalla tabella figlia.
D # 2) Come impostare più chiavi esterne in MySQL?
Risposta: Una tabella in MySQL può avere più CHIAVI ESTERE, che potrebbero dipendere dalla stessa tabella padre o da tabelle padre diverse.
Utilizziamo la tabella Employee / Department e aggiungiamo CHIAVE ESTERA per il nome del reparto e DepartmentId nella tabella Employee.
Fare riferimento alle istruzioni CREATE di entrambe le tabelle come di seguito
CREATE TABLE department(departmentId INT PRIMARY KEY NOT NULL DEFAULT 1000, departmentName VARCHAR(100) UNIQUE NOT NULL); ----xxxxx------xxxxx------xxxxx-------xxxxx------xxxxx CREATE TABLE employee(id INT PRIMARY KEY NOT NULL, name VARCHAR(100), address VARCHAR(100), age INT, dob DATE, deptId INT, depName VARCHAR(100), CONSTRAINT depIdFk FOREIGN KEY (deptId) REFERENCES department(departmentId) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT depNameFk FOREIGN KEY (depName) REFERENCES department(departmentName) ON UPDATE CASCADE ON DELETE CASCADE);
D # 3) Come disabilitare i vincoli di chiave esterna in MySQL?
Risposta: I vincoli FOREIGN KEY sono generalmente richiesti quando qualcuno sta tentando di troncare una tabella esistente a cui si fa riferimento. Per farlo, puoi usare il comando seguente:
SET FOREIGN_KEY_CHECKS=0;
Ciò imposterebbe una variabile di sessione e disabiliterebbe temporaneamente FOREIGN_KEY_CHECKS. Dopo questa impostazione, puoi andare avanti ed eseguire eliminazioni / troncamenti, che altrimenti non sarebbero stati possibili.
Ma assicurati che questo sia un privilegio di amministratore e dovrebbe essere usato con giudizio.
D # 4) Come trovo i riferimenti alla chiave esterna per una tabella in MySQL?
Risposta: Per elencare tutti i vincoli FOREIGN KEY che sono presenti, puoi utilizzare la tabella 'INNODB_FOREIGN_COLS' in 'INFORMATION_SCHEMA'.
Esegui semplicemente il comando seguente per ottenere tutte le dichiarazioni FOREIGN KEY esistenti per una determinata istanza del server MySQL.
ID | FOR_COL_NAME | REF_COL_NAME | POS |
---|---|---|---|
my_sql_foreign_key / depIdFk | deptId | departmentId | 1 |
Q # 5) La colonna referenziata come FOREIGN KEY dovrebbe essere una chiave primaria nella tabella referenziata?
Risposta: Per definizione di CHIAVE ESTERA, sarebbe richiesto che la colonna a cui si fa riferimento come CHIAVE ESTERA sia la CHIAVE PRIMARIA della tabella in cui viene fatto riferimento.
Tuttavia, con le versioni più recenti di MySQL e con il motore di database InnoDB, potresti anche fare riferimento a una colonna che ha FOREIGN KEY che ha un vincolo UNIQUE e potrebbe non essere necessariamente PRIMARY KEY.
Q # 6) FOREIGN KEY crea INDEX in MySQL?
Risposta: Sia per la chiave primaria che per il vincolo Unique, MySQL crea automaticamente un INDICE per tali colonne.
Poiché sappiamo già che i riferimenti FOREIGN KEY possono essere applicati solo a entrambe le colonne che sono chiavi primarie o colonne che hanno valori univoci, quindi tutte le colonne che vengono indicate come FOREIGN KEY hanno un indice creato su di esse.
Per visualizzare l'indice su una tabella, utilizzare il comando seguente:
SHOW INDEX from {dbName.tableName};
Quindi, per il nostro esempio Employee / Department, avevamo aggiunto deptId in Employee come FOREIGN KEY dalla tabella Department.
Vediamo gli indici creati nelle tabelle Employee e Department.
USE my_sql_foreign_key; SHOW INDEX from employee;
tavolo | Non_unique | Key_name | Seq_in_index | Nome_colonna | Fascicolazione | Cardinalità | Sottoparte | Confezionato | Nullo | Index_type |
---|---|---|---|---|---|---|---|---|---|---|
dipendente | 0 | PRIMARIO | 1 | id | PER | 0 | NULLO | NULLO | BTREE | |
dipendente | 1 | depIdFk | 1 | deptId | PER | 0 | NULLO | NULLO | SÌ | BTREE |
Puoi vedere 2 indici: uno è la chiave primaria per la tabella Employee e un altro è per FOREIGN KEY depId a cui si fa riferimento dalla tabella Department.
SHOW INDEX from department;
tavolo | Non_unique | Key_name | Seq_in_index | Nome_colonna | Fascicolazione | Cardinalità | Sottoparte | Confezionato | Nullo | Index_type |
---|---|---|---|---|---|---|---|---|---|---|
Dipartimento | 0 | PRIMARIO | 1 | departmentId | PER | 0 | NULLO | NULLO | BTREE |
Qui puoi vedere che per la tabella Department, abbiamo solo 1 indice per Primary Key (che viene indicato come FOREIGN KEY nella tabella Employee).
D # 7) Può FOREIGN KEY essere NULL in MySQL?
Risposta: Sì, è perfettamente corretto avere NULL per la colonna che ha una dipendenza FOREIGN KEY da un'altra tabella. Questo allude anche al fatto che NULL non è un valore reale, quindi non è abbinato / confrontato con i valori nella tabella genitore.
Conclusione
In questo tutorial, abbiamo appreso diversi concetti relativi all'uso di CHIAVI ESTERE nei database MySQL.
come trovare la chiave di sicurezza di rete su Windows 10
FOREIGN KEY facilita gli aggiornamenti e le eliminazioni con le restrizioni appropriate, ma a volte avere molte di queste relazioni potrebbe rendere l'intero processo di inserimento e / o eliminazione piuttosto complicato.
Abbiamo imparato come creare CHIAVI ESTERE e come possiamo aggiornare e rilasciare una CHIAVE ESTERA esistente dalla tabella figlia. Abbiamo anche imparato a conoscere diverse azioni di integrità referenziale e come possiamo ottenere comportamenti diversi utilizzando le diverse opzioni disponibili come CASCADE, NO ACTION, SET NULL, ecc.
Lettura consigliata
- Tutorial sulla creazione di tabelle in MySQL con esempi
- MySQL Insert Into Table - Inserisci sintassi ed esempi di istruzioni
- Tutorial MySQL Create View con esempi di codice
- Funzioni MySQL CONCAT e GROUP_CONCAT con esempi
- Tutorial sulle transazioni MySQL con esempi di programmazione
- MySQL UNION - Tutorial completo con esempi di unione
- Come scaricare MySQL per Windows e Mac
- Differenza tra SQL Vs MySQL Vs SQL Server (con esempi)