10.3 Tabulu projektēšana un saites

Tavs šīs stundas izaicinājums: Projektēt vairākas saistītas tabulas, izmantojot primārās un ārējās atslēgas.

SR 2.4.1. Analizē problēmu un veic dekompozīciju SR 2.4.6. Plāno un dokumentē izstrādes procesu

Teorija: Primārās un ārējās atslēgas

Datubāze ir tikai tik laba, cik labi tā ir projektēta. Galvenie principi:

Primary Key (PK) — viens lauks, kas unikāli identificē rindu (parasti id SERIAL).
Foreign Key (FK) — lauks vienā tabulā, kas norāda uz citas tabulas PK.
Normalizācija — neatkārto vienus un tos pašus datus vairākās tabulās.

-- Spēlētāji un viņu spēles (1 → daudzi)
CREATE TABLE speletaji (
    id SERIAL PRIMARY KEY,
    vards TEXT NOT NULL UNIQUE
);

CREATE TABLE speles (
    id SERIAL PRIMARY KEY,
    speletajs_id INTEGER NOT NULL REFERENCES speletaji(id) ON DELETE CASCADE,
    punkti INTEGER NOT NULL,
    spelets TIMESTAMP DEFAULT NOW()
);

ON DELETE CASCADE — ja izdzēš spēlētāju, automātiski tiek izdzēstas arī viņa spēles.

1. uzdevums: Saistītas tabulas

Izveido sistēmu spēlētājs → spēles.

Izpildes soļi:

Izdzēš iepriekšējo tabulu: DROP TABLE speletaji CASCADE;
Izveido speletaji ar UNIQUE vārdu.
Izveido speles ar speletajs_id INTEGER REFERENCES speletaji(id) ON DELETE CASCADE.
Pievieno 3 spēlētājus un 5 spēles dažādiem spēlētājiem.

2. uzdevums: Datu integritātes pārbaude

Pārbaudi, ka FK ierobežojumi strādā.

Izpildes soļi:

Mēģini ievadīt spēli ar neeksistējošu speletajs_id: INSERT INTO speles (speletajs_id, punkti) VALUES (999, 100);
Pieraksti, kāda kļūda parādās (foreign key violation).
Dzēš spēlētāju ar id=1: DELETE FROM speletaji WHERE id = 1;
Pārbaudi SELECT * FROM speles; — vai automātiski izdzēstas viņa spēles?

3. uzdevums: Daudzi-pret-daudziem

Izveido sarežģītāku struktūru: spēlētāji → komandas (m:n).

Izpildes soļi:

CREATE TABLE komandas (id SERIAL PRIMARY KEY, nosaukums TEXT NOT NULL);
Saites tabula: CREATE TABLE komandu_dalibnieki (speletajs_id INTEGER REFERENCES speletaji(id), komanda_id INTEGER REFERENCES komandas(id), PRIMARY KEY (speletajs_id, komanda_id));
Pievieno 2 komandas un 3 spēlētājus.
Pievieno spēlētājus komandām, izmantojot saites tabulu.

Papildus uzdevums: Constraints (ierobežojumi)

Izmēģini CHECK un NOT NULL ierobežojumus.

Izpildes soļi:

Pievieno tabulai speles ierobežojumu: ALTER TABLE speles ADD CONSTRAINT pozitivi_punkti CHECK (punkti >= 0);
Mēģini ievadīt negatīvos punktus — vai DB to atļauj?
Eksperimentē ar NOT NULL ierobežojumu.

Biežākās kļūdas

FK norāda uz neeksistējošu rindu: Vispirms ievadi vecāku tabulā, tad bērnu.
Aizmirsts CASCADE: Bez tā nevarēsi dzēst vecāku rindas, kamēr ir bērnu rindas.
Daudzi-pret-daudziem bez saites tabulas: NEKAD nelieto FK masīvu — vienmēr trešo tabulu.

Koda piemērs

CREATE TABLE speletaji (
    id SERIAL PRIMARY KEY,
    vards TEXT NOT NULL UNIQUE
);

CREATE TABLE speles (
    id SERIAL PRIMARY KEY,
    speletajs_id INTEGER NOT NULL REFERENCES speletaji(id) ON DELETE CASCADE,
    punkti INTEGER NOT NULL CHECK (punkti >= 0),
    spelets TIMESTAMP DEFAULT NOW()
);

INSERT INTO speletaji (vards) VALUES ('Anna'), ('Jānis');
INSERT INTO speles (speletajs_id, punkti) VALUES (1, 120), (1, 85), (2, 200);

Tabulu shēma izveidota.
3 spēļu rezultāti pievienoti.