Kenmerken van beperkingen voor externe sleutels:
Hieronder worden enkele belangrijke kenmerken van externe sleutelbeperkingen uitgelegd.
- Het gegevenstype van de externe sleutel die in de onderliggende tabel wordt gebruikt, moet hetzelfde zijn als het gegevenstype van de primaire sleutel die in de bovenliggende tabel wordt gebruikt om naar de externe sleutel te verwijzen.
- Er kan alleen naar elke indexkolom of meerdere kolommen worden verwezen als een externe sleutel voor de InnoDB-tabel.
- Verwijzingsprivileges of ten minste een van de privileges van de SELECT-, INSERT-, UPDATE- en DELETE-instructies zijn vereist om een externe sleutel te maken.
- Een refererende sleutel kan op twee manieren worden aangemaakt. Een met de instructie CREATE en een andere met de instructie ALTER.
Voorwaarde:
Voordat u een externe-sleutelbeperking maakt, moet u een database en bovenliggende tabel maken met de primaire sleutel. Stel dat de databasenaam is ‘ bibliotheek ' en het bevat twee bovenliggende tabellen met de naam ' boeken ' en ' lener ’. Maak verbinding met de MySQL-server met behulp van de mysql client en voer de volgende SQL-instructies uit om de database en de tabellen te maken.
CREËREN DATABASE bibliotheek;
GEBRUIK MAKEN VAN bibliotheek;
CREËREN TAFEL boeken(
ID kaart INT NIET NUL AUTO_INCREMENT ,
titel varchar (vijftig) NIET NUL ,
auteur varchar (vijftig) NIET NUL ,
uitgeverij varchar (vijftig) NIET NUL ,
HOOFDSLEUTEL (ID kaart)
) MOTOR = INNODB ;
CREËREN TAFEL leners(
ID kaart VARCHAR (vijftig) NIET NUL ,
naam varchar (vijftig) NIET NUL ,
adres varchar (vijftig) NIET NUL ,
e-mail varchar (vijftig) NIET NUL ,
HOOFDSLEUTEL (ID kaart)
) MOTOR = INNODB ;
Definieer Foreign Key Constraint met behulp van de CREATE-instructie
Maak een tabel met de naam ' book_borrow_info ' met externe sleutelbeperkingen door de volgende instructie uit te voeren. Hier de book_id veld is een vreemde sleutel voor deze tabel en elke waarde van dit veld moet bestaan in de ID kaart gebied van boeken tafel. boeken is de bovenliggende tabel en book_borrow_info is de kindertafel. Hier worden ook twee beperkingen ingesteld met de externe sleutel. Dit zijn CASCADE VERWIJDEREN en UPDATE CASCADE . Dat betekent dat als een primaire sleutel uit de bovenliggende tabel wordt verwijderd of bijgewerkt, de bijbehorende records met betrekking tot de onderliggende tabel met betrekking tot de externe sleutel worden verwijderd of dat de externe sleutel wordt bijgewerkt.
CREËREN TAFEL book_borrow_info(
lening_id VARCHAR (vijftig),
book_id INT ,
lening_datum DATUM NIET NUL ,
retourdatum DATUM NIET NUL ,
toestand VARCHAR (vijftien) NIET NUL ,
INHOUDSOPGAVE par_ind(book_id),
HOOFDSLEUTEL (lening_id,lening_datum),
VREEMDE SLEUTEL (book_id) REFERENTIES boeken(ID kaart)
AAN VERWIJDEREN CASCADE
AAN UPDATE CASCADE
) MOTOR = INNODB ;
Voer nu de volgende SQL-instructies uit om enkele records in beide tabellen in te voegen. De eerste INSERT-instructie zal vier records invoegen in: boeken tafel. De vier waarden van ID kaart gebied van boeken tabel 1, 2, 3 en 4 zijn voor het attribuut auto-increment. De tweede INSERT-instructie zal vier records invoegen in book_borrow_info gebaseerd op de ID kaart waarde van boeken tafel.
INSERT NAAR BINNEN boeken WAARDEN
( NUL , 'Om een spotvogel te doden', 'Harper Lee', 'Grand Central Publishing'),
( NUL , 'Honderd jaar eenzaamheid', 'Garcia Márquez', 'Lutfi Ozkok'),
( NUL , 'Een doorgang naar India', 'Forster, E.M.', 'BBC Hulton Fotobibliotheek'),
( NUL , 'Onzichtbare man', 'Ralph Ellison', 'Encyclopedia Britannica, Inc.');
INSERT NAAR BINNEN book_borrow_info WAARDEN
('123490', 1, '2020-02-15', '2020-02-25', 'Teruggekeerd'),
('157643', 2, '2020-03-31', '2020-03-10', 'In afwachting'),
('174562', 4, '2020-04-04', '2020-04-24', 'geleend'),
('146788', 3, '2020-04-10', '2020-01-20', 'geleend');
Als u een waarde probeert in te voegen in het externe sleutelveld van de onderliggende tabel die niet bestaat in het primaire sleutelveld van de bovenliggende tabel, dan zal MySQL een fout genereren. De volgende SQL-instructie genereert een fout omdat de bovenliggende tabel, boeken bevat geen id-waarde 10 .
INSERT NAAR BINNEN book_borrow_info WAARDEN('195684', 10, '2020-04-15', '2020-04-30', 'Teruggekeerd');
Na het uitvoeren van de volgende DELETE-instructie, wanneer het vierde record wordt verwijderd uit de boeken tabel en vervolgens de gerelateerde records uit de book_borrow_info tabel wordt automatisch verwijderd vanwege de externe sleutelbeperking.
VERWIJDEREN VAN boeken WAAR ID kaart= 4;KIES * van boeken;
KIES * van book_borrow_info;
Definieer Foreign Key Constraint met behulp van de ALTER-instructie
Voeg eerst enkele records in leners tabel en deze tabel zal in de volgende worden gedefinieerd als bovenliggende tabel LEEFTIJD uitspraak.
INSERT NAAR BINNEN leners WAARDEN('123490', 'Patrick Hout', '34 West Street LANCASTER LA14 9ZH', ' [e-mail beveiligd]'),
('157643', 'Ezra Martin', '10 The Grove BIRMINGHAM B98 1EU', ' [e-mail beveiligd]'),
('174562', 'John Innes Archie', '55 Hoofdweg LIVERPOOL L2 3OD', ' [e-mail beveiligd]'),
('146788', 'Frederik Hanson', '85 Highfield Road SHREWSBURY SY46 3ME', ' [e-mail beveiligd]');
Voer het volgende uit: LEEFTIJD statement om een andere externe sleutelbeperking in te stellen voor book_borrow_info tafel om de relatie mee te maken leners tafel. Hier, lening_id wordt gedefinieerd als een externe sleutel voor book_borrow_info tafel.
ALTER TABLE book_borrow_info ADD CONSTRAINT fk_borrowerVREEMDE SLEUTEL(lening_id)REFERENTIES leners(ID kaart)OP VERWIJDEREN CASCADE OP UPDATE BEPERKEN;
Voeg nu een record in book_borrow_info met geldige lening_id waarde die bestaat in ID kaart gebied van leners tafel. 157643 waarde bestaat in de lenerstabel en het volgende INSERT-statement wordt met succes uitgevoerd.
INSERT NAAR BINNEN book_borrow_info WAARDEN('157643', 1, '2020-03-10', '2020-03-20', 'Teruggekeerd');
De volgende INSERT-instructie genereert een foutmelding omdat de id-waarde 195680 bestaat niet in de lenerstabel.
INSERT NAAR BINNEN book_borrow_info WAARDEN('195680', 1, '2020-04-15', '2020-04-30', 'Teruggekeerd');
Conclusie:
Het correct definiëren van externe-sleutelbeperkingen is een zeer belangrijke taak voor het maken van een relationele database en het op de juiste manier beheren van gegevens tussen de tabellen. Het kennen van het gebruik van externe sleutelbeperkingen is zeer essentieel voor databaseontwerpers. Ik hoop dat dit artikel de nieuwe databaseontwerpers zal helpen het concept van externe sleutelbeperkingen te begrijpen en deze correct toe te passen in hun taken.