6 Vaikeväärtused ja kitsendused

Selles peatükis vaatame täpsemalt erinevaid vaikeväärtusi ning veergude ja tabelite kitsendusi. Alustame ka suurema andmebaasi loomisega, millesse saame juba järgmises peatükis andmeid lisama hakata.

Meeldetuletuseks, et SQL lause, mille abil on võimalus andmebaasist tabeleid kustutada, on selline:

• DROP TABLE <tabeli nimi>;

Vaikeväärtused

Vahel võib meie andmebaasis olla veerge, mille väärtusi me iga kord käsitsi sisestada ei taha, vaid pigem ainult erandjuhtudel. Selliste veergude korral on võimalik kasutada vaikeväärtusi. Veeru vaikeväärtuseid defineeritakse tabeli loomisel veeru definitsioonis järgmiselt:

• <veeru nimi> <andmetüüp> DEFAULT <vaikeväärtus>

Näiteks eelmises peatükis lõime me tabeli Toode, milles oli veerg lisamiskuupäev. Oleks ju mugav, kui me iga lisatud toote korral ei peaks käsitsi praegust kuupäeva täpsustama, vaid see tehtaks automaatselt meie eest ära. Selleks, et lisada veerule lisamiskuupäev vaikeväärtuseks praegune ajahetk, oleksime me võinud tabeli Toode luua hoopis nii:

• CREATE TABLE Toode (
id SERIAL PRIMARY KEY NOT NULL,
nimi VARCHAR (100) NOT NULL,
hind NUMERIC NOT NULL,
lisamiskuupäev DATE DEFAULT CURRENT_DATE NOT NULL,
aegumiskuupäev DATE NOT NULL
);

CURRENT_DATE, mille me lisasime veeru lisamiskuupäev vaikeväärtuseks, on ise funktsioon, mis viitab praegusele ajahetkele. Vaikeväärtuseks ei pea aga olema funktsioon, vaid kuupäeva võib ka käsitsi välja kirjutada (kujul ‘yyyy-mm-dd’), kui selleks vajadus ilmneb. Samuti saab vaikeväärtusi kasutada ka muude andmetüüpide korral (nt määrata hinna vaikeväärtuseks 1).

NB! Kui te soovite seda SQL lauset ka ise DBeaveris jooksutada, peate te esmalt eelmises peatükis loodud tabeli Toode ära kustutama jooksutades SQL lauset:

• DROP TABLE Toode;

Seda seetõttu, et andmebaasis ei saa olla kahte ühe ja sama nimega tabelit.

Veeru kitsendused

Veeru kitsendusi kasutatakse selleks, et piirata konkreetse veeru väärtusi. Kahe veeru kitsendusega oleme me juba tuttavad – NOT NULL ja PRIMARY KEY.

Lisaks neile kahele kitsendusele on kasutusel veel ka CHECK kitsendus, mis lubab meil täpsustada mingisuguse tingimuse, mida veerg täitma peaks. Kirjapandud tingimus peab alati tulemuseks andma tõeväärtuse true või false.

Näiteks tabeli Toode korral võiks me lisada veerule hind kitsenduse, mis kontrollib, et hind ei ole negatiivne arv. Selleks peaks tabeli loomisel olema veeru hind definitsioon järgnev:

• hind NUMERIC NOT NULL CHECK (hind > 0)

Kitsenduse CHECK (…) korral võib sulgudesse panna ka mitu tingimust, pannes erinevate tingimuste vahele sõna AND või OR. Neist esimest tuleb kasutada juhul, kui kõik kirja pandud tingimused peavad kehtima ning teist juhul, kui piisab vaid ühe tingimuse kehtimisest.

Veel üheks kitsenduseks on UNIQUE, mis nõuab, et kõik veeru väärtused oleksid unikaalsed. Seda oleks mõistlik kasutada näiteks inimese isikukoodi korral, sest me ei soovi, et kahele inimesele saaks sama isikukoodi sisestada.

Tabeli kitsendused

Tabeli kitsendused hõlmavad tihtipeale rohkem kui ühte veergu ning need lisatakse tabeli loomisel SQL lausesse komadega eraldatult peale veeru definitsioone.

Tabeli kitsenduse üldkuju:

• CONSTRAINT <kitsenduse nimi> <kitsendus>

Näiteks tabeli Toode korral võiks me kontrollida, et toote lisamiskuupäev ei oleks peale aegumiskuupäeva. Selle kitsenduse täitmiseks oleks me pidanud looma tabeli Toode järgnevalt:

• CREATE TABLE Toode (
id SERIAL PRIMARY KEY NOT NULL,
nimi VARCHAR (100) NOT NULL,
hind NUMERIC NOT NULL CHECK (hind > 0),
lisamiskuupäev DATE DEFAULT CURRENT_DATE NOT NULL,
aegumiskuupäev DATE NOT NULL,
CONSTRAINT kuupäevad_õiged CHECK (lisamiskuupäev <= aegumiskuupäev)
);

Suure andmebaasi loomine

Nüüd, kui meil on olemas vajalikud teadmised andmetüüpidest, vaikeväärtustest ning kitsendustest, on õige aeg proovida ühte lihtsat tabelit sisaldava andmebaasi asemel luua andmebaas, mis sisaldab endas mitut kompleksset tabelit.

