Basisprincipes van MySQL FOREIGN KEY Beperking met voorbeelden

basics mysql foreign key constraint with examples

Deze tutorial legt de basisprincipes van MySQL FOREIGN KEY Constraint uit, zoals de syntaxis, hoe je deze kunt toevoegen, declareren, verwijderen en wijzigen met voorbeelden:

In zeer eenvoudige bewoordingen wordt de FOREIGN KEY gebruikt om twee of meer tabellen in MySQL te koppelen.

MySQL-tabellen moeten worden verbonden om verschillende soorten gegevens op verschillende tijdstippen op te vragen en bij te werken. Daarom is het absoluut noodzakelijk om een ​​verbindingspunt tussen 2 tafels te hebben.

In deze tutorial bespreken we de verschillende manieren waarop externe sleutels worden gebruikt en hoe deze kunnen worden gedeclareerd en gewijzigd, en welke beperkingen het heeft op de algehele tabelstructuur.

Wat je leert:

• MySQL BUITENLANDSE SLEUTEL
  • Referentiële integriteitsacties
    • # 1) CASCADE
    • # 2) BEPERKEN / GEEN ACTIE
    • # 3) NULL INSTELLEN
    • # 4) STANDAARD INSTELLEN
  • Voeg een FOREIGN KEY-beperking toe met behulp van de ALTER TABLE-instructie
  • Een BUITENLANDSE SLEUTEL beperking laten vallen
  • Veel Gestelde Vragen
• Gevolgtrekking
  • Aanbevolen literatuur

MySQL BUITENLANDSE SLEUTEL

Syntaxis:

Hierboven staat de syntaxis die wordt gebruikt bij het specificeren van FOREIGN KEY voor een tabel tijdens het maken van een tabel of met ALTER TABLE-instructie.

interviewvragen over angularjs voor ervaren

Laten we de verschillende componenten van de syntaxis eens begrijpen:

• constrantName: Dit is de symbolische naam die we willen definiëren voor de FK-beperking die wordt gespecificeerd. Als dit wordt overgeslagen, wijst de MySQL-engine automatisch een naam toe aan de FK-beperking.
• referringColumnName: Dit is de kolom die verwijst naar de waarden in een andere tabel zoals gespecificeerd door de kolom in de tabel waarnaar wordt verwezen.
• Verwezen tafel / bovenliggende tafel: Dit verwijst naar de naam van de tabel waaruit de waarden zouden worden verwezen.
• Doorverwezen kolom: De kolomnaam in de tabel waarnaar wordt verwezen.
  
• Referentie optie: Dit zijn de acties die in beeld komen wanneer er een update- of verwijderactie wordt uitgevoerd op de tafel die de externe sleutelbeperking bevat. Zowel UPDATE als DELETE kunnen dezelfde of verschillende referentie-opties hebben.

Later in deze tutorial zouden we meer leren over verschillende acties voor referentiële integriteit.

Laten we eens kijken naar een voorbeeld van een verwijzing naar een BUITENLANDSE SLEUTEL met behulp van het voorbeeld Werknemer / Afdeling. We zullen een tabel Department maken met kolommen - departmentId (int & PRIMARY KEY) en departmentName (varchar).

Maak een tabelmedewerker met kolommen zoals hieronder:

Zoals u kunt zien, hebben we in de bovenstaande Medewerkerstabel de kolom departementId van het type Int gedeclareerd en de BUITENLANDSE SLEUTEL gedefinieerd in de tabel Afdeling in de kolom departementId.

Wat dit in wezen betekent dat de kolom departmentId in de tabel Employee alleen waarden kan bevatten die in de Department-tabel staan.

Laten we proberen om gegevens in deze tabellen in te voegen en kijken hoe FOREIGN KEY BEPERKING werkt.

• Maak eerst een record in de Afdelingstabel en voeg een record toe aan de Werknemer-tabel die verwijst naar de ID van het record dat aan de Afdelingstabel is toegevoegd.

U zult zien dat beide instructies zonder fouten worden uitgevoerd.

• Verwijs nu naar een waarde voor departmentId die niet bestaat.
  Bijvoorbeeld, in de onderstaande vraaginstructie maken we een werknemer met een niet-bestaande departmentId -10

• In dit scenario krijgen we een foutmelding zoals hieronder:

Dus, in het algemeen, wanneer FOREIGN KEY References worden gedefinieerd, is het belangrijk om ervoor te zorgen dat de tabel waarnaar wordt verwezen, gegevens moet hebben voordat er naar wordt verwezen.

Referentiële integriteitsacties

Laten we eerst proberen te begrijpen wat referentiële integriteit precies is.

Referentiële integriteit helpt om gegevens in een schone en consistente staat te houden waar er tabellen zijn die aan elkaar gerelateerd zijn met een BUITENLANDSE SLEUTEL relatie.

