Tabelite loomine ja välisvõti
Tabelite loomine
Tabeli loomise lause SQL keeles on kujul:
CREATE TABLE <tabeli nimi> ( ... );
Sulgudesse kirjutatakse esmalt komadega eraldatud veergude definitsioonid ning lõpus tabeli kitsendused.
Veeru definitsioon on kujul:
<veeru nimi> <veeru andmetüüp> [NOT NULL] [vaikeväärtused] [veeru kitsendused]
Enne tabeli loomise lause kirjutamist tuleks alati mõelda, mis on selle atribuudid, mis andmetüüpidega need peavad olema, milliste atribuutide korral võiks lubada väärtuse sisestamata jätmist või siis hoopis vaikeväärtuse sisestamist ning mis on ärireeglitest ja valdkonnast tulenevad kitsendused.
Olgu meil vaja väikebusse rentiva firma jaoks luua andmebaas, mille üks olemitüüp on bussid ja mis kirjeldaks renditavaid busse. Kuna tegemist on bussidega, mida renditakse B-kategooria juhiluba omavatele juhtidele, siis nende busside kaasreisijate arv ei tohi ületada 8. Bussi rentides soovib rentija teada nii kaasreisijate arvu kui ka, mis kütuseliigiga on tegemist, kas automaatkäigukastiga ja kui palju võtab kütet 100km kohta. Loome sellise tabeli arvestades, et on ka atribuute, mida vajab rendifirma.
CREATE TABLE Bussid ( id serial NOT NULL primary key, Reg_nr varchar(10) NOT NULL, VIN_kood varchar(17) NOT NULL, Valjalaskeaeg date NOT NULL CHECK (valjalaskeaeg<current_date), Kytuseliik_ID integer NOT NULL, Kytusekulu numeric(3,1) CHECK (kytusekulu>0), Automaat bit(1) NOT NULL, Istekohti integer NOT NULL CHECK ((istekohti<10) AND (istekohti>0)), sisestatud date NOT NULL DEFAULT current_date CHECK (sisestatud>valjalaskeaeg) );
Siin NOT NULL CHECK ((istekohti<10) AND (istekohti>0)) on veerukitsendus, sest see hõlmab ühte veergu. Selle korral kontrollitakse, kas istekohti on enam kui 0 ja vähem kui 10 (ehk siis 1-9). Samamoodi on veeru kitsendused lisatud bussi väljalaskeajale ja kütusekulule.
Tabeli loomise lause viimasele reale lisatud kitsendus CHECK (sisestatud>valjalaskeaeg) on tabeli kitsendus, sest see hõlmab kahte veergu: kontrollib, kas sisestamisaeg on suurem kui bussi väljalaskeaeg. Kõik sellised kitsendused aitavad vältida sisestamisvigu. CHECK tingimus võib sisaldada ka hulka. Näiteks kui oleks atribuut
sugu char(1) not null check (sugu in ('m', 'f'))
siis selle atribuudi korral kontrollitakse, et sugu oleks kas m (male) või f (female).
Tabeli kitsendusele võib anda ka nime. Näiteks kui looksime tabeli Lemmikloomad, siis selles võiks olla primaarvõtmeks kolme atribuudi kombinatsioon: nimi, synniaeg ja omaniku_ID (ühel omanikul pole kaht lemmiklooma, mis oleks sama nime ja sama sünniajaga). Sellisel juhul võiksime tabeli kitsenduseks kirjutada:
CONSTRAINT pk_lemmikloomad PRIMARY KEY (nimi, synniaeg, omaniku_ID)
Siin lõime tabeli kitsenduse nimega pk_lemmikloomad ja määrasime atribuutide kombinatsiooni, mis on selle tabeli primaarvõtmeks.
Pöördume nüüd tagasi tabeli Bussid juurde. Selles tabelis on kütusekulu asemel Kytuseliik_ID ja see on täisarvulist tüüpi, sest kütuseliigi jaoks oleks otstarbekas luua eraldi olemitüüp, et ei tekiks andmeliiasust. Seega loome ka tabeli Kytuseliigid:
CREATE TABLE Kytuseliigid ( id serial NOT NULL primary key, Nimi varchar(100) NOT NULL );
Välisvõtmed
Ühe välisvõtme lõite praktikumis, neist oli juttu ka loengus, kuid siin veidi tehnilisemalt, kuidas PostgreSQLis välisvõtmeid luua. Välisvõtme loomisel peavad seotavate atribuutide andmetüübid täpselt kattuma, näiteks peavad mõlemad olema täisarvulist tüüpi. NB! Kas serial andmetüübiga atribuut on täisarvulist tüüpi.
Loome seose ülaloodud tabelite Bussid ja Kytuseliigid vahel.
Välisvõti on kui kitsendus (tuleta meelde loengust, mis kitsendusi see loob) ja seega on vaja lisada kitsendus sellele tabelile, kus välisvõti (antud näites atribuut Kytuseliik_ID) asub. Ehk siis meie peame muutma tabelit Bussid ja lisama sellesse kitsenduse. Välisvõtme loomise lause koos kommentaaridega oleks:
ALTER TABLE Bussid /* Muudame tabelit Bussid, sest selles asub välisvõti */ ADD CONSTRAINT fk_bussid2kytuseliik /* Lisame kitsenduse ja paneme sellele nime */ FOREIGN KEY (Kytuseliik_ID) /* Määrame, et see kitsendus on välisvõti ja selleks välisvõtmeks saab atribuut kytuseliik_ID (noole algus) */ REFERENCES Kytuseliigid(id) /* Näitame, millise tabeli ja millise atribuudiga seos on (kuhu nool läheb) */ ON DELETE RESTRICT /* Määrame, mida teha kui ülemtabelis (Kytuseliigid) tahetakse kustutada mõnda kütuseliiki, mille id on alamtabelis (Bussid). RESTRICT tähendab, et me ei luba sellisel juhul seda kütuseliiki kustutada */ ON UPDATE CASCADE /* Määrame, mida teha kui ülemtabelis (Kytuseliigid) tahetakse muuta mõne kütuseliigi id-d, mille id on alamtabelis (Bussid). CASCADE tähendab, et muutustega minnakse kaasa ehk siis koheselt muutub see id ka Busside tabelis. */;
Nagu näete, siis tabelite seostamine välisvõtmetega aitab andmebaasi paremini hallata ning kontrollida. Seda just olukordades, kui tabelites olevaid andmeid või ka tabeleid ise muutma või kustutama hakatakse.
Kui soovid kontrollida, kas oskad luua välisvõtit, ava test.
| Operaatorid UNION ja JOIN | Vaadete loomine |