5 SQL beperkingen om een goede databasestructuur te garanderen

Belangrijkste opmerkingen

Het gebruik van SQL-constraints is cruciaal om de integriteit van een database te behouden en tegelijkertijd de consistentie van de inhoud te garanderen. Een van deze beperkingen is de NOT NULL beperking, die voorschrijft dat kolommen geen null waarden kunnen accepteren, waardoor het belang van het verstrekken van geldige informatie in deze velden wordt afgedwongen.

Om de integriteit en nauwkeurigheid van gegevens in een relationele database te behouden, is het belangrijk om primaire sleutelbeperkingen op specifieke kolommen of kolomsets te implementeren. Deze unieke identificaties voorkomen dubbele records en bevorderen een efficiënte organisatie en het terugvinden van informatie.

Vreemde-sleutelbeperkingen dienen om de integriteit van relationele databases te handhaven door referentiële integriteit af te dwingen, door ervoor te zorgen dat elke record in een kindtabel overeenkomt met een bestaande record in de oudertabel. Deze beperkingen zijn essentieel voor het behouden van de onderlinge verbondenheid van gegevens over meerdere tabellen en het voorkomen van ongeoorloofde wijzigingen of verwijderingen die de nauwkeurigheid en consistentie van de database als geheel in gevaar kunnen brengen.

Een goed georganiseerde database is cruciaal voor tal van functionaliteiten, maar zonder vaste protocollen voor gegevensopslag en -manipulatie kan er chaos ontstaan.

SQL-constraints worden gebruikt om richtlijnen op te stellen voor gegevensopslag binnen een specifieke tabel. Deze beperkingen zorgen ervoor dat het systeem een foutmelding genereert wanneer geprobeerd wordt om gegevens in te voegen of bij te werken die in strijd zijn met de vastgestelde parameters. Door dergelijke regels af te dwingen, kunnen databases de consistentie van gegevens behouden en naleving van standaarden afdwingen voor alle tabellen.

Er bestaan verschillende soorten SQL beperkingen, die zeer nuttig zijn om de integriteit en veiligheid van gegevens in een database te garanderen.

NOT NULL beperking

Standaard staan databasekolommen de opslag van nulwaarden toe. Null staat voor de afwezigheid van welke waarde dan ook. Omgekeerd bepaalt de not-null beperking dat een bepaalde kolom nulwaarden moet uitsluiten en in plaats daarvan een inhoudelijke waarde moet hebben.

De declaratieve verklaring dient om de vereiste af te dwingen dat elke rij in een tabel een overeenkomstige invoer moet hebben in de respectievelijke niet-nulbare kolommen, die worden aangeduid door de “NOT NULL”-restrictie. Met andere woorden, het is niet toegestaan om een nieuwe record in de database te plaatsen of op een andere manier te introduceren tenzij alle vereiste velden gevuld zijn met gegevens, omdat er anders een fout wordt gegenereerd.

Neem een geval waarin een tabelweergave van klanten noodzakelijk wordt geacht voor organisatorische doeleinden. In zo’n scenario kan het verstandig zijn om bepaalde onontbeerlijke gegevens van elke individuele klant in die database op te nemen, zoals de naam van de klant. Om naleving van de vereisten voor gegevensinvoer te garanderen, kan een beperking die bekend staat als “NOT NULL” worden toegepast op deze verplicht inclusieve attributen, waardoor wordt gegarandeerd dat er geen klantenaccount zal bestaan zonder dat deze cruciale identificatie-informatie wordt verstrekt.

 CREATE TABLE Customers (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255) NOT NULL,
    Mobile_No int NOT NULL,
    Age int
);

Als wordt geprobeerd een klantrecord in te voeren zonder het veld Leeftijd, zal de database een dergelijke poging erkennen zonder dat er foutmeldingen worden gegenereerd:

 INSERT INTO Customers (ID, LastName, FirstName, Mobile_No)
VALUES (123456, 'Dior', 'Christian', 723000000); 

Als men een record probeert in te voegen zonder het veld “Voornaam”, zal het databanksysteem een foutmelding genereren als antwoord:

 ERROR: null value in column "firstname" violates not-null constraint
Detail: Failing row contains (123456, Dior, null, 723000000, null). 

PRIMARY KEY Constraint

Een primaire sleutel is een essentieel element in elk relationeel databasemanagementsysteem en dient als een afzonderlijke identificatie voor elke invoer binnen een specifieke tabel. Deze aanduiding garandeert de uniekheid en individualiteit van de gegevens in deze kolom(pen), waardoor duplicatie wordt uitgesloten terwijl de organisatie en integriteit van alle opgeslagen informatie behouden blijft.