Simpel gezegd, referentiële integriteit verwijst naar de actie die we van de database-engine verwachten, wanneer een UPDATE of DELETE plaatsvindt in de tabel waarnaar wordt verwezen en die de FOREIGN KEY bevat.

Bijvoorbeeld, Stel in ons voorbeeld Werknemer / Afdeling dat we de Afdelings-ID voor een bepaalde rij in DB wijzigen. Dan zouden alle verwijzende rijen in de tabel Werknemer worden beïnvloed. We kunnen verschillende soorten referentiële integriteitscenario's definiëren die in dergelijke gevallen kunnen worden toegepast.

Notitie: Referentiële integriteit wordt gedefinieerd tijdens het instellen / declareren van FOREIGN KEY als onderdeel van de ON DELETE en ON UPDATE commando's / secties.

Raadpleeg hier een voorbeeldquery (voor het voorbeeld van de medewerker / afdeling):

Voeg enkele gegevens toe aan deze tabellen, zoals hieronder:

Er zijn 4 referentieacties die worden ondersteund door MySQL. Laten we proberen ze allemaal te begrijpen.

# 1) CASCADE

Dit is een van de meest gebruikte Referentiële Integriteitsacties. Als u DELETE en UPDATE instelt op CASCADE, worden de aangebrachte wijzigingen toegepast op de tabel waarnaar wordt verwezen in de referentietabel, d.w.z. in het voorbeeld Werknemer / Afdeling. Stel dat iemand een rij verwijdert in de Afdelingstabel door afdelingsnaam = ACCOUNTING te zeggen, dan worden alle rijen in de Werknemerstabel met afdeling_id als die van de Boekhoudingstabel ook verwijderd.

Laten we dit met een voorbeeld begrijpen:

Verwijder record uit de afdelingtabel waar departmentName = ’ACCOUNTING’

Aangezien het een CASCADE-verwijzingsactie is, verwachten we dat alle rijen met departmentID = 2 (wat voor ‘ACCOUNTING’ -afdeling is) ook worden verwijderd. Laten we opnieuw een SELECT-query uitvoeren op de tabel Werknemer.

Zoals u hierboven kunt zien, zullen vanwege de CASCADE-referentiële integriteit, de rijen in de Werknemer-tabel die verwijzen naar de verwijderde kolom als BUITENLANDSE SLEUTEL deze rijen laten verwijderen.

# 2) BEPERKEN / GEEN ACTIE

De modus RESTRICT of NO ACTION staat geen UPDATE- of DELETE-bewerkingen toe op de tabel met kolommen waarnaar in een bepaalde tabel wordt verwezen als FOREIGN KEY.
De NO ACTION-modus kan worden toegepast door simpelweg de clausules ON UPDATE en ON DELETE uit de tabelverklaring weg te laten.

Laten we hetzelfde voorbeeld proberen en in dit geval gewoon de actie ON UPDATE en ON DELETE Referentiële integriteit overslaan.
Wanneer we nu een item in de tabel waarnaar wordt verwezen, proberen te verwijderen, krijgen we een foutmelding omdat we de verwijzingsactie hebben ingesteld op RESTRICT

U ziet een fout zoals hieronder als u de bovenstaande DELETE-opdracht probeert uit te voeren.

# 3) NULL INSTELLEN

Met SET NULL zorgt elke UPDATE of DELETE in de tabel waarnaar wordt verwezen ervoor dat een NULL-waarde wordt bijgewerkt ten opzichte van de kolomwaarde die in de referentietabel is gemarkeerd als een FOREIGN KEY.

Met deze referentiële integriteitsactie zou de definitie van de Medewerkerstabel er als volgt uitzien:

Verwijder een rij in de tabel waarnaar wordt verwezen, zoals hieronder wordt weergegeven:

In dit geval zou de waarde waarnaar in de tabel Werknemer wordt verwezen, worden ingesteld op NULL. Voer een SELECT-query uit in de tabel Werknemer om de resultaten te zien.

# 4) STANDAARD INSTELLEN

SET DEFAULT-modus wanneer gespecificeerd zou resulteren in het vervangen van de standaardwaarde voor de kolom (zoals gespecificeerd tijdens de kolomdeclaratie), in het geval dat er naar DELETES in de tabel wordt verwezen.

Notitie - Vanaf MySQL-documentatie wordt de optie SET DEFAULT ondersteund door MySQL Parser, maar niet door DB-engines zoals InnoDB. Dit kan in de toekomst worden ondersteund.

Om dergelijk gedrag echter te ondersteunen, kunt u overwegen SET NULL te gebruiken en een trigger op de tabel te definiëren die een standaardwaarde kan instellen.

Voeg een FOREIGN KEY-beperking toe met behulp van de ALTER TABLE-instructie

