MySQL FOREIGN KEY – idegen kulcs

A FOREIGN KEY (idegen kulcs) egy olyan adatbázis-megszorítás, amely két tábla között kapcsolatot hoz létre, és biztosítja a kapcsolódó adatok érvényességét.

A FOREIGN KEY segítségével megadhatjuk, hogy egy tábla egyik oszlopa egy másik tábla egy meghatározott oszlopára hivatkozik.

Például egy webáruházban:

• van egy vasarlok tábla, amely a vásárlókat tárolja;
• van egy rendeles tábla, amely a rendeléseket tárolja.

Egy rendelésnek tudnia kell, hogy melyik vásárlóhoz tartozik.

Erre használjuk a FOREIGN KEY-t.

Egy egyszerű példa

Van két táblánk, a vásárlókat és a rendeléseket tároló tábla:

CREATE TABLE vasarlok (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nev VARCHAR(100) NOT NULL
);

CREATE TABLE rendelesek (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    vasarlo_id INT UNSIGNED NOT NULL,

    FOREIGN KEY (vasarlok_id) REFERENCES vasarlok(id)
);

A kapcsolat:

vasarlok
┌────┬───────────────┐
│ id │ nev           │
├────┼───────────────┤
│ 1  │ Kovács Péter  │
│ 2  │ Nagy Anna     │
│ 3  │ Szabó Béla    │
└────┴───────────────┘
            ↑
            │
            │ hivatkozás
            │
Rendelesek  │
┌────┬─────────────┐
│ id │ vasarlo_id  │
├────┼─────────────┤
│ 1  │ 1           │
│ 2  │ 2           │
│ 3  │ 1           │
└────┴─────────────┘

Az rendelesek.vasarlo_id az vasarlok.id mezőre hivatkozik.

Ez azt jelenti, hogy az rendelesek táblában szereplő vasarlo_id értéknek léteznie kell a vasarlok táblában.

FOREIGN KEY a gyakorlatban

Tegyük fel, hogy a vasarlok tábla tartalma:

id    nev
----------------
1     Kovács Péter
2     Nagy Anna
3     Szabó Béla

Ezért a következő INSERT helyes:

INSERT INTO rendelesek (vasarlo_id) VALUES (2);

Hiszen a vasarlok táblában létezik a 2 azonosító.

A következő viszont hibás:

INSERT INTO orders (customer_id) VALUES (99);

A 99 azonosító nem létezik a vasarlok táblában.

A FOREIGN KEY ezért megakadályozza az adat beszúrását.

Ezzel biztosítható az úgynevezett referenciális integritás.

Referenciális integritás

A referenciális integritás azt jelenti, hogy egy táblában nem hivatkozhatunk olyan rekordra, amely a hivatkozott táblában nem létezik.

Például:

A rendelesek.vasarlo_id csak olyan érték lehet, amely megtalálható a vasarlok.id mezőben.

A FOREIGN KEY részei

FOREIGN KEY (vasarlo_id) REFERENCES vasarlok(id)

Két fontos rész található benne.

FOREIGN KEY (vasarlo_id)

Ez megadja, hogy melyik oszlop lesz az idegen kulcs.

REFERENCES vasarlok(id)

Ez megadja, hogy melyik tábla melyik oszlopára hivatkozunk.

Tehát:

FOREIGN KEY (vasarlo_id) REFERENCES vasarlok(id)
          ↓                         ↓
      hivatkozó oszlop      hivatkozott oszlop

A FOREIGN KEY és a PRIMARY KEY kapcsolata

A hivatkozott oszlopnak általában PRIMARY KEY vagy megfelelő UNIQUE kulcsnak kell lennie.

❗ A PRIMARY KEY és a FOREIGN KEY nem ugyanaz.

Az adattípusoknak összeillőnek kell lenniük

A FOREIGN KEY és a hivatkozott kulcs adattípusának kompatibilisnek kell lennie.

Például:

vasarlok.id INT UNSIGNED

esetén:

rendelesek.vasarlo_id INT UNSIGNED

a megfelelő választás.

Nem célszerű például:

vasarlok.id INT UNSIGNED
rendelesek.vasarlo_id VARCHAR(20)

A UNSIGNED tulajdonságra is figyelni kell. Ha egy idegen kulcs egy INT UNSIGNED elsődleges kulcsra hivatkozik, akkor maga az idegen kulcs is legyen INT UNSIGNED.

FOREIGN KEY létrehozása a CREATE TABLE során

A FOREIGN KEY-t már a tábla létrehozásakor megadhatjuk.

CREATE TABLE rendelesek (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    vasarlo_id INT UNSIGNED NOT NULL,
    rendeles_datum DATE NOT NULL,

    CONSTRAINT fk_rendelesek_vasarlo
        FOREIGN KEY (vasarlo_id)
        REFERENCES vasarlok(id)
);

A CONSTRAINT után megadhatjuk a megszorítás nevét: "fk_rendelesek_vasarlo"

Ez a név segít később azonosítani a FOREIGN KEY-t.

FOREIGN KEY hozzáadása meglévő táblához

Ha a táblák már léteznek, később is létrehozhatjuk a kapcsolatot.

ALTER TABLE rendelesek ADD
    FOREIGN KEY (vasarlo_id) REFERENCES vasarlok(id);

Ezzel hozzáadjuk a FOREIGN KEY megszorítást az rendelesek táblához.

Mi történik törléskor?

Fontos kérdés:

Mi történjen akkor, ha törölni akarunk egy olyan rekordot, amelyre másik tábla hivatkozik?

Például:

Mi történjen, ha ezt végrehajtjuk?

DELETE FROM vasarlok WHERE id = 1;

A FOREIGN KEY beállításától függően többféle viselkedés lehetséges.

ON DELETE RESTRICT

A RESTRICT megakadályozza a szülőrekord törlését, ha még van rá hivatkozás.

FOREIGN KEY (vasarlo_id)
    REFERENCES vasarlok(id)
    ON DELETE RESTRICT

Ha az 1 azonosítójú vásárlónak vannak rendelései, akkor:

DELETE FROM vasarlok WHERE id = 1;

nem hajtható végre.

Ez biztonságos megoldás lehet olyan adatoknál, amelyeket nem szeretnénk véletlenül törölni.

ON DELETE CASCADE

A CASCADE azt jelenti, hogy a szülőrekord törlésekor a kapcsolódó gyermekrekordok is törlődnek.

FOREIGN KEY (vasarlo_id)
    REFERENCES vasarlok(id)
    ON DELETE CASCADE

Ha töröljük:

DELETE FROM vasarlok WHERE id = 1;

akkor a hozzá tartozó rendelések is törlődnek.

Ezért a CASCADE használatával óvatosan kell bánni.

ON DELETE SET NULL

A SET NULL esetén a gyermekrekord megmarad, de a FOREIGN KEY értéke NULL lesz.

FOREIGN KEY (vasarlo_id)
    REFERENCES vasarlok(id)
    ON DELETE SET NULL

Ehhez a mezőnek engednie kell a NULL értéket:

ON DELETE NO ACTION

A NO ACTION azt jelenti, hogy a hivatkozott rekord törlése nem történhet meg, ha az sértené a FOREIGN KEY szabályát.

MySQL esetén az InnoDB tárolómotornál a NO ACTION gyakorlatilag a RESTRICT-hez hasonló viselkedést eredményez.

ON UPDATE

Nemcsak törlésre, hanem módosításra is meghatározhatjuk, mi történjen.

CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id)
    REFERENCES customers(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE

Ha a szülőtábla kulcsértéke megváltozik, akkor a kapcsolódó FOREIGN KEY értékek is követik a változást.

Egy táblában több FOREIGN KEY is lehet

Egy táblának nem csak egy idegen kulcsa lehet.

Például egy rendeléshez tartozhat vásárló és termék:

CREATE TABLE rendelesi_elemek (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    rendeles_id INT UNSIGNED NOT NULL,
    termek_id INT UNSIGNED NOT NULL,

    FOREIGN KEY (rendeles_id)
        REFERENCES rendelesek(id),

    FOREIGN KEY (termek_id)
        REFERENCES termekek(id)
);

Több oszlopból álló FOREIGN KEY

FOREIGN KEY több oszlopból is állhat.

Például:

FOREIGN KEY (iranyitoszam, vasarlo_szam)
    REFERENCES vasarlok(iranyitoszam, vasarlo_szam)

Ilyenkor a két oszlop együtt alkotja a hivatkozást.

A hivatkozott oldalon ennek megfelelő összetett PRIMARY KEY vagy UNIQUE kulcs szükséges.

FOREIGN KEY törlése

Ha egy meglévő FOREIGN KEY-t el szeretnénk távolítani, ismernünk kell a constraint nevét.

Például:

ALTER TABLE rendelesek
DROP FOREIGN KEY fk_rendelesek_vasarlo;

Ez eltávolítja a FOREIGN KEY megszorítást.

FOREIGN KEY módosítása

A FOREIGN KEY-t általában nem közvetlenül módosítjuk.

Gyakori megoldás:

1. régi FOREIGN KEY törlése;
2. új FOREIGN KEY létrehozása.

Mikor használjunk FOREIGN KEY-t?

FOREIGN KEY-t akkor érdemes használni, amikor két tábla között valódi adatbázis-szintű kapcsolat van.

A FOREIGN KEY előnye, hogy nem csak a programozóra bízzuk az adatok helyességét.

Az adatbázis maga is ellenőrzi a kapcsolatot.

1. Gyakorlófeladat

Készíts egy adatbázist egy iskola számára.

students tábla

Hozd létre a következő mezőkkel:

• id – INT UNSIGNED, automatikusan növekvő, PRIMARY KEY
• name – legfeljebb 100 karakter, kötelező
• email – legfeljebb 150 karakter, kötelező

courses tábla

• id – INT UNSIGNED, automatikusan növekvő, PRIMARY KEY
• name – legfeljebb 100 karakter, kötelező

enrollments tábla

Ez tárolja, hogy melyik diák melyik kurzusra jelentkezett.

• student_id
• course_id
• enrolled_at

Feladat:

1. Hozd létre mindhárom táblát.
2. Az enrollments.student_id legyen FOREIGN KEY a students.id mezőre.
3. Az enrollments.course_id legyen FOREIGN KEY a courses.id mezőre.
4. Az enrollments táblában a student_id és course_id együtt legyen PRIMARY KEY.

2. Gyakorlófeladat

Készíts egy adatbázist egy iskola számára, amelyben a diákok, tanárok, tantárgyak, vizsgák és vizsgaeredmények adatait tároljuk.

students tábla

Hozd létre a következő mezőkkel:

• id – nem negatív egész szám, automatikusan növekszik, elsődleges kulcs
• name – legfeljebb 100 karakterből álló szöveg, kötelező
• email – legfeljebb 150 karakterből álló szöveg, kötelező és egyedi
• birth_date – dátum, kötelező

teachers tábla

• id – nem negatív egész szám, automatikusan növekszik, elsődleges kulcs
• name – legfeljebb 100 karakterből álló szöveg, kötelező
• email – legfeljebb 150 karakterből álló szöveg, kötelező és egyedi

subjects tábla

Ez a tábla az iskola tantárgyait tárolja.

• id – nem negatív egész szám, automatikusan növekszik, elsődleges kulcs
• name – legfeljebb 100 karakterből álló szöveg, kötelező és egyedi
• teacher_id – nem negatív egész szám, kötelező

Feladat:

• A teacher_id legyen FOREIGN KEY, amely a teachers tábla megfelelő rekordjára hivatkozik.

exams tábla

Ez tárolja, hogy egy adott tantárgyból mikor kerül sor vizsgára.

• id – nem negatív egész szám, automatikusan növekszik, elsődleges kulcs
• subject_id – nem negatív egész szám, kötelező
• exam_date – dátum, kötelező
• max_points – nem negatív egész szám, kötelező

További követelmény:

• A maximálisan megszerezhető pontszám legalább 1 és legfeljebb 100 lehet.

Feladat:

• A subject_id legyen FOREIGN KEY, amely a subjects tábla megfelelő rekordjára hivatkozik.

exam_results tábla

Ez a tábla tárolja, hogy egy adott diák egy adott vizsgán hány pontot ért el.

• student_id – nem negatív egész szám, kötelező
• exam_id – nem negatív egész szám, kötelező
• points – nem negatív egész szám, kötelező

Feladat:

• A student_id legyen FOREIGN KEY, amely a students tábla megfelelő rekordjára hivatkozik.
• Az exam_id legyen FOREIGN KEY, amely az exams tábla megfelelő rekordjára hivatkozik.
• Egy diák egy vizsgához csak egy eredménnyel rendelkezhet.
• Ennek megfelelően a student_id és az exam_id együtt legyenek az elsődleges kulcs.

További követelmények

• Mindegyik id mező automatikusan növekedjen.
• Ahol egy mező kötelezőként van megadva, ott ne lehessen NULL értéket tárolni.
• A diákok és a tanárok e-mail címe legyen egyedi.
• Egy tantárgy neve is legyen egyedi.
• A foreign key-k segítségével biztosítsd, hogy ne lehessen olyan diákhoz, tanárhoz, tantárgyhoz vagy vizsgához kapcsolódó rekordot létrehozni, amely nem létezik.