In elke relationele database is een primaire sleutel een essentieel onderdeel dat bestaat uit onderscheidende waarden en geen nullen kan bevatten. Een fundamenteel principe voor het structureren van tabellen stelt dat elke tabel slechts één primaire sleutel mag hebben. Deze primaire sleutel kan bestaan uit een enkele kolom of uit meerdere kolommen, die samen bekend staan als samengestelde primaire sleutels.

Bij het opbouwen van een database met klantgegevens is het essentieel om unieke identificatiecodes vast te stellen voor elke individuele klant. Om dit doel te bereiken kan het concept van een primaire sleutelrestrictie worden geïmplementeerd, die ervoor zorgt dat geen twee cliënten identieke identificatienummers hebben.

Om een primaire sleutelrestrictie in een MySQL database te implementeren, kan de volgende voorbeeldcode worden gebruikt:

 CREATE TABLE Customers (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
   PRIMARY KEY (ID)
); 

De database weigert elke poging van een gebruiker om gegevens in te dienen die een dubbele identifier bevatten. Als dit gebeurt, wordt er een uitzondering gemaakt om aan te geven dat er dubbele informatie is. In het gegeven scenario probeert de persoon twee items toe te voegen met hetzelfde identificatienummer:

 INSERT INTO Customers (ID, LastName, FirstName, Age)
VALUES (1, 'John', 'Doe', 35 );

 INSERT INTO Customers (ID, LastName, FirstName, Age)
VALUES (1, 'Mary', 'Jane', 35 ); 

De database zal een foutmelding tonen:

 Duplicate entry '1' for key 'PRIMARY' 

De implementatie van een primaire sleutel dient om het voorkomen van dubbele identificatorwaarden binnen een gegeven dataset te voorkomen, zoals blijkt uit het feit dat het wijzigen van de unieke identificator die is toegewezen aan een bepaalde record zal resulteren in de afwijzing van een poging tot invoegen of bijwerken door het databasesysteem. Deze functie is vooral belangrijk voor het handhaven van gegevensintegriteit en het vermijden van inconsistenties bij het omgaan met grote verzamelingen informatie zoals die in klantenbestanden.

FOREIGN KEY Constraint

Foreign keys dienen als een manier om associaties te maken tussen twee tabellen door te verwijzen naar de primaire sleutel van een tabel in een secundaire of gerelateerde tabel. Dit zorgt voor een verbinding en onderlinge afhankelijkheid tussen gegevens in deze afzonderlijke entiteiten, wat het leggen van verbanden en verbindingen vergemakkelijkt door gedeelde overeenkomsten.

De relatie tussen twee tabellen in een relationele database kan worden vastgesteld door het gebruik van primaire en vreemde sleutels. In deze context wordt de tabel die als primaire gegevensbron dient en een unieke identificatie voor elke record heeft, de “ouder”-tabel genoemd, terwijl de tabel die afhankelijk is van informatie uit een andere tabel om zijn eigen records te maken, de “kind”-tabel wordt genoemd. Hieruit volgt dat elk gegeven in de kindtabel moet overeenkomen met een bestaand gegeven in de oudertabel, wat dient als middel voor associatie en validatie.

De implementatie van een foreign key restrictie zorgt voor het behoud van relaties tussen gekoppelde databasetabellen door elke bewerking te verbieden die deze connecties in gevaar kan brengen. Specifiek wordt het verwijderen of verwijderen van een tabel die dient als een vreemde sleutel naar een andere tabel beperkt, waardoor de gelijktijdige beëindiging van beide gerelateerde tabellen nodig is om te voorkomen dat hun associatie wordt verstoord.

In tegenstelling tot een primaire sleutel, die uniek is binnen een tabel, kunnen vreemde sleutels bestaan in meerdere instanties binnen een enkele tabel en kunnen ze nulwaarden bevatten. Bijvoorbeeld, in de gegeven illustratie vereist het aanmaken van een bestelling het gebruik van de waarde van de foreign key klant_id.

 CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

INSERT INTO Customers(customer_id, first_name, last_name)
VALUES (1, 'Christian', 'Dior');

INSERT INTO Orders(order_id, customer_id, order_date)
VALUES (1, 1, '2023-08-07'); 

Als wordt geprobeerd een transactie te genereren zonder een reeds bestaande identificatie voor de klant, geeft het systeem een foutmelding.

 Cannot add or update a child row: a foreign key constraint fails
(`db_9_4ee205c`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY
(`customer_id`) REFERENCES `customers` (`customer_id`)) 

UNIQUE Constraint

