Constraints

Op deze pagina:

NOT NULL contraint
UNIQUE contraint
PRIMARY KEY contraint
FOREIGN KEY contraint
CHECK contraint
DEFAULT contraint
INDEX contraint
AUTO INCREMENT

SQL constraints worden gebruikt om regels in te stellen voor gegevens in een tabel. Constraints beperken het soort gegevens dat in een tabel mag. Dit zorgt voor nauwkeurigheid en betrouwbaarheid in de tabel. Als iets niet aan de contraint voldoet, dan gaat de mutatie niet door. Constraints kunnen gelden voor een kolom of voor een hele tabel.

Veel gebruikte constraints:

NOT NULL: De kolom mag geen NULL waarden hebben.
UNIQUE: Alle waarden moeten verschillend zijn in de kolom.
PRIMARY KEY: Een combinatie van NOT NULL en UNIQUE. Geeft iedere rij een unieke ID.
FOREIGN KEY: Een unieke ID voor een rij/record in een andere tabel.
CHECK: Zorgt ervoor dat alle waarden in een kolom aan een specifieke voorwaarde voldoen.
DEFAULT: Geeft een default voor die gevallen waar geen waarde is opgegeven.
INDEX: Wordt gebruikt om sneller gegevens op te kunnen vragen of te kunnen maken.

Constraints kunnen worden opgegeven bij het maken van een tabel met CREATE TABLE, of nadien met ALTER TABLE.

Constraints algemene vorm:


CREATE TABLE tabelnaam (
    kolom1 datatype constraint,
    kolom2 datatype constraint,
    kolom3 datatype constraint,
    ....
);

NOT NULL contraint

Standaard kan een kolom NULL waarden bevatten. De NOT NULL constraint op een kolom zorgt ervoor dat er geen NULL waarden voor de kolom worden geaccepteerd. Dit betekent dat elk record in de kolom altijd een waarde heeft.

Voorbeeld NOT NULL op CREATE TABLE:


CREATE TABLE Personen (
    ID int NOT NULL,
    Achternaam varchar(255) NOT NULL,
    Voornaam varchar(255) NOT NULL,
    Leeftijd int
);

Voorbeeld SQL NOT NULL op ALTER TABLE:


ALTER TABLE Personen
MODIFY Leeftijd int NOT NULL;

UNIQUE contraint

De UNIQUE constraint zorgt ervoor dat alle waarden in een kolom verschillend zijn. Zowel de UNIQUE als de PRIMARY KEY constraint geven een garantie voor unieke waarden in een kolom. Je kan meerdere UNIQUE contraints hebben per tabel, maar slechts één PRIMARY KEY constraint per tabel.

Voorbeeld UNIQUE Constraint op CREATE TABLE:


--SQL Server / Oracle / MS Access:
CREATE TABLE Personen (
    ID int NOT NULL UNIQUE,
    Achternaam varchar(255) NOT NULL,
    Voornaam varchar(255),
    Leeftijd int
);

--MySQL:
CREATE TABLE Personen (
    ID int NOT NULL,
    Achternaam varchar(255) NOT NULL,
    Voornaam varchar(255),
    Leeftijd int,
    UNIQUE (ID)
);

--MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Personen (
    ID int NOT NULL,
    Achternaam varchar(255) NOT NULL,
    Voornaam varchar(255),
    Leeftijd int,
    CONSTRAINT UC_Persoon UNIQUE (ID,Achternaam)
    /* UC_Persoon is hier de naam voor de constraint
     UC = Unique Constraint */
);

Voorbeeld UNIQUE Constraint op ALTER TABLE:


--MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Personen
ADD UNIQUE (ID); 

--MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Personen
ADD CONSTRAINT UC_Persoon UNIQUE (ID,Achternaam);
/* UC_Persoon is hier de naam voor de constraint
 UC = Unique Constraint */

Voorbeeld DROP een UNIQUE Constraint:


--MySQL:
ALTER TABLE Personen
DROP INDEX UC_Persoon; 

--SQL Server / Oracle / MS Access:
ALTER TABLE Personen
DROP CONSTRAINT UC_Persoon;

PRIMARY KEY contraint

De PRIMARY KEY constraint geeft een unieke ID aan elk record in een tabel.Primary keys moeten UNIQUE waarden bevatten en mogen geen NULL waarden hebben.Een tabel mag slechts één Primary key hebben. Een Primary key kan bestaan uit één of meerdere kolommen.

Voorbeeld PRIMARY KEY op CREATE TABLE:


--MySQL:
CREATE TABLE Personen (
    ID int NOT NULL,
    Achternaam varchar(255) NOT NULL,
    Voornaam varchar(255),
    Leeftijd int,
    PRIMARY KEY (ID)
);

--SQL Server / Oracle / MS Access:
CREATE TABLE Personen (
    ID int NOT NULL PRIMARY KEY,
    Achternaam varchar(255) NOT NULL,
    Voornaam varchar(255),
    Leeftijd int
);

--MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Personen (
    ID int NOT NULL,
    Achternaam varchar(255) NOT NULL,
    Voornaam varchar(255),
    Leeftijd int,
    CONSTRAINT PK_Persoon PRIMARY KEY (ID,Achternaam)
);
/*In bovenstaand voorbeeld bestaat de 
PRIMARY KEY uit 2 kolommen (ID + Achternaam).*/

Voorbeeld PRIMARY KEY op ALTER TABLE:


--MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Personen
ADD PRIMARY KEY (ID);

--MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Personen
ADD CONSTRAINT PK_Persoon PRIMARY KEY (ID,Achternaam);
/*Als je een primary key toevoegt aan een 
bestaande tabel, dan moet de kolom/kolommen 
al een NOT NULL constraint hebben.*/

Voorbeeld DROP een PRIMARY KEY Constraint:


--MySQL:
ALTER TABLE Personen
DROP PRIMARY KEY; 

--SQL Server / Oracle / MS Access:
ALTER TABLE Personen
DROP CONSTRAINT PK_Persoon;

FOREIGN KEY contraint

Een FOREIGN KEY wordt gebruikt om twee tabellen aan elkaar te koppelen.

Een FOREIGN KEY is één of meerdere velden in een tabel die refereert naar de PRIMARY KEY in een andere tabel. De tabel met de FOREIGN KEY is de kind tabel en de andere tabel is de ouder tabel.

De FOREIGN KEY constraint wordt gebruikt om te voorkomen dat verbindingen tussen tabellen kapot gemaakt worden.

De FOREIGN KEY constraint zorgt er ook voor dat er geen ongeldige gegevens in de FOREIGN KEY kolom komt, want het moeten waarden zijn die in de andere tabel ook voorkomen.

Voorbeeld FOREIGN KEY op CREATE TABLE:


--MySQL:
CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersoonID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (PersoonID) REFERENCES Personen(PersoonID)
);

--SQL Server / Oracle / MS Access:
CREATE TABLE Orders (
    OrderID int NOT NULL PRIMARY KEY,
    OrderNumber int NOT NULL,
    PersoonID int FOREIGN KEY REFERENCES Personen(PersoonID)
);

--MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersoonID int,
    PRIMARY KEY (OrderID),
    CONSTRAINT FK_PersoonOrder FOREIGN KEY (PersoonID)
    REFERENCES Personen(PersoonID)
);

Voorbeeld FOREIGN KEY op ALTER TABLE:


--MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Orders
ADD FOREIGN KEY (PersoonID) REFERENCES Personen(PersoonID);

--MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Orders
ADD CONSTRAINT FK_PersoonOrder
FOREIGN KEY (PersoonID) REFERENCES Personen(PersoonID);

Voorbeeld DROP een FOREIGN KEY Constraint:


--MySQL:
ALTER TABLE Orders
DROP FOREIGN KEY FK_PersoonOrder;

--SQL Server / Oracle / MS Access:
ALTER TABLE Orders
DROP CONSTRAINT FK_PersoonOrder;

CHECK contraint

De CHECK Constraint wordt gebruikt om de waarden die in een kolom geplaatst kunnen worden te beperken.

Als je een CHECK constraint zet op een enkele kolom, dan beperk je de waarden die in die kolom mogen.

Als je een CHECK constraint zet op een tabel, dan beperk je de waarden die in bepaalde kolommen mogen afhankelijk van waarden in andere kolommen in de rij.

Voorbeeld CHECK op CREATE TABLE:


--MySQL:
CREATE TABLE Personen (
    ID int NOT NULL,
    Achternaam varchar(255) NOT NULL,
    Voornaam varchar(255),
    Leeftijd int,
    CHECK (Leeftijd>=18)
);

--SQL Server / Oracle / MS Access:
CREATE TABLE Personen (
    ID int NOT NULL,
    Achternaam varchar(255) NOT NULL,
    Voornaam varchar(255),
    Leeftijd int CHECK (Leeftijd>=18)
);

--MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Personen (
    ID int NOT NULL,
    Achternaam varchar(255) NOT NULL,
    Voornaam varchar(255),
    Leeftijd int,
    Stad varchar(255),
    CONSTRAINT CHK_Persoon CHECK (Leeftijd>=18 AND Stad='Schiedam')
);

Voorbeeld CHECK op ALTER TABLE:


--MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Personen
ADD CHECK (Leeftijd>=18);

--MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Personen
ADD CONSTRAINT CHK_PersoonLeeftijd CHECK (Leeftijd>=18 AND Stad='Schiedam');

Voorbeeld DROP een CHECK Constraint:


--SQL Server / Oracle / MS Access:
ALTER TABLE Personen
DROP CONSTRAINT CHK_PersoonLeeftijd;
 
--MySQL:
ALTER TABLE Personen
DROP CHECK CHK_PersoonLeeftijd;

DEFAULT contraint

De DEFAULT Constraint wordt gebruikt om een defaultwaarde toe te kennen aan een kolom. Deze waarde zal aan records gegeven worden die geen waarde kregen toegewezen. Via de DEFAULT CONSTRAINT kunnen ook systeemwaarden worden toegekend door een functie als GETDATE().

Voorbeeld DEFAULT op CREATE TABLE:


--My SQL / SQL Server / Oracle / MS Access:
CREATE TABLE Personen (
    ID int NOT NULL,
    Achternaam varchar(255) NOT NULL,
    Voornaam varchar(255),
    Leeftijd int,
    Stad varchar(255) DEFAULT 'Schiedam'
);

CREATE TABLE Orders (
    ID int NOT NULL,
    OrderNumber int NOT NULL,
    OrderDatum date DEFAULT GETDATE()
);

Voorbeeld DEFAULT op ALTER TABLE:


--MySQL:
ALTER TABLE Personen
ALTER Stad SET DEFAULT 'Schiedam';

--SQL Server:
ALTER TABLE Personen
ADD CONSTRAINT df_Stad 
DEFAULT 'Schiedam' FOR Stad;

--MS Access:
ALTER TABLE Personen
ALTER COLUMN Stad SET DEFAULT 'Schiedam';

--Oracle:
ALTER TABLE Personen
MODIFY Stad DEFAULT 'Schiedam';

Voorbeeld DROP een DEFAULT Constraint:


--MySQL:
ALTER TABLE Personen
ALTER Stad DROP DEFAULT;

--SQL Server / Oracle / MS Access:
ALTER TABLE Personen
ALTER COLUMN Stad DROP DEFAULT;

INDEX contraint

De CREATE INDEX wordt gebruikt om indexen te maken in tabellen. Indexen worden gebruikt om gegevens sneller op te kunnen zoeken. je ziet de indexen niet, ze versnellen alleen de zoekopdrachten. Een tabel bijwerken waar een index op staat kost juist weer meer tijd omdat ook de index bijgewerkt moet worden. Een index is dus vooral handig op een kolom waar veel op gezocht wordt.

CREATE INDEX algemene vorm:


--dubbele waarden toegestaan:
CREATE INDEX index_name
ON tabelnaam (kolom1, kolom2, ...);

CREATE UNIQUE INDEX algemene vorm:


--dubbele waarden niet toegestaan:
CREATE UNIQUE INDEX index_name
ON tabelnaam (kolom1, kolom2, ...);

CREATE INDEX voorbeeld:


CREATE INDEX idx_Achternaam
ON Personen (Achternaam);

CREATE INDEX idx_pnaam
ON Personen (Achternaam, Voornaam);

DROP INDEX algemene vorm:


--MS Access:
DROP INDEX index_naam ON tabelnaam;

--SQL Server:
DROP INDEX tabelnaam.index_naam;

--DB2/Oracle:
DROP INDEX index_naam;

--MySQL:
ALTER TABLE tabelnaam
DROP INDEX index_naam;

AUTO INCREMENT

AUTO INCREMENT genereert automatisch een uniek nummer als een nieuw record wordt toegevoegd in de tabel. Dit is vaak het veld dat als PRIMARY KEY wordt gebruikt.

Algemene vorm voor MySQL:


CREATE TABLE Personen (
    Persoonid int NOT NULL AUTO_INCREMENT,
    Achternaam varchar(255) NOT NULL,
    Voornaam varchar(255),
    Leeftijd int,
    PRIMARY KEY (Persoonid)
);

Als je een nieuw record toevoegd, dan hoef je het veld dat een AUTO_INCREMENT heeft niet op te geven, dit wordt automatisch gevuld.

Standaard is de startwaarde voor AUTO_INCREMENT 1 en er zal steeds 1 bij worden opgeteld voor elk nieuw record.

Als je een andere startwaarde wil dan kan dat met:


ALTER TABLE Personen AUTO_INCREMENT=100;

Algemene vorm voor SQL Server


CREATE TABLE Personen (
    Persoonid int IDENTITY(1,1) PRIMARY KEY,
    Achternaam varchar(255) NOT NULL,
    Voornaam varchar(255),
    Leeftijd int
);

De MS SQL Server gebruikt IDENTITY voor de auto-increment functie. De twee waarden die worden meegegeven via IDENTITY zijn de startwaarde en de stapgrootte van de verhoging.

Algemene vorm voor Access:


CREATE TABLE Personen (
    Persoonid AUTOINCREMENT PRIMARY KEY,
    Achternaam varchar(255) NOT NULL,
    Voornaam varchar(255),
    Leeftijd int
);

MS Access gebruikt AUTOINCREMENT voor de auto-increment functie. Standaard is de startwaarde 1 en de stapgrootte 1, maar je kan extra paramaters aan AUTOINCREMENT meegeven om dit aan te passen, bijvoorbeeld AUTOINCREMENT(100,2) voor een startwaarde 100 en stapgrootte 2.

Algemene vorm voor Oracle:

Oracle heeft een wat andere methode bedacht.

Je moet een auto-increment veld maken met het sequence object (dit object genereert een nummer reeks).

CREATE SEQUENCE algemene vorm:


CREATE SEQUENCE seq_Persoon
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10;

Hiermee maak je een sequence object met als naam seq_Persoon, die start met 1 en een stapgroote heeft van 1. De cache wordt in dit voorbeeld ingesteld op 10 waarden ten behoeve van de performance. De cache waarde geeft aan hoeveel waarden er in het geheugen worden bewaard voor snellere toegang.Om een nieuw record toe te voegen moet je de nextval functie gebruiken. Deze functie haalt de volgende waarde op van seq_Persoon:


INSERT INTO Personen (Persoonid,Voornaam,Achternaam)
VALUES (seq_Persoon.nextval,'Jan','Smit');