Database-integriteitscontrole
Een ander belangrijk ding om te weten over databases is CONSTRAINS. Met behulp van beperkingen kunt u gegevenswijzigingen in uw tabellen controleren en hun integriteit en consistentie behouden.
Wat is gegevensconsistentie als we het hebben over een database?
Laten we onze webshop nemen met medewerkers-, product- en taaktabellen . We weten al dat er taken in de takentabel kunnen zijn die aan niemand zijn toegewezen: de employee_id van dergelijke rijen is NULL.
Maar wat gebeurt er als er een item in de takentabel staat met een employee_id gelijk aan bijvoorbeeld 115? Zo'n medewerker hebben wij immers niet. We hebben geen werknemer met id = 115 in de werknemerstabel. Tegelijkertijd staat er een link naar een medewerker met dit id in de taaktabel. Dit is een voorbeeld van gegevensinconsistentie .
Dus hoe verzoenen we deze gegevens? Idealiter zou het zo zijn dat de SQL-server, bij elke gegevenswijziging, al deze nuances beheerst. En er is zo'n mogelijkheid, het heet FOREIGN_KEY.
Als een kolom in uw tabel niet alleen getallen bevat, maar id-rijen uit een andere tabel, dan kan dit expliciet worden opgegeven.
Een BUITENLANDSE SLEUTEL toevoegen
Zo'n sleutel kan aan de tabel worden toegevoegd, zowel tijdens het maken als daarna, met behulp van ALTER TABLE. Het formaat is niet wezenlijk anders. We zullen beide opties presenteren.
De algemene vorm van zo'n sleutel/regel is:
FOREIGN KEY (column)
REFERENCES table(column)
Laten we deze sleutel/regel toevoegen aan de takentabel om ervoor te zorgen dat alle employee_id's uit de tabel verwijzen naar een bestaand item in de werknemerstabel. Dit script ziet er als volgt uit:
ALTER TABLE task
ADD FOREIGN KEY (employee_id)
REFERENCES employee(id)
En als we zouden besluiten om deze regel toe te voegen op het moment dat we de taaktabel maakten, dan zou de code er als volgt uitzien:
CREATE TABLE task (
id INT,
name VARCHAR(100),
employee_id INT,
deadline DATE,
PRIMARY KEY (id),
FOREIGN KEY (employee_id)
REFERENCES employee(id)
);
Overigens zijn er situaties waarin de string waarnaar we verwijzen een unieke samengestelde sleutel heeft: bijvoorbeeld "Naam en geboortejaar" of "productCatogoryId en productId". Dan kan FOREIGN KEY als volgt worden geschreven:
FOREIGN KEY (our_column1, our_column2)
REFERENCES table(their_column1, their_column2)
BUITENLANDSE SLEUTEL en veranderende gegevens
Stel je nu een situatie voor waarin we besloten om enkele gegevens in de werknemerstabel bij te werken en onze werknemers-ID is gewijzigd. Wat gebeurt er met de gegevens in de taaktabel? Dat klopt, ze worden irrelevant en de integriteit van onze database wordt geschonden.
Om dit te voorkomen, kunt u SQL Server vertellen om de employee_id te wijzigen van alle rijen in alle tabellen die verwijzen naar deze specifieke gewijzigde id wanneer de id in de werknemerstabel verandert.
Dergelijke scripts worden OnUpdate en OnDelete genoemd . Wat te doen als de record-ID verandert en wat te doen als de record wordt verwijderd?
Met de verwijdering is niet alles zo eenvoudig. Als u afhankelijke objecten in de database hebt die worden weergegeven door tekenreeksen die naar elkaar verwijzen, is er een grote verscheidenheid aan gedragsscenario's mogelijk bij het verwijderen van één object.
Laten we zeggen dat we een sitegebruiker verwijderen, wat betekent dat we al zijn persoonlijke correspondentie moeten verwijderen. Maar het is onwaarschijnlijk dat we al zijn openbare opmerkingen moeten verwijderen.
Of een medewerker stopt. Het zou vreemd zijn als hij zou stoppen en tegelijkertijd alle aan hem toegewezen taken uit de database zouden verdwijnen. Maar als ze niet door hem waren aangesteld, was het ook slecht afgelopen. Het is juister om ervoor te zorgen dat de werknemer kan stoppen nadat hij al zijn taken aan andere mensen heeft toegewezen.
Hier is hoe we deze scenario's kunnen beschrijven met behulp van FOREIGN KEY. De algemene vorm van zo'n sleutel/regel is:
FOREIGN KEY (column)
REFERENCES table(column)
[ON DELETE reference_option]
[ON UPDATE reference_option]
Wat te doen bij het verwijderen (ON DELETE) of wijzigen (ON UPDATE) van records? In totaal kunnen er 5 opties zijn voor de SQL-server om in elk van deze situaties te handelen:
| # | referentie_optie | Uitleg |
|---|---|---|
| 1 | BEPERKEN | Schakel actie uit als tekenreeksverwijzingen worden gevonden |
| 2 | CASCADE | ID wijzigen in afhankelijke rijen |
| 3 | NUL INSTELLEN | Stel id in afhankelijke rijen in op NULL |
| 4 | GEEN ACTIE | Niets te doen |
| 5 | STANDAARD INSTELLEN x | Stel id in afhankelijke putten in op x |
Zo kunnen we onze taaktabel aanpassen:
ALTER TABLE task
ADD FOREIGN KEY (employee_id)
REFERENCES employee(id)
ON UPDATE CASCADE
ON DELETE RESTRICT;
Wat staat hier geschreven:
BIJ UPDATE CASCADE : Als de id-sleutel in de werknemerstabel verandert, verander dan ook de employee_id in de taaktabel die ernaar verwijst.
BIJ WISSEN BEPERKING : Als een rij wordt verwijderd uit de werknemerstabel en er wordt naar verwezen vanuit de taaktabel, voorkom dan dat de rij wordt verwijderd uit de werknemerstabel.
