Referentiële integriteit

Wat is referentiële integriteit?

Referentiële integriteit is de regel in een database dat een vreemde sleutel altijd moet verwijzen naar een rij die echt bestaat. Zegt een bestelling dat ze bij klant 4712 hoort, dan moet klant 4712 ook in de klantentabel staan. De waarde mag niet wijzen naar een klant die nooit werd aangemaakt of al verwijderd is.

Het is een van de basisgaranties van een relationele database. Een primaire sleutel geeft elke rij een unieke code. Een vreemde sleutel laat de ene tabel naar de andere wijzen. Referentiële integriteit is de belofte dat die verwijzingen nooit in het niets uitkomen.

Vergelijk het met een bibliotheek. Elke uitlening noteert een boek via zijn catalogusnummer. Referentiële integriteit betekent dat je nooit een uitlening kan registreren voor een catalogusnummer dat niet in de catalogus staat.

Wat is een verweesd record?

Een verweesd record is een rij waarvan de vreemde sleutel wijst naar een ouder die er niet meer is. Een bestelregel die verwijst naar bestelling 900, terwijl bestelling 900 niet langer bestaat, is zo'n wees. Het kind bleef staan, de ouder verdween.

Wezen zijn precies het probleem dat referentiële integriteit tegenhoudt. Ze zorgen voor rapporten die niet kloppen, voor joins die stilletjes rijen laten vallen en voor totalen die verschillen naargelang je in welke tabel begint te tellen. Zit een database vol wezen, dan kan je er niet meer op vertrouwen dat een telling van bestelregels overeenkomt met een telling van bestellingen.

Hoe de database het afdwingt

Je legt een vreemde-sleutelbeperking op de kindtabel en benoemt daarbij de oudertabel en de kolom waarmee ze moet overeenkomen. Vanaf dan controleert de database elke schrijfactie.

Die beperking houdt twee soorten fouten tegen. Je kan geen kindrij toevoegen of aanpassen met een sleutelwaarde waar geen ouder bij hoort. En je kan geen ouder verwijderen zolang er nog kinderen naar wijzen, tenzij je de database vooraf vertelt wat er dan wel moet gebeuren.

Die tweede keuze leg je vast met een referentiële actie:

• RESTRICT of NO ACTION. De verwijdering of aanpassing wordt geweigerd zolang er nog een kind naar de ouder verwijst. Voor de meeste bedrijfsdata is dit de veilige standaard.

• CASCADE. Verwijder je de ouder, dan verdwijnen de kinderen mee, en pas je de sleutel van de ouder aan, dan volgen de kinderen. Dat past bij echte ouder-kinddata, zoals een bestelling en haar bestelregels.

• SET NULL. Het kind blijft bestaan, maar zijn vreemde sleutel wordt leeggemaakt. Dat werkt alleen als de kolom leeg mag zijn.

Zo ziet een vreemde sleutel met een referentiële actie eruit in SQL:

CREATE TABLE bestelregel (
  id            INT PRIMARY KEY,
  bestelling_id INT NOT NULL,
  FOREIGN KEY (bestelling_id)
    REFERENCES bestelling (id)
    ON DELETE CASCADE
);

Met deze regel weigert de database zelf een bestelregel voor een bestelling die niet bestaat, en ruimt ze de regels op zodra een bestelling verdwijnt.

Referentiële integriteit in analytische systemen

Operationele databases achter een CRM, een ERP of een webshop leunen zwaar op referentiële integriteit, want een foute verwijzing kan daar een lopende transactie breken. Analytische systemen gaan er anders mee om.

Veel data warehouses en lakehouses dwingen vreemde sleutels helemaal niet af, of ze leggen ze enkel vast als documentatie die de engine nooit controleert. De reden is snelheid: elke vreemde sleutel nakijken tijdens het laden van miljoenen rijen vraagt veel tijd, en meestal is de data stroomopwaarts al gecontroleerd. In Power BI nemen de relaties tussen een feitentabel en zijn dimensies de referentiële rol over. Wijst een feit naar een dimensiewaarde die ontbreekt, dan verschijnt er een lege rij.

De garantie blijft dus belangrijk voor analyse, ze wordt alleen vroeger afgedwongen. Tests op datakwaliteit en controles via datareconciliatie nemen het werk over dat de beperking anders in het bronsysteem zou doen.

Waar moet je op letten bij referentiële integriteit

De laadvolgorde telt. Je moet ouders vóór kinderen invoeren. Eerst alle klanten, dan hun bestellingen. Grote laadopdrachten zetten de controles vaak even uit, laden alles in en zetten de controles daarna weer aan. Dat loopt luid vast als er toch een wees is binnengeslopen.

Cascadeverwijderingen gaan verder dan je denkt. Eén CASCADE kan een volgende in gang zetten. Wie één klant verwijdert, wist misschien meteen al zijn bestellingen, en daarna elke bestelregel onder die bestellingen. Breng de ketting in kaart voor je erop vertrouwt.

Een warehouse erft de wezen van de bron. Dwong het bronsysteem nooit integriteit af, dan komt de rommel intact in je rapporteringslaag terecht. Onderzoek de data en test op wezen in plaats van ervan uit te gaan dat de sleutels netjes kloppen.

Zachte verwijderingen vragen eigen regels. Een ouder markeren als inactief in plaats van hem te verwijderen houdt de vreemde sleutel geldig, maar dan moeten je rapporten wel weten dat ze die inactieve ouders eruit moeten filteren.