Megszorítások

Megszorítások és triggerek

• Ezeket az AB rendszer fogja betartani/megkövetelni
• Megszorítás adatelemek közötti kapcsolat
  • pl. kulcs megszorítások
• Triggerek események bekövetkezésekor történik
  • pl. adatelem beszúrásakor/törlésekor

Megszorítások típusai

• Kulcsok
• Idegen kulcsok
  • hivatkozási épség megszorítás
  • \(\large{\Pi_{sör}(Felszolgál)\subseteq\Pi_{név}(Sör)}\)
• Érték-alapú megszorítás
  • Típusmegkötések, lehetséges értékek
• Sor-alapú megszorítás
  • Mezők közötti kapcsolatok leírása

Idegen kulcsok

• Reláció attribútumainak értékei egy másik reláció értékei között együttesen meg kell, hogy jelenjen
  • pl. a Felszolgál(kocsma, sör, ár) táblánál elvárjuk, hogy az itt felsorolt sörök a Sörök tábla név oszlopában is szerepeljenek
• Megadni hasonlóan lehet, mint az elsődleges kulcsnál
  • Egy attribútumnál egyszerűen utána
    • Mantra: <attribútum> REFERENCES <reláció>(<attribútum>)
  • Több attribútumnál a séma elemeként
    • Mantra: FOREIGN KEY(<attribútumok listája>) REFERENCES <reláció>(<attribútumok>)
    • Sorrendbe kell megadni...
• A hivatkozott attribútum(ok)nak is kulcsoknak kell lenniük
  • PRIMARY KEY vagy UNIQUE

Idegen kulcs megszorítás sérülése

Adott S szülő és R hivatkozó reláció esetén

• Egy R-be történő beszúrásnál S-ben nem szereplő értéket adunk meg
• Egy S-beli törlés az R-ben "lógó" sorokat eredményezhet

Idegen kulcs megszorítás biztosítása

• Beszúrás megtagadása nem létező hivatkozásra
• Törlés megtagadása létező kapcsolat esetén
• Módszerek:
  • Default
  • Továbbgyűrűzés - CASCADE
    • Sor törlése
    • Sor módosítása
  • Set NULL

Stratégia kiválasztása

• Idegen kulcs deklarálása után megadható
  • ON [UPDATE, DELETE] [SET NULL, CASCADE]
• Ennek hiányában az alapértelmezett stratégia működik

Idegen kulcs megszorítás - Példa

CREATE TABLE Felszolgál (
    kocsma  CHAR(20),
    sör     CHAR(20),
    ár      REAL,
    FOREIGN KEY(sör)
        REFERENCES Sörök(név)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

Attribútum megszorítások

• Adott oszlop értékeire vonatkozik
• CHECK(<feltétel>) az attribútum deklarációja után
• A feltételben legfelső szinten csak a szóban forgó attribútum szerepelhet
  • Más attribútumok csak a feltételhez használt alkérdésben szerepelhetnek
• A CHECK feltételt csak beszúráskor ellenőrzi a rendszer
• A feltétel nem teljesülése a utasítás megtagadását okozza

Attribútum ellenőrzés - Példa

CREATE TABLE Felszolgál (
    kocsma  CHAR(20),
    sör     CHAR(20) CHECK ( sör IN
        (SELECT név FROM Sörök)),
    ár      REAL CHECK (ár <= 5.00)
);

Sor-alapú megszorítások

• CHECK(<feltétel>) megszorítás séma elemenként
• Feltétel tetszőleges oszlop használható
  • Más reláció oszlopai csak alkérdésekben
• Csak beszúrásnál és módosításnál ellenőrzi a rendszer

Sor-alapú megszorítások - Példa

CREATE TABLE Felszolgál (
    kocsma  CHAR(20),
    sör     CHAR(20),
    ár      REAL,
    CHECK (kocsma = 'Joe bárja' OR ár <= 5.00)
);

Globális megszorítás

• Adatbázissémához tartoznak
• Mantra: CREATE ASSERTION <név> CHECK(<feltétel>);
• A feltétel tetsuőleges táblára és oszlopra hivatkozhat
• Bármely módosításnál ellenőrzött
  • Okosabb rendszerek a hivatkozott relációk módosításához köti az ellenőrzést

Globális megszorítás - Példa

Kocsmák, ahol a sörök átlagosan drágábbak 5.00 (EA szerint dollárnál)

CREATE ASSERTION CsakOlcsó CHECK(
    NOT EXISTS(
        SELECT kocsma
        FROM Felszolgál
        GROUP BY kocsma
        HAVING 5.00 < AVG(ár)
));

Alkesz(név, cím, telefon) és Kocsma(név, cím, engedélySzám) táblákban nem lehet több kocsma, mint alkesz

CREATE ASSERTION TöbbAlkesz CHECK(
    (SELECT COUNT(*) FROM Kocsma) >=  
    (SELECT COUNT(*) FROM Alkesz)  
);

Triggerek

• A globális megszorítások erősek, de olykor problémásak
• Attribútum és sor alapú megszorítások egyszerűbbek, de pont ezért nem elég kifejezőek

• A triggerek esetén megadható, mikor történjen az ellenőrzés

• CEA szabályok

  • Event-contition-action

CREATE TIGGER SörTrig
    BEFORE INSERT ON Felszolgál -- Esemény
    REFERENCING NEW ROW AS ÚjSor
    FOR EACH ROW
    WHEN (ÚjSor.sör NOT IN      -- Feltétel
        (SELECT név FROM Sörök))
    INSERT INTO Sörök(név)      -- Akció
        VALUES(Újsor.sör);