basics mysql foreign key constraint with examples
Aquest tutorial explica els conceptes bàsics de MySQL FOREIGN KEY Constraint, com ara la seva sintaxi, com afegir-lo, declarar-lo, deixar-lo anar i canviar-lo amb exemples:
En termes molt senzills, la CLAU ESTRANGERA s’utilitza per enllaçar dues o més taules a MySQL.
Cal connectar les taules MySQL per consultar i actualitzar diversos tipus de dades en diferents moments. Per tant, és imprescindible tenir un punt d’enllaç entre dues taules.
En aquest tutorial, analitzarem els diferents usos de les claus estrangeres i com es pot declarar i canviar, i quines restriccions té a l'estructura general de la taula.
Què aprendreu:
CLAU ESTRANGERA MySQL
Sintaxi:
CONSTRAINT {constraintName} FOREIGN KEY (referringColumnName) REFERENCES {referredTable}({referredColumn}) ON UPDATE {reference-option} ON DELETE {reference-option} A sobre es mostra la sintaxi que s’utilitza quan s’especifica FOREIGN KEY contra una taula mentre es crea la taula o amb Instrucció ALTER TABLE.
Comprenem els diferents components de la sintaxi:
- constrantName: Aquest és el nom simbòlic que volem definir per a la restricció FK que s’especifica. Si s'omet això, el motor MySQL automàticament assigna un nom a la restricció FK.
- referentColumnaNom: Aquesta és la columna que es referiria als valors d’una altra taula tal com s’especifica a la columna de la taula referida.
- Taula referida / taula principal: Es refereix al nom de la taula a partir de la qual es derivarien els valors.
- Columna referida: El nom de la columna a la taula referida.
- Opció de referència: Aquestes són les accions que apareixen a la imatge quan es fa una acció d'actualització o eliminació a la taula que conté la restricció de clau externa. Tant ACTUALITZAR com SUPRIMIR poden tenir les mateixes opcions de referència o diferents.
Més endavant en aquest tutorial coneixerem diferents accions d’integritat referencial.
Vegem un exemple de referència de CLAU ESTRANGERA mitjançant l’exemple Empleat / Departament. Crearem un departament de taula amb columnes - departmentId (int & PRIMARY KEY) i departmentName (varchar).
CREATE TABLE department(departmentId INT PRIMARY KEY NOT NULL, departmentName VARCHAR(100));Creeu una taula Empleat amb les columnes següents:
| Columna | Tipus |
|---|---|
| identificador | INT (clau principal) |
| nom | VARCHAR |
| dept_id | INT (clau estrangera) referenciada des de la taula de departaments |
| adreça | VARCHAR |
| edat | 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); Com podeu veure, a la taula Empleat anterior, hem declarat la columna deptId del tipus Int i hem definit la CLAU ESTRANGERA a la taula Departament de la columna departmentId.
Això significa essencialment que la columna deptId de la taula Empleats només pot contenir valors que es troben a la taula Departament.
Intentem inserir dades en aquestes taules i comprovem com funciona la CONSTRICCIÓ DE CLAUS ESTRANGERES.
- Creeu primer un registre a la taula Departament i afegiu un registre a la taula Empleats que faci referència a l’identificador del registre que s’ha afegit a la taula Departament.
INSERT INTO department VALUES (1, 'ENGINEERING') --------- INSERT INTO EMPLOYEE VALUES (1, 'AMIT KUMAR', 'MUMBAI', 32, '1988-02-12',1); Veureu que ambdues sentències s’executarien sense errors.
- Ara feu referència a un valor per a departmentId que no existeix.
Per exemple, a la declaració de consulta següent, estem creant un empleat amb un departament ID inexistent -10
INSERT INTO EMPLOYEE VALUES (1, 'DARREN JOHNSON', 'CHICAGO', 32, '1988-02-12',10);- En aquest escenari, obtindrem un error com el següent:
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)Per tant, a grans trets, quan es defineixen les referències de CLAUS ESTRANGERES, és important assegurar-se que la taula a la qual es fa referència ha de contenir dades abans que es faci referència.
Accions d’integritat referencial
Intentem primer comprendre què és exactament la integritat referencial.
La integritat referencial ajuda a mantenir les dades en un estat net i coherent on hi ha taules relacionades entre si amb una relació de CLAU EXTERIOR.
Simplement, la integritat referencial fa referència a l'acció que esperem del motor de base de dades, quan es produeix una ACTUALITZACIÓ o SUPRIMIT a la taula de referència que conté la CLAU ESTRANGERA.
Per exemple, a l'exemple del nostre empleat / departament, suposem que canviem l'identificador de departament per a una fila determinada de la base de dades. A continuació, es veuran afectades totes les files de referència de la taula Empleats. Podem definir diferents tipus d’escenaris d’integritat referencial que es podrien aplicar durant aquests casos.
Nota: La integritat referencial es defineix durant la configuració / declaració de la clau estrangera com a part de les seccions / ordres ON DELETE i ON UPDATE.
Consulteu una mostra de consulta aquí (per a l'exemple d'empleat / 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}); Inseriu algunes dades a aquestes taules de la manera següent:
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) Hi ha 4 accions de referència compatibles amb MySQL. Intentem entendre cadascun d’ells.
# 1) CASCADA
Aquesta és una de les accions d’integritat referencial més utilitzades. Si definiu DELETE i UPDATE a CASCADE s’aplicarien els canvis fets a la taula de referència de la taula de referència, és a dir, a l’exemple Empleat / Departament. Suposem que algú suprimeix una fila de la taula de departaments que ha de dir departament_nom = COMPTABILITAT, i també se suprimiran totes les files de la taula d'empleats que tinguin departament_id com la de la taula de comptabilitat.
Entenguem això amb un exemple:
com fer una matriu de cadenes a Java
SELECT * FROM employee;| identificador | nom | adreça | edat | dob | deptId |
|---|---|---|---|---|---|
| 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 | LONDRES | 40 | 1980.07.13 | 3 |
| 5 | PRITI KUMARI | DELHI | 35 | 1985.12.11 | 2 |
| 6 | FRANK BALDING | NOVA YORK | 35 | 1985.08.25 | 5 |
Suprimiu el registre de la taula Departament on departmentName = 'COMPTABILITAT'
DELETE from DEPARTMENT WHERE departmentName='ACCOUNTING';Ara, com que és una acció referencial CASCADE, esperaríem que totes les files que tinguin departmentID = 2 (que és per al departament 'COMPTABILITAT') també s'hagin de suprimir. Tornem a fer una consulta SELECT a la taula Empleats.
SELECT * FROM employee;| identificador | nom | adreça | edat | dob | deptId |
|---|---|---|---|---|---|
| 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 | LONDRES | 40 | 1980.07.13 | 3 |
| 6 | FRANK BALDING | NOVA YORK | 35 | 1985.08.25 | 5 |
Com podeu veure més amunt, a causa de la integritat referencial de CASCADE, les files de la taula Empleats que feien referència a la columna suprimida com a CLAU ESTRANGERA se suprimiran aquestes files.
# 2) RESTRICTE / SENSE ACCIÓ
El mode RESTRICT o NO ACTION no permetrà cap operació ACTUALITZACIÓ o SUPRIMIT a la taula que tingui columnes a les quals es faci referència com a CLAU ESTRANGERA en alguna taula.
El mode NO ACCIÓ es pot aplicar simplement ometent les clàusules ON UPDATE i ON DELETE de la declaració de taula.
Provem el mateix exemple i, en aquest cas, ometeu l'acció ACTIVITAT D'ACTUALITZACIÓ i ACTIVITAT D'INtegritat referencial.
Ara, quan intentem suprimir qualsevol entrada de la taula de referència, obtindríem un error ja que hem definit l'acció referencial a RESTRICT
DELETE FROM department WHERE departmentName='ACCOUNTING';Veureu un error com el següent si intenteu executar l'ordre SUPRIMIR anterior.
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
Amb SET NULL, qualsevol ACTUALITZACIÓ o SUPRIMIÓ de la taula de referència farà que s’actualitzi un valor NULL amb el valor de columna marcat com a CLAU ESTRANGERA a la taula de referència.
Amb aquesta acció d'integritat referencial, la definició de la taula d'empleats seria la següent:
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); Elimineu una fila de la taula de referència com es mostra a continuació:
DELETE FROM department WHERE departmentName='ACCOUNTING';Ara, en aquest cas, el valor referenciat a la taula Empleats s'establiria a NULL. Feu una consulta SELECT a la taula Employee per veure els resultats.
SELECT * FROM employee;| identificador | nom | adreça | edat | dob | deptId |
|---|---|---|---|---|---|
| 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 | LONDRES | 40 | 1980.07.13 | 3 |
| 5 | PRITI KUMARI | DELHI | 35 | 1985.12.11 | NUL |
| 6 | FRANK BALDING | NOVA YORK | 35 | 1985.08.25 | 5 |
# 4) SET DEFAULT
Si s'especifica el mode SET DEFAULT, es substituirà el valor per defecte per la columna (tal com s'especifica durant la declaració de columna), en cas que es faci referència a qualsevol ESBORRAMENT de la taula.
Nota - Segons Documentació MySQL , l'opció SET DEFAULT és compatible amb MySQL Parser, però no motors DB com InnoDB. Això es pot donar suport en el futur.
Tot i això, per donar suport a aquest comportament, podeu considerar utilitzar SET NULL i definir un activador a la taula que pugui establir un valor per defecte.
per a què s’utilitza la programació c ++
Afegiu restricció de la clau estrangera mitjançant la sentència ALTER TABLE
Moltes vegades pot passar que vulguem afegir una restricció FOREIGN KEY a una taula existent que no la tingui.
Suposem que, a l'exemple Employee and Department, hem creat una taula d'empleats sense cap restricció FOREIGN KEY i més endavant volem introduir-la. Això es pot aconseguir mitjançant l'ordre ALTER TABLE.
Intentem entendre-ho amb un exemple.
Suposem que tenim una taula d'empleats amb la definició següent per a l'ordre CREATE.
CREATE TABLE employee(id INT PRIMARY KEY NOT NULL, name VARCHAR(100), address VARCHAR(100), age INT, dob DATE, deptId INT);Aquí tenim una columna deptId però no hi ha cap restricció de CLAU ESTRANGERA. En aquest cas, fins i tot sense tenir una taula de departaments, podem especificar qualsevol valor mentre inserim registres.
Ara, més endavant, suposem que tenim una taula de departaments separada i volem enllaçar-hi DepartmentId com a CLAU ESTRANGERA a la taula Empleats
ALTER TABLE employee ADD CONSTRAINT depIdFk FOREIGN KEY (deptId) REFERENCES department(departmentId) ON UPDATE CASCADE ON DELETE CASCADE; Què passa si aquesta taula té dades existents? Podem ALTERAR la taula i afegir la restricció FOREIGN KEY?
La resposta és sí: podem amb la condició que els valors existents a la columna als quals es faci referència des d’una altra taula hagin de tenir aquests valors existents a la mateixa taula pare.
Creeu una taula d'empleats sense restricció FOREIGN KEY, afegiu algunes dades i proveu d'afegir una restricció FOREIGN KEY mitjançant l'ordre 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); Creeu una taula de departaments i afegiu FOREIGN KEY al camp 'deptId' de la taula d'empleats, tal com es mostra a continuació:
CREATE TABLE department(departmentId INT PRIMARY KEY NOT NULL DEFAULT 1000, departmentName VARCHAR(100));Arribats a aquest punt, si intentem afegir la restricció FOREIGN KEY,
ALTER TABLE employee ADD CONSTRAINT depIdFk FOREIGN KEY (deptId) REFERENCES department(departmentId) ON UPDATE CASCADE ON DELETE CASCADE; Aleshores obtindrem un error, ja que la taula Empleats conté algunes dades, però la restricció d’integritat referencial no es pot complir, ja que la taula de departaments encara no té dades.
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 tenir la restricció FOREIGN KEY, primer caldrà afegir dades a la taula de departaments. Inserim els registres obligatoris a la taula Departament.
INSERT INTO department VALUES (1, 'ENGINEERING'),(10,'ACCOUNTING');Afegiu de nou la restricció FOREIGN KEY executant la mateixa sentència ALTER TABLE. Notareu que, aquesta vegada, l'ordre és correcta i la taula d'empleats s'actualitza correctament per tenir deptId com a CLAU ESTRANGERA de la taula de departaments.
Eliminació d'una restricció de la clau estrangera
De manera similar a l’addició d’una restricció FOREIGN KEY, també és possible suprimir / suprimir una restricció FOREIGN KEY d’una taula.
Això es pot aconseguir mitjançant l'ordre ALTER TABLE.
Sintaxi:
ALTER TABLE {childTable} DROP FOREIGN KEY {foreign key constraint name}; Aquí ‘childTable’ és el nom de la taula que té definida la restricció FOREIGN KEY, mentre que el ‘nom de la restricció de clau estrangera’ és el nom / símbol que s’ha utilitzat per definir la KEY FOREIGN.
Vegem un exemple amb la taula Empleat / Departament. Per eliminar una restricció anomenada 'depIdFk' de la taula Empleat, utilitzeu l'ordre següent:
ALTER TABLE employee DROP FOREIGN KEY depIdFk;Preguntes freqüents
P # 1) Com puc canviar les claus externes a MySQL?
Resposta: FOREGIN KEY es pot afegir / eliminar mitjançant l'ordre ALTER TABLE.
Per canviar o afegir una nova CLAU EXTERIOR, podeu utilitzar l'ordre ALTER i definir la columna FOREIGN KEY i taula de referències a la qual es farà referència des de la taula fill.
Q # 2) Com configurar diverses claus externes a MySQL?
Resposta: Una taula de MySQL pot tenir diverses CLAUS EXTERNES, que podrien dependre de la mateixa taula principal o de diferents taules pares.
Utilitzem la taula Empleat / Departament i afegim CLAU ESTRANGERA per al nom del departament, així com DepartmentId a la taula Empleat.
Consulteu les sentències CREATE de les dues taules de la següent manera
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); P # 3) Com desactivar les restriccions de clau externa a MySQL?
Resposta: Les restriccions de CLAU EXTERIOR solen ser necessàries quan algú intenta truncar una taula existent a la qual es fa referència. Per fer-ho, podeu utilitzar l'ordre següent:
SET FOREIGN_KEY_CHECKS=0;Això establiria una variable de sessió i desactivaria temporalment els FOREIGN_KEY_CHECKS. Després d'aquest paràmetre, podeu continuar i realitzar eliminacions / truncaments, que d'una altra manera no haurien estat possibles.
obriu els fitxers .jar de Windows 10
Però assegureu-vos que es tracta d’un privilegi d’administrador i que s’ha d’utilitzar amb criteri.
Q # 4) Com puc trobar les referències de claus externes per a una taula a MySQL?
Resposta: Per tal d’enumerar totes les restriccions de la CLAU ESTRANGERA que hi ha, podeu utilitzar la taula 'INNODB_FOREIGN_COLS' a 'INFORMATION_SCHEMA'.
Simplement executeu l'ordre següent per obtenir totes les declaracions de FOREIGN KEY existents per a una instància de servidor MySQL determinada.
| Identificació | FOR_COL_NAME | REF_COL_NAME | POS |
|---|---|---|---|
| my_sql_foreign_key / depIdFk | deptId | departmentId | 1 |
P # 5) La columna a la qual es fa referència com a CLAU ESTRANGERA ha de ser una clau principal a la taula de referència?
Resposta: Per definició de CLAU ESTRANGERA, caldria que la columna a la qual es fa referència com a CLAU ESTRANGERA sigui la CLAU PRIMÀRIA de la taula on es fa referència.
Tanmateix, amb les versions més recents de MySQL i amb el motor de base de dades InnoDB, també podríeu fer referència a una columna que tingui FOREIGN KEY que té una restricció ÚNICA i que potser no sigui necessàriament PRIMARY KEY.
Q # 6) La FORAIGN KEY crea INDEX a MySQL?
Resposta: Tant per a la restricció de clau primària com per a la única, MySQL crea automàticament un INDEX per a aquestes columnes.
Com que ja sabem que les referències FOREIGN KEY només es poden aplicar a les columnes que siguin claus primàries o a les columnes que tinguin valors únics, de manera que totes les columnes anomenades FOREIGN KEY tenen un índex creat a la seva contra.
Per veure l’índex d’una taula, utilitzeu l’ordre següent:
SHOW INDEX from {dbName.tableName};Per tant, per a l’exemple del nostre empleat / departament, havíem afegit deptId a Employee com a CLAU ESTRANGERA a la taula del departament.
Vegem els índexs creats a les taules d'empleats i departaments.
USE my_sql_foreign_key; SHOW INDEX from employee; | Taula | No únic | Nom_clau | Seq_in_index | Nom_columna | Col·lació | Cardinalitat | Sub_part | Embalat | Nul | Tipus_índex |
|---|---|---|---|---|---|---|---|---|---|---|
| empleat | 0 | PRIMÀRIA | 1 | identificador | A | 0 | NUL | NUL | BTREE | |
| empleat | 1 | depIdFk | 1 | deptId | A | 0 | NUL | NUL | SÍ | BTREE |
Podeu veure 2 índexs: un és la clau principal de la taula Empleats i un altre és per al dipòsit de CLAU ESTRANGER que es fa referència a la taula de departaments.
SHOW INDEX from department;| Taula | No únic | Nom_clau | Seq_in_index | Nom_columna | Col·lació | Cardinalitat | Sub_part | Embalat | Nul | Tipus_índex |
|---|---|---|---|---|---|---|---|---|---|---|
| departament | 0 | PRIMÀRIA | 1 | departmentId | A | 0 | NUL | NUL | BTREE |
Aquí podeu veure que, per a la taula de departaments, només tenim 1 índex per a la clau primària (que es fa referència com a CLAU ESTRANGERA a la taula Empleats).
P # 7) Pot la clau estrangera ser nul a MySQL?
Resposta: Sí, està perfectament bé tenir NULL per a la columna que té una dependència de CLAU ESTRANGERA en una altra taula. Això també fa al·lusió al fet que NULL no és un valor real, per tant, no es compara / compara amb els valors de la taula principal.
Conclusió
En aquest tutorial, hem après sobre diferents conceptes relacionats amb l’ús de CLAUS EXTERNES a les bases de dades MySQL.
FOREIGN KEY facilita les actualitzacions i les elimina amb les restriccions adequades, però de vegades tenir moltes relacions d’aquest tipus pot fer que tot el procés d’inserció i / o supressió sigui força feixuc.
Vam aprendre a crear CLAUS EXTERNES i a actualitzar i deixar anar una CLAU EXTERNA de la taula infantil. També vam conèixer diferents accions d’integritat referencial i com podem aconseguir un comportament diferent mitjançant les diferents opcions disponibles com CASCADE, NO ACCION, SET NULL, etc.
Lectura recomanada
- Tutorial MySQL Create Table amb exemples
- MySQL Insereix a la taula: insereix sintaxi i exemples de sentències
- Tutorial MySQL Create View amb exemples de codi
- Funcions MySQL CONCAT i GROUP_CONCAT amb exemples
- Tutorial de transaccions MySQL amb exemples de programació
- MySQL UNION: tutorial complet amb exemples d'unió
- Com descarregar MySQL per a Windows i Mac
- Diferència entre SQL vs MySQL vs SQL Server (amb exemples)