Een unieke beperking is een mechanisme in relationele databases dat de uniciteit van de waarden van een bepaalde kolom in alle rijen binnen een tabel garandeert. Net als primaire sleutels zorgt deze beperking voor consistentie van gegevens door duplicatie van informatie te voorkomen. Het niet implementeren van een dergelijke beperking kan resulteren in een ongeorganiseerde database die handmatige interventie vereist om overbodige records te identificeren en te verwijderen.

Men kan meerdere UNIQUE beperkingen gebruiken binnen een enkele tabel in tegenstelling tot een enkele PRIMARY KEY, zoals wordt gedemonstreerd door de creatie van een tabel “Klanten” met onderscheidende identifiers en telefoonnummers. De implementatie van een dergelijke beperking wordt uitgevoerd door het gebruik van de volgende syntaxis binnen het MySQL database management systeem:

 CREATE TABLE Customers (
    ID int NOT NULL UNIQUE,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Mobile_No BIGINT UNIQUE
 ); 

In gevallen waarin meerdere vermeldingen worden gemaakt in een database met identieke telefoonnummers, wordt een foutmelding gegenereerd om deze duplicatie aan te geven.

 INSERT INTO Customers (ID, LastName, FirstName, Mobile_No)
VALUES (123456, 'Dior', 'Christian', 254000000 );

INSERT INTO Customers (ID, LastName, FirstName, Mobile_No)
VALUES (7891011, 'Dedan', 'Kimathi', 254000000 ); 

De melding of waarschuwing die wordt gegenereerd als reactie op een mislukte aanmeldpoging, die informatie bevat over het gebruikersaccount en eventuele bijbehorende rechten, volgt gewoonlijk een gestandaardiseerd formaat dat bestaat uit specifieke details zoals de datum en tijd van de mislukte authenticatiepoging, de gebruikersnaam of accountnaam die aan de poging is gekoppeld, een korte beschrijving van de aard van de mislukking (bijv,

 Duplicate entry '254000000' for key 'Mobile_No' 

De beperking UNIQUE, een specifiek type databasebeperking, garandeert dat geen twee records in de database identieke waarden hebben voor de velden klant-ID of mobiel nummer.

CHECK beperking

Een CHECK beperking dient om het toegestane bereik van waarden die kunnen worden ingevoerd binnen een bepaalde kolom te reguleren, waardoor de algehele integriteit en authenticiteit van de daarin opgeslagen gegevens wordt gehandhaafd. Door dergelijke beperkingen op te leggen aan een specifieke kolom, kan deze worden beperkt om alleen die waarden te accepteren die expliciet zijn goedgekeurd, waardoor een extra beveiligingslaag wordt geboden tegen mogelijke afwijkingen of onbevoegde invoer.

De CHECK beperking vereist dat elke aangewezen rij of record in de gespecificeerde tabel wordt geëvalueerd als waar of onbekend. In het geval van een onjuiste evaluatie geeft de database een foutmelding.

Om de toegang tot bepaalde klantrecords te beperken op basis van leeftijd, is het mogelijk om een controlebeperking te gebruiken in de tabel Klanten van een bepaald relationeel databasemanagementsysteem zoals PostgreSQL. Deze beperking zou vereisen dat alle personen die een dienst zoeken minstens achttien jaar oud zijn, waardoor pogingen om diensten te verlenen aan minderjarigen worden voorkomen. Hieronder volgt een illustratie van dit concept in de vorm van SQL-code:

 CREATE TABLE Customers (
    ID int NOT NULL,
    Age int CHECK(Age>=18),
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Mobile_No BIGINT UNIQUE
 ); 

Als iemand probeert de leeftijd van een klant in te voeren die onder de drempel van 18 jaar ligt, kan

 INSERT INTO Customers (ID, Age, LastName, FirstName, Mobile_No)
VALUES (123456, 15, 'Dior', 'Christian', 1254000000 ); 

De database kan een melding weergeven die aangeeft dat er een probleem is:

 ERROR: new row for relation "customers" violates check constraint
"customers_age_check"
Detail: Failing row contains (123456, 15, Dior, Christian, 1254000000) 

Hoe SQL-constraints toevoegen aan en verwijderen uit databases

De flexibiliteit van SQL-constraints is waardevol omdat ze kunnen worden gewijzigd met behulp van de ALTER TABLE-instructie. Met deze veelzijdige opdracht kunnen gebruikers de beperkingen van hun tabel aanpassen aan veranderende gegevenseisen.

Naast de SQL beperkingen die hier genoemd zijn, is er een uitgebreide reeks beperkingen die onderzocht kunnen worden om een database op maat te maken. Beginnen met de constraints die in dit stuk worden beschreven zou een geschikt startpunt zijn.