Serie blogposts over databases en zaken die daarbij horen (DEEL 1)

De gemiddelde database kent een groot aantal datatypen, de meest gebruikte (uit de lijst van PostgreSQL):

bigint
signed acht-byte integer

bigserial
serial8 autoincrement acht-byte integer

bit
bit string van vaste lengte

bit varying(n) (varbit(n))
bit string van variabele lengte

boolean (bool)
logische Boolean (true/false)

bytea
binaire data

character varying(n) (varchar(n))
variable-length character string

character(n) (char(n))
character string van vaste lengte

cidr
IPv4 of IPv6 netwerk addres

date
datum

double precision (float8)
floating point nummer

inet
IPv4 of IPv6 host addres

integer (int, int4)
signed vier-byte integer

interval(p)
stappen in tijd

macaddr
MAC addres

money
geld

numeric [ (p, s) ] (decimal [ (p, s) ])
exact getal met selecteerbare precisie

real (float4)
floating point nummer met enkele precisie

smallint (int2)
signed twee-byte integer

serial (serial4)
autoincrement vier-byte integer

text
string van variabele lengte

time [ (p) ] [ without time zone ]
tijd

time [ (p) ] with time zone (timetz)
tijd met tijdzone

timestamp [ (p) ] [ without time zone ] (timestamp)
datum en tijd

timestamp [ (p) ] with time zone (timestamptz)
datum and tijd, met tijdzone

Voor limieten op de datatypen verwijs ik je graag naar de manual.

Zoals je ziet: een mooie lijst, en nog lang niet alles.
Daarnaast zijn er nog zaken als Domains, Composite Types e.d. maar dat ligt buiten de scope van deze post.


De Foreign Key (FK)

Een goed opgezette database bestaat altijd uit meerdere tabellen. Deze tabellen hebben in de meeste gevallen een bepaalde koppeling met elkaar (denk hierbij bijvoorbeeld aan het koppelen van een gebruiker aan een forum bericht).
De kolom user_id in de forum posts tabel verwijst dus naar een bepaald record in de users tabel. Een "foreign tuple". Om op dit vlak problemen te voorkomen zoals ontbrekende records in de users tabel of het aanwezig zijn van een bericht in de posts tabel zonder dat de user aanwezig is (hoe je er ook naar kijkt, goed is het niet) hebben we foreign keys.
De FK zorgt er in feite voor dat je database niet als los zand aan tegen elkaar hangt, maar dat het een stevig geheel is dat boven alles consistent te houden is.
De foreign key voor dit geval zouden we als volgt opbouwen:
Omdat er een bepaalde actie moet optreden op de forum posts tabel leggen we vanaf deze tabel een FK van user_id naar het bijhorende veld in de users tabel.
We hebben nu een keuze te maken: moet een bericht verwijderd worden als een user verwijderd wordt of mag een user niet verwijderd worden als hij berichten gepost heeft.
Hiervoor gebruiken we de CASCADE of RESTRICT regel.
In het volgende voorbeeld implementeer ik een FK met RESTRICT regel (user mag niet verwijderd worden wanneer hij al posts heeft)

CREATE TABLE users (
user_id SERIAL PRIMARY KEY,
username VARCHAR,
...
);

CREATE TABLE posts (
post_id SERIAL PRIMARY KEY
user_id INTEGER REFERENCES users (user_id) ON DELETE RESTRICT,
...
);

De bovenstaande tabellen zijn nu onlosmakelijk aan elkaar verbonden. Je kunt geen berichten meer toevoegen zonder bestaande user id en een user kan niet meer verwijderd worden zodra er een bericht bestaat.
Een extra deel is het zgn. DEFERRABLE zijn van een FK (het uitstellen van controles tot het einde van je transactie). Dit is handig wanneer je bijvoorbeeld een circulaire FK hebt liggen (2 records zijn afhankelijk van elkaar, maar worden in dezelfde transactie toegevoegd).
Let hierbij wel op: het gebruiken van transacties is in dit geval vereist. Dat is het eigenlijk sowieso, maar ik weet dat de meesten zich hier niet aan houden, of ze hebben de mogelijkheid niet.


Goed, dat was zo ongeveer de eerste post, in elkaar gezet als haastklus. Ik heb het ook wel eens druk, zoals sommige mensen zullen weten...
Reacties zijn natuurlijk welkom, vooral gebreken aan informatie e.d. heb ik veel aan, dit kan ik dan altijd nog toevoegen.