Integriteitsconstraints in SQL: een gids met voorbeelden

Stel je een ziekenhuisdatabase voor waarin allergieën van patiënten niet leeg mogen blijven, of een financieel systeem waarin transactiebedragen altijd positieve getallen moeten zijn. In deze en talloze andere scenario’s vertrouwen we op integriteitsconstraints om ervoor te zorgen dat onze data accuraat, consistent en betrouwbaar blijft. 

In SQL zijn integriteitsconstraints regels die we opleggen aan onze databasetabellen om de datakwaliteit te behouden. Ze helpen fouten voorkomen, bedrijfsregels afdwingen en zorgen dat onze data de échte wereld en de bijbehorende entiteiten en relaties correct weerspiegelt.

In dit artikel duiken we in de essentiële typen integriteitsconstraints in SQL, met heldere uitleg en praktische voorbeelden in een PostgreSQL-database. Hoewel we PostgreSQL-syntax gebruiken, zijn de concepten eenvoudig te vertalen naar andere SQL-dialecten.

Wil je meer leren over SQL? Bekijk dan deze lijst met SQL-cursussen.

Wat zijn integriteitsconstraints in SQL?

Stel dat we een tabel hebben met gebruikersinformatie voor een webapplicatie. Bepaalde gegevens, zoals de leeftijd van een gebruiker, zijn mogelijk optioneel omdat dit gebruikers niet belemmert om de applicatie te gebruiken. Maar een wachtwoord voor elke gebruiker is essentieel om te kunnen inloggen. Om dit te borgen, zouden we een integriteitsconstraint plaatsen op de kolom met wachtwoorden in de gebruikerstabel, zodat elke rij een wachtwoord bevat.

Kort gezegd zijn integriteitsconstraints cruciaal voor:

• Het voorkomen van ontbrekende data.
• Ervoor zorgen dat alle data voldoet aan de verwachte typen en waardebereiken.
• Het onderhouden van correcte koppelingen tussen data in verschillende tabellen.

In dit artikel behandelen we de volgende essentiële integriteitsconstraints in SQL:

• PRIMARY KEY: Identificeert elk record in een tabel uniek.
• NOT NULL: Zorgt dat een kolom geen NULL-waarden kan bevatten.
• UNIQUE: Zorgt dat alle waarden in een kolom of combinatie van kolommen uniek zijn.
• DEFAULT: Levert een standaardwaarde voor een kolom wanneer er geen waarde is opgegeven.
• CHECK: Zorgt dat alle waarden in een kolom aan een specifieke voorwaarde voldoen.
• FOREIGN KEY: Legt relaties tussen tabellen vast door te verwijzen naar een primaire sleutel in een andere tabel.

Casus: universiteitsdatabase

Laten we een relationele database voor een universiteit bekijken. Deze database bevat drie tabellen: students, courses en enrollments.

Tabel Students

De tabel students bevat informatie over alle studenten van de universiteit.

• student_id: De identifier van de student
• first_name: De voornaam van de student.
• last_name: De achternaam van de student.
• email: Het e-mailadres van de student.
• major: De studierichting van de student.
• enrollment_year: Het jaar waarin de student is ingeschreven.

Tabel Courses

De tabel courses bevat informatie over de vakken die aan de universiteit worden aangeboden.

• course_id: De vakidentifier.
• course_name: De naam van het vak.
• department: De afdeling waaronder het vak valt.

Tabel Enrollments

De tabel enrollments slaat op welke studenten in welke vakken zijn ingeschreven.

• student_id: De identifier van de student die is ingeschreven voor het vak.
• course_id: De identifier van het vak.
• year: Het inschrijvingsjaar.
• grade: Het cijfer van de student voor dit vak.
• is_passing_grade: Een boolean die aangeeft of het cijfer voldoende is.

In dit artikel gebruiken we deze voorbeelddatabase en laten we verschillende manieren zien om dataintegriteit af te dwingen. We gebruiken PostgreSQL-syntax in onze queries. De concepten zijn echter eenvoudig te vertalen naar andere smaken van SQL.

PRIMARY KEY-constraint

