Ez a tanács amilyen rövid, olyan fontos: A jó szakember tervezőként igyekszik tökéletes adatszerkezetet kialakítani, programozóként pedig mindig számít arra, hogy nem tökéletes az adatszerkezet és az adattartalom.

A tábla- és meződefinícióink legyenek a lehető legrészletesebbek még akkor is, ha egyes dolgok specifikálása adott környezetben felesleges, mert az alapbeállítást ismétlik csak meg. Tegyük fel például, hogy egy mezőnél megengedjük, hogy NULL-t tartalmazzon, ekkor elég lenne ennyit írni:

CREATE TABLE munkatarsak
(
  ...
  mostani_fizetes INT,
  ...
)

hiszen az alapértelmezett szabály az, hogy a mezők tartalmazhatnak NULL-t, amennyiben kifejezetten ezt meg nem tiltjuk. Ennek ellenére írjuk ezt:

CREATE TABLE munkatarsak
(
  ...
  mostani_fizetes INT NULL,
  ...
);

Ennek dokumentációs értéke van: a tábladefinícióra ránézve azonnal eszünkbe jut, hogy ide NULL is bekerülhet és ezért például először is el kell azon gondolkodnunk, hogy vajon mit is fog jelenteni a NULL.

Más esetekben nemcsak a dokumentálást szolgálja az adott környezetben felesleges megkötés, hanem a biztonságot is, mint például az adatbázismotor és a karakterkészlet definíciója ennél a MySQL tábladefiníciónál:

CREATE TABLE munkatarsak
(
  csaladinev VARCHAR(20) NOT NULL,
  ...
) ENGINE=InnoDB CHARACTER SET=utf8;

Azért gondolhatjuk az explicit definíciót feleslegesnek, mert az InnoDB és az utf8 is alapértelmezett az aktuális környezetben. De nézzük csak meg, mit is jelent az aktuális környezet:

Ha tehát pl. frissíti a rendszergazda a MySQL-t, vagy megváltoztatja a konfigurációs beállításokat és újra generáljuk a tábláinkat vagy átvisszük az adatainkat egy másik helyre, akkor könnyen előfordulhat, hogy már más lesz az adatbázismotor vagy a karakterkódolás.

Mindezek ellen megvédenek az explicit definíciók – amikor látszólag feleslegesen rágjuk a szájába az adatbáziskezelőnek, hogy mit várunk el tőle. Nem jó gyakorlat az, hogy 'minek mondjam neki, úgyis tudja'. Tudja, persze, csak épp lehet, hogy holnap már másképp tudja...

Egy lekérdezést általában úgy írunk meg, hogy valaki elmondja a követelményeket, aztán megnézzük a már létező adatokat, létrehozzuk a lekérdezést, majd megnézzük, hogy a várt eredményt produkálta-e. Ha azt produkálta, akkor elégedetten kipipáljuk a feladatot és hátradőlünk a székben.

Márpedig a hátradőléssel ilyenkor azért még vigyázni kellene, mert nem biztos, hogy a széknek van háttámlája.

A jó adatbázis-szakember (vagy egyéb programozó-féleség) módszeresen összegyűjti, hogy milyen esetek fordulhatnak elő és megvizsgálja, hogy mindig jól működik-e a kreálmánya. Ehhez persze kell némi lelkierő, mert olyan jó lenne hinni abban, hogy végre túl vagyunk a feladaton és nem azon törni a fejünket, hogyan kínozzuk tovább kedves szellemi gyermekünket (és magunkat).

Tegyük föl például, hogy a cégünknek vannak kedvenc partnerei és készítettünk egy kis táblát, amelybe beleírtuk, hogy ki igényelt már törzsvásárlói kártyát és ki nem. A tábla most így néz ki:

Az a feladat, hogy gyűjtsük ki azokat, akiknél az igenyelt mező tartalma nem 1, mert nekik küldeni fog a cég egy értesítőt. A feladat nem nehéz, hamar készen vagyunk vele:

SELECT id,nev FROM kedvencek WHERE igenyelt <> 1

Megnézzük az eredményt, stimmel, Lomha1 és Lomha2 van benne, minden rendben, most jöhet a hátradőlés...

Két hónappal később jövünk rá, hogy hokedlin ültünk, amikor tajtékozva beront a szobába szeretett főnökünk, Mr. Teufel és ordítva kérdezi, hogy drága unokaöccse, Nímand Jenő miért nem kapott értesítést a törzsvásárlói kártyáról???

Gyorsan lekérdezzük az unokaöccs rekordját és ezt látjuk:

Hoppá. Ezek szerint bekerülhetett ide olyan rekord is, ahol az igenyelt mező nincs kitöltve. Hát erre nem számítottunk, pedig ez mégsem olyan ritka, mint a spanyol inkvizíció látogatása... Szerencsére könnyű kijavítani a lekérdezést:

