Mitä rajoitteita PostgreSQL tukee?

Question

Accepted Answer

PostgreSQL valvoo tietojen eheyttä **rajoitteiden** (constraints) avulla — säännöt taulukon tiedoille. Tavallisten rajoitteiden (`PRIMARY KEY`, `FOREIGN KEY`, `UNIQUE`, `NOT NULL`, `CHECK`, `DEFAULT`) lisäksi Postgres tarjoaa tehokkaita vaihtoehtoja, kuten **EXCLUDE**-rajoitteet ja joustavat `CHECK`-lausekkeet.

## Tavalliset rajoitteet

```sql
CREATE TABLE bookings (
  id SERIAL PRIMARY KEY,                                -- unique identifier
  room_id INT NOT NULL REFERENCES rooms(id),            -- foreign key (referential integrity)
  email VARCHAR(255) UNIQUE NOT NULL,                    -- unique + required
  guests INT CHECK (guests > 0 AND guests <= 10),       -- value constraint
  status TEXT DEFAULT 'pending'                          -- default value
);
```

```text
PRIMARY KEY  → unique row identifier
FOREIGN KEY  → references another table (referential integrity, with ON DELETE options)
UNIQUE       → no duplicate values
NOT NULL     → must have a value
CHECK        → value must satisfy a condition
DEFAULT      → default value when not provided
```

## CHECK-rajoitteet — toimialueen sääntöjen valvonta

```sql
CHECK (price > 0)
CHECK (end_date > start_date)               -- logical consistency between columns
CHECK (status IN ('active', 'inactive', 'pending'))
CHECK (email ~ '^[^@]+@[^@]+\.[^@]+$')        -- regex validation
```

PostgreSQL:n `CHECK`-rajoitteet voivat valvoa monimutkaisia liiketoimintasääntöjä, myös sarakkeiden välisiä ehtoja ja regex-kuvioita.

## EXCLUDE-rajoitteet — tehokas PostgreSQL-ominaisuus

```sql
-- prevent OVERLAPPING bookings for the same room (impossible with UNIQUE alone!)
CREATE TABLE bookings (
  room_id INT,
  during TSRANGE,                            -- a time range
  EXCLUDE USING GIST (room_id WITH =, during WITH &&)   -- no two rows where room_id is
);                                                       -- equal AND time ranges OVERLAP
```

**EXCLUDE**-rajoitteet (PostgreSQL-kohtainen ominaisuus) estävät rivejä, jotka ovat ristiriidassa mukautetun operaattorin kanssa — kuuluisasti estävät **päällekkäiset aikavälit** (kaksinkertaiset varaukset), mitä `UNIQUE`-rajoite ei pysty tekemään.

## Lykkäävät rajoitteet

```sql
-- check constraints at COMMIT instead of immediately (useful for circular references)
FOREIGN KEY (...) REFERENCES ... DEFERRABLE INITIALLY DEFERRED
```

## Miksi se on tärkeää

Rajoitteet ovat välttämättömiä **tietojen eheydelle** — ne valvovat tietosääntöjä tietokannan tasolla (viimeinen puolustuslinja, aina valvottu sovelluskoodista riippumatta), joten PostgreSQL:n rajoitetuista on tärkeää ymmärtää tietokantasuunnittelua.

Tavallisten rajoitteiden (`PRIMARY KEY`, `FOREIGN KEY` viittauseheyden kanssa, `UNIQUE`, `NOT NULL`, `CHECK`, `DEFAULT`) tunteminen ja kunkin valvomisen ymmärtäminen on perustavaa laatua olevaa skeemasuunnittelua, mikä tarjoaa tietokannan tason takuita (ainutlaatuisuus, voimassa olevat viitteet, vaaditut arvot, kelvollinen data), jotka suojaavat virheiltä, kilpailutilanteista ja epäjohdonmukaisesta datasta tavoin, jota pelkkä sovellukskoodi ei pysty tekemään.

PostgreSQL:n **`CHECK`**-rajoitteet ovat erityisen kykeneviä, valvoen monimutkaisia liiketoimintasääntöjä, myös sarakkeiden välisiä ehtoja (loogista johdonmukaisuutta, kuten `end_date > start_date`) ja jopa regex-kuvioita — antaen koodataa toimialueen säännöt suoraan skeemaan.

Erityisen arvokas on PostgreSQL-kohtainen **`EXCLUDE`**-rajoite, joka estää ristiriitaisia rivejä mukautettujen operaattoreiden avulla — etenkin **päällekkäisten aikavälejä** estämisen (kaksinkertaiset varaukset huoneelle/resurssille), tehokas ominaisuus, jota tavallinen `UNIQUE`-rajoite *ei pysty* tarjoamaan ja joka ratkaisee elegantisti yleisen tosielämän ongelman (ajoitus, varaukset) tietokannan tasolla.

Rajoitteiden ymmärtäminen — tavalliset eheyden kannalta, Postgresq:n joustavat CHECK-lausekkeet ja tehokkaat EXCLUDE-pohjainen ristiriitojen estämiseen — on tärkeää tietokantasuunnitteluun, joka valvoo oikeellisuutta automaattisesti.

Koska tietojen eheys on kriittistä ja rajoitteet tarjoavat takuita, joita sovellukskoodi ei pysty antamaan, ja koska Postgres tarjoaa tehokkaita vaihtoehtoja (rikkaat CHECK, EXCLUDE päällekkäisille) perustumisen lisäksi, PostgreSQL:n rajoitetuista ymmärtäminen on arvokas, käytännöllisesti olennainen tieto vankkien tietokantojen rakentamiseen, EXCLUDE-rajoite erityisesti olevan erottuva, hyödyllinen PostgreSQL-ominaisuus, jonka arvo on tiedossa ajoitus- ja varausjärjestelmille.