Luku 6

Tiedon eheys

Tiedon eheys viittaa siihen, että tietokannassa oleva tieto on paikkansa pitävää ja ristiriidatonta. Päävastuu tiedon laadusta on toki käyttäjällä tai sovelluksella, joka muuttaa tietokantaa, mutta myös tietokannan suunnittelija voi vaikuttaa asiaan lisäämällä tauluihin ehtoja, jotka tarkkailevat tietokantaan syötettävää tietoa.

Sarakkeiden ehdot

Voimme määrittää taulun luonnin yhteydessä sarakkeisiin liittyviä ehtoja, joita tietokantajärjestelmä valvoo tiedon lisäämisen ja muuttamisen yhteydessä. Näillä ehdoilla voi ohjata sitä, millaista tietoa tietokantaan ilmestyy. Tyypillisiä ehtoja ovat seuraavat:

UNIQUE

Ehto UNIQUE tarkoittaa, että kyseisessä sarakkeessa tulee olla eri arvo joka rivillä. Esimerkiksi seuraavassa taulussa vaatimuksena on, että joka tuotteella on eri nimi:

CREATE TABLE Tuotteet (id INTEGER PRIMARY KEY, nimi TEXT UNIQUE, hinta INTEGER);

Ehto UNIQUE voi kohdistua myös useampaan sarakkeeseen, jolloin se merkitään erikseen sarakkeiden jälkeen:

CREATE TABLE Tuotteet (id INTEGER PRIMARY KEY, nimi TEXT, hinta INTEGER, UNIQUE(nimi,hinta));

Tämä tarkoittaa, että taulussa ei voi olla kahta riviä, joilla on sama nimi ja sama hinta.

NOT NULL ja DEFAULT

Ehto NOT NULL tarkoittaa, että kyseisessä sarakkeessa ei saa olla arvoa NULL. Esimerkiksi seuraavassa taulussa tuotteen hinta ei saa olla tyhjä:

CREATE TABLE Tuotteet (id INTEGER PRIMARY KEY, nimi TEXT, hinta INTEGER NOT NULL);

Tähän liittyy myös määre DEFAULT, jonka seurauksena sarake saa tietyn oletusarvon, jos sille ei anneta arvoa rivin lisäämisessä. Esimerkiksi voimme määrittää oletusarvon 0 näin:

CREATE TABLE Tuotteet (id INTEGER PRIMARY KEY, nimi TEXT, hinta INTEGER DEFAULT 0);

CHECK

Yleisempi tapa luoda ehto on käyttää avainsanaa CHECK, jonka jälkeen voi kirjoittaa minkä tahansa ehtolausekkeen. Esimerkiksi seuraava komento luo taulun tuotteista, jossa rivin ehtona on hinta >= 0 eli hinta ei saa olla negatiivinen:

CREATE TABLE Tuotteet (id INTEGER PRIMARY KEY, nimi TEXT, hinta INTEGER, CHECK (hinta >= 0));

Ehtojen valvonta

Ehtojen hyötynä on, että tietokantajärjestelmä valvoo niitä ja kieltäytyy tekemästä lisäystä tai muutosta, joka rikkoisi ehdon. Seuraavassa on esimerkki tästä SQLitessä:

sqlite> CREATE TABLE Tuotteet (id INTEGER PRIMARY KEY, nimi TEXT, hinta INTEGER, CHECK (hinta >= 0));
sqlite> INSERT INTO Tuotteet(nimi,hinta) VALUES ('retiisi',4);
sqlite> INSERT INTO Tuotteet(nimi,hinta) VALUES ('selleri',7);
sqlite> INSERT INTO Tuotteet(nimi,hinta) VALUES ('nauris',-2);
Error: CHECK constraint failed: Tuotteet
sqlite> SELECT * FROM Tuotteet;
1|retiisi|4
2|selleri|7
sqlite> UPDATE Tuotteet SET hinta=-2 WHERE id=2;
Error: CHECK constraint failed: Tuotteet

Kun koetamme lisätä tauluun Tuotteet rivin, jossa hinta on negatiivinen, tämä rikkoo ehdon hinta >= 0 ja SQLite ei salli rivin lisäämistä vaan antaa virheen CHECK constraint failed: Tuotteet. Samalla tavalla käy, jos koetamme muuttaa olemassa olevan rivin sarakkeen hinnan negatiiviseksi jälkeenpäin.