SELECT id,nev FROM kedvencek WHERE igenyelt <> 1 OR igenyelt IS NULL

de az évvégi jutalomnak már biztosan búcsút inthetünk.

A NULL sok bosszúságot tud okozni (erről sok példában igyekeztem meggyőzni az Olvasót). Ahol lehet, kerüljük tehát, írjunk minél több mezőhöz a tábla-definícióban NOT NULL megkötést és az adatbeviteli programok is követeljék meg a mezők kitöltését!

Ahol lehet. Azért ez nagyon fontos. Ha túlzásba visszük a NULL elüldözését, helyét mesterségesen kreált értékek veszik át és ezek semmivel sem jobbak nála, sőt. Amikor az adatbevivőnek nem áll rendelkezésére valamely információ, viszont létre kell hoznia egy új rekordot, mert az ügyfél nem várhat, akkor kitalál valami tartalmat kínjában és azt írja be.

'Szóköz mező' alatt olyan szöveges mezőket értek, amelyben csak szóközök vannak. Lehetőleg ne használjunk valamely állapot (pl. a kitöltetlenség) jelzésére ilyen mezőt, mert könnyen hiba történhet.

1. probléma: több adatbáziskezelőnél nehéz a csupán szóközökből álló értéket egyáltalán bevinni. A MySQL például a VARCHAR mezőknél az eltárolni kívánt sztring végéről eltávolítja a szóközöket a 5.0.3-nál korábbi verzióknál, így aztán persze a csupa szóközből üres sztringet csinál.

2. probléma: Ha mégis sikerül bevinni ilyen értéket, 'az rosszul látható', szemmel nem megkülönböztethető az üres mezőtől.

Elmondható, hogy semmi előnye az ilyen adatkódolásnak, hátránya viszont annál több van.

A tábla definíciójában előírt megkötések védelmet nyújtanak nemkívánatos adat-kombinációk létrejötte ellen. Előírhatjuk például, hogy egy mező nem tartalmazhat NULL-t:

CREATE TABLE munkatarsak
(
  csaladi_nev VARCHAR(30) NOT NULL,
  ...
);

A kettős védelem elve azt mondja ki, hogy

Több haszna is van a kettős védelem kialakításának.

Érthető üzeneteket kap a felhasználó

Ha csak az adatbáziskezelő védekezik a fent látható NOT NULL megkötés segítségével, a program viszont az adatbevivőre bízza, hogy kitölti-e ezt a mezőt, akkor NULL nem kerülhet ugyan be az adatbázisba, de ilyen hibajelzés fog megjelenni szegény jobb sorsra érdemes adatrögzítő képernyőjén:

ERROR 1048 (23000) at line 18: Column 'csaladi_nev' cannot be null

ami enyhén szólva nem túl felhasználóbarát. Az adatbeviteli programnak gondoskodnia kell arról, hogy a felhasználó (adatrögzítő) ilyesfajta üzenetet kapjon:

A családi nevet kötelező kitölteni!

és persze a program ne próbálja meg a hiányos adatokat bevinni az adatbázisba, mert különben jön az adatbáziskezelő fentebbi rejtélyes zsémbelése.

A felhasználói program a felhasználót kell, hogy védje a bajoktól, mint egy szülő a gyerekét, nem megengedve neki, hogy veszélyes helyekre bolyongjon és közben kedves szóval, érthetően megmagyarázva neki, hogy miért nem mehet a sínekre játszani.

Az adatbáziskezelő a maga tulajdonát és az eltévedt látogatók testi épségét védi; tőle nem várható el ugyanaz a kedvesség. A sínek közelében bóklászó adatrögzítőt ugyan nem fogja elütni a vonat, de lehet, hogy elpityeredik, amikor az adatbáziskezelő bácsi ráüvölt, hogy

ERROR 1048 (23000) at line 18! Kérem a biztonsági sávot elhagyni!!!

Fokozott biztonság

Mindkét védelemre a nagyobb biztonság miatt is szükség van. Baj csak akkor történhet, ha a szülő is elbóbiskolt, a gyerek is a sínek felé kószált és a vasúti biztonsági rendszer is elromlott.

Adatbázis-védelem: Mert nem csak egy bejárat van

Hiába van az egyik felhasználói programban megfelelő adatellenőrzés kialakítva, holnap írhat valaki egy másikat, amelyben ezek a védelmek nem megfelelőek. Ugyanazt a táblát több program is módosíthatja! Az egyik lehet például webes, a másik egy offline alkalmazás, a harmadik egy kötegelt program, amely nagy tömegű adatot egyszerre importál a táblába.

Program-védelem: Mert a CONSTRAINT nem mindig létezik

