basics mysql foreign key constraint with examples
Acest tutorial explică elementele de bază ale MySQL FOREIGN KEY Constraint, cum ar fi sintaxa sa, cum să o adăugați, să declarați, să renunțați și să o schimbați cu exemple:
În termeni foarte simpli, cheia străină este utilizată pentru a lega două sau mai multe tabele în MySQL.
Tabelele MySQL trebuie să fie conectate pentru a interoga și actualiza diferite tipuri de date în diferite momente din timp. Prin urmare, este imperativ să existe un punct de legătură între 2 tabele.
În acest tutorial, vom discuta despre diferite utilizări ale cheilor străine și despre cum poate fi declarat și modificat și ce constrângeri are asupra structurii generale a tabelului.
Ce veți învăța:
Cheia MySQL FOREIGN
Sintaxă:
CONSTRAINT {constraintName} FOREIGN KEY (referringColumnName) REFERENCES {referredTable}({referredColumn}) ON UPDATE {reference-option} ON DELETE {reference-option} Mai sus este sintaxa utilizată atunci când se specifică cheia străină împotriva unui tabel în timpul creării tabelei sau cu Instrucțiunea ALTER TABLE.
Să înțelegem diferitele componente ale sintaxei:
- nume constrant: Acesta este numele simbolic pe care vrem să îl definim pentru constrângerea FK care este specificată. Dacă acest lucru este omis, motorul MySQL auto atribuie un nume constrângerii FK.
- referenceColumnName: Aceasta este coloana care se va referi la valorile dintr-un alt tabel, așa cum este specificat de coloana din tabelul menționat.
- Tabel recomandat / tabel părinte: Aceasta se referă la numele tabelului din care ar fi trimise valorile.
- Coloana recomandată: Numele coloanei din tabelul menționat.
- Opțiune de referință: Acestea sunt acțiunile care apar în imagine atunci când se face o acțiune de actualizare sau ștergere pe masă care deține constrângerea cheii externe. Atât UPDATE, cât și DELETE pot avea opțiuni de referință identice sau diferite.
Vom afla despre diferite acțiuni de integritate referențială mai târziu în acest tutorial.
Să vedem un exemplu de referință CHEIE STRĂINĂ folosind exemplul Angajat / Departament. Vom crea un tabel Departament cu coloane - departmentId (int & PRIMARY KEY) și departmentName (varchar).
CREATE TABLE department(departmentId INT PRIMARY KEY NOT NULL, departmentName VARCHAR(100));Creați un tabel Angajați cu coloane după cum urmează:
| Coloană | Tip |
|---|---|
| id | INT (cheie primară) |
| Nume | VARCHAR |
| dept_id | INT (cheie străină) la care se face referire din tabelul departamentului |
| abordare | VARCHAR |
| vârstă | 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); După cum puteți vedea, în tabelul angajaților de mai sus, am declarat coloana deptId de tip Int și am definit cheia străină din tabelul Departamentului pe coloana departmentId.
Ce înseamnă, în esență, că coloana deptId din tabelul Angajaților poate conține numai valori care se află în tabelul Departament.
Să încercăm să introducem date în aceste tabele și să vedem cum funcționează CONSTRINȚA CHEIE STRĂINĂ.
- Creați mai întâi o înregistrare în tabelul Departament și adăugați o înregistrare în tabelul Angajaților referindu-vă la ID-ul înregistrării care a fost adăugat la tabelul Departament.
INSERT INTO department VALUES (1, 'ENGINEERING') --------- INSERT INTO EMPLOYEE VALUES (1, 'AMIT KUMAR', 'MUMBAI', 32, '1988-02-12',1); Veți vedea că ambele declarații vor fi executate fără erori.
- Acum faceți referire la o valoare pentru departmentId care nu există.
De exemplu, în declarația de interogare de mai jos, creăm un angajat cu un departament inexistentId -10
INSERT INTO EMPLOYEE VALUES (1, 'DARREN JOHNSON', 'CHICAGO', 32, '1988-02-12',10);- În acest scenariu, vom primi o eroare ca mai jos:
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)Deci, în linii mari, atunci când sunt definite referințele cheie străine, este important să vă asigurați că tabelul la care se face referire ar trebui să conțină date înainte de a fi trimis.
Acțiuni referențiale de integritate
Să încercăm mai întâi să înțelegem ce este exact Integritatea referențială.
Integritatea referențială ajută la menținerea datelor într-o stare curată și consecventă în care există tabele legate între ele cu o relație de cheie străină.
Simplu spus, Integritatea referențială se referă la acțiunea pe care o așteptăm de la motorul bazei de date, atunci când se întâmplă o ACTUALIZARE sau ȘTERGERE în tabelul de referință care conține CHEEA STRĂINĂ.
De exemplu, în exemplul nostru de angajat / departament, să presupunem că schimbăm ID-ul departamentului pentru un anumit rând din DB. Apoi, toate rândurile de referință din tabelul Angajaților vor fi afectate. Putem defini diferite tipuri de scenarii de integritate referențială care ar putea fi aplicate în astfel de cazuri.
Notă: Integritatea referențială este definită în timpul configurării / declarației FOREIGN KEY ca parte a comenzilor / secțiunilor ON DELETE și ON UPDATE.
Consultați un exemplu de interogare aici (pentru exemplul Angajat / Departament):
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}); Introduceți câteva date în aceste tabele după cum urmează:
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) Există 4 acțiuni de referință care sunt acceptate de MySQL. Să încercăm să le înțelegem pe fiecare dintre ele.
# 1) CASCADA
Aceasta este una dintre cele mai frecvent utilizate acțiuni de integritate referențială. Setarea DELETE și UPDATE la CASCADE ar aplica modificările făcute tabelului de referință de pe tabelul de referință, adică în exemplul Angajat / Departament. Să presupunem că cineva șterge un rând din tabelul Departament care trebuie să spună departament_name = ACCOUNTING, apoi toate șirurile din tabelul Angajaților care au departament_id ca și cel din tabelul Contabilitate ar fi șterse și ele.
Să înțelegem acest lucru cu un exemplu:
SELECT * FROM employee;| id | Nume | abordare | vârstă | dob | deptId |
|---|---|---|---|---|---|
| 1 | AMIT KUMAR | MUMBAI | 32 | 12.02.1988 | 1 |
| Două | RYAN HILMAN | SEATTLE | 43 | 15.03.1977 | 1 |
| 3 | KAVITA SINGH | DELHI | 42 | 18.02.1978 | 4 |
| 4 | DAVID BECKHAM | LONDRA | 40 | 13.07.1980 | 3 |
| 5 | PRITI KUMARI | DELHI | 35 | 1985-12-11 | Două |
| 6 | FRANK BALDING | NEW YORK | 35 | 25.08.1985 | 5 |
Ștergeți înregistrarea din tabelul Departamentului în care departamentulNume = „CONTABILITATE”
DELETE from DEPARTMENT WHERE departmentName='ACCOUNTING';Acum, întrucât este o acțiune referențială CASCADE, ne-am aștepta să fie șterse și toate rândurile care au departamentulID = 2 (care este pentru departamentul „CONTABILITATE”). Să facem din nou o interogare SELECT pe tabela Angajați.
SELECT * FROM employee;| id | Nume | abordare | vârstă | dob | deptId |
|---|---|---|---|---|---|
| 1 | AMIT KUMAR | MUMBAI | 32 | 12.02.1988 | 1 |
| Două | RYAN HILMAN | SEATTLE | 43 | 15.03.1977 | 1 |
| 3 | KAVITA SINGH | DELHI | 42 | 18.02.1978 | 4 |
| 4 | DAVID BECKHAM | LONDRA | 40 | 13.07.1980 | 3 |
| 6 | FRANK BALDING | NEW YORK | 35 | 25.08.1985 | 5 |
După cum puteți vedea mai sus, datorită integrității referențiale CASCADE, rândurile din tabelul Angajaților care se refereau la coloana ștearsă ca TASTĂ STRĂINĂ vor avea aceste rânduri șterse.
# 2) RESTRICȚIE / FĂRĂ ACȚIUNE
Modul RESTRICT sau FĂRĂ ACȚIUNE nu va permite nicio operațiune ACTUALIZARE sau ȘTERGERE de pe tabel cu coloane la care se face referire ca TASTĂ STRĂINĂ într-un anumit tabel.
Modul NO ACTION poate fi aplicat prin simpla omitere a clauzelor ON UPDATE și ON DELETE din declarația tabelului.
Să încercăm același exemplu și, în acest caz, săriți pur și simplu acțiunea ON UPDATE și ON DELETE Integrity Referential.
Acum, când încercăm să ștergem orice intrare din tabelul de referință, vom primi o eroare deoarece am setat acțiunea referențială la RESTRICT
DELETE FROM department WHERE departmentName='ACCOUNTING';Veți vedea o eroare de genul de mai jos dacă încercați să executați comanda DELETE de mai sus.
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
Cu SET NULL, orice ACTUALIZARE sau ȘTERGERE din tabelul de referință ar face ca o valoare NULL să fie actualizată în raport cu valoarea coloanei marcată ca o CHEIE STRĂINĂ în tabelul de referință.
Cu această acțiune de integritate referențială, definiția tabelului angajaților va deveni după cum urmează:
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); Ștergeți un rând din tabelul de referință așa cum se arată mai jos:
DELETE FROM department WHERE departmentName='ACCOUNTING';Acum, în acest caz, valoarea de referință din tabelul Angajaților ar fi setată la NULL. Efectuați o interogare SELECT pe tabela Angajați pentru a vedea rezultatele.
SELECT * FROM employee;| id | Nume | abordare | vârstă | dob | deptId |
|---|---|---|---|---|---|
| 1 | AMIT KUMAR | MUMBAI | 32 | 12.02.1988 | 1 |
| Două | RYAN HILMAN | SEATTLE | 43 | 15.03.1977 | 1 |
| 3 | KAVITA SINGH | DELHI | 42 | 18.02.1978 | 4 |
| 4 | DAVID BECKHAM | LONDRA | 40 | 13.07.1980 | 3 |
| 5 | PRITI KUMARI | DELHI | 35 | 1985-12-11 | NUL |
| 6 | FRANK BALDING | NEW YORK | 35 | 25.08.1985 | 5 |
# 4) SET DEFAULT
Setați modul DEFAULT atunci când este specificat, va duce la înlocuirea valorii implicite pentru coloană (așa cum este specificat în timpul declarației coloanei), în cazul în care se face referire la orice ȘTERGERE din tabel.
Notă - Conform Documentație MySQL , opțiunea SET DEFAULT este acceptată de MySQL Parser, dar nu de motoare DB precum InnoDB. Acest lucru ar putea fi susținut în viitor.
Cu toate acestea, pentru a susține un astfel de comportament, puteți lua în considerare utilizarea SET NULL și definiți un declanșator pe masă care ar putea seta o valoare implicită.
Adăugați constrângere cheie străină folosind instrucțiunea ALTER TABLE
De multe ori se poate întâmpla ca s-ar putea să dorim să adăugăm o constrângere FOREIGN KEY la un tabel existent care nu o are.
Să presupunem că, în exemplul Angajat și Departament, am creat o tabelă a angajaților fără nici o constrângere FOREIGN KEY și mai târziu vrem să introducem constrângerea. Acest lucru poate fi realizat folosind comanda ALTER TABLE.
Să încercăm să înțelegem acest lucru cu un exemplu.
Să presupunem că avem un tabel Angajați cu definiția de mai jos pentru comanda CREATE.
CREATE TABLE employee(id INT PRIMARY KEY NOT NULL, name VARCHAR(100), address VARCHAR(100), age INT, dob DATE, deptId INT);Aici, avem o coloană deptId, dar nu există o constrângere de cheie străină. În acest caz, chiar și fără a avea un tabel Departament, putem specifica orice valori în timp ce inserăm înregistrări.
Acum, să presupunem mai târziu că avem un tabel departament separat și vrem să legăm departamentul ID acolo ca cheie străină cu tabelul angajaților.
ALTER TABLE employee ADD CONSTRAINT depIdFk FOREIGN KEY (deptId) REFERENCES department(departmentId) ON UPDATE CASCADE ON DELETE CASCADE; Ce se întâmplă dacă acest tabel are date existente? Putem ALTERA tabelul și adăugați restricția FOREIGN KEY?
Răspunsul este da - putem, cu condiția ca valorile existente în coloană, care urmează să fie referite dintr-un alt tabel, să aibă acele valori existente în tabelul părinte.
Creați o tabelă a angajaților fără constrângere FOREIGN KEY, adăugați câteva date și încercați să adăugați o constrângere FOREIGN KEY utilizând comanda ALTER.
cum se deschid fișiere SWF cu Adobe Flash Player
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ți un tabel departament și adăugați cheia străină în câmpul „deptId” din tabelul angajaților, după cum se arată mai jos:
CREATE TABLE department(departmentId INT PRIMARY KEY NOT NULL DEFAULT 1000, departmentName VARCHAR(100));În acest moment, dacă încercăm să adăugăm constrângerea cheie străină,
ALTER TABLE employee ADD CONSTRAINT depIdFk FOREIGN KEY (deptId) REFERENCES department(departmentId) ON UPDATE CASCADE ON DELETE CASCADE; Apoi vom primi o eroare, deoarece tabelul Angajaților conține unele date, dar constrângerea referențială de integritate nu poate fi îndeplinită, deoarece tabelul Departamentului nu are încă date.
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)Pentru a avea constrângerea FOREIGN KEY, va trebui mai întâi să adăugăm date în tabelul Departamentului. Să introducem înregistrările necesare în tabelul Departamentului.
INSERT INTO department VALUES (1, 'ENGINEERING'),(10,'ACCOUNTING');Adăugați din nou constrângerea FOREIGN KEY executând aceeași instrucțiune ALTER TABLE. Veți observa că de data aceasta, comanda are succes, iar tabela Angajaților este actualizată cu succes pentru a avea deptId ca cheie străină din tabelul Departament.
Eliminarea unei constrângeri cheie străină
Similar cu adăugarea unei constrângeri cheie străină, este, de asemenea, posibil să scăpați / ștergeți o constrângere cheie străină existentă dintr-un tabel.
Acest lucru poate fi realizat folosind comanda ALTER TABLE.
Sintaxă:
ALTER TABLE {childTable} DROP FOREIGN KEY {foreign key constraint name}; Aici „childTable” este numele tabelului care are definită constrângerea cheii străine, în timp ce „numele constrângerii cheii străine” este numele / simbolul care a fost utilizat pentru a defini cheia străină.
Să vedem un exemplu folosind tabelul Angajat / Departament. Pentru a elimina o constrângere numită „depIdFk” din tabelul Angajaților, utilizați comanda de mai jos:
ALTER TABLE employee DROP FOREIGN KEY depIdFk;întrebări frecvente
Q # 1) Cum pot schimba cheile străine în MySQL?
Răspuns: Tasta FOREGIN poate fi adăugată / eliminată folosind comanda ALTER TABLE.
Pentru a schimba sau adăuga o nouă cheie străină, puteți utiliza comanda ALTER și defini coloana cheie externă și tabelul de referință care ar fi referențiat din tabelul copil.
Q # 2) Cum se setează mai multe chei străine în MySQL?
Răspuns: Un tabel din MySQL poate avea mai multe CHEI STRĂINE, care ar putea depinde de același tabel părinte sau de tabele părinte diferite.
Să folosim tabelul Angajat / Departament și să adăugăm CHEIE STRĂINĂ pentru numele Departamentului, precum și Departamentul în tabelul Angajaților.
Consultați instrucțiunile CREATE ale ambelor tabele ca mai jos
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); Q # 3) Cum se dezactivează constrângerile cheii străine în MySQL?
Răspuns: Constrângerile cheie străine sunt de obicei necesare atunci când cineva încearcă să trunchieze un tabel existent la care se face referire. Pentru a face acest lucru, puteți utiliza comanda de mai jos:
SET FOREIGN_KEY_CHECKS=0;Aceasta ar seta o variabilă de sesiune și ar dezactiva temporar FOREIGN_KEY_CHECKS. După această setare, puteți continua și efectua ștergeri / trunchiere, ceea ce altfel nu ar fi fost posibil.
Dar vă rugăm să vă asigurați că acesta este un privilegiu de administrator și că trebuie utilizat cu prudență.
Î # 4) Cum găsesc referințele cheie străine pentru un tabel în MySQL?
Răspuns: Pentru a enumera toate constrângerile cheie străine care sunt prezente, puteți utiliza tabelul „INNODB_FOREIGN_COLS” din „INFORMATION_SCHEMA”.
Pur și simplu rulați comanda de mai jos pentru a obține toate declarațiile FOREIGN KEY existente pentru o anumită instanță de server MySQL.
| ID | FOR_COL_NAME | REF_COL_NAME | POS |
|---|---|---|---|
| my_sql_foreign_key / depIdFk | deptId | departmentId | 1 |
Q # 5) Ar trebui ca coloana la care se face referire ca TASTĂ STRĂINĂ să fie o cheie principală în tabelul de referință?
Răspuns: Prin definiția CHEIEI STRĂINE, ar fi necesar ca coloana la care se face referire ca CHEIE STRĂINĂ să fie CHEIA PRIMARĂ din tabelul la care se face referire.
Cu toate acestea, cu versiunile mai noi de MySQL și cu motorul de bază de date InnoDB, puteți face referință și la o coloană care are cheia străină care are o constrângere UNICĂ și poate să nu fie neapărat cheia primară.
Q # 6) KEY STRĂINĂ creează INDEX în MySQL?
Răspuns: Atât pentru constrângerea cheie primară, cât și pentru constrângerea unică, MySQL creează automat un INDEX pentru astfel de coloane.
Întrucât știm deja că referințele FOREIGN KEY pot fi aplicate doar oricărei coloane care sunt chei primare sau coloane care au valori unice, prin urmare, toate coloanele care sunt denumite FOREIGN KEY au un index creat împotriva lor.
Pentru a vizualiza indexul pe un tabel, utilizați comanda de mai jos:
SHOW INDEX from {dbName.tableName};Deci, pentru exemplul nostru de angajat / departament, am adăugat deptId în angajați ca cheie străină din tabelul departamentului.
Să vedem indexurile create în tabelele Angajați și Departament.
USE my_sql_foreign_key; SHOW INDEX from employee; | Masa | Non_unică | Key_name | Seq_in_index | Nume_coloană | Colaţionare | Cardinalitatea | Sub_partea | bătătorit | Nul | Tip_indice |
|---|---|---|---|---|---|---|---|---|---|---|
| angajat | 0 | PRIMAR | 1 | id | LA | 0 | NUL | NUL | BTREE | |
| angajat | 1 | depIdFk | 1 | deptId | LA | 0 | NUL | NUL | DA | BTREE |
Puteți vedea 2 indici - unul este cheia principală pentru tabelul Angajaților și altul este pentru depEd DE CHEIE STRĂINĂ, la care se face referire din tabelul Departament.
SHOW INDEX from department;| Masa | Non_unică | Key_name | Seq_in_index | Nume_coloană | Colaţionare | Cardinalitatea | Sub_partea | bătătorit | Nul | Tip_indice |
|---|---|---|---|---|---|---|---|---|---|---|
| departament | 0 | PRIMAR | 1 | departmentId | LA | 0 | NUL | NUL | BTREE |
Aici puteți vedea că pentru tabelul departamentului avem doar 1 index pentru cheia primară (care este menționată ca cheie străină în tabelul angajaților).
Q # 7) KEY EXTERNA poate fi NULL în MySQL?
Răspuns: Da, este perfect în regulă să aveți NULL pentru coloana care are o dependență de cheie străină pe un alt tabel. Acest lucru face, de asemenea, aluzie la faptul că NULL nu este o valoare reală, prin urmare nu este potrivită / comparată cu valorile din tabelul părinte.
Concluzie
În acest tutorial, am aflat despre diferite concepte legate de utilizarea TASTELOR STRĂINE în bazele de date MySQL.
TASTĂ STRĂINĂ ușurează actualizările și șterge cu restricții adecvate, dar uneori, având o mulțime de astfel de relații ar putea face întregul proces de inserare și / sau ștergere destul de greoaie.
Am învățat cum să creăm chei străine și cum putem actualiza și scoate o cheie străină existentă din tabelul copil. De asemenea, am aflat despre diferite acțiuni de integritate referențială și despre cum putem realiza un comportament diferit folosind diferite opțiuni disponibile, cum ar fi CASCADE, NO ACTION, SET NULL etc.
Lectură recomandată
- Tutorial MySQL Create Table cu exemple
- MySQL Insert Into Table - Inserați sintaxa declarațiilor și exemple
- Tutorial MySQL Create View cu exemple de cod
- Funcțiile MySQL CONCAT și GROUP_CONCAT cu exemple
- MySQL Transaction Tutorial cu exemple de programare
- MySQL UNION - Tutorial cuprinzător cu exemple de uniune
- Cum se descarcă MySQL pentru Windows și Mac
- Diferența dintre SQL și MySQL împotriva SQL Server (cu exemple)