Meie loodava andmebaasi teemaks on Netflix, mis on teadupärast suur voogedastusplatvorm. Alloleval pildil on toodud välja loodava andmebaasi mudel. Uuri sellel olevaid tabeleid, veerge, veergude andmetüüpe ning tabelite vahelisi seoseid.

Nagu mudelilt näha, siis meil on järgnevad tabelid:

• Riigid
• Teosed
• Isikud
• Lavastamine
• Naitlemine

Võib-olla paistab silma ka see, et kui me varasemalt rääkisime, et eesnimi ja perekonnanimi peaks alati eraldatud olema, siis antud mudelil on tabelis Isik need aga ühiseks veeruks nimi kokku liidetud. Selle põhjuseks on see, et Netflixi korral ei vaata me enam isiku nimesid, vaid vahepeal on tegu nn lavanimedega. Näiteks on järgmises peatükis Netflixi andmebaasi lisatavate andmete hulgas muusik 50 Cent, kelle korral me ei saa nime eesnimeks ning perekonnanimeks jaotada.

Proovime nüüd Netflixi andmebaasi mudelil olevad tabelid DBeaverisse lisada. Selleks koosta esmalt ise tabelite loomiseks vajalikud SQL laused, järgides etteantud nõudeid. Seejärel kontrolli enda koostatud SQL lauseid etteantud vastuste abil ja alles siis jooksuta tabelite loomiseks vajalikke SQL lauseid DBeaveri SQL konsoolis.

Tabeli Riigid veerud ning nende nõuded on järgnevad:

• id
  • andmetüüp serial
  • primaarvõti
• nimi
  • andmetüüp varchar (100)
  • ei luba tühjasi väärtusi
• rahvastikuarv
  • andmetüüp bigint
  • ei luba tühjasid väärtusi
  • rahvastikuarv peab olema suurem kui 0 ja väiksem kui 10000000000
• pindala
  • andmetüüp integer
  • ei luba tühjasid väärtusi
  • pindala peab olema suurem kui 0
• skp_elaniku_kohta
  • andmetüüp numeric
  • ei luba tühjasid väärtusi
  • skp_elaniku_kohta peab olema suurem kui 0
• interneti_kasutajad
  • andmetüüp integer
  • ei luba tühjasid väärtusi
  • interneti_kasutajad väärtus peab olema vähemalt 0

Tabelil Riigid peab olema ka tabeli kitsendus kasutajaid_vahem_kui_elanikke, mis kontrollib, et interneti_kasutajad <= rahvastikuarv.

Tabeli Teosed veerud ning nende nõuded on järgnevad:

• id
  • andmetüüp serial
  • primaarvõti
• on_film
  • andmetüüp bit
  • ei luba tühjasi väärtusi
• pealkiri
  • andmetüüp varchar (150)
    
  • ei luba tühjasid väärtusi
• riigi_id
  • andmetüüp integer
• lisamise_kuupaev
  • andmetüüp date
• valjalaske_aasta
  • andmetüüp smallint
  • ei luba tühjasid väärtusi
• vanusepiirang
  • andmetüüp varchar (20)
• kestus
  • andmetüüp varchar (20)
  • ei luba tühjasid väärtusi
• zanr
  • andmetüüp varchar (50)
  • ei luba tühjasid väärtusi
• kirjeldus
  • andmetüüp varchar (500)
  • ei luba tühjasid väärtusi

Tabeli Isikud veerud ning nende nõuded on järgnevad:

• id
  • andmetüüp serial
  • primaarvõti
• nimi
  • andmetüüp varchar (100)
  • ei luba tühjasid väärtusi

Tabeli Lavastamine veerud ning nende nõuded on järgnevad:

• id
  • andmetüüp serial
  • primaarvõti
• teose_id
  • andmetüüp integer
  • ei luba tühjasid väärtusi
• lavastaja_id
  • andmetüüp integer
  • ei luba tühjasid väärtusi

Tabeli Naitlemine veerud ning nende nõuded on järgnevad:

• id
  • andmetüüp serial
  • primaarvõti
• teose_id
  • andmetüüp integer
  • ei luba tühjasid väärtusi
• naitleja_id
  • andmetüüp integer
  • ei luba tühjasid väärtusi

Nüüd, kui vastused on kontrollitud, võib jooksutada neid SQL lauseid DBeaveri SQL konsoolis. Kui sul on soov jooksudada kõiki SQL lauseid korraga, tuleb varasemalt kasutatud käivitamise nupu asemel vajutada alloleval pildil olevat nuppu. See nupp jooksutab konsooli kirjutatut kui skripti, mis tähendab seda, et ühe lause asemel jooksutatakse järjest kõik kirjapandud SQL laused.

Veateadete ilmnemisel kontrolli oma SQL lauset – suure tõenäosusega esineb selles viga. Kui te saate aga veateate: ‘SQL Error [42601]: ERROR: syntax error at or near “)” Position: 1‘, siis see on tingitud sellest, et SQL lauses on tühjad read näiteks veeru definitsioonide vahel. Kustuta SQL lausest kõik tühjad read ning proovi seejärel uuesti.

Kindlasti kontrolli, et pärast kõikide tabelite loomist oleksid need ka DBeaveris nähtaval. Kui tabelid ei ole nähtaval ning SQL lausete jooksutamisel vigu ei esinenud, proovi andmebaasi vaadet värskendada. Selleks parem klikka andmebaasi public peal ning avanenud valikutest klikka Refresh.