Viittausten ehdot

Voimme liittää myös tauluihin ehtoja, jotka pitävät huolen siitä, että tauluissa olevat viittaukset viittaavat todellisiin riveihin. Tämä tapahtuu luomalla viiteavain (foreign key), joka ilmaisee, mihin taulussa oleva rivi viittaa.

Tarkastellaan esimerkkinä seuraavia tauluja:

CREATE TABLE Opettajat (id INTEGER PRIMARY KEY, nimi TEXT);
CREATE TABLE Kurssit (id INTEGER PRIMARY KEY, nimi TEXT, opettaja_id INTEGER);

Tässä tarkoituksena on, että taulun Kurssit sarake opettaja_id viittaa taulun Opettajat sarakkeeseen id, mutta tietokannan käyttäjä voi antaa sarakkeen opettaja_id arvoksi mitä tahansa (esim. luvun 123), jolloin tietokannan sisältö muuttuu epämääräiseksi.

Voimme parantaa tilannetta kertomalla taulun Kurssit luonnissa, että sarake opettaja_id on viiteavain tauluun Opettajat:

CREATE TABLE Kurssit (id INTEGER PRIMARY KEY, nimi TEXT, opettaja_id INTEGER REFERENCES Opettajat);

Tämän jälkeen voimme luottaa siihen, että taulussa Kurssit sarakkeen opettaja_id arvot viittaavat todellisiin riveihin taulussa Opettajat.

Tässä on esimerkki viiteavaimen käyttämisestä:

sqlite> PRAGMA foreign_keys = ON;
sqlite> CREATE TABLE Opettajat (id INTEGER PRIMARY KEY, nimi TEXT);
sqlite> CREATE TABLE Kurssit (id INTEGER PRIMARY KEY, nimi TEXT, opettaja_id INTEGER
   ...>                       REFERENCES Opettajat);
sqlite> INSERT INTO Opettajat (nimi) VALUES ('Kaila');
sqlite> INSERT INTO Opettajat (nimi) VALUES ('Kivinen');
sqlite> SELECT * FROM Opettajat;
1|Kaila
2|Kivinen
sqlite> INSERT INTO Kurssit (nimi, opettaja_id) VALUES ('Laskennan mallit',2);
sqlite> INSERT INTO Kurssit (nimi, opettaja_id) VALUES ('Ohjelmoinnin perusteet',123);
Error: FOREIGN KEY constraint failed   

Taulussa Opettaja on kaksi opettajaa, joiden id-numerot ovat 1 ja 2. Niinpä kun koetamme lisätä tauluun Kurssit rivin, jossa opettaja_id on 123, SQLite ei salli tätä vaan saamme virheilmoituksen FOREIGN KEY constraint failed.

Viittaukset ja poistot

Viittausten ehtoihin liittyy tavallisia sarakkeiden ehtoja mutkikkaampia tilanteita, koska viittaukset ovat kahden taulun välisiä. Erityisesti mitä tapahtuu, jos taulusta yritetään poistaa rivi, johon viitataan toisen taulun rivillä?

Yleensä oletuksena tietokannoissa riviä ei voi poistaa, jos siihen on viittaus muualta. Esimerkiksi jos koetamme äskeisen esimerkin päätteeksi poistaa taulusta Opettajat rivin 2, tämä ei onnistu, koska siihen viitataan taulussa Kurssit:

sqlite> DELETE FROM Opettajat WHERE id=2;
Error: FOREIGN KEY constraint failed

Halutessamme voimme kuitenkin määrittää taulun luonnissa tarkemmin, mitä tapahtuu tässä tilanteessa. Esimerkiksi yksi vaihtoehto on ON DELETE CASCADE, mikä tarkoittaa, että rivin poistuessa myös siihen viittaavat rivit poistetaan. Saamme tämän aikaan näin:

CREATE TABLE Kurssit (id INTEGER PRIMARY KEY, nimi TEXT,
                      opettaja_id INTEGER REFERENCES Opettajat ON DELETE CASCADE);

Nyt jos tietokannasta poistetaan opettaja, niin samalla poistetaan automaattisesti kaikki kurssit, joita hän opettaa. Tämä on kuitenkin usein kyseenalainen vaihtoehto, koska tämän seurauksena tietokannan tauluista voi kadota yllättäen tietoa.

Jatka tästä seuraavaan osaan: