Als u een onderneming heeft, moet u de waarde en behoefte aan gegevens in uw bedrijf zijn tegengekomen. De middelen hebben om databases op te slaan en te manipuleren, voegt meer waarde toe aan het bedrijf.
Databases zijn volgens een bepaalde conventie georganiseerd en stellen u in staat gegevens in verbanden te structureren, wat ons brengt bij relationele databases, die sinds de jaren zeventig zijn omarmd als een vorm van gegevensbeheer. En in de huidige markt hebben relationele databases de voorkeur vanwege hun mogelijkheden bij het manipuleren van gegevens.
Hoewel er veel beschikbare relationele databases zijn, heeft MySQL de leidende positie bereikt en staat het wereldwijd op nummer twee, volgens Statista, vanaf januari 2022.
In de SQL-server zijn beperkingen vooraf gedefinieerde regels en beperkingen die worden afgedwongen in een enkele of meerdere kolommen; ze zijn gekoppeld aan de waarden in de kolom en helpen bij het handhaven van de integriteit, nauwkeurigheid en betrouwbaarheid van de gegevens in de opgegeven kolommen.
Simpel gezegd, alleen gegevens die aan de beperkingsregel voldoen, worden met succes in de kolom ingevoegd. De invoegbewerking wordt beëindigd als de gegevens niet aan de criteria voldoen.
Dit bericht gaat ervan uit dat je relationele databases bent tegengekomen, met name MySQL, en je kijkt ernaar uit om je kennis in het domein te versterken. Uiteindelijk zal ik enkele tips delen voor interactie met Foreign key constraints.
Primaire sleutelbeperkingen – een samenvatting
Een tabel in SQL omvat een kolom of meerdere met sleutelwaarden die elke rij in de systemen nauwkeurig lokaliseren. De kolom of kolommen met de titel primaire sleutel (PK) van de tabel hebben de rol van het afdwingen van de entiteitsintegriteit van de tabel. Primaire sleutelbeperkingen garanderen unieke gegevens en worden vaak gedefinieerd op een identiteitskolom.
Na het specificeren van de primaire sleutelbeperkingen voor uw tabel, legt de database-engine automatisch gegevensuniek op door unieke indexen te genereren voor elk van de primaire kolommen. Primaire sleutels bieden een extreem voordeel bij gebruik in query’s door snelle gegevenstoegang te bieden.
Als primaire sleutelbeperkingen zijn gedefinieerd voor meerdere kolommen, wordt dit een samengestelde of samengestelde primaire sleutel genoemd. En in dit geval kan elke primaire sleutelkolom dubbele waarden bevatten. De gecombineerde waarden van alle kolommen in de primaire sleutel moeten echter uniek zijn.
Een goed voorbeeld is een geval waarin je een tabel hebt met de kolommen `id`, `names` en `age`. Wanneer u de primaire sleutelbeperking op de combinatie van `id` en `names` definieert, kunt u dubbele instanties van ofwel `id`- of `names`-waarden hebben. Toch moet elke combinatie uniek zijn om dubbele rijen te voorkomen. U kunt dus records hebben met `id=1` en `name=Walter`, en `age-22′ en `id=1`, `name=Henry` en `age=27`, maar u kunt geen andere records hebben met `id=1` en `name=Walter` omdat de combinatie niet uniek is.
Hier zijn enkele essentiële aspecten om te weten:
Buitenlandse sleutelbeperkingen – een overzicht
Een externe sleutel (FK) omvat een kolom of een combinatie van meerdere die wordt gebruikt om een koppeling tussen twee tabellen te maken en te binden en beheert de gegevens die moeten worden opgeslagen in een externe sleuteltabel.
Een verwijzing naar een externe sleutel houdt in dat er een koppeling wordt gemaakt tussen twee tabellen; wanneer naar een kolom of kolommen met de primaire sleutel voor een andere tabel wordt verwezen door een kolom of kolommen in een andere tabel.
In het referentiescenario voor externe sleutels wordt een verbinding tot stand gebracht tussen twee tabellen wanneer naar een kolom of kolommen met primaire sleutels in een tabel wordt verwezen door kolommen in een andere tabel.
In een praktisch gebruiksvoorbeeld kunt u een tabel hebben, Sales.SalesOrderHeader, met een refererende sleutel die linkt naar een andere tabel, Sales.Person, omdat er een logische relatie bestaat tussen verkopers en verkooporders.
Hier gaat de SalesPersonID in de kolom SalesOrderHeader samen met de primaire sleutelkolom van de SalesPerson-tabel. De externe sleutel van de tabel SalesPerson is de kolom SalesPersonID in de SalesOrderHeader.
Deze relatie definieert een regel: een SalesPersonID-waarde kan niet in uw SalesOrderHeader-tabel voorkomen als deze niet bestaat in de SalesPerson-tabel.
Een tabel kan naar maximaal 253 andere kolommen en tabellen verwijzen als externe sleutels, ook wel uitgaande verwijzingen genoemd. Sinds 2016 heeft de SQL-server het aantal tabellen en kolommen waarnaar u kunt verwijzen in een enkele tabel, ook wel inkomende referenties genoemd, verhoogd van 253 naar 10.000. De toename brengt echter enkele beperkingen met zich mee:
Wat zijn de voordelen van buitenlandse sleutels?
Zoals eerder vermeld, spelen externe sleutelbeperkingen een essentiële rol bij het waarborgen van de integriteit en gegevensconsistentie in de relationele database. Hier volgt een overzicht van redenen waarom externe sleutelbeperkingen essentieel zijn.
Indexen van buitenlandse sleutelbeperkingen
Externe sleutelbeperkingen creëren niet automatisch overeenkomstige indexen zoals de primaire. U kunt handmatig indexen maken voor beperkingen op externe sleutels; het is gunstig om de volgende redenen.
- Kolommen met externe sleutels worden vaak gebruikt in de join-criteria bij het combineren van gegevens uit gerelateerde tabellen in query’s door de kolommen te matchen die aan de beperking zijn gekoppeld. Indexen helpen de database bij het vinden van bijbehorende gegevens in een vreemde tabel.
- Als u primaire sleutelbeperkingen wijzigt, worden deze gecontroleerd met de vreemde beperkingen in gerelateerde tabellen.
Het is niet verplicht om indexen te maken. U kunt nog steeds gegevens uit twee tabellen combineren zonder de primaire en externe sleutelbeperkingen op te geven. Door externe sleutelbeperkingen toe te voegen, worden de tabellen echter geoptimaliseerd en gecombineerd in een query die voldoet aan de criteria voor het gebruik van de sleutels. Als u de primaire sleutelbeperkingen wijzigt, worden deze gecontroleerd met de vreemde in gerelateerde.
Tips om Foreign Key Constraints in SQL te creëren
Je hebt al veel tijd besteed aan de speculatie; antwoord op het waarom. Laten we onze focus verleggen en beperken tot de tactieken voor het creëren van externe sleutelbeperkingen; antwoord op het hoe.
Een `Foreign Key`-veld in een tabel verwijst naar de `Primary Key` van een andere. De tabel met de primaire sleutel is uw bovenliggende tabel. En de tabel met de externe sleutel wordt de onderliggende tabel genoemd. Laten we erin duiken.
Een externe sleutel maken tijdens het maken van een tabel
Bij het maken van een tabel kunt u ook een externe sleutelbeperking creëren om de referentiële integriteit te behouden. Hier is hoe het te doen:
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
De bovenstaande code maakt een tabel met de naam ‘orders’ met de primaire integer-sleutel ‘order_id’, een ander integer ‘customer_id’ en de datum ‘order_date’. In dit geval wordt de FOREIGN KEY-beperking toegevoegd aan de kolom ‘customer_id’ en wordt verwezen naar de ‘customer_id’ in uw tabel ‘customers’.
Een vreemde sleutel maken na het maken van een tabel
Stel dat u al een tabel heeft gemaakt en een externe sleutelbeperking wilt toevoegen; gebruik de instructie `ALTER TABLE` in uw code. Kijk naar het codefragment hieronder.
ALTER TABLE orders ADD FOREIGN KEY (customer_id) REFERENCES customers(customer_id);
In dit geval heeft u een externe sleutelbeperking ‘customer_id’ kolom toegevoegd aan de tabel ‘orders’ om te verwijzen naar de kolom ‘customer_id’ in de tabel ‘customers’.
Een externe sleutel maken zonder te controleren op bestaande gegevens
Wanneer u een externe-sleutelbeperking aan een tabel toevoegt, controleert de database automatisch op bestaande gegevens om te zorgen voor consistentie met de beperking. Als u echter weet dat de gegevens consistent zijn en u een beperking wilt toevoegen zonder de consistentiecontrole, kunt u dit als volgt doen.
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id) REFERENCES customers(customer_id) NOT VALIDATE;
De opdracht NOT VALIDATE vertelt de database om niet te controleren op bestaande gegevens. Dit specifieke geval is nuttig in specifieke gevallen. Bijvoorbeeld wanneer u enorme hoeveelheden gegevens heeft en het validatieproces wilt voltooien.
Een Foreign Key aanmaken via DELETE/UPDATE
Terwijl u externe-sleutelbeperkingen maakt, kunt u bepalen welke actie moet worden ondernomen in gevallen waarin de rij waarnaar wordt verwezen, wordt bijgewerkt of verwijderd. In dit geval gebruikt u trapsgewijze referentiële integriteitsbeperkingen om de te ondernemen acties te dicteren. Ze bevatten:
#1. GEEN ACTIE
Net als bij veel andere databases is de regel ‘GEEN ACTIE’ het standaardgedrag wanneer u een externe sleutelbeperking maakt. Dit betekent dat er geen actie wordt ondernomen wanneer de rij waarnaar wordt verwezen wordt verwijderd of bijgewerkt.
De database-engine genereert een fout als de externe sleutelbeperking wordt geschonden. Dit wordt echter niet aanbevolen, omdat dit kan leiden tot problemen met de referentiële integriteit, aangezien de beperking van de externe sleutel moet worden afgedwongen. Hier is een voorbeeld van hoe het te doen:
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE NO ACTION ON UPDATE NO ACTION;
#2. CASCADE
De ‘CASCADE’-regel is een andere optie voor de acties ‘ON DELETE’ en ‘ON UPDATE’ bij het maken van externe sleutelbeperkingen. Wanneer ingevoerd, betekent dit dat telkens wanneer een rij wordt bijgewerkt of verwijderd in de bovenliggende tabellen, de rijen waarnaar wordt verwezen dienovereenkomstig worden bijgewerkt of verwijderd. Deze techniek is krachtig bij het handhaven van referentiële integriteit. Hier is een voorbeeld:
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE ON UPDATE CASCADE;
U moet scherp zijn bij het gebruik van deze regel, omdat deze ongewenste gevolgen kan hebben als deze niet zorgvuldig wordt gebruikt. U wilt voorkomen dat u per ongeluk te veel gegevens verwijdert of kringverwijzingen maakt. Gebruik deze optie daarom alleen indien nodig en met de nodige voorzichtigheid.
Er zijn enkele regels voor het gebruik van CASCADE:
- U kunt CASCADE niet opgeven als een tijdstempelkolom deel uitmaakt van de externe sleutel of waarnaar wordt verwezen.
- Als uw tabel een INSTEAD OF DELETE-trigger heeft, kunt u ON DELETED CASCADE niet specificeren.
- U kunt ON UPDATE CASCADE niet specificeren als uw tabel een INSTEAD OF UPDATE-trigger heeft.
#3. NUL INSTELLEN
Wanneer u een overeenkomstige rij in de bovenliggende tabel verwijdert of bijwerkt, worden alle waarden waaruit de refererende sleutel bestaat, op null gezet. Deze beperkingsregel vereist dat kolommen met externe sleutels nullbaar zijn om uit te voeren en kunnen niet worden gespecificeerd voor tabellen met IN PLAATS VAN UPDATE-triggers. Hier is een voorbeeld van hoe het te doen.
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE SET NULL ON UPDATE SET NULL
In dit geval heeft u de externe sleutelkolom ‘customer_id’ in de tabel ‘orders’ ingesteld op null als de overeenkomstige rij in de tabel ‘klanten’ wordt verwijderd of bijgewerkt.
#4. STANDAARD INSTELLEN
Hier stelt u alle waarden in die de externe sleutel standaard maken, op voorwaarde dat de rij waarnaar wordt verwezen in de bovenliggende tabel wordt bijgewerkt of verwijderd.
Deze beperking wordt uitgevoerd als alle externe sleutelkolommen standaarddefinities hebben. Als een kolom nullbaar is, is de standaardwaarde ingesteld op NULL. Merk op dat deze optie niet kan worden opgegeven voor tabellen met triggers IN PLAATS VAN UPDATE. Hier is een voorbeeld:
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE SET DEFAULT ON UPDATE SET DEFAULT;
In het bovenstaande geval heeft u de ‘customer_id’ in de tabel “orders” ingesteld op de standaardwaarde, wat gebeurt wanneer de overeenkomstige rij in de tabel “klanten” wordt verwijderd of bijgewerkt.
laatste woorden
In deze handleiding heeft u een opfriscursus gehad over de beperkingen van de primaire sleutel en heeft u zich verdiept in de beperkingen van de externe sleutel. U bent ook verschillende technieken tegengekomen om externe sleutelbeperkingen te creëren. En hoewel er veel manieren zijn om externe sleutelbeperkingen te creëren, heeft dit bericht de methoden ontrafeld.
En in de hoop dat je nieuwe technieken onder de knie hebt; je bent niet beperkt tot het combineren ervan. De beperkingsmethoden CASCADE, SET NULL, SET DEFAULT en NO ACTION kunnen bijvoorbeeld worden gecombineerd op tabellen met referentiële relaties.
Als uw tabel GEEN ACTIE tegenkomt, keert deze terug naar andere beperkingsregels. In andere gevallen kan een DELETE-actie een combinatie van deze regels activeren en wordt de NO ACTION-regel als laatste uitgevoerd.
Bekijk vervolgens het SQL-spiekbriefje.