Lehet, hogy az adatbáziskezelő, amely alá áttelepítjük az adatainkat nem támogatja azt a fajta megkötést, amelyre szükségünk van. Szerencsére a NOT NULL gyakorlatilag mindegyiknél megy, de vannak más, problémás esetek.

A MySQL például elfogadja a szintaktikailag helyes CHECK megkötéseket, csak éppen nem hajtja őket végre. Az Oracle a MySQL megvásárlása óta nem tartotta fontosnak ezt a régóta ismert hiányosságot kijavítani, sajnos. Ha tehát az adataink pl. MariaDB-ben vannak és áthozzuk őket MySQL alá, egyszerre megszűnnek a CHECK védelmek, anélkül, hogy tudnánk róla. Amennyiben a felhasználói programok nem tartalmaztak védelmeket, az adataink ebben a pillanatban sérülékennyé váltak. (A MariaDB a MySQL-nek az a vele teljesen kompatibilis és szintén nyílt, ingyenes változata, amelyet az eredeti fejlesztő csapat vitt tovább a felvásárlás után. Itt működnek a CHECK megkötések.)

Másik példa: A MySQL támogatja a FOREIGN KEY megkötéseket, ha InnoDB adatbázismotort használunk, de a MyISAM motor nem támogatja ezeket. Megadhatunk FOREIGN KEY-t, ha szintaktikailag helyes, akkor nem kapunk hibajelzést – csak éppen nem működik. És ez így van a MariaDB-nél is. Amikor tehát átvisszük az adatainkat egyik MySQL adatbázisból egy másikba és nem kötjük ki a tábla létrehozásakor explicit módon, hogy InnoDB-t akarunk használni (ld.: maximális szájbarágás elve):

CREATE TABLE munkatarsak
(
  csaladinev VARCHAR(20) NOT NULL,
  ...
) ENGINE=InnoDB;

továbbá abban a MySQL környezetben (verzió, konfigurációs fájl...), ahová átvisszük az adatainkat, a MyISAM a default, akkor ott nem fognak működni a FOREIGN KEY beállításaink. Amennyiben az adatbeviteli program nem végez ellenőrzést, vidáman bekerülhetnek olyan hivatkozások, amelyek nem létező elemekre mutatnak. És még csak adatbáziskezelőt sem váltottunk, hanem pusztán adatbázis-motort...

Program-védelem: A kitöltés kikényszerítéséhez

Amikor nem akarjuk, hogy üresen maradjon egy mező, akkor az első logikus lépésnek az tűnik, hogy NOT NULL megkötést teszünk rá:

CREATE TABLE t1(
  csaladinev VARCHAR(20),
  utonev VARCHAR(20),
  elso_mhely VARCHAR(20) NOT NULL,
  mostani_mhely VARCHAR(20) NULL
);

Ez a megkötés önmagában csak az ilyen adatbevitel ellen véd meg:

ha viszont az adatbeviteli programban ilyesmi is előfordulhat:

azaz a program nem rendelkezik az elso_mhely mezőről, akkor az adatbázisba a mezőhöz tartozó alapértelmezett (DEFAULT) érték kerül be. Ha nem adunk meg a mezőhöz DEFAULT értéket, akkor jó eséllyel üres sztring kerül a mezőbe, mert általában ez a szöveges mezők DEFAULT-jának a default-ja. Ezzel valószínűleg nem leszünk elégedettek, mert ha nem akartuk a mezőt olyan módon üresen hagyni, hogy NULL legyen benne, akkor nem fog az sem tetszeni nekünk, ha ezen a másik módon lesz üres.

Sebaj,mondhatjuk, akkor kitalálunk valami jó kis DEFAULT értéket, például ezt:

CREATE TABLE t1(
  csaladinev VARCHAR(20),
  utonev VARCHAR(20),
  elso_mhely VARCHAR(20) NOT NULL DEFAULT '[semmi]',
  mostani_mhely VARCHAR(20) NULL
);

és a probléma meg van oldva.

Nos, a rossz hír az, hogy a probléma nem veszett el, csak átalakult, mint oly sokszor teszi ő az energiához hasonlóan. (Minek folyományaként a programozók gyakran érzik úgy, hogy az ő energiájuk viszont igenis elvész, ők maguk meg szívesen átalakulnának bármivé, ami nem programozó.) Ugyanis eredetileg az volt a célunk, hogy a mező mindig értelmesen ki legyen töltve, ennek következményeként ne tartalmazhasson NULL-t, mert az a kitöltetlenséget jelezné. Most viszont csak azt értük el, hogy valami más jelzi a kitöltetlenséget – és ezt legfeljebb egy politikus tudná fényes győzelemként értelmezni.

A default érték kétféle lehet:

1. értelmes, érvényes érték
2. jelző érték, ami azt mutatja, hogy itt még nincs érvényes adat – ami igazából logikailag ekvivalens a NULL-lal.

Jelen esetben erről az utóbbiról van szó, tehát semmivel nem jutottunk előbbre, mindössze a valódi NULL-t helyettesítettük egy logikaival.

Itt nyilván nem létezik értelmes default érték, mert az első munkahely személyenként más és más. Azaz ebben az esetben az adatbeviteli program kizárólagos felelőssége, hogy csak megfelelően kitöltött elso_mhely mezőt engedjen létrehozni. Legjobb, ha a tábladefinícióban megengedjük a NULL-t, éppen csak egy megjegyzésben írjuk oda, hogy a programnak kell kikényszerítenie a nem NULL tartalmat. Az adatbáziskezelő most nem igazán tud segíteni.

Ha időről időre ellenőrizni akarjuk, hogy valóban minden adatbeviteli program helyesen működik-e, mindössze ezt a lekérdezést kell lefuttatnunk:

Amennyiben ez nulla sort szolgáltat, akkor megnyugodhatunk.

A részleges INSERT-en kívül a tömeges adatbevitel is produkálhatja a NULL implicit átalakulását. Erről részletesebben itt olvashatunk. A probléma ugyanaz, mint a fentebb vázolt esetben és a tanács itt így hangzik:

Az ellenőrzés praktikus módja a zsilipelés, amikor az adatokat egy átmeneti táblába töltjük be (itt persze megengedünk NULL-t) és ebből a táblából lekérdezésekkel választjuk ki a hibátlan rekordokat, a többieket pedig vagy módosítjuk, vagy elvetjük.

Amikor INNER JOIN-t használunk, mindig tegyük fel magunknak a kérdést: Vajon biztosan minden elemnek megvan a párja? Nem történhet adatvesztés amiatt, hogy egy azonosítóról csak azt hisszük, hogy szerepel a hivatkozott táblában is?

Vegyük például ezt az egyszerű kis alkatrészjegyzéket, amelyben a megnevezések helyett csak egy hivatkozást helyeztünk el egy szótár-táblára:

CREATE TABLE szotar(
  szo_id INT PRIMARY KEY,
  szo    VARCHAR(55) NOT NULL
);

INSERT INTO szotar VALUES
  (101,'kalapács'),
  (102,'szög');

CREATE TABLE alkatreszek(
  alkatresz_id INT PRIMARY KEY,
  nev_id       INT NOT NULL
  # FOREIGN KEY (alkatresz_id) REFERENCES szotar(szo_id)
  # hát ez sajnos lemaradt
);

INSERT INTO alkatreszek VALUES
  (1,101),
  (2,999);
  # Hoppá, 999 nincs a szótárban!

Az alkatreszek táblában valaki elfelejtette elhelyezni a megfelelő FOREIGN KEY megkötést annak érdekében, hogy ne lehessen olyan nev_id azonosítót bevinni, amelynek nincs szo_id párja a szótárban. Amikor lefuttatjuk ezt a lekérdezést:

SELECT alkatresz_id,szo
FROM alkatreszek
INNER JOIN szotar ON nev_id = szo_id;

akkor a 2-es azonosítójú tétel teljesen hiányozni fog a kimeneten:

Nem kell különösebben magyarázni, mekkora bajokat okozhat ez! Ez az INNER JOIN óriási hátránya: egy hiányzó segédinformáció láthatatlanná tehet fontos adatokat. Soha ne felejtsük el, hogy az INNER JOIN nagy bűvész, könnyen eltüntetheti a holminkat!

A megoldás persze egyszerű – használjunk OUTER JOIN-t:

SELECT alkatresz_id,szo
FROM alkatreszek
LEFT JOIN szotar ON nev_id = szo_id;

Így már meg fog jelenni a 2-es azonosítójú tétel is a kimeneten, NULL megnevezéssel.

Aki ilyen helyzetben INNER JOIN-t használ, az nem eléggé kétarcú szakember!

A NULL helyett persze kiadhatunk valami információt is arról, hogy melyik kód hiányzik a szótárból:

# Standard SQL megoldás CASE használatával
SELECT alkatresz_id,
  CASE
    WHEN szo_id IS NULL THEN CONCAT(nev_id,' - nincs a szótárban')
    ELSE szo
  END AS megnevezés
FROM alkatreszek
LEFT JOIN szotar ON nev_id = szo_id;

# MySQL-specifikus megoldás COALESCE függvénnyel
SELECT alkatresz_id, COALESCE(szo,CONCAT(nev_id,' - nincs a szótárban')) AS megnevezés
FROM alkatreszek
LEFT JOIN szotar ON nev_id = szo_id;

Az eredmény ez lesz: