MySQL foreign keys en relaties uitleggen en instellen

Een foreign key legt een relatie vast tussen twee tabellen in een MySQL-database, zodat gegevens consistent blijven. Bijvoorbeeld: een bestelling moet altijd horen bij een bestaande klant. In dit artikel leggen we uit wat foreign keys zijn, hoe je ze aanmaakt, welke storage engine je nodig hebt en hoe je veelvoorkomende fouten oplost.

Wat is een foreign key?

Een foreign key is een kolom in een tabel die verwijst naar de primary key van een andere tabel. Hiermee leg je een relatie vast tussen tabellen, bijvoorbeeld tussen klanten en bestellingen. MySQL controleert bij elke invoer, update of verwijdering of de relatie geldig blijft, dit heet referentiële integriteit.

Foreign keys werken alleen met de InnoDB storage engine. MyISAM ondersteunt geen foreign keys, dus controleer bij het aanmaken van een tabel altijd of InnoDB is geselecteerd.

Foreign key aanmaken met SQL

De meest betrouwbare manier om een foreign key toe te voegen is via SQL. Hieronder een voorbeeld waarbij de tabel bestellingen verwijst naar de tabel klanten:

CREATE TABLE klanten (
 id INT AUTO_INCREMENT PRIMARY KEY,
 naam VARCHAR(100)
) ENGINE=InnoDB;

CREATE TABLE bestellingen (
 id INT AUTO_INCREMENT PRIMARY KEY,
 klant_id INT,
 product VARCHAR(100),
 FOREIGN KEY (klant_id) REFERENCES klanten(id)
 ON DELETE CASCADE
 ON UPDATE CASCADE
) ENGINE=InnoDB;

De opties ON DELETE en ON UPDATE bepalen wat er gebeurt als de gekoppelde rij wordt verwijderd of aangepast. CASCADE verwijdert of past gerelateerde rijen automatisch aan, SET NULL zet de waarde op leeg en RESTRICT blokkeert de actie zolang er nog gekoppelde rijen bestaan.

Foreign keys beheren via phpMyAdmin

Heb je liever een visuele interface, dan kun je foreign keys ook instellen via phpMyAdmin, dat standaard beschikbaar is in Plesk. Volg onderstaande stappen om een relatie toe te voegen aan een bestaande tabel.

Veelvoorkomende fouten en oplossingen

Foreign keys leveren regelmatig foutmeldingen op, vooral bij bestaande databases. Hieronder de meest voorkomende oorzaken.

Errno 150: dit betekent meestal dat het datatype van beide kolommen niet exact overeenkomt, of dat de doeltabel geen InnoDB gebruikt. Controleer of beide kolommen bijvoorbeeld allebei INT zijn, met dezelfde lengte en signed/unsigned instelling.

Cannot delete or update a parent row: deze melding verschijnt als je een rij probeert te verwijderen waar nog gekoppelde rijen naar verwijzen, terwijl de regel op RESTRICT staat. Verwijder eerst de gekoppelde rijen of pas de ON DELETE regel aan.

Foreign key constraint is incorrectly formed: controleer of de kolom waarnaar je verwijst daadwerkelijk een primary key of unieke index heeft. Zonder index kan MySQL geen relatie afdwingen.

Loop je vast met database- of serverinstellingen, raadpleeg dan de kennisbank of neem contact op met support. Overweeg je over te stappen naar een hostingpartij met betere database-ondersteuning, bekijk dan de mogelijkheden om over te stappen naar Tandata.