Primaarvõti – kas tehniline või naturaalvõti?
27.08.2006 | GunnarVäike arutluskäik andmebaasi tabelite primaarvõtmete teemal. Kogemus andmebaasidega tegelemisel näitab, et see teema vajab valgustamist, sest tihtipeale ei tea tehnilise võtme kasutajad seda, miks nad just tehnilist võtit kasutavad ja naturaalvõtme kasutajad ei saa aru, et nad kõnnivad ringi kummagi jala otsas plekkämber kolisemas ning liikumist takistamas. Mina valin primaarvõtmeks alati tehnilise võtme ning mul on selle kohta ka omad põhjendused. Teisel seisukohal olijad tundku ennast vabalt ja esitagu omad seisukohad.
Tehniline võti
Tehniliseks võtmeks nimetatakse (unikaalset, minimaalset) kandidaatvõtit, mille väljade väärtuste kombinatsioon võimaldab antud tabeli rea alati eksimatult identifitseerida ning millesse kuuluvatel väljadel puudub seos andmemudeliga. Tehniliseks võtmeks võib olla näiteks automaatselt suureneva väärtusega numbriline väli, mis on alati unikaalne. MySQL-is saab tabeli tehniliseks võtmeks valida täisarvulise välja, mille auto_increment atribuudil on tõene väärtus. MS Accessis on numbrilisel väljal vastavaks atribuudiks Auto Numeric. Oracle’i andmebaasiserverites, näiteks, kasutatakse spetsiaalseid numereerimise objekte, mille nimetuseks on sequence. Sequence’i käest saab alati küsida tema järgmise väärtuse, et omistada see sisestava rea tehnilise võtme väljale.
Miks tehniline primaarvõti?
Tehnilisel võtmel puudub andmemudeli andmete loogikaga igasugune seos. Tehnilise võtme väli saab väärtuse ühe korra, siis kui vastav rida tabelisse sisestatakse ning rohkem tehnilisse võtmesse kuuluvate väljade väärtus ei muutu. See tähendab seda, et tehniline võti on ainuke võimalik valikuvariant primaarvõtmeks, sest naturaalvõtme väljade väärtused võivad muutuda, samuti võivad nad rikkuda unikaalsuse tingimust. Seda kõike tänu sellele, et inimtegevusega on alati seotud mõningane vigade tekke tõenäosus - me pole ideaalsed ja me teeme vigu. Seega ei sobi ka meie andmemudeli loogikat kandvad väljad primaarvõtmesse. Tehniline võti on sellest puudusest prii.
Tabelite vahelised seosed
Ka tehnilise võtme korral on alluvates tabelites võõrvõtmeks väli, mis vastab ülemise tabeli primaarvõtmele. See tähendab seda, et tehnilise primaarvõtme korral on meil ka tehnilised võõrvõtmed. Samuti tähendab see seda, et ülemise tabeli naturaalvõtme väärtuste saamiseks peame me päringusse alati kaasama ka ülemise tabeli. See mõjutab küll jõudlust natuke, kuid ajaline kokkuhoid süsteemi disainis ja hoolduses kaalub selle tavaliselt üles.
Naturaalvõti
Naturaalvõtmeks nimetatakse (unikaalset, minimaalset) kandidaatvõtit, mille väljade väärtuste kombinatsioon võimaldab antud tabeli rea alati eksimatult identifitseerida ning millesse kuuluvad väljad sisaldavad andmemudeli loogikat kandvaid välju. Näiteks valitakse füüsiliste isikute tabeli primaarvõtmeks tihtipeale isikukood. Ülikooli õppekavade tabeli primaarvõtmeks on õppekava kood, raamatu primaarvõtmeks on raamatu unikaalne kood jne. Palju on selliseid juhtumeid, kus naturaalvõtme väljade arv on suurem ühest, miks mitte kuni neli või viis välja.
Tabelite vahelised seosed
Naturaalvõtme korral tuleb samuti kõik primaarvõtme väljad kanda alluva tabeli võõrvõtmeks. Kuigi tabelite ühendamise tingimusi on päringutes rohkem, saab nii mõnessegi päringusse kaasata oluliselt vähem tabeleid, sest võõrvõtme väljad sisaldavad meile olulist infot. Halb külg on see, et mida sügavamale me mööda tabelite vahelisi seoseid sammume, seda suuremaks muutub alluvate tabelite primaarvõtme väljade arv. Eeldades, et iga alluv tabel toob unikaalsuse tingimusena naturaalvõtmesse kaasa ühe välja, võime koostada valemi primaarvõtme väljade arvu kohta t-ndal tasemel asetsevas tabelis:
kus on n on primaarvõtme väljade arv esimese taseme tabelis ning t on vaadeldava tabeli taseme arv tabelite hierarhias. Näiteks, kui tabelile isik allub tabel palk ja sellele allub tabel palga_rida, siis tabeli palga_rida primaarvõtme väljade arvuks on
Kui isikute tabeli primaarvõtmeks on isikukood, siis on esimese taseme tabelis primaarvõtme välju üks ning seega n=1. Palga tabeli primaarvõtmeks on isikukood ja isikule vastava palga järjekorra number. Seega teises tasemes on väljade arv meil kaks. Palga rea tabeli primaarvõtme moodustavad isikukood, palga järjekorra number ja palga rea järjekorra number.
Näiteid naturaalvõtme ebaedust
Naturaalvõtme valiku mõttetuse kohta on mul tuua mõned reaalelulised näited. Mõlemal juhul on tegemist sellise ohuga, mille hindamisega vähem kogenud andmebaasi disainerid arvestada ei oska või ei pea selliste ohtude analüüsimist otstarbekaks. Samas olgu selgituseks öeldud, et korra mudelisse kaasatud lolluseid sealt hiljem enam odavalt välja ei juuri.
Isikukood primaarvõtmena
Esimene näide pärineb ammusest ajast, kui ma kirjutasin alltöövõtjana ühte riiklikku registrit, mis tänaseks on ammu liidetud teiste, suuremate rakenduste koosseisu nende üheks osaks. Siin sai tehtud saatuslik viga, et isikute tabeli primaarvõtmeks oli andmemudelis valitud isikukood. Esimesel või teisel piloodil tuli välja, et see valik oli ebaõnnestunud - nimelt tahtsid samas vallas teha teineteisest sõltumatult sama tegevust kaks isikut, kellel oli erinev nimi, kuid sama isikukood.
Loomulikult pidime tegema andmebaasi struktuuris muudatuse ning kirjutama ringi terve hulga päringuid ja süsteemi kasutusliidese koodi. Hetkel, mil neid muudatusi tuli teha, oli suurem osa tööst juba tehtud ning antud muudatuse sisse viimine võttis lisaaega umbes nädala.
Õppekavakood primaarvõtmena
Küllaltki hiljuti oli mul au kokku puutuda probleemiga, mille põhjustas Haridusministeeriumi lohakus. Ülikooli andmebaasi õppekavade tabeli primaarvõtmeks on õppekava kood. Ühel heal päeval akrediteeris Haridusministeerium ära ühe uue õppekava ning enne kui keegi jõudis märgata, et uuele kavale omistatud kood on juba kasutusel, sai paber alla ministri allkirja ning kõik oli äkki ametlik. Pole vist vaja öelda, et ega keegi seda apsakat enam parandama ei hakanud ministeeriumi poolel.
Mida see aga andmebaasi poolel endaga kaasa tõi? Lühidalt öeldes paraja peavalu. Õppeinfosüsteemides on õppekava väga oluline objekt, ning see käib läbi suuremast osast süsteemi loogikast, jõudes otsapidi välja ka lepingute ja arveldustega seotud andmeteni. Andmebaasis, mille oli disaininud süsteemiga eelnevalt tegelenud firma, oli tabelite primaarvõtmeteks valitud enamasti naturaalvõtmed (kuigi mõnedes kohtades kasutati ka tehnilist võtit; näiteks kohad, kus naturaalvõti hakkas väljade arvult juba liiga priskeks kosuma).
Niisiis tuli lisada õppekava tabeli primaarvõtmesse uus väli. Kokku võttis see muudatus aega umbes nädala, sest peale tabelite primaarvõtmete tuli läbi käia ka kõik andmebaasi protseduurid, mis õppekavade tabelit kasutasid. Kokkuvõttes puudutas muudatus oma paarikümmet tabelit ja andmebaasi protseduuri. Samuti tuli seoses sellega läbi käia päris hea osa süsteemi koodist.
Tehnilise võtme korral oleks muudatus puudutanud ainult õppekavade tabelit. Lisandunud oleks üks väli ning muutunud oleks tabeli unikaalne indeks. Samuti oleks muudatus puudutanud paari-kolme andmebaasi protseduuri ning paari-kolme moodulit süsteemi koodis. See muudatus oleks aega võtnud koos testimisega ühe tööpäeva.
Naturaalvõti ja veebid
Veebides sisaldavad lingid tihti unikaalseid koode, mis vastavad valitud andmeobjekti primaarvõtmele. Näiteks toodete nimekirjas on toote detailinfo lingiks midagi stiilis tooted.php?id=1029 või siis /tooted/1029.html. See on paljuski seepärast nii, et enamasti MySQL-i peal elavate veebide programmeerijad õpivad esimesi samme tehes kiiresti ära, et “auto_increment on id” ja “primaarvõti on alati auto_increment”. Kaugemale nad enamasti mõelda ega süveneda ei viitsi. Seega kasutavad nad parimat lähenemist teadmatult. Seda näitab tihtipeale kood, mis ei sisalda täiendavaid andmeloogika kontrolle, mille järgi tingib vajaduse tehniliste võtmete kasutamine.
Tehnilise võtme korral on tabeli primaarvõtme välju enamasti üks (seega sellele vastavate seoste võõrvõtme otsas istub ainult üks väli). Seda välja on väga hea kasutada linkides. Naturaalvõtme kasutamine muudaks lingid pikaks ning küllaltki segaseks. Kumb on parem, kas üks parameeter lingis või näiteks neli parameetrit lingis?
Kokkuvõte
Näeme, et tehnilist võtit on palju otstarbekam kasutada kui naturaalvõtit, samuti seda, et tehniline võti on ainus võti, mis vastab primaarvõtme nõuetele. Seosed üle tehnilise võtme on kiired, kuna võtmesse kuuluvate väljade arv on minimaalne ning väljad on täisarvulist tüüpi, mis on omakorda andmebaaside jaoks n.ö. “kiire väljatüüp”. Tehnilised võtmed on garanteeritult unikaalsed ning mingite kasutajate poolsete vigadega ei saa juba kasutusel olevaid tehnilise võtme väärtusi uuesti genereerida.