Rajoitteet (CONSTRAINT)

• Perusavain (PRIMARY KEY)
• Kaksoisarvojen poisto (UNIQUE)
• Viiteavaimet (FOREIGN KEY)
• Tarkistukset (CHECK)
• Taulun poistaminen

CONSTRAINT-määritteellä voidaan CREATE TABLE –lauseen yhteyteen lisätä kenttien sisältöä rajoittavia ehtoja. Rajoitteita voidaan lisätä tauluihin myös jälkikäteen. Usein CONSTRAINT-määritettä käytetään myös perusavaimen määrittelyn yhteydessä, koska sillä voidaan antaa kaikille rajoituksille ja avaimille omat yksilölliset nimet joiden perusteella näitä voidaan myöhemmin poistaa tai muuttaa. CONSTRAINT-määritteen käyttäminen on täysin valinnaista. Kaikki rajoitteet voidaan luoda ilman CONSTRAINT-määritettä. Mahdollisia rajoitemäärityksiä ovat:

• PRIMARY KEY (perusavain)

• UNIQUE (kaksoisarvojen esto)

• FOREIGN KEY (viiteavain)

• CHECK (tarkistukset)

Perusavain (PRIMARY KEY)

Perusavain voidaan määritellä CONSTRAINT-lauseen kanssa.

Muutetaan tyontekija-taulun aiempi perusavainmäärittely CONSTRAINT-määrittelyksi:

CREATE TABLE Tyontekija (
TyontekijaID INTEGER NOT NULL,
Etunimi VARCHAR(32) NOT NULL,
Sukunimi VARCHAR(64) NOT NULL,
Hetu CHAR(11) NOT NULL,
Palkka NUMERIC(7,2) NOT NULL DEFAULT 2000,
Syntymaaika DATE NOT NULL ,
Osasto INTEGER NOT NULL DEFAULT 1,
CONSTRAINT Tyontekija_PK
PRIMARY KEY (TyontekijaID)
)

CONSTRAINT-määritteen perään annetaan jokaiselle rajoitteelle yksilöllinen nimi. PRIMARY KEY –määritteen jälkeen luetellaan sulkeissa kenttä tai kentät, jotka muodostavat perusavaimen. CONSTRAINT-määritteen voi myös jättää kokonaan pois, mutta tämä voi hankaloittaa rajoitteiden poistamista.

Sama määrittely, kuin edellä, mutta nyt ilman CONSTRAINT-määritettä.

CREATE TABLE Tyontekija (
TyontekijaID INTEGER NOT NULL,
Etunimi VARCHAR(32) NOT NULL,
Sukunimi VARCHAR(64) NOT NULL,
Hetu CHAR(11) NOT NULL,
Palkka NUMERIC(7,2) NOT NULL DEFAULT 2000,
Syntymaaika DATE NOT NULL ,
Osasto INTEGER NOT NULL DEFAULT 1,
PRIMARY KEY (TyontekijaID)
)

Yhdistettyjen avainten määrittely onnistuu luettelemalla kaikki perusavaimeen liittyvät kentät pilkulla eroteltuina PRIMARY KEY –sanojen jälkeen.

Kaksoisarvojen poisto (UNIQUE)

UNIQUE-määritteellä voidaan estää kaksoisarvojen esiintyminen jossakin kentässä. Jos lisäämme työntekijän tietoihin kentän sosiaaliturvatunnusta varten niin voimme UNIQUE-määritteellä varmistaa ettei ole mahdollista antaa kahdelle työntekijälle samaa henkilötunnusta.

Varmistetaan ettei kahdella työntekijällä ole samaa henkilötunnusta:

CREATE TABLE Tyontekija (TyontekijaID INTEGER NOT NULL,
Etunimi VARCHAR(32) NOT NULL,
Sukunimi VARCHAR(64) NOT NULL,
Hetu CHAR(11) NOT NULL,
Palkka NUMERIC(7,2) NOT NULL DEFAULT 2000,
Syntymaaika DATE NOT NULL ,
Osasto INTEGER NOT NULL DEFAULT 1,
CONSTRAINT Tyontekija_PK
PRIMARY KEY (TyontekijaID),
CONSTRAINT Tyontekija_Hetu_U
UNIQUE (Hetu)
)

Unique-määritteen voi laittaa myös heti kentän määrittelyn jälkeen, mutta tällaisessa tapauksessa määritykselle ei pysty antamaan nimeä eikä pysty yhdistämään useampaa kenttää.

Sama määrittely kuin edellä, mutta nyt suoraan kenttämäärittelyn yhteydessä:

CREATE TABLE Tyontekija (TyontekijaID INTEGER NOT NULL,
Etunimi VARCHAR(32) NOT NULL,
Sukunimi VARCHAR(64) NOT NULL,
Hetu CHAR(11) NOT NULL UNIQUE,
Palkka NUMERIC(7,2) NOT NULL DEFAULT 2000,
Syntymaaika DATE NOT NULL ,
Osasto INTEGER NOT NULL DEFAULT 1,
CONSTRAINT Tyontekija_PK
PRIMARY KEY (TyontekijaID)
)

Jos haluttaisiin estää kahden samanlaisen etunimi ja sukunimi yhdistelmän tallentaminen niin se tehtäisiin seuraavasti:

CREATE TABLE Tyontekija (TyontekijaID INTEGER NOT NULL,
Etunimi VARCHAR(32) NOT NULL,
Sukunimi VARCHAR(64) NOT NULL,
Hetu CHAR(11) NOT NULL,
Palkka NUMERIC(7,2) NOT NULL DEFAULT 2000,
Syntymaaika DATE NOT NULL ,
Osasto INTEGER NOT NULL DEFAULT 1,
CONSTRAINT Tyontekija_PK
PRIMARY KEY (TyontekijaID),
CONSTRAINT Tyontekija_Hetu_U
UNIQUE (Hetu),
CONSTRAINT Tyontekija_Nimi_U
UNIQUE (Etunimi, Sukunimi)
)

Viiteavaimet (FOREIGN KEY)

Viiteavaimien määrittely edellyttää, että viitattava taulu on oltava myös olemassa.

Tyontekijamme Osasto-kentästä pitäisi luoda viittaus Osasto-tauluun joten ensimmäiseksi täytyy luoda Osasto-taulu:

CREATE TABLE Osasto (OsastoID INTEGER NOT NULL,
OsastoNimi VARCHAR(64) NOT NULL,
CONSTRAINT Osasto_PK
PRIMARY KEY (OsastoID)
)

Nyt voidaan luoda tarvittu viiteavain Tyontekija-tauluun. Viiteavaimet luodaan FOREIGN KEY – ja REFERENCES -määritteillä.

CREATE TABLE Tyontekija (TyontekijaID INTEGER NOT NULL,
Etunimi VARCHAR(32) NOT NULL,
Sukunimi VARCHAR(64) NOT NULL,
Palkka NUMERIC(7,2) NOT NULL DEFAULT 2000,
Syntymaaika DATE NOT NULL ,
Osasto INTEGER NOT NULL DEFAULT 1,
CONSTRAINT Tyontekija_PK
PRIMARY KEY (TyontekijaID),
CONSTRAINT Tyontekija_Sotu_U
UNIQUE (Sotu),
CONSTRAINT Tyontekija_FK_O
FOREIGN KEY (Osasto)
REFERENCES Osasto (OsastoID)
)

FOREIGN KEY –määritteen jälkeen luetellaan suluissa kenttä tai kentät joista luodaan viite-eheys. Heti tämän jälkeen seuraa REFERENCES –sana, jonka perään kirjoitetaan viitattavan taulun nimi ja suluissa kenttä johon viitataan. Jos viitattava kenttä on taulun perusavain niin kenttää ei ole pakko mainita.

Viite-eheysmäärittelylle voidaan lisäksi kertoa miten pitää toimia jos eheyttä yritetään rikkoa poistamalla tai päivittämällä äiti-taulun kenttiä. Valittavana on kaksi vaihtoehtoa:

• NO ACTION

• CASCADE

NO ACTION on oletus, jos mitään ei ole määritelty. NO ACTION –määritys tarkoittaa, että tietokannan hallintajärjestelmä huomaa tehtyään vaaditun päivityksen tai poiston, että tapahtuma rikkookin viite-eheyden, ja peruuttaa tekemänsä muutoksen. Käytännössä siis ei sallita muuttaa tai poistaa sellaisia arvoja äiti-taulusta joihin on olemassa viittaus lapsi-taulusta.

CASCADE-määritys vyöryttää äiti-tauluun kohdistuvan operaation lapsi-tauluun. Jos äiti-taulusta poistetaan tietueita niin poistetaan myös lapsi-taulusta kaikki ne tietueet, jotka viittaavat poistettavaan äiti-taulun tietueeseen. Päivitettäessä tietoja äiti-taulussa niin päivitetään vastaavat kentät myös lapsi-taulussa.

Lisätään Tyontekija-taulun Osasto-kentän viite-eheysmäärittelyyn säännöt, jotka sallivat päivityksien vyörymisen, mutta kieltävät poistot. Käytännössä siis jos osastoID vaihtuu joskus niin se päivittyy automaattisesti tyontekijän tietoihin. Osastoa ei voida poistaa niin kauan, kuin sillä toimii yksikin työntekijä.

CREATE TABLE Tyontekija (TyontekijaID INTEGER NOT NULL,
Etunimi VARCHAR(32) NOT NULL,
Sukunimi VARCHAR(64) NOT NULL,
Palkka NUMERIC(7,2) NOT NULL DEFAULT 2000,
Syntymaaika DATE NOT NULL ,
Osasto INTEGER NOT NULL DEFAULT 1,
CONSTRAINT Tyontekija_PK
PRIMARY KEY (TyontekijaID),
CONSTRAINT Tyontekija_Sotu_U
UNIQUE (Sotu),
CONSTRAINT Tyontekija_FK_O
FOREIGN KEY (Osasto)
REFERENCES Osasto (OsastoID)
ON UPDATE CASCADE
ON DELETE NO ACTION
)

Tarkistukset (CHECK)

CHECK-lauseella voidaan luoda hyvinkin monimutkaisia tarkistussääntöjä, jotka kohdistuvat yhteen tai useampaan kenttään samanaikaisesti.

CHECK-lause voi sisältää hyvinkin monimutkaisia ehtoja esitettynä monimutkaisilla SELECT-lauseilla ja alikyselyillä. Minimi- ja maksimirarvojen määritteleminen on kuitenkin hyvin yksinkertaista.

Määritellään tyontekijän palkan alarajaksi 0 ja ylärajaksi 30000. Samalla määritellään rajat syntymäajalle.

CREATE TABLE Tyontekija (TyontekijaID INTEGER NOT NULL,
Etunimi VARCHAR(32) NOT NULL,
Sukunimi VARCHAR(64) NOT NULL,
Palkka NUMERIC(7,2) NOT NULL DEFAULT 2000,
Syntymaaika DATE NOT NULL ,
Osasto INTEGER NOT NULL DEFAULT 1,
CONSTRAINT Tyontekija_PK
PRIMARY KEY (TyontekijaID),
CONSTRAINT Tyontekija_Sotu_U
UNIQUE (Sotu),
CONSTRAINT Tyontekija_FK_O
FOREIGN KEY (Osasto)
REFERENCES Osasto (OsastoID)
ON UPDATE CASCADE
ON DELETE NO ACTION,
CONSTRAINT Tyontekija_Check_P
CHECK ( Palkka> 0 AND Palkka < 30000 ),
CONSTRAINT Tyontekija_Check_S
CHECK ( Syntymaaika> '1900-1-1')
)

Taulun poistaminen

Joskus tarvitsee myös pystyä poistamaan luodut taulut. Tämä onnistuu helposti DROP TABLE -komennolla. Esimerkiksi poistetaan edellä luotu Tyontekija-taulu:

DROP TABLE Tyontekija;