Tietokannan rakenteen määritys (DDL)

Tietokannan rakenteen määrittäminen (Data Definition Language, DDL; kutsutaan joskus myös nimellä Schema Manipulation Language, SML) tapahtuu SQL:ssä mm. komennoilla CREATE, ALTER ja DROP.

Tietotyypit

SQL-standardi määrittää sarakkeille erilaisia tietotyyppivaihtoehtoja. SQL-kieli on vahvasti tyypittävä, mikä tarkoittaa, että jokaiselle taulun sarakkeelle on määritettävä tietotyyppi kun taulu luodaan. Yleiset, standardin mukaisia tietotyyppejä on lueteltu alla olevassa taulukossa.

Tietotyyppi	Selite	Käyttöesimerkki	Esimerkkisarake	Esimerkkiarvo
CHAR, CHARACTER	määrätyn mittainen merkkijono	`CHAR(10)`	`henkilötunnus`	`'111111-XXX'`
VARCHAR, CHARACTER VARYING	vaihtelevan mittainen merkkijono	`VARCHAR(20)`	`etunimi`	`'Seppo'` tai `'Viljami'`
INTEGER, INT	kokonaisluku	`INT`	`syntymävuosi`	`1990`
NUMERIC	(desimaali)luku (kokonaisosa ja desimaaliosa mahdollisesti rajoitettu)	`NUMERIC(7,2)`	`kuukausipalkka`	`2600.12`
BOOLEAN	totuusarvo	`BOOLEAN`	`tilaus_vahvistettu`	`TRUE`
DATE	päivämäärä	`DATE`	`tilauspvm`	`'2025-01-12'`
TIMESTAMP	aikaleima	`TIMESTAMP`	`maksuaika`	`'12:00:00'`
ARRAY	lista	`INTEGER[7]`	`lottonumerot`	`[1, 2, 3, 4, 5, 6, 7]`
TEXT	tekstimuotoinen sarake	`TEXT`	`arvostelu`	`'Kissa istuu puussa.'`

Mainittujen tietotyyppien lisäksi eri tuotteissa on lisäksi muita tietotyyppejä, kuten tietotyypit suuren binääridatan kuten teksti, kuvat, audio ja video tallentamiseen sekä tietotyypit paikkatiedolle.

Taulujen hallinta

Tauluja hallitaan komennoilla CREATE, ALTER ja DROP. Ennen komentojen syntaksiin tutustumista muistellaan luvussa 3.1 käsiteltyjä, relaatioihin liittyviä termejä, mutta tällä kertaa SQL:n näkökulmasta:

Taululla on otsake (header). Otsake sisältää sarakkeiden nimet niiden luontijärjestyksessä.
Taululla on asteluku (degree). Asteluku kertoo sarakkeiden määrän.
Taululla on kardinaalisuus. Taulun kardinaalisuus on taulun rivien lukumäärä.
Sarakkeella on kardinaalisuus. Sarakkeen kardinaalisuus on sen sisältämien erilaisten arvojen lukumäärä.

astun	asnimi	kaup	tyyppi	mpiiri
a123	Virtanen	Mikkeli	y	i
a125	Kojo & Lipas	Kouvola	y	i
a134	Liekki	Tampere	y	l

# monitaulut

Taulujen luominen

Taulu luodaan komennolla CREATE TABLE, ja sen yleinen syntaksi on seuraava.

CREATE TABLE taulu (
	sarake tietotyyppi[, 
	sarake tietotyyppi]*
);

Esimerkiksi esimerkkitietokannan tuote-taulu voitaisiin luoda seuraavalla SQL-lauseella.

Huomautus

Jos taulun luominen onnistuu, SQLite ei anna mitään tulostaulua.

Materiaalin esimerkeissä voit tarkistaa taulun luomista esimerkiksi .tables-komennolla (näyttää kaikki taulut) tai .schema taulu (näyttää taulun taulu skeeman).

Esimerkiksi:

CREATE TABLE tuote (
    tuotetun CHAR(4),
    tuotenimi VARCHAR(15),
    malli VARCHAR(10),
    ahinta INT,
    vari VARCHAR(10)
);

.schema tuote

Huomaa, että komennot ajetaan heti peräkkäin. Materiaalin esimerkeissä tietokanta alustetaan jokaisen ajon jälkeen.

# ex-sql-create1

Harjoittele

Kokeile muokata SQL-lausetta niin, että se luo myös taulun nimeltä osasto. Taulussa on sarakkeet osastotunnus, osoite, pinta-ala (kahden desimaalin tarkkuudella) ja onko_toiminnassa, joka on BOOLEAN-tietotyyppiä. Lisää tauluun rivejä. Voit esikatsella lisätyt taulut ja rivit komennolla

SELECT * FROM tuote;
SELECT * FROM osasto;

Perusavain

Perusavain (primary key, PK) on sarake tai sarakejoukko, joka yksilöi taulun rivit. Toisin sanoen perusavaimen ansiosta jokainen taulun rivi on erilainen. Taululle voidaan määrittää perusavain joko taulun luontikomennolla tai myöhemmin muokkaamalla taulua. Taulun luontikomennolla perusavaimen lisääminen muuttaa syntaksia seuraavalla tavalla:

CREATE TABLE taulu (
	sarake tietotyyppi [PRIMARY KEY]?[, 
	sarake tietotyyppi]*
);

Esimerkiksi

CREATE TABLE tuote (
	tuotetun CHAR(5) PRIMARY KEY,
	tuotenimi VARCHAR(20)
);

Kuten relaatiolla, myös taululla voi olla vain yksi perusavain. Jos perusavain koostuu useammasta kuin yhdestä sarakkeesta, se täytyy luoda sarakkeiden määrityksen jälkeen:

CREATE TABLE taulu (
	sarake tietotyyppi[, 
	sarake tietotyyppi]*[,
	PRIMARY KEY (sarake[, sarake]*)]?
);

Esimerkiksi

CREATE TABLE tilaus (
	tuotetun CHAR(5),
	astun CHAR(5),
	maara INT,
	PRIMARY KEY (tuotetun, astun)
);

Viiteavain

Viiteavain (foreign key, FK) on perusavaimen ohella tärkein relaatiotietokannan eheysrajoite. Viiteavain on sarake tai sarakejoukko, joka yksilöi rivin toisessa taulussa. Tavallisesti viiteavain viittaa toisen taulun perusavaimeen tai sen osaan. Sitä taulua, jonka perusavaimeen viiteavain viittaa, sanotaan taulujen välisen suhteen isäntätauluksi ja viittaavaa taulua renkitauluksi. Viiteavain rajoittaa viittaavan taulun sarakkeen arvojoukon isäntätaulun viitatun sarakkeen arvoihin. Viiteavaimen määritys ei ole pakollinen, jotta taulujen välille voidaan muodostaa liitos SQL-lauseella.

Kuvio: Viite- ja perusavaimet kolmessa taulussa.

Viitatun ja viittaavan sarakkeen tulee olla samaa tietotyyppiä. SQL-kielessä viiteavaimet määritetään joko taulun luonnin yhteydessä CREATE TABLE-käskyllä tai muokkaamalla jo olemassa olevaa taulua. Yleinen syntaksi on seuraava.

CREATE TABLE taulu (
	sarake tietotyyppi[, 
	sarake tietotyyppi]*[,
	PRIMARY KEY (sarake[, sarake]*)]?[,
	FOREIGN KEY (sarake[, sarake]*) REFERENCES isäntätaulu (sarake[, sarake]*)
	  [ON UPDATE [RESTRICT|SET NULL|CASCADE|SET DEFAULT]]?
	  [ON DELETE [RESTRICT|SET NULL|CASCADE|SET DEFAULT]]?
	]*
);

Tässä CREATE TABLE-käskyllä luotava taulu tulee olemaan suhteen renkitaulu. FOREIGN KEY-lisämäärettä seuraa sulkujen sisällä pilkkulista, jossa luetellaan renkitaulun viittaavat sarakkeet, esimerkiksi:

CREATE TABLE tilausrivi (
	asiakastunnus CHAR(10),
	tuotetunnus CHAR(10),
	maara INT,
	PRIMARY KEY (asiakastunnus, tuotetunnus),
	FOREIGN KEY (asiakastunnus) REFERENCES asiakas (asiakastunnus)
	  ON UPDATE RESTRICT
	  ON DELETE SET NULL,
	FOREIGN KEY (tuotetunnus) REFERENCES tuote (tuotetunnus)
	  ON UPDATE RESTRICT
	  ON DELETE SET DEFAULT
);

Yllä olevat lisämääreet kertovat tietokannanhallintajärjestelmälle, miten toimitaan kun isäntätaulun viitatun sarakkeen arvoissa tapahtuu muutoksia tai rivejä poistetaan (ns. viite-eheyden säilyttäminen):

RESTRICT estää sarakkeen muutokset tai rivin poiston isäntätaulussa.
SET NULL asettaa sarakkeen arvon tyhjäarvoksi renkitaulussa.
CASCADE vyöryttää isäntätaulussa tapahtuneet muutokset renkitaulun sarakkeeseen.
SET DEFAULT asettaa renkitaulun sarakkeen arvoksi oletusarvon.

Harjoittele

Luo yllä olevan kuvion mukaiset taulut (kurssi, opettaja, kurssitoteutus) eheysrajoiteineen. Alustava pohja SQL-lauseille on annettu alla. Lisää tauluihin rivejä.

# ex-sql-ddlpkfk

Muut eheysrajoitteet

Sarakkeille voidaan asettaa eheysrajoitteita perus- ja viiteavaimen lisäksi. Yleisimmät, SQL-standardin määrittämät eheysrajoitteet ovat seuraavat:

DEFAULT määritetään sarakkeen oletusarvo.
NOT NULL kielletään tyhjäarvot sarakkeessa.
UNIQUE sarakkeen arvojen täytyy olla yksilöiviä.
CHECK voidaan määrittää hyväksyttävät arvot ehtolausekkeella.

Esimerkiksi:

CREATE TABLE asiakas (
	asiakastunnus CHAR(10) PRIMARY KEY,
	etunimi VARCHAR(20) NOT NULL,
	sukunimi VARCHAR(30) NOT NULL,
	email VARCHAR(50) UNIQUE,
	asiakkuus VARCHAR(10) DEFAULT 'perus',
	puhelin VARCHAR(20) CHECK (puhelin LIKE '+358%'),
	bonushyvitys DECIMAL(5,2) CHECK (bonushyvitys > 0)
);

# moniddl2

Taulujen muokkaaminen

Taulujen rakennetta muokataan DDL-komennolla ALTER TABLE. Komennolla voidaan esimerkiksi:

Muuttaa sarakkeen tietotyyppiä.
Muuttaa sarakkeen nimeä.
Poistaa tai luoda uusi sarake.
Poistaa tai luoda eheysrajoitteita.

Luodaan alla esimerkiksi (PostgreSQL) ensin osa taulun sarakkeista tietotyyppeineen ja lisätään vasta sen jälkeen eheysrajoitteet ja kolmas sarake:

CREATE TABLE tilausrivi (
	asiakastunnus CHAR(10),
	tuotetunnus CHAR(10)
);

ALTER TABLE tilausrivi
ADD PRIMARY KEY (asiakastunnus, tuotetunnus);

ALTER TABLE tilausrivi
ADD FOREIGN KEY (asiakastunnus) REFERENCES asiakas (asiakastunnus);

ALTER TABLE tilausrivi
ADD COLUMN maara INTEGER;

ALTER-käskyn toteutuksissa on suuriakin tuotekohtaisia eroja. Yleisenä piirteenä voidaan pitää ADD,DROP ja SET -avainsanoja.

Taulujen poistaminen

Taulu ja kaikki sen sisältämä data poistetaan komennolla DROP jota seuraa taulun nimi. Jos tauluun viitataan viiteavaimilla, ne voivat estää poisto-operaation.

DROP TABLE tilausrivi;

Muiden tietokantaobjektien hallinta

Muita tietokantaobjekteja kuten näkymiä, triggereitä ja proseduureja voidaan myös hallita komennoilla CREATE, ALTER ja DROP. Tarkastellaan seuraavaksi yksinkertaisia esimerkkejä eri tietokantaobjektien luomisesta.

Näkymät (view) ovat SELECT-lauseen tuloksista luotuja aputauluja. Ne eivät kuitenkaan sisällä dataa, vaan ovat nimensä mukaisesti reaaliaikainen näkymä datasta. Näkymän voi käsittää tallennetuksi SELECT-lauseeksi, jolla saavutetaan mm. yksinkertaisemmat kyselyt sovellusohjelmassa (ts. tehdään hakulause näkymään, ei tauluihin), yleisimpien ad hoc -kyselyiden tallentaminen ja hienojakoisempi oikeuksienhallinta.

# ex-sql-view1

Yllä olevan lauseen avulla luodaan näkymä, joka sisältää SELECT-lauseensa mukaisen näkymän dataan. Näkymän sisältöä voidaan tarkastella edelleen SELECT-lauseella, esim. SELECT * FROM itäiset_asiakkaat;. SELECT-lause, jolla näkymä luodaan voi olla miten monimutkainen tahansa.

Triggerit (trigger) ovat ohjelmakoodia tai SQL:ää, joka suoritetaan automaattisesti jonkin ehdon täyttyessä. SQL-standardi määrittää, että triggerin käynnistymisen saa aikaan joko tauluun tai näkymään kohdistuva SELECT-, INSERT-, UPDATE- tai DELETE-käsky. Alla on esimerkki triggeristä (Oracle), joka sallii tyontekija-tauluun kohdistuvat poisto-operaatiot, mutta tallentaa lokiin vanhat arvot sekä poisto-operaation suoritusajan ja suorittajan.

CREATE TRIGGER tyontekija_bd
BEFORE DELETE ON tyontekija FOR EACH ROW
DECLARE
	kayttaja VARCHAR2(12);
BEGIN
	SELECT user 
	INTO kayttaja
	FROM dual;
	
	INSERT INTO tyontekija_loki (nimi, palkka, ostun, poistopvm, poistaja)
	VALUES (:old.nimi, :old.palkka, :old.ostun, SYSDATE, kayttaja);
END;
/

Proseduurit ja funktiot ovat tietokantaan tallennettua ohjelmakoodia, joka suoritetaan kutsuttaessa. Niiden käyttökohteet voivat olla melkein mitä tahansa, kohdealueesta ja sen tarpeista riippuen. SQL-standardi määrittää funktion rutiiniksi, joka voi palauttaa yhden arvon. Proseduuri puolestaan on rutiini, joka voi tuottaa useita tulosteparametreja. Proseduureilla ja funktioilla saadaan tavallisesti luotua monimutkaisempaa toiminnallisuutta kuin triggereillä, sillä niiden toiminnallisuus ei rajoitu SQL:ään. Eri tuotteilla on erilaisia tapoja ja kieliä hallita proseduureja ja funktioita, esimerkiksi:

Oracle Database: PL/SQL tai Java.
SQL Server: T/SQL tai .NET-kielet.
PostgreSQL: PL/pgSQL, pl/python, pl/perl jne.
libSQL (moderni SQLite:n muunnelma): WebAssembly

Triggereitä, proseduureja tai funktioita ei käsitellä materiaalissa tämän tarkemmin.

Indeksit

Indeksit eli hakemistot ovat tietorakenteita, jotka voivat tehdä hakuoperaatioista nopeampia. SQL-standardi ei määritä viitekehystä indekseille, joten eri tuotteissa on hyvinkin erilaisia indeksointitoteutuksia. Tarkastellaan seuraavaksi yleisellä tasolla relaatiotietokannanhallintajärjestelmiin vakiintuneita indeksointitapoja.

Huomautus

Tämä osio menee hieman tietokannan fyysisen mallintamisen tasolle. Osio ei ole olennaista tietää tietokannan käytön kannalta. On silti hyvin tiedostaa, miksi indeksointia tehdään tietokannoissa ja miksi se on kannattavaa.

Rakenne

Vakiintunut indeksityyppi relaatiotietokannoissa on ns. B+-puu (B+-tree). Sen mukaisesti indeksin rakenne on puuta muistuttava ja jokaiseen ei-lehtisolmuun sijoitetaan n puuosoitinta (pointer) ja n-1 avainarvoa. Avainarvo kertoo, mistä hakulauseen ehdoissa määritettyä tauluriviä etsitään: jos etsittävä arvo on avainarvoa pienempi, seurataan avainarvon vasemmalta puolelta löytyvää puuosoitinta. Jos taas etsittävä arvo on yhtä suuri tai suurempi kuin avainarvo, seurataan avainarvon oikealta puolelta löytyvää puuosoitinta päätyen lopulta puun lehtisolmuun, jossa on viittaus levylle.

Indeksipuun lehtisolmuissa on avainarvoja, levyosoitteita sekä lohko-osoittimia. Levyosoitteet viittaavat siihen fyysiseen kohtaan levyllä, johon indeksoidun taulun rivi on tallennettu. Lohko-osoitin viittaa indeksin seuraavaan lehtisolmuun. Avainarvot ovat sarakkeiden arvoja indeksoidusta sarakkeesta.

Oletetaan hakulause SELECT * FROM henkilö WHERE ikä = 38; ja oletetaan indeksin olevan yllä olevan kuvion mukainen. Tietokannanhallintajärjestelmä seuraa indeksiä aloittaen juurisolmusta. Juurisolmusta seurataan avainarvon 23 oikealta puolelta löytyvää puuosoitinta, koska 38 > 23. Seuraavan tason solmusta seurataan avainarvon 34 oikealta puolelta löytyvää puuosoitinta, koska 38 > 34. Lehtisolmusta seurataan levyosoitinta levyosoitteeseen, jonne kyseinen rivi on tallennettu ja luetaan rivi muistiin.

Toiminta

B+-puu pyritään tavallisesti pitämään muistissa lehtisolmuja lukuun ottamatta. Kuvitellaan taulu tuote, jossa on sarakkeet tuotetunnus, tuotenimi, ja hinta. Taulun perusavain on tuotetunnus-sarake ja taulussa on 10 000 riviä. Oletetaan yksinkertainen SQL-hakulause:

SELECT tuotetunnus, hinta
FROM tuote
WHERE tuotetunnus = 5200;

Ilman indeksiä tietokannanhallintajärjestelmä joutuu lukemaan läpi pahimmassa tapauksessa taulun kaikki 10 000 riviä ja vertaamaan tuotetunnus -sarakkeen arvoa haluttuun arvoon 5200. Jos oletetaan yksinkertaistettuna, että yhden lohkon lukeminen levyltä kestää 10 ms, ja yksi rivi mahtuu yhteen lohkoon, koko taulun lukeva operaatio kestää 10 000 riviä * 10 ms = 100 sekuntia.

Jos taas tuotetunnus-sarake on indeksoitu, mutta indeksi ei ole muistissa, joutuu tietokannanhallintajärjestelmä tekemään n+1 levylukua: rivin luvun levyltä sekä n lukua indeksiin. Huomaa, että tässä n on indeksipuun korkeus, joka voi olla merkittävästi pienempi kuin taulussa olevien rivien lukumäärä. Voitto nopeudessa tuleekin siitä, että pahimmassakin tilanteessa rivien lukuja saadaan minimoitua.

Indeksit määritetään SQL-rajapinnan kautta DDL-lauseilla. Alla on esitetty yksinkertaisia esimerkkejä indeksien määrittämisestä.

CREATE INDEX asiakas_idx
ON asiakas (etunimi, sukunimi);

DROP INDEX asiakas_idx;