De universiteit wil elke student uniek kunnen identificeren. Voor- en achternamen hiervoor gebruiken is geen goed idee, omdat meerdere studenten dezelfde naam kunnen hebben. Ook e-mailadressen zijn niet ideaal, want studenten kunnen hun e-mailadres wijzigen.

De gebruikelijke oplossing om unieke identificatie te garanderen is het toekennen van een unieke identifier aan elke student, die we opslaan in de kolom student_id. We kunnen een PRIMARY KEY-constraint toepassen op de kolom student_id om te garanderen dat elke student een unieke identifier heeft.

Deze constraint wordt gedefinieerd in het CREATE TABLE-commando, direct na de specificatie van het kolomdatatype:

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    email TEXT,
    major TEXT,
    enrollment_year INT
);Mogelijk gemaakt door

De bovenstaande query maakt de tabel met studenten met de zes kolommen die we hierboven noemden.

De PRIMARY KEY-constraint zorgt ervoor dat:

1. Elke student een student_id heeft.
2. Elke student_id uniek is.

PRIMARY KEY-constraint op meerdere kolommen

In sommige situaties hebben we meerdere kolommen nodig om elke rij uniek te identificeren. Neem de tabel enrollments. Een student kan zich voor meerdere vakken inschrijven, wat resulteert in meerdere rijen met dezelfde student_id. Evenzo kan een vak meerdere ingeschreven studenten hebben, wat leidt tot meerdere rijen met dezelfde course_id.

Omdat geen enkel veld afzonderlijk een rij uniek kan identificeren, wordt elk inschrijvingsrecord bepaald door een combinatie van student_id, course_id en year.

Wanneer meerdere kolommen betrokken zijn, wordt de PRIMARY KEY-constraint aan het einde van het CREATE TABLE-commando opgegeven.

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    year INT,
    grade INT,
    is_passing_grade BOOLEAN,
    PRIMARY KEY (student_id, course_id, year)
);Mogelijk gemaakt door

Integriteitsconstraints na het aanmaken van de tabel

Er zijn twee manieren om integriteitsconstraints toe te voegen. We hebben zojuist gezien hoe je dit doet bij het aanmaken van de tabel.

Stel dat de tabel al bestaat, maar je vergat de PRIMARY KEY-constraint te specificeren. Je kunt deze ook achteraf definiëren met het ALTER TABLE-commando, als volgt:

ALTER TABLE enrollments
ADD CONSTRAINT enroll_pk 
PRIMARY KEY (student_id, course_id, year);Mogelijk gemaakt door

In de ALTER TABLE-query hebben we de constraint enroll_pk genoemd (staat voor enrollment primary key). Deze naam kan elke door jou gekozen identifier zijn, maar het is aan te raden een naam te kiezen die het doel van de constraint beknopt weergeeft.

Het is best practice om integriteitsconstraints te benoemen, omdat dit verschillende voordelen biedt:

• Het maakt verwijzen ernaar eenvoudiger, vooral als je de constraint later moet wijzigen of verwijderen.
• Het ondersteunt het beheer en de organisatie van constraints, zeker in databases met veel constraints.

NOT NULL-constraint

De universiteit wil zeker weten dat van elke student de naam en het e-mailadres in de database staan. Ze willen niet dat medewerkers per ongeluk een van deze velden overslaan.

Om dit te waarborgen, kunnen we NOT NULL-constraints gebruiken op deze drie kolommen bij het aanmaken van de tabel:

CREATE TABLE students (
    student_id INT,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    email TEXT NOT NULL,
    major TEXT,
    enrollment_year INT
);Mogelijk gemaakt door

De bovenstaande query gebruikt de NOT NULL-constraints om te garanderen dat de kolommen first_name, last_name en email geen NULL- (ongedefinieerde) waarden kunnen hebben.

Een NOT NULL-constraint toevoegen aan een bestaande tabel kan met het ALTER TABLE-commando. De syntax voor het toevoegen van NOT NULL-constraints is als volgt:

ALTER TABLE students
ALTER COLUMN first_name SET NOT NULL,
ALTER COLUMN last_name SET NOT NULL,
ALTER COLUMN email SET NOT NULL;Mogelijk gemaakt door

UNIQUE-constraint

E-mailadressen zijn per definitie uniek voor iedere persoon. Als we zeker weten dat een veld nooit dubbele waarden mag bevatten, is het goed om dit op databaseniveau af te dwingen. Dat helpt fouten voorkomen en de dataintegriteit te waarborgen.

Het toevoegen van deze constraint gaat op dezelfde manier als de andere.

CREATE TABLE students (
    ...
    email TEXT UNIQUE,
    ...
);Mogelijk gemaakt door

In ons geval willen we echter ook de NOT NULL-constraint afdwingen. We kunnen meerdere constraints op één kolom zetten door ze met een spatie te scheiden: 

CREATE TABLE students (
    ...
    email TEXT UNIQUE NOT NULL,
    ...
);Mogelijk gemaakt door

Let op: de volgorde maakt niet uit; we hadden ook NOT NULL UNIQUE kunnen gebruiken.

Een UNIQUE-constraint toevoegen aan een bestaande tabel doe je als volgt:

ALTER TABLE students 
ADD CONSTRAINT unique_emails UNIQUE (email);Mogelijk gemaakt door

UNIQUE-constraint op meerdere kolommen

Stel dat we willen garanderen dat de cursusnaam binnen elke afdeling uniek is in de tabel courses. In dat geval moeten de kolommen course_name en department samen uniek zijn.

Wanneer meerdere kolommen betrokken zijn, wordt de constraint aan het einde van het CREATE TABLE-commando toegevoegd:

CREATE TABLE courses (
    course_id INT,
    course_name TEXT,
    department TEXT,
    UNIQUE (course_name, department)
);Mogelijk gemaakt door

We kunnen de constraint ook toevoegen door een bestaande tabel te wijzigen. In dat geval geven we een tuple met de kolomnamen op:

ALTER TABLE courses 
ADD CONSTRAINT unique_course_name_department 
UNIQUE (course_name, department);Mogelijk gemaakt door

NOT NULL UNIQUE vs PRIMARY KEY-constraints 

We hebben gezien dat een PRIMARY KEY-constraint zowel uniciteit als geen ontbrekende waarden afdwingt. Je vraagt je dus misschien af wat het verschil is tussen:

course_id INT PRIMARY KEYMogelijk gemaakt door

course_id INT UNIQUE NOT NULLMogelijk gemaakt door

Het verschil tussen NOT NULL UNIQUE en PRIMARY KEY in een tabel zit in hun beoogde doel en gebruik. 

Hoewel beide uniciteit en niet-nullbaarheid afdwingen op kolom(men), kan een tabel slechts één PRIMARY KEY hebben, bedoeld om elk record in de tabel uniek te identificeren.

De combinatie NOT NULL UNIQUE kan daarentegen op extra kolommen worden toegepast om een unieke waarde per rij af te dwingen, zonder null-waarden, ter ondersteuning van specifieke bedrijfsregels. Een tabel kan een onbeperkt aantal NOT NULL UNIQUE-constraints hebben.

Het bestaan van beide biedt meer flexibiliteit in databaseontwerp, zodat je op meerdere manieren uniciteit en dataintegriteit kunt afdwingen, terwijl je onderscheid maakt tussen de primaire identifier van een record en andere belangrijke, unieke attributen binnen een tabel.

DEFAULT-constraint

Studenten hebben soms wat tijd nodig na hun inschrijving om een studierichting te kiezen. De universiteit wil dat de waarde van de kolom major de string ’Undeclared’ is voor studenten die hun richting nog niet hebben gekozen.

Daarvoor kunnen we een standaardwaarde instellen met de DEFAULT-constraint. We kunnen de tabel students als volgt wijzigen:

ALTER TABLE students
ALTER COLUMN major SET DEFAULT 'Undeclared';Mogelijk gemaakt door

Als we in plaats daarvan een DEFAULT-constraint willen instellen bij het aanmaken van de tabel, dan vermelden we die na het kolomdatatype:

CREATE TABLE students (
    ...
    major TEXT DEFAULT 'Undeclared',
    ...
);Mogelijk gemaakt door

CHECK-constraint

Aan deze universiteit lopen cijfers van 0 tot 100. Zonder constraint accepteert de kolom grade in de tabel enrollment elke integerwaarde. We lossen dat op met een CHECK-constraint om af te dwingen dat waarden tussen 0 en 100 liggen.

ALTER TABLE enrollments
ADD CONSTRAINT grade_range CHECK (grade BETWEEN 0 AND 100);Mogelijk gemaakt door

In het algemeen stellen CHECK-constraints ons in staat specifieke voorwaarden te valideren waaraan de data moet voldoen. Deze zijn belangrijk om dataconsistentie en -integriteit te waarborgen.

Een CHECK-constraint kan meer dan één kolom omvatten. Laten we deze gebruiken om te garanderen dat grade en is_passing_grade consistente waarden hebben. Stel dat een cijfer voldoende is als het ten minste 60 is. Dan kunnen we ervoor zorgen dat is_passing_grade TRUE is dan en slechts dan als het cijfer minstens 60 is. Laten we dit in de tabelcreatie doen om te laten zien hoe CHECK-constraints in het CREATE TABLE-commando worden gedeclareerd:

CREATE TABLE enrollments (
    ...
    grade INT,
    is_passing_grade BOOLEAN,
    CONSTRAINT grade_check CHECK (grade BETWEEN 0 AND 100),
    CONSTRAINT is_passing_grade CHECK (
        (grade >= 60 AND is_passing_grade = TRUE) OR
        (grade < 60 AND is_passing_grade = FALSE)
    )
);Mogelijk gemaakt door

Er is een probleem met de bovenstaande constraint. Wanneer een student zich voor een vak inschrijft, heeft die nog geen cijfer. We moeten dus NULL-waarden toestaan in de kolom grade. Als we dat doen, moeten we ook de constraint voor de voldoende-score aanpassen, zodat die NULL is wanneer het cijfer nog niet is vastgesteld. Zo werk je de constraint bij om hiermee rekening te houden:

CREATE TABLE enrollments (
    ...    
    grade INT NULL DEFAULT NULL,
    is_passing_grade BOOLEAN NULL DEFAULT NULL,
    CONSTRAINT grade_check CHECK (grade BETWEEN 0 AND 100),
    CONSTRAINT is_passing_grade CHECK (
        (grade IS NULL AND is_passing_grade IS NULL) OR
        (grade >= 60 AND is_passing_grade = TRUE) OR
        (grade < 60 AND is_passing_grade = FALSE)
    ),
    ...
);Mogelijk gemaakt door

Let op dat we bij zowel de kolom grade als is_passing_grade NULL hebben toegevoegd aan het datatype en als standaardwaarde. Dit is puur voor de leesbaarheid.

Laten we nu bekijken welke voorwaarden we kunnen afdwingen met een CHECK-constraint.

Bereikvoorwaarden

We kunnen waarborgen dat waarden in een kolom binnen een specifiek bereik vallen.

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
CHECK (column_name BETWEEN min_value AND max_value);Mogelijk gemaakt door

Lijstvoorwaarden

We kunnen valideren dat de waarde van een kolom overeenkomt met een waarde uit een specifieke lijst.

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
CHECK (column_name IN ('Value1', 'Value2', 'Value3'));Mogelijk gemaakt door

Vergelijkingsvoorwaarden

We kunnen waarden in een kolom vergelijken om te waarborgen dat ze aan een bepaalde voorwaarde voldoen (groter dan, kleiner dan, enz.).

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
CHECK (column_name > some_value);Mogelijk gemaakt door

Patroonmatching-voorwaarden

We kunnen patroonmatching gebruiken (bijv. met LIKE of SIMILAR TO) om tekstdata te valideren.

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
CHECK (column_name LIKE 'pattern');Mogelijk gemaakt door

Logische voorwaarden

We kunnen meerdere voorwaarden toestaan met logische operatoren (AND, OR, NOT).

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
CHECK (condition1 AND condition2 OR condition3);Mogelijk gemaakt door

Samengestelde voorwaarden

Past een voorwaarde toe over meerdere kolommen.

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
CHECK (column1 + column2 < some_value);Mogelijk gemaakt door

FOREIGN KEY-constraint