Vaak kan het gebeuren dat we een FOREIGN KEY-beperking willen toevoegen aan een bestaande tabel die deze niet heeft.

Stel dat we in het voorbeeld Werknemer en Afdeling een werknemerstabel hebben gemaakt zonder enige beperking van de BUITENLANDSE SLEUTEL en later willen we de beperking introduceren. Dit kan worden bereikt met het ALTER TABLE-commando.

Laten we dit proberen te begrijpen met een voorbeeld.

Stel dat we een werknemerstabel hebben met de onderstaande definitie voor de opdracht CREATE.

Hier hebben we een kolom afd.Id maar geen beperking FOREIGN KEY. In dit geval kunnen we, zelfs zonder een Afdelingstabel, alle waarden specificeren tijdens het invoegen van records.

Veronderstel nu dat we een aparte Afdelingstabel hebben en dat we afdelingId daar als BUITENLANDSE SLEUTEL willen koppelen aan de Werknemer-tabel.

Wat moet ik doen als deze tabel bestaande gegevens bevat? Kunnen we de tabel VERANDEREN en een beperking van de BUITENLANDSE SLEUTEL toevoegen?

Het antwoord is ja - dat kunnen we met de voorwaarde dat de bestaande waarden in de kolom waarnaar vanuit een andere tabel wordt verwezen, dezelfde waarden moeten hebben in de bovenliggende tabel zelf.

Maak een Employee-tabel zonder de FOREIGN KEY-beperking, voeg wat gegevens toe en probeer een FOREIGN KEY-beperking toe te voegen met de ALTER-opdracht.

Maak een afdelingstabel en voeg BUITENLANDSE SLEUTEL toe aan het veld ‘afdelingId’ in de tabel Werknemer, zoals hieronder weergegeven:

Als we op dit punt proberen een beperking van de BUITENLANDSE SLEUTEL toe te voegen,

Dan krijgen we een foutmelding, aangezien de Medewerkerstabel enkele gegevens bevat, maar er niet kan worden voldaan aan de referentiële integriteitsbeperking aangezien de Afdelingstabel nog geen gegevens bevat.

Om de beperking FOREIGN KEY te hebben, moeten we eerst gegevens toevoegen aan de Department-tabel. Laten we de vereiste records invoegen in de Afdelingstabel.

SQL server interviewvragen en antwoorden voor 5 jaar ervaring

Voeg de FOREIGN KEY-beperking opnieuw toe door dezelfde ALTER TABLE-instructie uit te voeren. U zult merken dat de opdracht deze keer is gelukt en dat de tabel Werknemer met succes is bijgewerkt zodat afdId als BUITENLANDSE SLEUTEL uit de Afdelingstabel is gehaald.

Een BUITENLANDSE SLEUTEL beperking laten vallen

Net als bij het toevoegen van een FOREIGN KEY-beperking, is het ook mogelijk om een ​​bestaande FOREIGN KEY-beperking uit een tabel te verwijderen / verwijderen.

Dit kan worden bereikt met de opdracht ALTER TABLE.

Syntaxis:

Hier is ‘childTable’ de naam van de tabel waarvoor de beperking FOREIGN KEY is gedefinieerd, terwijl de ‘naam van de beperking van de externe sleutel’ de naam / het symbool is dat werd gebruikt om de FOREIGN KEY te definiëren.

Laten we een voorbeeld bekijken met behulp van de tabel Werknemer / Afdeling. Gebruik de onderstaande opdracht om een ​​beperking met de naam ‘depIdFk’ uit de tabel Werknemer te verwijderen:

Veel Gestelde Vragen

V # 1) Hoe kan ik externe sleutels in MySQL wijzigen?

Antwoord: FOREGIN KEY kan worden toegevoegd / verwijderd met het ALTER TABLE commando.
Om een ​​nieuwe FOREIGN KEY te wijzigen of toe te voegen, kunt u het ALTER-commando gebruiken en de FOREIGN KEY en referentietabelkolom definiëren waarnaar zou worden verwezen vanuit de onderliggende tabel.

Vraag 2) Hoe stel ik meerdere externe sleutels in MySQL in?

Antwoord: Een tabel in MySQL kan meerdere FOREIGN KEYS hebben, die kunnen afhangen van dezelfde bovenliggende tabel of verschillende bovenliggende tabellen.

Laten we de tabel Werknemer / Afdeling gebruiken en BUITENLANDSE SLEUTEL toevoegen voor Afdelingsnaam en Afdeling-ID in de Werknemer-tabel.

Raadpleeg de CREATE-instructies van beide tabellen zoals hieronder

V # 3) Hoe kunnen externe sleutelbeperkingen in MySQL worden uitgeschakeld?

Antwoord: FOREIGN KEY-beperkingen zijn meestal vereist wanneer iemand probeert een bestaande tabel waarnaar wordt verwezen, af te kappen. Om dat te doen, kunt u de onderstaande opdracht gebruiken:

Dit zou een sessievariabele instellen en tijdelijk de FOREIGN_KEY_CHECKS uitschakelen. Na deze instelling kunt u doorgaan en verwijderingen / afkappen uitvoeren, wat anders niet mogelijk zou zijn geweest.

Maar zorg ervoor dat dit een admin-privilege is en oordeelkundig moet worden gebruikt.

V # 4) Hoe vind ik de referentiesleutel voor een tabel in MySQL?

Antwoord: Om alle aanwezige FOREIGN KEY-beperkingen op te sommen, kunt u de ‘INNODB_FOREIGN_COLS’ tabel in ‘INFORMATION_SCHEMA` gebruiken.

Voer gewoon de onderstaande opdracht uit om alle FOREIGN KEY-declaraties op te halen voor een gegeven MySQL-serverinstantie.

V # 5) Moet de kolom waarnaar wordt verwezen als BUITENLANDSE SLEUTEL een primaire sleutel zijn in de tabel waarnaar wordt verwezen?

Antwoord: Per definitie van BUITENLANDSE SLEUTEL, zou het vereist zijn dat de kolom waarnaar wordt verwezen als BUITENLANDSE SLEUTEL de PRIMAIRE SLEUTEL moet zijn van de tabel waarnaar wordt verwezen.
Met de nieuwere versies van MySQL en met de InnoDB-database-engine kunt u echter ook verwijzen naar een kolom met FOREIGN KEY die een UNIEKE beperking heeft en niet noodzakelijk PRIMAIRE SLEUTEL hoeft te zijn.

V # 6) Creëert FOREIGN KEY INDEX in MySQL?

Antwoord: Voor zowel primaire sleutel als unieke beperking maakt MySQL automatisch een INDEX voor dergelijke kolommen.

Omdat we al weten dat verwijzingen naar BUITENLANDSE SLEUTEL alleen kunnen worden toegepast op kolommen die Primaire sleutels zijn of op kolommen met Unieke waarden, hebben alle kolommen die worden aangeduid als BUITENLANDSE SLEUTEL een index die er tegen is gemaakt.

Gebruik de onderstaande opdracht om de index op een tabel te bekijken:

Voor ons voorbeeld Werknemer / Afdeling hadden we dus afdelingId in Werknemer toegevoegd als een BUITENLANDSE SLEUTEL uit de Afdelingstabel.

Laten we eens kijken naar de gemaakte indexen in de tabellen voor medewerkers en afdelingen.

U kunt 2 indexen zien - een is de primaire sleutel voor de Werknemer-tabel en een andere is voor FOREIGN KEY depId waarnaar wordt verwezen vanuit de Afdelingstabel.

Hier kunt u zien dat we voor de afdelingstabel slechts 1 index hebben voor de primaire sleutel (waarnaar wordt verwezen als BUITENLANDSE SLEUTEL in de tabel Werknemer).

V # 7) Kan de BUITENLANDSE SLEUTEL NULL zijn in MySQL?

Antwoord: Ja, het is prima om NULL te hebben voor de kolom die een FOREIGN KEY-afhankelijkheid heeft van een andere tabel. Dit verwijst ook naar het feit dat NULL geen echte waarde is en daarom niet wordt vergeleken met waarden in de bovenliggende tabel.

Gevolgtrekking

In deze tutorial hebben we geleerd over verschillende concepten met betrekking tot het gebruik van FOREIGN KEYS in MySQL-databases.

FOREIGN KEY vereenvoudigt het bijwerken en verwijderen met de juiste beperkingen, maar soms kan het hebben van veel van dergelijke relaties het hele proces van invoegen en / of verwijderen behoorlijk omslachtig maken.

We hebben geleerd hoe we BUITENLANDSE SLEUTELS kunnen maken en hoe we een bestaande BUITENLANDSE SLEUTEL kunnen bijwerken en verwijderen uit de onderliggende tabel. We leerden ook over verschillende acties voor referentiële integriteit en hoe we ander gedrag kunnen bereiken met behulp van de verschillende beschikbare opties zoals CASCADE, NO ACTION, SET NULL, enz.

Aanbevolen literatuur

• MySQL Create Table Tutorial met voorbeelden
• MySQL invoegen in tabel - Verklaring syntaxis en voorbeelden invoegen
• MySQL Create Bekijk zelfstudie met codevoorbeelden
• MySQL CONCAT- en GROUP_CONCAT-functies met voorbeelden
• MySQL-transactiehandleiding met programmeervoorbeelden
• MySQL UNION - Uitgebreide tutorial met Union-voorbeelden
• Hoe MySQL voor Windows en Mac te downloaden
• Verschil tussen SQL versus MySQL versus SQL Server (met voorbeelden)