basics mysql foreign key constraint with examples
Denna handledning förklarar grunderna i MySQL UTLÄNDSK NYCKEL-begränsning som dess syntax, hur man lägger till, deklarerar, släpper och ändrar den med exempel:
I mycket enkla termer används den UTLÄNDSKA KEY för att länka två eller flera tabeller i MySQL.
MySQL-tabeller måste anslutas för att kunna fråga och uppdatera olika typer av data vid olika tidpunkter. Därför är det absolut nödvändigt att ha en kopplingspunkt mellan två tabeller.
I denna handledning kommer vi att diskutera olika användningar av främmande nycklar och hur deklareras och ändras, och vilka begränsningar den har för den övergripande tabellstrukturen.
Vad du kommer att lära dig:
MySQL UTLÄNDSK NYCKEL
Syntax:
CONSTRAINT {constraintName} FOREIGN KEY (referringColumnName) REFERENCES {referredTable}({referredColumn}) ON UPDATE {reference-option} ON DELETE {reference-option} Ovan är syntaxen som används när du anger UTLÄNDSK NYCKEL mot ett bord medan du skapar eller med ALTER TABLE uttalande.
Låt oss förstå de olika komponenterna i syntaxen:
- constrantName: Detta är det symboliska namnet vi vill definiera för den FK-begränsning som anges. Om detta hoppas över tilldelar MySQL-motorn automatiskt ett namn till FK-begränsningen.
- referringColumnName: Det här är den kolumn som refererar till värdena i en annan tabell som anges av kolumnen i den hänvisade tabellen.
- Hänvisad tabell / överordnad tabell: Detta hänvisar till namnet på tabellen från vilken värdena skulle hänvisas.
- Hänvisad kolumn: Kolumnnamnet i den hänvisade tabellen.
- Referensalternativ: Det här är de åtgärder som kommer in i bilden när en uppdatering eller radering sker på bordet som har begränsningen för främmande nycklar. Både UPPDATERING och RADERA kan ha samma eller olika referensalternativ.
Vi skulle lära oss om olika referensintegritetsåtgärder senare i den här självstudien.
Låt oss se ett exempel på en utländsk nyckelreferens med exemplet Anställd / avdelning. Vi skapar en tabell Avdelning med kolumner - departmentId (int & PRIMARY KEY) och departmentName (varchar).
CREATE TABLE department(departmentId INT PRIMARY KEY NOT NULL, departmentName VARCHAR(100));Skapa en tabell Anställd med kolumner enligt nedan:
| Kolumn | Typ |
|---|---|
| id | INT (Primär nyckel) |
| namn | VARCHAR |
| dept_id | INT (Foreign Key) hänvisas från avdelningstabellen |
| adress | VARCHAR |
| ålder | INT |
| dob | DATUM |
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); Som du kan se har vi i ovanstående anställdstabell deklarerat deptId-kolumn av typen Int och definierat UTLÄNDSK NYCKEL från Institutionstabellen på kolumn departmentId.
Vad detta egentligen betyder att kolumnen deptId i tabellen Anställda bara kan innehålla värden som finns i avdelningstabellen.
Låt oss försöka infoga data i dessa tabeller och se hur UTLÄNDSK NYCKELKONSTRAINT fungerar.
- Skapa först en post i avdelningstabellen och lägg till en post i tabellen medarbetare som refererar till ID för den post som har lagts till i avdelningstabellen.
INSERT INTO department VALUES (1, 'ENGINEERING') --------- INSERT INTO EMPLOYEE VALUES (1, 'AMIT KUMAR', 'MUMBAI', 32, '1988-02-12',1); Du kommer att se att båda uttalandena skulle köras utan fel.
- Referera nu till ett värde för departmentId som inte finns.
Till exempel, i frågeförklaringen nedan skapar vi en anställd med en icke-existerande departmentId -10
INSERT INTO EMPLOYEE VALUES (1, 'DARREN JOHNSON', 'CHICAGO', 32, '1988-02-12',10);- I det här scenariot får vi ett fel som nedan:
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)När det gäller utländska nyckelreferenser är det viktigt att se till att tabellen som refereras ska ha data innan den hänvisas.
Referensintegritetsåtgärder
Låt oss först försöka förstå vad exakt referensintegritet är.
Referensintegritet hjälper till att upprätthålla data i ett rent och konsekvent tillstånd där det finns tabeller relaterade till varandra med en utländsk nyckel-relation.
Enkelt uttryckt hänvisar referensintegritet till den åtgärd som vi förväntar oss av databasmotorn att utföra, när en UPPDATERING eller RADERING inträffar i den refererade tabellen som innehåller UTLÄNDSK NYCKEL.
Till exempel, antar att vi ändrar avdelnings-ID för en viss rad i DB. Då skulle alla referensrader i tabellen Anställda påverkas. Vi kan definiera olika typer av referensintegritetsscenarier som kan användas under sådana fall.
Notera: Referensintegritet definieras under UTLÄNDSK KEY-installation / -deklaration som en del av kommandona / sektionerna ON DELETE och ON UPDATE.
Se en exempelfråga här (för exempel på anställd / avdelningen):
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}); Infoga några data i dessa tabeller enligt nedan:
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) Det finns fyra referensåtgärder som stöds av MySQL. Låt oss försöka förstå var och en av dem.
gratis popup-blockerare för Google Chrome
# 1) CASCADE
Detta är en av de mest använda referensintegritetsåtgärderna. Om du ställer in DELETE och UPPDATERING till CASCADE tillämpas ändringarna som gjorts i den refererade tabellen på referensbordet, dvs i exemplet Anställd / avdelning. Antag att någon raderar en rad i avdelningstabellen som måste säga avdelningsnamn = REDOVISNING, då raderas också alla rader i tabellen Anställd med avdelning_id som i redovisningstabellen.
Låt oss förstå detta med ett exempel:
SELECT * FROM employee;| id | namn | adress | ålder | dob | avd |
|---|---|---|---|---|---|
| 1 | AMIT KUMAR | MUMBAI | 32 | 1988-02-12 | 1 |
| två | 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 | två |
| 6 | FRANK BALDING | NEW YORK | 35 | 1985-08-25 | 5 |
Ta bort post från avdelningstabellen där departmentName = 'ACCOUNTING'
DELETE from DEPARTMENT WHERE departmentName='ACCOUNTING';Nu, eftersom det är en CASCADE-referensåtgärd, förväntar vi oss att alla rader som har avdelningsID = 2 (vilket är för avdelningen ”REDOVISNING”) också ska tas bort. Låt oss göra en SELECT-fråga på tabellen Anställd igen.
SELECT * FROM employee;| id | namn | adress | ålder | dob | avd |
|---|---|---|---|---|---|
| 1 | AMIT KUMAR | MUMBAI | 32 | 1988-02-12 | 1 |
| två | 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 |
| 6 | FRANK BALDING | NEW YORK | 35 | 1985-08-25 | 5 |
Som du kan se ovan kommer de raderna att raderas på grund av CASCADE-referensintegritet.
# 2) BEGRÄNSNING / INGEN ÅTGÄRD
RESTRICT- eller NO ACTION-läge tillåter inte UPPDATERING eller RADERING på tabellen med kolumner som refereras till UTLÄNDSK NYCKEL i någon tabell.
NO ACTION-läget kan tillämpas genom att helt enkelt utelämna satserna PÅ UPPDATERING och PÅ RADERA från tabelldeklarationen.
Låt oss prova samma exempel och i det här fallet helt enkelt hoppa över åtgärden PÅ UPPDATERING och PÅ RADERA referensintegritet.
Nu när vi försöker ta bort en post i den refererade tabellen skulle vi få ett fel eftersom vi har satt referensåtgärden till RESTRICT
DELETE FROM department WHERE departmentName='ACCOUNTING';Du kommer att se ett fel något liknande nedan om du försöker utföra ovanstående DELETE-kommando.
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
Med SET NULL skulle någon UPPDATERING eller DELETE i den refererade tabellen få ett NULL-värde att uppdateras mot kolumnvärde som är markerat som en UTLÄNDSK NYCKEL i referensbordet.
Med denna referensintegritetsåtgärd skulle definitionen av tabellen Anställd bli som följer:
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); Ta bort en rad i den refererade tabellen som visas nedan:
DELETE FROM department WHERE departmentName='ACCOUNTING';I det här fallet skulle det refererade värdet i tabellen Anställd ställas till NULL. Gör en VÄLJ-fråga på tabellen Anställd för att se resultaten.
SELECT * FROM employee;| id | namn | adress | ålder | dob | avd |
|---|---|---|---|---|---|
| 1 | AMIT KUMAR | MUMBAI | 32 | 1988-02-12 | 1 |
| två | 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 | NULL |
| 6 | FRANK BALDING | NEW YORK | 35 | 1985-08-25 | 5 |
# 4) STÄLL IN STANDARD
INSTÄLLNING AV STANDARD-läge när det anges skulle resultera i att standardvärdet för kolumnen ersätts (som anges under kolumndeklarationen), om eventuella DELETES i tabellen refereras.
Notera - Enligt MySQL-dokumentation , alternativet SET DEFAULT stöds av MySQL Parser men inte DB-motorer som InnoDB. Detta kan stödjas i framtiden.
För att stödja ett sådant beteende kan du dock överväga att använda SET NULL och definiera en trigger på bordet som kan ställa in ett standardvärde.
Lägg till utländsk nyckelbegränsning med ALTER TABLE
Många gånger kan det hända att vi kanske vill lägga till en utländsk nyckelbegränsning i en befintlig tabell som inte har den.
Anta att i exempel på anställda och avdelningar skapade vi en anställdstabell utan någon utländsk nyckelbegränsning och senare vill vi införa begränsningen. Detta kan uppnås med kommandot ALTER TABLE.
Låt oss försöka förstå detta med ett exempel.
Antag att vi har en anställdstabell med definitionen nedan för CREATE-kommandot.
CREATE TABLE employee(id INT PRIMARY KEY NOT NULL, name VARCHAR(100), address VARCHAR(100), age INT, dob DATE, deptId INT);Här har vi en kolumnavgränsning men ingen utländsk nyckelbegränsning. I det här fallet, även utan att ha en avdelningstabell, kan vi ange några värden när vi infogar poster.
Anta nu att vi har en separat avdelningstabell och vi vill länka avdelningen där som UTLÄNDSK NYCKEL till anställdstabellen.
ALTER TABLE employee ADD CONSTRAINT depIdFk FOREIGN KEY (deptId) REFERENCES department(departmentId) ON UPDATE CASCADE ON DELETE CASCADE; Vad händer om den här tabellen har befintliga data? Kan vi ändra tabellen och lägga till utländska nyckelbegränsningar?
Svaret är ja - vi kan med villkoret att de befintliga värdena i kolumnen som ska refereras från en annan tabell ska ha de värden som finns i själva modertabellen.
Skapa en anställdstabell utan utländsk nyckelbegränsning, lägg till lite data och försök lägga till en utländsk nyckelbegränsning med ALTER-kommandot.
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); Skapa en avdelningstabell och lägg till UTLÄNDSK NYCKEL mot fältet 'deptId' i tabellen Anställda enligt nedan:
CREATE TABLE department(departmentId INT PRIMARY KEY NOT NULL DEFAULT 1000, departmentName VARCHAR(100));Vid denna punkt, om vi försöker lägga till utländsk nyckelbegränsning,
ALTER TABLE employee ADD CONSTRAINT depIdFk FOREIGN KEY (deptId) REFERENCES department(departmentId) ON UPDATE CASCADE ON DELETE CASCADE; Då får vi ett fel, eftersom anställdstabellen innehåller vissa data men referensintegritetsbegränsningen inte kan uppfyllas eftersom avdelningstabellen saknar data ännu.
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)För att ha begränsningen för utländsk nyckel måste vi först lägga till data i avdelningstabellen. Låt oss infoga de obligatoriska posterna i avdelningstabellen.
INSERT INTO department VALUES (1, 'ENGINEERING'),(10,'ACCOUNTING');Lägg till utrikesnyckelbegränsningen igen genom att köra samma ALTER TABLE-sats. Du kommer att märka att den här gången är kommandot framgångsrikt och anställdstabellen uppdateras framgångsrikt så att den har deptId som UTLÄNDSK NYCKEL från avdelningstabellen.
Släppa en utländsk nyckelbegränsning
På samma sätt som att lägga till en utländsk nyckelbegränsning är det också möjligt att släppa / ta bort en befintlig utländsk nyckelbegränsning från en tabell.
Detta kan uppnås med ALTER TABLE-kommandot.
Syntax:
ALTER TABLE {childTable} DROP FOREIGN KEY {foreign key constraint name}; Här är 'childTable' namnet på tabellen som har definierat utländsk nyckelbegränsning, medan 'främmande nyckelbegränsningsnamn' är namnet / symbolen som användes för att definiera den UTLÄNDSKA KEY.
Låt oss se ett exempel med tabellen Anställd / avdelning. För att släppa en begränsning med namnet 'depIdFk' från tabellen Anställd, använd kommandot nedan:
ALTER TABLE employee DROP FOREIGN KEY depIdFk;Vanliga frågor
F # 1) Hur kan jag byta främmande nycklar i MySQL?
Svar: FOREGIN KEY kan läggas till / tas bort med kommandot ALTER TABLE.
För att ändra eller lägga till en ny UTLÄNDSK NYCKEL, kan du använda ALTER-kommandot och definiera UTLÄNDSK NYCKEL och referenstabellkolumn som refereras till från underordnade tabellen.
F # 2) Hur ställer jag in flera främmande nycklar i MySQL?
Svar: En tabell i MySQL kan ha flera utländska nycklar, vilket kan bero på samma överordnade tabell eller olika överordnade tabeller.
Låt oss använda tabellen Anställd / avdelning och lägga till UTLÄNDSK KEY för avdelningsnamn såväl som DepartmentId i tabellen Anställda.
Se CREATE uttalanden för båda tabellerna enligt nedan
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); F # 3) Hur inaktiverar du begränsningar för främmande nycklar i MySQL?
Svar: UTLÄNDSKA NYCKELBEGRÄNSNINGAR krävs vanligtvis när någon försöker trunka en befintlig tabell som det hänvisas till. För att göra det kan du använda kommandot nedan:
SET FOREIGN_KEY_CHECKS=0;Detta skulle ställa in en sessionsvariabel och tillfälligt inaktivera FOREIGN_KEY_CHECKS. Efter denna inställning kan du fortsätta och utföra raderingar / avkortningar, vilket annars inte varit möjligt.
Men se till att detta är ett administratörsbehörighet och bör användas på ett klokt sätt.
F # 4) Hur hittar jag de främmande nyckelreferenser för en tabell i MySQL?
Svar: För att lista upp alla utländska nyckelbegränsningar som finns kan du använda tabellen ”INNODB_FOREIGN_COLS” i ”INFORMATION_SCHEMA”.
Kör bara kommandot nedan för att få alla utländska nyckeldeklarationer som finns för en given MySQL-serverinstans.
| ID | FOR_COL_NAME | REF_COL_NAME | POS |
|---|---|---|---|
| my_sql_foreign_key / depIdFk | avd | avdelningId | 1 |
F # 5) Bör den refererade kolumnen som UTLÄNDSK NYCKEL vara en primär nyckel i den refererade tabellen?
Svar: Per definition av UTLÄNDSK NYCKEL krävs det att kolumnen som refereras till UTLÄNDSK NYCKEL ska vara PRIMÄR NYCKEL från tabellen där den refereras.
Men med de nyare versionerna av MySQL och med InnoDB-databasmotorn kan du också referera till en kolumn som har UTLÄNDSK NYCKEL som har en UNIK begränsning och kanske inte nödvändigtvis är PRIMÄR NYCKEL.
F # 6) Skapar UTLÄNDSK NYCKEL INDEX i MySQL?
Svar: För både primär nyckel och unik begränsning skapar MySQL automatiskt en INDEX för sådana kolumner.
Eftersom vi redan vet att utländska nyckelreferenser endast kan tillämpas på endera kolumnen som är primära nycklar eller kolumner som har unika värden, har därför alla kolumner som kallas utländska nycklar skapat ett index mot dem.
För att se indexet på en tabell använder du kommandot nedan:
SHOW INDEX from {dbName.tableName};Så för vårt anställd / avdelningsexempel hade vi lagt till avdelning i anställd som en UTLÄNDSK NYCKEL från avdelningstabellen.
Låt oss se de skapade indexen i tabellerna för anställda och avdelningar.
USE my_sql_foreign_key; SHOW INDEX from employee; | Tabell | Icke-unikt | Nyckelnamn | Seq_in_index | Kolumnnamn | Sortering | Kardinalitet | Underdel | Packade | Null | Index_type |
|---|---|---|---|---|---|---|---|---|---|---|
| anställd | 0 | PRIMÄR | 1 | id | TILL | 0 | NULL | NULL | BTREE | |
| anställd | 1 | depIdFk | 1 | avd | TILL | 0 | NULL | NULL | JA | BTREE |
Du kan se två index - ett är den primära nyckeln för tabellen Anställd och ett annat är för UTLÄNDSK NYTTBILD som refereras från avdelningstabellen.
SHOW INDEX from department;| Tabell | Icke-unikt | Nyckelnamn | Seq_in_index | Kolumnnamn | Sortering | Kardinalitet | Underdel | Packade | Null | Index_type |
|---|---|---|---|---|---|---|---|---|---|---|
| avdelning | 0 | PRIMÄR | 1 | avdelningId | TILL | 0 | NULL | NULL | BTREE |
Här kan du se att för avdelningstabellen har vi bara 1 index för primär nyckel (som kallas UTLÄNDSK NYCKEL i anställdstabellen).
F # 7) Kan UTLÄNDSK NYCKEL vara NULL i MySQL?
Svar: Ja, det är helt ok att ha NULL för kolumnen som har ett UTLÄNDSKT-beroende på ett annat bord. Detta antyder också att NULL inte är ett verkligt värde och därför inte matchas / jämförs med värden i modertabellen.
Slutsats
I den här handledningen lärde vi oss om olika begrepp relaterade till användning av utländska nycklar i MySQL-databaser.
UTLÄNDSK NYCKEL underlättar uppdateringar och raderingar med lämpliga begränsningar, men ibland kan det vara svårt att ha många sådana förhållanden under hela processen för insättning och / eller radering.
Vi lärde oss hur man skapar utländska nycklar och hur vi kan uppdatera och släppa en befintlig utländsk nyckel från underordnade tabellen. Vi lärde oss också om olika referensintegritetsåtgärder och hur vi kan uppnå olika beteenden med hjälp av de olika tillgängliga alternativen som CASCADE, NO ACTION, SET NULL, etc.
Rekommenderad läsning
- MySQL Skapa tabellhandledning med exempel
- MySQL Insert In Table - Insert Statement Syntax & Exempel
- MySQL Skapa vyhandledning med kodexempel
- MySQL CONCAT och GROUP_CONCAT-funktioner med exempel
- MySQL-transaktionshandledning med programmeringsexempel
- MySQL UNION - Omfattande handledning med fackliga exempel
- Hur man laddar ner MySQL för Windows och Mac
- Skillnad mellan SQL Vs MySQL Vs SQL Server (med exempel)