Foreign key-constraints worden gebruikt om kolommen van twee tabellen aan elkaar te koppelen en zo de referentiële integriteit van de data te waarborgen. Kort gezegd wijst een foreign key in de ene tabel naar een primaire sleutel in een andere tabel, waarmee wordt aangegeven dat de rijen in deze twee tabellen gerelateerd zijn. Dit zorgt ervoor dat je geen rij kunt hebben met een foreign key die niet overeenkomt met een rij in de gekoppelde tabel met de primaire sleutel.

In ons voorbeeld verwijst elk record in de tabel enrollments naar een student en een vak via respectievelijk de kolommen student_id en course_id. Zonder constraints is er niets dat garandeert dat de waarden van deze identifiers in de tabel enrollments overeenkomen met bestaande entries in de tabellen students en courses.

Zo borgen we dit bij het aanmaken van de tabel enrollments:

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    ...
    FOREIGN KEY (student_id) REFERENCES Students(student_id),
    FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);Mogelijk gemaakt door

Let op: om dit foreign keys te laten zijn, moeten het respectievelijk primaire sleutels zijn in de tabellen students en courses.

Net als bij andere constraints kunnen we deze ook na het aanmaken van de tabel definiëren met het ALTER TABLE-commando:

ALTER TABLE enrollments
ADD CONSTRAINT fk_student_id
FOREIGN KEY (student_id)
REFERENCES students(student_id);

ALTER TABLE enrollments
ADD CONSTRAINT fk_course_id
FOREIGN KEY (course_id)
REFERENCES courses(course_id);Mogelijk gemaakt door

Alles samenbrengen

In dit artikel hebben we verschillende integriteitsconstraints laten zien en hoe je ze gebruikt om een universiteitsdatabase te verbeteren. Hier is de definitieve versie van het CREATE TABLE-commando, waarin alles wat we hebben geleerd is gecombineerd.

Eerst definiëren we de tabel students:

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    major TEXT DEFAULT 'Undeclared',
    enrollment_year INT,
    CONSTRAINT year_check CHECK (enrollment_year >= 1900),
    CHECK (major IN (
        'Undeclared',
        'Computer Science',
        'Mathematics',
        'Biology',
        'Physics',
        'Chemistry',
        'Biochemistry'
    ))
);Mogelijk gemaakt door

Vervolgens definiëren we de tabel courses:

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name TEXT NOT NULL,
    department TEXT NOT NULL,
    UNIQUE (course_name, department),
    CHECK (department IN (
        'Physics & Mathematics',
        'Sciences'
    ))
);Mogelijk gemaakt door

Tot slot definiëren we de tabel enrollments, waarmee we de relaties tussen studenten en vakken vastleggen:

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    year INT CHECK (year >= 1900),
    grade INT NULL DEFAULT NULL,
    is_passing_grade BOOLEAN NULL DEFAULT NULL,
    CONSTRAINT grade_check CHECK (grade BETWEEN 0 AND 100),
    CONSTRAINT is_passing_grade CHECK (
        (grade IS NULL AND is_passing_grade IS NULL) OR
        (grade >= 60 AND is_passing_grade = TRUE) OR
        (grade < 60 AND is_passing_grade = FALSE)
    ),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id),
    PRIMARY KEY (student_id, course_id, year)
);Mogelijk gemaakt door

We hebben in dit laatste voorbeeld een paar extra constraints toegevoegd. Een universiteit heeft een bekende set afdelingen en studierichtingen. Daarom is de set waarden die de kolommen major en department kunnen hebben eindig en vooraf bekend. In zulke situaties is het aan te raden een CHECK-constraint te gebruiken om te garanderen dat de kolommen alleen waarden uit die bekende set kunnen aannemen.

Conclusie

In dit artikel hebben we de verschillende typen integriteitsconstraints in SQL besproken en hoe je ze implementeert met PostgreSQL. We hebben primaire sleutels, NOT NULL-constraints, UNIQUE-constraints, DEFAULT-constraints, CHECK-constraints en FOREIGN KEY-constraints behandeld, telkens met praktische voorbeelden.

Door deze concepten te begrijpen, kunnen we de nauwkeurigheid, consistentie en betrouwbaarheid van onze data waarborgen.

Wil je meer leren over het efficiënt organiseren van data? Bekijk dan deze cursus Database Design.