ARRA VONATKOZIK: SQL Server (2016-tól) Azure SQL DatabaseAzure SQL Data WarehouseParallel Data Warehouse
Az elsődleges és az idegen kulcs kétféle megszorítás, amellyel az adatok integritását kényszeríthetjük ki. SQL táblák szerver. Ezek fontos adatbázis-objektumok.
Ezt a témát a következő szakaszok ismertetik.
Elsődleges kulcskorlátok
Az idegen kulcs megszorításai
Kapcsolódó feladatok
Általában egy táblázatnak van egy oszlopa vagy oszlopkombinációja, amely olyan értékeket tartalmaz, amelyek egyedileg azonosítják a táblázat minden sorát. Ezt az oszlopot vagy oszlopokat a tábla elsődleges kulcsának (PK) nevezik, és ez biztosítja a tábla entitásának integritását. Az elsődleges kulcs megszorításait gyakran egy identitásoszlopban határozzák meg, mert ezek garantálják az adatok egyediségét.
Amikor beállít egy elsődleges kulcs megkötést egy táblára, az adatbázismotor garantálja az adatok egyediségét automatikus létrehozás egyedi index az elsődleges kulcs oszlopaiban. Ez az index gyors adathozzáférést is biztosít az elsődleges kulcs használatakor a lekérdezésekben. Ha egy elsődleges kulcs megszorítása több oszlopban is meg van határozva, akkor az értékek megkettőzhetők ugyanabban az oszlopban, de az elsődleges kulcs kényszerdefiníciójában szereplő összes oszlopból származó értékkombinációk mindegyikének egyedinek kell lennie.
Ahogy az alábbi ábrán látható, az oszlopok Termék azonosítóÉs VendorID az asztalban Beszerzés.TermékVendorösszetett elsődleges kulcs kényszert alkotnak az adott táblához. Ez biztosítja, hogy a táblázat minden sorában Termék szállítója egyedi értékkombinációval rendelkezik Termék azonosítóÉs VendorID. Ez megakadályozza az ismétlődő sorok beszúrását.
Egy táblának csak egy elsődleges kulcs megkötése lehet.
Az elsődleges kulcs legfeljebb 16 oszlopot tartalmazhat, és a kulcs teljes hossza nem haladhatja meg a 900 bájtot.
Az elsődleges kulcs által létrehozott index nem okozhatja, hogy a táblában lévő indexek száma meghaladja a 999 nem fürtözött indexet és az 1 fürtözött indexet.
Ha az elsődleges kulcs megszorítása nem határozza meg, hogy az index fürtözött vagy nem fürtözött, akkor fürtözött index jön létre, ha nem létezik a táblában.
Minden olyan oszlopot, amelynek elsődleges kulcsa megszorítása van, nem nullázhatóként kell megadni. Ha nincs megadva semmisség, akkor az elsődleges kulcs megszorításával rendelkező összes oszlop nem nullázható.
Ha egy elsődleges kulcs egy CLR felhasználó által definiált adattípus oszlopán van definiálva, akkor a típus megvalósításának támogatnia kell a bináris rendezést.
Az idegen kulcs (FK) egy oszlop vagy oszlopkombináció, amelyet két tábla adatai közötti kapcsolat kikényszerítésére használnak az idegen kulcs táblában tárolható adatok szabályozása érdekében. Ha egy tábla elsődleges kulcsát tartalmazó egy vagy több oszlopra hivatkozik egy másik tábla egy vagy több oszlopa, akkor kapcsolat jön létre az idegen kulcs hivatkozásában a két tábla között. Ez az oszlop idegen kulcs lesz a második táblázatban.
Például táblázat Sales.SalesOrderHeader táblázathoz kapcsolva Sales.SalesPerson idegen kulcs használatával, mert logikai kapcsolat van az értékesítési rendelések és az értékesítők között. Oszlop SalesPersonID az asztalban Sales.SalesOrderHeader megegyezik a táblázat elsődleges kulcs oszlopával eladó. Oszlop SalesPersonID az asztalban Sales.SalesOrderHeader egy idegen kulcs a táblához eladó. Ennek a kapcsolatnak az idegen kulccsal történő létrehozásával a for érték SalesPersonID táblázatba nem illeszthető be SalesOrderHeader ha jelenleg nincs a táblázatban eladó.
Azon táblák és oszlopok maximális száma, amelyekre egy tábla idegen kulcsként (kimenő hivatkozásként) hivatkozhat, 253. Az SQL Server 2016 253-ról növeli azon további táblák és oszlopok számát, amelyek ugyanabban a táblában (bejövő hivatkozások) hivatkozhatnak az oszlopokra. 10 000-ig. (Legalább 130-as kompatibilitási szint szükséges.) A növekedésnek a következő korlátai vannak:
A 253 idegenkulcs-hivatkozás túllépése csak a DML DELETE műveleteknél támogatott. Az UPDATE és MERGE műveletek nem támogatottak.
Jelenleg nem érhető el több mint 253 idegenkulcs-hivatkozás oszloptári indexekhez, memóriaoptimalizált táblákhoz, Stretch-adatbázishoz vagy particionált idegenkulcs-táblázatokhoz.
Az elsődleges kulcs megszorításaival ellentétben az idegenkulcs-kényszer létrehozása nem hozza létre automatikusan a megfelelő indexet. Azonban gyakran szükséges manuálisan létrehozni egy indexet egy idegen kulcson a következő okok miatt:
Az idegen kulcsú oszlopokat gyakran használják az összekapcsolási feltételekben, amikor együtt használják a kapcsolódó táblákból származó adatok lekérdezésekor. Ezt úgy éri el, hogy egy vagy több oszlopot leképez az egyik tábla idegenkulcs-megszorításában egy másik tábla egy vagy több elsődleges vagy egyedi kulcsú oszlopára. Az index lehetővé teszi az adatbázismotor számára, hogy gyorsan megtalálja a kapcsolódó adatokat egy idegenkulcs-táblázatban. Az index létrehozása azonban nem kötelező. Két kapcsolódó tábla adatai akkor is kombinálhatók, ha a táblák között nincs elsődleges kulcs vagy idegen kulcs megkötés, de két tábla közötti idegenkulcs-kapcsolat azt mutatja, hogy a két tábla úgy van optimalizálva, hogy egy lekérdezésben együtt használhatók legyenek, ahol a kulcsok kritériumként használják.
Az idegenkulcs-megszorítások a kapcsolódó táblákban ellenőrzik az elsődleges kulcs megszorításainak változásait.
Az idegenkulcs-megszorítás fő célja az idegenkulcs-táblában tárolható adatok szabályozása, de ez a megszorítás az elsődleges kulcstáblában lévő adatok módosítását is szabályozza. Például, amikor egy értékesítési vezető számára töröl egy sort a táblázatból Sales.SalesPerson Az, akinek az azonosítója az értékesítési rendelésekben használatos a táblázatban Sales.SalesOrderHeader, a két tábla hivatkozási integritása sérül. Távoli menedzser értékesítési rendelései egy táblázatban SalesOrderHeader táblázat adataira mutató hivatkozás nélkül érvénytelenné válnak eladó.
Az idegen kulcs megszorítása megakadályozza ezt a helyzetet. Egy megszorítás a hivatkozási integritást a következő módon kényszeríti ki: megtiltja az elsődleges kulcs táblában lévő adatok módosítását, ha az ilyen módosítás érvénytelenítené a hivatkozást az idegen kulcs táblában. Ha az elsődleges kulcs táblázat egy sorának törlésekor vagy a kulcs értékének megváltoztatásakor azt találja, hogy az elsődleges kulcs törölt vagy módosított értéke megfelel a bizonyos értéket egy másik táblában lévő idegen kulcs megszorítása esetén nem történik művelet. Az idegenkulcs-megszorítással rendelkező sor sikeres frissítéséhez vagy törléséhez először törölnie kell az idegenkulcs-táblázatban lévő idegenkulcs-adatokat, vagy módosítania kell az idegen kulcsot egy másik elsődleges kulcsadattal összekapcsoló idegenkulcs-táblázat adatait.
A lépcsőzetes hivatkozási integritási megszorítások segítségével meghatározhatja, hogy az adatbázismotor milyen műveleteket hajtson végre, amikor a felhasználó olyan kulcsot próbál törölni vagy frissíteni, amelyre még mindig idegen kulcsok mutatnak. A következő lépcsőzetes műveletek határozhatók meg.
NINCS MŰVELET
Az adatbázismotor hibát generál, majd a szülőtábla sorában végrehajtott törlési vagy frissítési műveletet visszaállítja.
VÍZESÉS
A megfelelő sorok frissítésre kerülnek vagy eltávolításra kerülnek a hivatkozási táblából, ha adott string frissítve vagy eltávolítva a szülőtáblából. A CASCADE érték nem adható meg, ha az oszlop típusú időbélyeg egy idegen vagy hivatkozási kulcs része. Az ON DELETE CASCADE művelet nem adható meg olyan táblában, amelynél a TÖRLÉS HELYETT trigger van megadva. Az ON UPDATE CASCADE záradék nem használható olyan táblákon, amelyek UPDATE triggerrel rendelkeznek.
SET NULL
Az idegen kulcsot alkotó összes érték NULL lesz, amikor a szülőtábla megfelelő sorát frissítik vagy törlik. Az idegen kulcs oszlopainak érvénytelennek kell lenniük ahhoz, hogy ez a korlátozás érvényesüljön. Nem állítható be olyan táblákhoz, amelyekben UPDATE triggerek vannak megadva.
ALAPÉRTELMEZETTRE ÁLLÍTÁS
Az idegen kulcsot alkotó összes érték az alapértelmezett értékre áll be, amikor a szülőtábla megfelelő sorát törlik vagy frissítik. Ennek a megszorításnak való megfeleléshez minden idegen kulcs oszlopnak alapértelmezett definícióval kell rendelkeznie. Ha az oszlop nullázható, és nincs kifejezetten definiálva alapértelmezett érték, akkor az oszlop alapértelmezett értéke NULL lesz. Nem állítható be olyan táblákhoz, amelyekben UPDATE triggerek vannak megadva.
A CASCADE, SET NULL, SET DEFAULT és NO ACTION kulcsszavak kombinálhatók olyan táblázatokban, amelyek kölcsönösen hivatkoznak egymásra. Ha az adatbázismotor a NO ACTION kulcsszóval találkozik, leállítja és visszaállítja a kapcsolódó CASCADE, SET NULL és SET DEFAULT műveleteket. Ha egy DELETE utasítás a CASCADE, SET NULL, SET DEFAULT és NO ACTION kulcsszavak kombinációját tartalmazza, akkor az összes CASCADE, SET NULL és SET DEFAULT művelet végrehajtásra kerül, mielőtt az adatbázismotor NO ACTION műveletet keresne.
A lépcsőzetes hivatkozási műveletek az UPDATE vagy AFTER DELETE triggerek aktiválódnak, például:
Minden, az eredeti DELETE vagy UPDATE utasítás által közvetlenül okozott lépcsőzetes hivatkozási művelet végrehajtásra kerül először.
Ha vannak AFTER triggerek definiálva a megváltozott táblákon, akkor ezek a triggerek az összes lépcsőzetes művelet végrehajtása után aktiválódnak. Ezek a triggerek a lépcsőzetes műveletek fordított sorrendjében futnak. Ha több trigger van megadva ugyanahhoz a táblához, akkor azok véletlenszerű sorrendben aktiválódnak, hacsak nincs dedikált első és utolsó tábla trigger megadva. Ezt a sorrendet az eljárás határozza meg.
Ha a lépcsőzetes műveletsorozatok olyan táblából származnak, amely egy TÖRLÉS vagy UPDATE művelet közvetlen célpontja volt, akkor nincs meghatározva a sorrend, amelyben az eseménysorozatok aktiválják az eseményindítókat. Azonban az egyik műveletsorozat mindig elindítja az összes triggert, mielőtt a következő.
A DELETE vagy UPDATE művelet közvetlen célpontjaként szolgáló táblán lévő AFTER eseményindító attól függetlenül aktiválódik, hogy módosult-e valamelyik sor. Ebben az esetben a kaszkádolás semmilyen más táblát nem érint.
Ha az előző triggerek egyike DELETE vagy UPDATE műveleteket hajt végre más táblákon, akkor ezek a műveletek kiválthatják saját lépcsőzetes műveletsorozataikat. Ezeket a másodlagos munkafolyamatokat a rendszer minden DELETE vagy UPDATE művelethez feldolgozza, miután az elsődleges munkafolyamatok összes triggerét végrehajtották. Ez a folyamat rekurzív módon megismételhető a következő DELETE vagy UPDATE műveletekhez.
A CREATE, ALTER, DELETE vagy más DDL-műveletek végrehajtása az eseményindítókon belül a DDL-indítók tüzelését okozhatja. Ez további DELETE vagy UPDATE műveleteket eredményezhet, amelyek további lépcsőzetes szekvenciákat indítanak el, és elindítják a saját triggereiket.
Ha hiba történik a lépcsőzetes referenciaműveletek egy adott sorozatában, akkor ebben a sorozatban nem indul el az AFTER trigger, és az adott sorozat által generált DELETE vagy UPDATE műveletek visszaállnak.
A táblában, amelyen egy INSTEAD OF trigger van definiálva, lehet egy REFERENCES záradék is, amely egy adott lépcsőzetes műveletet határoz meg. A lépcsőzetes művelet céltábláján lévő AFTER trigger azonban végrehajthat egy INSERT, UPDATE vagy DELETE utasítást egy másik táblán vagy nézeten, amely egy INTEAD OF triggert indít el az adott objektumon.
Az alábbi táblázat az elsődleges kulcs és az idegen kulcs megszorításaival kapcsolatos gyakori feladatokat sorolja fel.
Így észrevétlenül közelítettünk egy nagyon fontos témához - az elsődleges és az idegen kulcsokhoz. Ha az előbbit szinte mindenki használja, akkor az utóbbit valamiért figyelmen kívül hagyják. De hiába. Az idegen kulcsok nem jelentenek problémát, valódi segítséget jelentenek az adatintegritásban.
Sokat beszéltünk már a kulcsterületekről, de soha nem használtuk őket. A legérdekesebb az, hogy minden működött. Ez az alap előnye, esetleg hátránya Microsoft adatok SQL Server és MS Access. Paradox táblákban ez a trükk nem működik, és kulcsmező nélkül a táblázat csak olvasható lesz.
A kulcsok bizonyos mértékig megszorítások, és a CHECK utasítással együtt is szóba jöhetnek, mivel a deklaráció hasonló módon történik, és még a CONSTRAINT utasítás is használatos. Nézzük meg ezt a folyamatot egy példán keresztül. Ehhez létrehozunk egy táblázatot két mezőből: „guid” és „vcName”. Ebben az esetben a "guid" mező elsődleges kulcsként van beállítva:
TÁBLÁZAT LÉTREHOZÁSA Globally_Unique_Data (guid egyedi azonosító DEFAULT NEWID(), vcName varchar(50), CONSTRAINT PK_guid ELSŐDLEGES KULCS (Útmutató))
A legfinomabb itt a CONSTRAINT vonal. Mint tudjuk, utána kulcsszó jön a megszorítás neve, és ez alól a deklarációs kulcs sem kivétel. Az elsődleges kulcs elnevezéséhez javaslom a PK_name használatát, ahol a név annak a mezőnek a neve, amelyből a főkulcs lesz. A PK rövidítés az elsődleges kulcsból (elsődleges kulcs) származik.
Ezt követően a megszorításoknál használt CHECK kulcsszó helyett egy PRIMARY KEY utasítás van, ami azt jelzi, hogy nem ellenőrzésre, hanem elsődleges kulcsra van szükségünk. A zárójelek egy vagy több olyan mezőt jeleznek, amelyekből a kulcs lesz.
Ne feledje, hogy egy kulcsmezőben nem lehet két sor azonos értékű, ebben az elsődleges kulcs megszorítása megegyezik az egyedi megszorítással. Ez azt jelenti, hogy ha a vezetéknév tárolására szolgáló mezőt elsődleges kulcsnak állítja be, akkor nem lehet két különböző nevű Ivanovot beírni egy ilyen táblába. Ez sérti az elsődleges kulcsra vonatkozó megkötést. Ez az oka annak, hogy a kulcsok megszorítások, és megegyeznek a CHECK megszorítással. De ez nem csak az elsődleges kulcsokra és az egyediséget biztosító másodlagos kulcsokra igaz.
BAN BEN ezt a példát, az elsődleges kulcs egy egyedi azonosító (GUID) típusú mező. Ennek a mezőnek az alapértelmezett értéke a NEWID szerver eljárás végrehajtásának eredménye.
Figyelem
Egy táblához csak egy elsődleges kulcs hozható létre
A példák egyszerűsítése érdekében kulcsként célszerű numerikus típust használni, és ha az adatbázis lehetővé teszi, akkor jobb, ha "autoincrement" típusú (automatikusan növekvő / csökkenő szám). Az MS SQL Serverben egy ilyen mező az IDENTITY, az MS Accessben pedig egy „számláló” típusú mező.
A következő példa bemutatja, hogyan hozhat létre terméktáblázatot elsődleges kulcsként automatikusan növekvő egész mezővel:
TÁBLÁZAT LÉTREHOZÁSA Termékek (azonosító int IDENTITY(1, 1), termék varchar(50), ár pénz, mennyiség numerikus (10, 2), CONSTRAINT PK_id PRIMARY KEY (id))
Leggyakrabban ezt a kulcstípust fogjuk használni, mert a könnyen olvasható számok a kulcsmezőben tárolódnak, és könnyebben és látványosabbá válik velük a munka.
Az elsődleges kulcs egynél több oszlopot tartalmazhat. A következő példa egy táblát hoz létre, amelyben az "id" és a "Product" mezők alkotják az elsődleges kulcsot, ami azt jelenti, hogy mindkét mezőben egyedi index jön létre:
TÁBLÁZAT LÉTREHOZÁSA Termékek1 (azonosító int IDENTITY(1, 1), termék varchar(50), ár pénz, mennyiség numerikus (10, 2), CONSTRAINT PK_id PRIMARY KEY (azonosító, [termék neve]))
A programozók nagyon gyakran hoznak létre egy adatbázist egy kulcsmezővel egész szám formájában, ugyanakkor a feladatban egyértelmű, hogy bizonyos mezőknek egyedinek kell lenniük. És miért ne hozna azonnal elsődleges kulcsot azokból a mezőkből, amelyeknek egyedinek kell lenniük, és nem kell külön megoldásokat készíteni erre a problémára.
A többoszlopos elsődleges kulcs egyetlen hátránya a kapcsolatok létrehozásának problémája. Itt különféle módszerekkel kell kijutni, de a probléma továbbra is megoldható. Csak be kell írnia egy egyedi azonosító típusú mezőt, és létre kell hoznia a kapcsolatot. Igen, ebben az esetben kapunk egy egyedi elsődleges kulcsot és egy egyedi azonosító típusú mezőt, de ennek eredményeként ez a redundancia nem lesz nagyobb, mint ugyanaz a tábla, ahol az elsődleges kulcs az egyedi azonosító, és egyediségi megszorítások vannak beállítva azokra a mezőkre, amelyeket meg kell adni. egyedi. Mit válasszunk? Attól függ konkrét feladatés ami a legjobban működik az Ön számára.
Az idegen kulcs egyben CONSTRAINT kényszer is, és két tábla közötti kapcsolatot képvisel. Tegyük fel, hogy két táblázata van:
Egy embernek több telefonja is lehet, ezért az adattárolást különböző táblázatokba osztottuk. Az 1.4. ábra szemlélteti a két tábla kapcsolatát. Ha már dolgozott linkelt táblákkal, akkor ez elég lesz Önnek. Ha először hall a kapcsolatokról, akkor próbáljuk meg közelebbről megvizsgálni a problémát.
Például vegyünk egy háromfős asztalt. Az 1.3. táblázat a "Nevek" tábla tartalmát mutatja. Csak három sor van, és mindegyiknek megvan a maga egyedi főkulcsa. Az egyediség kedvéért a táblázat létrehozásakor a kulcsot automatikusan növekményes mezővé tesszük.
1.3. táblázat A nevek táblázat tartalma
1.4. táblázat. A Telefonok táblázat tartalma
Az 1.4. táblázat öt telefonszámot tartalmaz. A mesterkulcs mezőben található egy egyedi mesterkulcs is, amely szintén automatikusan növelhető. A másodlagos kulcs egy kapcsolat a nevek tábla elsődleges kulcsával. Hogyan működik ez a kapcsolat? Petrovnak az 1-es a fő kulcsa a nevek táblában, a Telefonok táblában pedig a másodlagos kulcsban keressük az 1-es számot, és megkapjuk Petrov telefonszámait. Ugyanez vonatkozik a többi bejegyzésre is. Vizuálisan a kapcsolat az 1.5. ábrán látható.
Az ilyen adattárolás nagyon kényelmes. Ha nem lehetne linkelt táblákat létrehozni, akkor a Nevek táblában egy mezőben kellene az összes telefonszámot kitölteni. Ez kényelmetlen a használat, a támogatás és az adatok visszakeresése szempontjából.
Egy táblázatban több Név mezőt is létrehozhat, de kérdés, hogy hányat. Egy embernek csak 1 telefonja lehet, nekem például 3 van, nem számítva a dolgozókat. Nagyszámú mezők adatredundanciához vezetnek.
Lehetséges, hogy a nevek táblázatban minden telefonnak külön sora legyen a vezetéknévvel, de ez csak egy ilyen egyszerű példánál egyszerű, amikor csak a vezetéknevet kell megadni, és egyszerűen több bejegyzést is megadhat Petrov számára. több telefonszámot. És ha 10 vagy 20 mező van? Tehát két idegen kulccsal összekapcsolt tábla létrehozása látható az 1.6-os listában.
Felsorolás 1.6. Idegen kulccsal összekapcsolt táblák létrehozása
CREATE TABLE Nevek (idName int IDENTITY(1,1), vcName varchar(50), CONSTRAINT PK_guid PRIMARY KEY (idName),) CREATE TABLE Telefonok (idPhone int IDENTITY(1,1), idName int, vcPhone varchar(10), CONSTRAINT PK_idPhone PRIMARY KEY (idPhone), CONSTRAINT FK_idName FOREIGN KEY (idName) HIVATKOZÁSOK Nevek (idName))
Olvassa el figyelmesen a lista tartalmát. Ez azért elég érdekes, mert néhány olyan operátort használ, amelyeket már tárgyaltunk, és további példa nem akadályozza meg. Mindkét táblához létrejön egy kulcsmező, amely először jön, int típusú, és 1-től automatikusan eggyel növekszik. A kulcsmező egy CONSTRAINT megszorítással mesterkulcs lesz.
A Telefonok tábla leírásában az utolsó sor egy számunkra új deklarációt tartalmaz, mégpedig egy idegen kulcs deklarációt a FOREIGN KEY operátor használatával. Amint látja, ez is egy korlátozás, és egy kicsit később látni fogja, miért. A zárójelek azt a táblázatmezőt jelzik, amelyet egy másik táblához kell kapcsolni. Ezt követi a REFERENCES (referencia) kulcsszó, annak a táblának a neve, amellyel a kapcsolatnak lennie kell (Nevek) és zárójelben a mező neve ("idName"). Így létrejött egy kapcsolat, amely az 1.4. ábrán látható.
Figyelem!
Az idegen kulcs csak egy másik tábla elsődleges kulcsára vagy egyedi megszorításra hivatkozhat. Ez azt jelenti, hogy a REFERENCES kulcsszót a tábla nevének kell követnie, és csak az elsődleges kulcs vagy egy EGYEDI kényszerű mező adható meg zárójelben. Más mezők nem adhatók meg.
Most, ha meg tudja tölteni adatokkal a táblákat. A következő három parancs hozzáadja az 1.3 táblázatban látott három vezetéknevet:
INSERT INTO Nevek(vcName) VALUES("Petrov") INSERT INTO Nevek(vcName) VALUES("Ivanov") INSERT INTO Nevek(vcName) VALUES("Sidorov")
Ha már dolgozott SQL-lel, akkor a telefontáblázathoz is hozzáadhat bejegyzéseket. Ezeket a parancsokat kihagyom, de a CD-n a Chapter1 könyvtárában az Foreign_keys.sql fájlban láthatod őket.
Most az a feladatunk, hogy megnézzük, mik az idegen kulcs korlátozó műveletei, találjuk ki. Különböző táblázatokban kifejezett kapcsolatot jeleztünk két mező között. Ha olyan rekordot próbál felvenni a telefontáblázatba, amely az "idName" mezőben olyan azonosítóval rendelkezik, amely nem létezik a vezetékneveket tartalmazó táblázat azonos nevű mezőjében (a név eltérhetett volna), hiba történik. . Ez megszakítja a kapcsolatot a két tábla között, és az idegen kulcs megszorítása megakadályozza a kapcsolat nélküli rekordok létezését.
A korlátozás a rekordok módosítására vagy törlésére is vonatkozik. Például, ha megpróbálja törölni a Petrov vezetéknevű sort, akkor egy idegenkulcs-kényszer hibaüzenetet kap. Nem törölhet olyan rekordokat, amelyek külsőleg kapcsolódó sorokkal rendelkeznek. Először is törölnie kell az összes telefont ehhez a bejegyzéshez, és csak ezt követően lehet törölni magát a sort Petrov néven.
Az idegenkulcs létrehozása során a CASCADE TÖRLÉSE vagy az UPDATE CASCADE értéke adható meg. Ebben az esetben, ha törli a Petrov rekordot a Nevek táblából, vagy megváltoztatja az azonosítót, akkor a Telefonok tábla Petrov sorhoz tartozó összes rekordja automatikusan frissül. Soha. Nem, írnod kell nagybetűvel V: SOHA ne csináld ezt. Mindent kézzel kell eltávolítani vagy megváltoztatni. Ha a felhasználó véletlenül töröl egy bejegyzést a nevek táblából, akkor a megfelelő telefonok is törlődnek. Akkor van értelme idegen kulcsot létrehozni, ha a korlátozó erejének fele eltűnik! Mindent csak manuálisan kell elvégezni, és egyáltalán nem ajánlott az azonosítók megváltoztatása.
Maguk a táblák törlését is a gyermektáblával, vagyis a Telefonokkal kell kezdeni, és csak ezután lehet törölni a fő nevek táblát.
Végül megmutatom, hogyan lehet két táblázatból gyönyörűen párosítani a neveket és a telefonszámokat:
SELECT vcName, vcPhone FROM nevek, telefonok WHERE Names.idName=Phones.idName
Az ilyen típusú lekérdezésekről bővebben a 2. fejezetben fogunk beszélni. Egyelőre egy példát mellékeltem, hogy bemutassam a csatolt táblák erejét.
Egy tábla legfeljebb 253 idegen kulcsot tartalmazhat, ami még a legösszetettebb adatbázisokhoz is elegendő. Személy szerint olyan adatbázisokkal kellett dolgoznom, ahol az idegen kulcsok száma táblánként nem haladta meg a 7-et. Ha több, akkor valószínűleg az adatbázis helytelenül van megtervezve, bár vannak kivételek.
Maga a tábla is maximum 253 idegen kulcsot tartalmazhat. A táblákban lévő idegen kulcsok kevésbé gyakoriak, többnyire legfeljebb 3. Leggyakrabban egy táblának sok hivatkozása lehet más táblákra.
Az idegen kulcs ugyanarra a táblára hivatkozhat, amelyben létrehozták. Például van egy táblázat a pozíciókról egy szervezetben, ahogy az 1.5. táblázatban látható. A táblázat három mezőből áll: elsődleges kulcs, idegen kulcs és munkakör. Bármely szervezetben sok pozíció lehet, de logikus lenne egy táblázatban feltüntetni a nevüket és az alárendeltségi struktúrájukat. Ehhez az idegen kulcsot hozzá kell rendelni a feladattábla elsődleges kulcsához.
1.5. táblázat. Táblázat belső hivatkozással
Ennek eredményeként azt kapjuk, hogy a vezérigazgatónak nulla idegen kulcsa van, pl. ez a pozíció az összes többi élén áll. A kereskedelmi igazgatónál és a főigazgatónál az idegen kulcs a főigazgató sorára mutat. Ez azt jelenti, hogy ez a két pozíció közvetlenül a vezérigazgató alá tartozik. Stb.
Nézzük meg, hogyan hozhatjuk létre mindezt SQL lekérdezés formájában:
TÁBLÁZAT LÉTREHOZÁSA Pozíciók (idPosition int IDENTITY(1,1), idParentPosition int, vcName varchar(30), CONSTRAINT PK_idPosition PRIMARY KEY (idPosition), CONSTRAINT FK_idParentPosition FOREIGN KEY (idParentPosition) REFERENCESPosition)
Mint látható, az idegen kulcs egyszerűen ugyanarra a táblára vonatkozik, amelyet létrehozunk. A CD-n a Chapter1 könyvtárban az idegen_kulcsok_self.sql fájlban láthatunk példát ennek a táblának az elkészítésére, adatokkal való kitöltésére és a pozíciók megjelenítésére, az alárendeltségük figyelembevételével. A következő fejezetben részletesebben megvizsgáljuk az ilyen táblázatokkal való munka lehetőségét.
Eddig a klasszikus összefüggést tekintettük, amikor a fő adattábla egy sora megfelel a hozzá tartozó tábla egy sorának. Az ilyen kapcsolatot egy-a-többhöz hívják. De vannak más kapcsolatok is, és most egy másikat fogunk figyelembe venni - egytől egyig, amikor a fő táblázat egyik rekordja kapcsolódik egy másik rekordhoz. Ennek megvalósításához elegendő mindkét tábla elsődleges kulcsát összekapcsolni. Mivel az elsődleges kulcsok nem ismételhetők, mindkét táblában csak egy sor kapcsolható össze.
A következő példa két olyan táblát hoz létre, amelyeknek kapcsolata van az elsődleges kulcsok között:
TÁBLÁZAT Nevek LÉTREHOZÁSA (idName egyedi azonosító DEFAULT NEWID(), vcName varchar(50), CONSTRAINT PK_guid ELSŐDLEGES KULCS (idName)) TÁBLÁZAT LÉTREHOZÁSA Telefonok (idPhone egyedi azonosító DEFAULT NEWID(), vcPhone varchar(10), CONSTRAINTIDP (PRIMARYIDPhone) CONSTRAINT FK_idPhone IDEGEN KULCS (idPhone) HIVATKOZÁS Nevek (idName))
Csak az egyik táblához kell idegen kulcs. Mivel a kapcsolat egy az egyhez, nem mindegy, hogy melyik táblában hozza létre.
A legösszetettebb kapcsolat a sok a sokhoz, ahol egy tábla sok rekordja megegyezik egy másik tábla sok rekordjával. Ennek megvalósításához nem elég két tábla, három tábla kell.
Először is meg kell értened, mikor használható a sok-sok kapcsolat? Tegyük fel, hogy van két táblázata: a ház lakóinak listája és a telefonszámok listája. Egy lakásban több szám is lehet, ami azt jelenti, hogy két telefon is tartozhat ugyanahhoz a vezetéknévhez. Tehát egy-a-többhöz kapcsolatról van szó. Másrészt egy lakásban (közösségi lakásban vagy csak bérlőben) lehet két család, aki a tulajdonos telefonját használja, ami azt jelenti, hogy a telefon és a lakó kapcsolata is egy a többhez. A legnehezebb megoldás pedig az, hogy egy kommunális lakásban van két telefon. Ebben az esetben a lakás több lakója mindkét számot használja. Így kiderül, hogy "sok" család "sok" telefont használhat (sok-sok kommunikáció).
Hogyan valósítsunk meg egy sok-sok kapcsolatot? Első pillantásra ez lehetetlen a relációs modellben. 10 éve kerestem különböző változatokés ennek eredményeként egy tábla jött létre, amely túlcsordult az adatredundanciától. De egy nap kaptam egy feladatot, aminek köszönhetően már abból a feltételből is kitűnő megoldás került a felszínre - létre kell hozni két táblát a lakások és telefonok lakóiról, és csak az elsődleges kulcsot kell megvalósítani bennük. Ebben a táblázatban nincs szükség idegen kulcsokra. És itt a táblák közötti kommunikációnak a harmadik, összekötő táblán keresztül kell történnie. Első pillantásra ez nehéz és nem egyértelmű, de ha megérti ezt a módszert, látni fogja a megoldás teljes erejét.
Az 1.6. és 1.7. táblázat példákat mutat be a vezetéknevekre és a telefonszámokra. Az 1.8 táblázat pedig egy hivatkozási táblázatot mutat.
1.6. táblázat. Vezetéknév táblázat
1.7. táblázat. Telefon asztal
1.8. táblázat. Telefon asztal
Lássuk most, mi lesz az adatkeresés logikája a sok-sok kapcsolatban. Tegyük fel, hogy meg kell találnunk az összes Ivanovhoz tartozó telefont. Ivanov elsődleges kulcsa 1. A hivatkozási táblában megtaláljuk az összes olyan rekordot, amelyben a "Kapcsolat a névvel" mező 1-gyel egyenlő. Ezek az 1-es és a 2-es rekordok. Ezekben a rekordokban az 1-es és a 2-es azonosító található. a "Kapcsolat a telefonnal" mezőben, és így Ivanov birtokolja a telefontáblázatban szereplő számokat, amelyek az 1. és 2. sorban találhatók.
Most oldjuk meg az inverz problémát – határozzuk meg, hogy ki férhet hozzá az 567575677 telefonszámhoz. A telefonszámtáblázatban ennek a számnak a kulcsa 3. Minden olyan rekordot keresünk a hivatkozási táblázatban, ahol a „Kapcsolat a telefonnal” mező egyenlő. 3-ig. Ezek 4-es és 5-ös számú rekordok, amelyek a "Kapcsolat a névhez" mezőben a 2-es és 3-as értéket tartalmazzák. Ha most megnézi a vezetéknevek táblázatát, a 2. és 3. szám alatt Petrov és Sidorov látható. Tehát ez a két lakos használja az 567575677 telefonszámot.
Nézze át mindhárom táblázatot, és győződjön meg arról, hogy megértette, melyik telefonszám melyik lakoshoz tartozik, és fordítva. Ha látja ezt az összefüggést, megérti, hogy ez olyan egyszerű, mint három fillér, és gyorsan megvalósíthatja a projektjeiben.
TÁBLÁZAT Nevek LÉTREHOZÁSA (idName egyedi azonosító DEFAULT NEWID(), vcName varchar(50), CONSTRAINT PK_guid ELSŐDLEGES KULCS (idName)) TÁBLÁZAT LÉTREHOZÁSA Telefonok (idPhone egyedi azonosító DEFAULT NEWID(), vcPhone varchar(10), CONSTRAINThonidP (PRIMARYidPhonePK) CREATE TABLE LinkTable (idLinkTable egyedi azonosító DEFAULT NEWID(), idName egyedi azonosító, idPhone egyedi azonosító, CONSTRAINT PK_idLinkTable PRIMARY KEY (idLinkTable), CONSTRAINT FK_idPhone FOREIGN KEY (IDLinkTable) CONSTRAINT FOREIGN KEY (CONSTRAINT FK_idPhone FOREIGN KEY) IGN KEY (idName) REFERENCIÁK Nevek (idName) )
Az összekötő táblának két idegen kulcsa van, amelyek a név- és telefonszámtáblázathoz kapcsolódnak, és egy elsődleges kulcs, amely egyedivé teszi a rekordokat.
A GUID mezőt választottam elsődleges kulcsnak, mert így kényelmesebb az adott feladat megoldása. A helyzet az, hogy rekordokat kell beszúrnunk két táblába, és mindkét esetben ugyanazt a kulcsot kell megadnunk. A GUID érték előállítható, majd felhasználható, amikor adatokat szúr be mindkét táblába.
Kulcsként használhatunk automatikusan növekvő mezőt is, de ebben az esetben a probléma kicsit nehezebben megoldható, pontosabban kényelmetlen a probléma megoldása. Például telefonszám hozzáadásakor először be kell szúrni a megfelelő sort a táblázatba, majd meg kell keresni, meg kell határozni a sorhoz rendelt kulcsot, majd létre kell hozni a kapcsolatot.
Ebben a szakaszban csak a táblázatok létrehozására szorítkozunk, és a 2.8-as szakaszban visszatérünk ehhez a témához, és megtanuljuk, hogyan kell dolgozni a kapcsolódó táblázatokkal. Az egy az egyhez és az egy a sokhoz kapcsolatokkal végzett munka nem sokban különbözik, mivel ebben a sémában csak két tábla szerepel. A több-többhöz kapcsolat az összekapcsolási tábla miatt egy kicsit bonyolultabb, ezért a 2.27. részben külön foglalkozunk vele.
Az ábrán egy táblázat (5-ös fokozat aránya) látható, amely egy feltételezett vállalkozás alkalmazottairól tartalmaz néhány információt. A táblázat sorai soroknak felelnek meg. Minden sor valójában egy valós világbeli objektum leírása (jelen esetben egy alkalmazott), amelynek jellemzőit az oszlopok tartalmazzák. A relációs kapcsolatok az entitáshalmazoknak, míg a sorok az entitásoknak felelnek meg. A táblázat relációs kapcsolatot reprezentáló oszlopait hívjuk attribútumokat.
Minden attribútum egy tartományon van definiálva, így a tartomány az adott attribútum érvényes értékeinek halmazaként fogható fel. Ugyanazon tartományon ugyanannak a relációnak több attribútuma, sőt különböző relációk attribútuma is definiálható.
Egy olyan attribútumot hívunk meg, amelynek értéke egyedileg azonosítja a sorokat kulcs (vagy egyszerűen kulcs). A kulcs a "Személyzeti szám" attribútum, mivel ennek értéke a vállalkozás minden alkalmazottja esetében egyedi. Ha a sorokat csak több attribútum értékének összefűzésével azonosítjuk, akkor a relációnak összetett kulcsa van.
elsődleges kulcs- relációs adatmodellben a reláció egyik lehetséges kulcsa, fő kulcsnak (vagy alapértelmezett kulcsnak) választott.
Egy reláció több kulcsot is tartalmazhat. Mindig az egyik kulcsot deklarálják elsődleges, értékei nem frissíthetők. Az összes többi relációs kulcsot meghívjuk lehetséges kulcsok.
Az elmélet szempontjából a reláció összes potenciális (lehetséges) kulcsa ekvivalens, azaz azonos egyediség és minimális tulajdonságokkal rendelkeznek. Elsődlegesként azonban általában az egyik lehetséges kulcsot választják, amely bizonyos gyakorlati célokra a legkényelmesebb, például létrehozásához külső kulcsokat más módon, vagy fürtözött index létrehozásához. Ezért általában a legkisebb méretűt választják elsődleges kulcsnak ( fizikai tárolás) és/vagy tartalmazza a legkevesebb attribútumot.
Ha elsődleges kulcs egyetlen attribútumból áll, ezt nevezzük egyszerű kulcs.
Ha elsődleges kulcs két vagy több attribútumból áll, úgy hívják összetett kulcs. Tehát a keresztnév, vezetéknév, apanév, útlevélszám, útlevél-sorozat nem lehet külön-külön elsődleges kulcs, hiszen kettő vagy több ember számára ugyanaz lehet. De nincs két azonos típusú, azonos sorozatú és sorszámú személyi okmány. Ezért egy személyekre vonatkozó adatokat tartalmazó relációban az elsődleges kulcs az attribútumok egy részhalmaza lehet, amely a személyes dokumentum típusából, sorozatából és számából áll.
A hierarchikus és hálózati adatmodellekkel ellentétben a relációs modellben nem szerepel a csoportkapcsolat fogalma. A különböző relációk sorai közötti asszociációk tükrözésére a kulcsok megkettőzését használják.
Azokat az attribútumokat hívják meg, amelyek más kapcsolatok kulcsainak másolatai idegen kulcsok.
Például az OSZTÁLY és az ALKALMAZOTT közötti kapcsolat az elsődleges kulcs másolásával jön létre "Osztályszám" az első viszonyból a másodikba. Így ahhoz, hogy egy adott alosztály alkalmazottainak listáját megkapjuk, szükséges: 1) A RÉSZLET táblából állítsa be az attribútum értékét "Osztályszám" A megadott "Osztálynév"-nek megfelelő. 2) válassza ki az összes rekordot az EMPLOYEE táblából, attribútumértéket "Osztályszám" amely megegyezik az előző lépésben kapottal. Annak érdekében, hogy megtudja, melyik osztályon dolgozik egy alkalmazott, fordított műveletet kell végrehajtania: 1) Határozza meg "Osztályszám" az ALKALMAZOTT táblázatból. 2) A kapott érték alapján az OSZTÁLY táblázatban találunk egy bejegyzést.
18. Normalizálás relációs adatbázisokban, normálforma fogalma az adatbázistervezésben.
normál forma - egy kapcsolat olyan tulajdonsága egy relációs adatmodellben, amely azt redundancia szempontjából jellemzi, ami potenciálisan logikailag hibás mintavételi vagy adatváltozási eredményhez vezethet. A normál formát úgy határozzuk meg, mint a követelmények összességét, amelyeknek egy relációnak meg kell felelnie.
Az adatbázis normál formátumúvá alakításának folyamatát ún normalizálás . A normalizálás célja az adatbázis szerkezetének olyan formába hozása, amely minimális redundanciát biztosít, vagyis a normalizálásnak nem célja a teljesítmény csökkentése vagy növelése, illetve az adatbázis méretének csökkentése vagy növelése. A normalizálás végső célja az adatbázisban tárolt információk esetleges inkonzisztenciájának csökkentése.
A redundanciát általában úgy küszöböljük ki, hogy a relációkat felbontjuk oly módon, hogy minden relációban csak elsődleges tények tárolódnak (vagyis olyan tények, amelyek nem származnak más tárolt tényekből).
funkcionális függőségek.
A relációs adatbázis strukturális és szemantikai információkat is tartalmaz. Az adatbázis szerkezetét a benne lévő kapcsolatok száma és típusa, valamint a kapcsolatok sorai között létező egy-a többhez kapcsolatok határozzák meg. A szemantikai rész leírja azon funkcionális függőségek halmazát, amelyek ezen kapcsolatok attribútumai között léteznek. Adjuk meg a funkcionális függőség definícióját.
19. 1NF: Alapvető definíciók és transzformációs szabályok.
Az első normál forma tárgyalásához két definíciót kell megadni:
egyszerű tulajdonság - attribútum, amelynek értékei atomi (oszthatatlanok).
Komplex attribútum - több, ugyanazon vagy különböző tartományon definiálható atomi attribútumok kombinálásával kapjuk (vektornak vagy adataggregátumnak is nevezik).
Az első normál forma meghatározása:
egy reláció 1NF-ben van, ha az összes attribútum értéke atomi. . Ellenkező esetben ez egyáltalán nem táblázat, és az ilyen attribútumokat le kell bontani.
Vegyünk egy példát:
A vállalat HR adatbázisának olyan információkat kell tárolnia az alkalmazottakról, amelyekkel kapcsolatban megpróbálhatja reprezentálni
EMPLOYEE (EMPLOYEE_NUMBER, NAME, BIRTH_DATE, WORK_HISTORY, CHILDREN).
Ennek a kapcsolatnak a gondos vizsgálatából az következik, hogy az attribútumok "munkatörténet"És "gyermekek"összetettek, ráadásul az attribútum "munkatörténet" egy másik összetett attribútumot is tartalmaz "fizetés_történet".
Ezek az egységek így néznek ki:
HISTORY_WORK (DATE_RECEPTION, NÉV, HISTORY_SALARY),
HISTORY_SALARY (DATE_POINTMENT, SALRY),
GYERMEKEK (CHILD_NAME, BIRTH_YEAR).
Ezek kapcsolatát az ábra mutatja. 3.3.
3.3. ábra. Kezdeti kapcsolat.
Ahhoz, hogy az eredeti ALKALMAZOTT relációt az első normál formára hozzuk, négy relációra kell bontani, amint az a következő ábrán látható: 
3.4. Normalizált kapcsolatok halmaza.
Itt minden reláció elsődleges kulcsa kékkel van kiemelve, az idegen kulcsok neve pedig kékkel. Emlékezzünk vissza, hogy az idegen kulcsok az eredeti relációban létező funkcionális függőségek megjelenítésére szolgálnak. Ezeket a funkcionális függőségeket nyilakkal ellátott vonalak jelzik.
A normalizációs algoritmust E.F. Codd a következőképpen írja le:
20. 2NF: Alapvető definíciókés az átalakítási szabályokat.
Nagyon gyakran egy reláció elsődleges kulcsa több attribútumot tartalmaz (ebben az esetben hívják meg összetett) - lásd például az ábrán látható GYERMEKEK összefüggést. 3.4 19. kérdés. Ez vezeti be a fogalmat teljes funkcionális függőség.
Meghatározás:
Egy nem kulcs attribútum funkcionálisan függ egy összetett kulcstól, ha funkcionálisan függ a kulcs egészétől, de funkcionálisan nem függ egyik alkotó attribútumától sem.
Példa:
Legyen SUPPLY reláció (N_BESZÁLLÍTÓ, TERMÉK, ÁR).
Egy szállító szállíthat különböző árukat, és ugyanazt az árut különböző szállítók szállíthatják. Akkor a kapcsolat kulcsa "N_beszállító + cikk". Minden szállító ugyanazon az áron szállítson árut. Ekkor a következő funkcionális függőségeink vannak:
A "price" attribútum nem teljes funkcionális függése a kulcstól a következő anomáliához vezet: amikor egy termék ára megváltozik, a kapcsolat teljes áttekintése szükséges ahhoz, hogy megváltoztassuk a szállítóiról szóló összes rekordot. Ez az anomália annak a következménye, hogy két szemantikai tény egy adatstruktúrában egyesül. A következő kiterjesztés megadja a kapcsolatot a 2NF-ben:
Így lehet adni
A második normál forma meghatározása: Egy reláció 2NF-ben van, ha 1NF-ben van, és minden nem kulcs attribútum funkcionálisan függ a kulcstól.
21. 3NF: Alapvető definíciókés az átalakítási szabályokat.
A harmadik normálforma tárgyalása előtt be kell vezetni a fogalmat: tranzitív funkcionális függőség.
Meghatározás:
Legyen X, Y, Z valamilyen reláció három attribútuma. Ebben az esetben X --> Y és Y --> Z, de nincs fordított megfeleltetés, pl. Z -/-> Y és Y -/-> X. Ekkor Z tranzitívan függ X-től.
Legyen egy reláció TÁROLÁS ( CÉG, WAREHOUSE, VOLUME), amely információkat tartalmaz a raktárakból árut fogadó cégekről és e raktárak mennyiségéről. Kulcs attribútum - "cég". Ha minden cég csak egy raktárból tud árut fogadni, akkor ebből a szempontból a következő funkcionális függőségek vannak:
Ez anomáliákat okoz:
Ezen anomáliák kiküszöbölése érdekében az eredeti relációt két részre kell bontani:
A harmadik normál forma meghatározása:
Egy reláció 3NF-ben van, ha 2NF-ben van, és minden nem kulcs attribútum tranzitív módon nem függ az elsődleges kulcstól.
A könyvben korábban rámutattunk bizonyos kapcsolatokra, amelyek a mintatáblázatok egyes mezői között léteznek. A Vevők tábla snum mezője például megfelel az Értékesítők és a Megrendelések tábla snum mezőjének. A Vevők tábla cnum mezője megegyezik a Rendelések tábla cnum mezőjével is. Az ilyen típusú kapcsolatokat referenciális integritásnak neveztük; és a vita során láttad, hogyan használható.
Ebben a fejezetben részletesebben megvizsgálja a hivatkozási integritást, és mindent megtud azokról a korlátozásokról, amelyekkel támogathatja azt. Azt is látni fogja, hogy ez a korlátozás hogyan érvényesül, ha DML módosító parancsokat használ. Mivel a hivatkozási integritás magában foglalja a mezők vagy mezőcsoportok összekapcsolását, gyakran táblákon keresztül, ez a művelet valamivel összetettebb lehet, mint más megszorítások. Emiatt jó, ha teljesen ismeri, még akkor is, ha nem tervezi táblák létrehozását. A módosító parancsok hatékonyabbá tehetők hivatkozási integritási megszorítással (mint más megszorítások esetében is, de a hivatkozási integritási megszorítások a definiálástól eltérő táblákra is hatással lehetnek), és bizonyos lekérdezési függvények, például az összekapcsolások, ismételten strukturálódnak. a referenciális integritási viszonyok szempontjából (ahogyan a 8. fejezetben kiemeltük).
Amikor egy tábla egyik mezője egy másikra hivatkozik, azt idegen kulcsnak nevezzük; és azt a mezőt, amelyre hivatkozik, szülőkulcsnak nevezzük. Tehát a Vevők tábla snum mezője az idegen kulcs, és az snum mező, amelyre az Értékesítők táblában hivatkozik, a szülőkulcs.
Hasonlóképpen, a Rendelések tábla cnum és snum mezői olyan idegen kulcsok, amelyek a Vevők és az Értékesítők táblában megnevezett szülőkulcsokra hivatkoznak. Az idegen kulcs és a szülőkulcs nevének nem kell azonosnak lennie, ez csak egy konvenció, amit követünk, hogy érthetőbb legyen az összekapcsolás.
Nyilvánvaló, hogy az idegen kulcsban minden egyes értéket egyszer, és csak egyszer kell megjeleníteni a szülőkulcsban.
Valójában egy adott idegenkulcs-érték csak egy szülőkulcs-értékre hivatkozhat anélkül, hogy feltételeznénk, hogy a fordítottja lehetséges: azaz. tetszőleges számú idegen kulcs utalhat egyetlen szülőkulcs értékére. Ezt a példáink mintatáblázataiban láthatja. Mind Hoffman, mind Clemens hozzá van rendelve a Peelhez, így mindkét idegen kulcs értéke megegyezik ugyanazzal a szülőkulccsal, ami nagyszerű. Az idegen kulcs értékének csak egy szülőkulcs értékére kell hivatkoznia, de a szülőkulcs értéke tetszőleges számú idegenkulcs-értékre hivatkozhat. Szemléltetésképpen a 19.1. ábrán láthatók a Vevők táblából azok az idegenkulcs-értékek, amelyek megegyeztek a Salesmen táblában szereplő szülőkulcsukkal. Az egyszerűség kedvéért nem vettük figyelembe a példához nem kapcsolódó nemet.
A legtöbb megszorításhoz hasonlóan ez is lehet táblázat- vagy oszlopkényszer, olyan táblázat formájában, amely lehetővé teszi több mező egyetlen idegen kulcsként történő használatát.
* Ugyanannyi oszlopot kell tartalmazniuk.
* Ebben a sorrendben az idegen kulcs oszlopok listájának első, második, harmadik stb. oszlopának ugyanolyan adattípusúnak és méretűnek kell lennie, mint az oszloplista első, második, harmadik stb. oszlopainak. szülő kulcs. A két oszlop listájának oszlopai nem lehetnek azonos névvel, bár példáinkban ezt a módszert alkalmaztuk a kapcsolat egyértelműbbé tétele érdekében.
Hozzunk létre egy Vevők táblát a Salespeople táblára hivatkozó idegen kulcsként definiált snum mezővel: CREATE TABLE Customers (cnum integer NOT NULL PRIMARY KEY cname char(10), city char(10), snum integer, FOREIGN KEY (snum) HIVATKOZÁSOK Értékesítők (snum ); Ügyeljen arra, hogy ha az ALTER TABLE-t használja a CREATE TABLE helyett, akkor az idegen kulcsban és a szülőkulcsban megadott értékeknek hivatkozási integritási állapotban kell lenniük. Bár az ALTER TABLE nagyon hasznos - a kényelem érdekében először strukturális alapelveket kell kialakítania a rendszerben, amikor csak lehetséges, mint például a hivatkozási integritás.
Az adatbázis integritása szempontjából a belső megszakítások (vagy kivételek) természetesen nem kívánatosak. Ha engedélyezi őket, és egyben meg akarja őrizni az adatbázis integritását, akkor a Rendelések tábla snum és cnum mezőit az Értékesítési tábla és a Vevő tábla mezőinek független idegen kulcsaként deklarálhatja. Valójában a snum mező használata a Rendelések táblában, ahogy mi is tettük, nem kötelező, bár hasznos volt megtenni a változatosság kedvéért. A Vevők táblában, a Rendelések táblában és a Vevők táblában az egyes ügyfelek rendeléseit összekötő cnum mezőt mindig meg kell osztani, hogy megtaláljuk az adott rendeléshez megfelelő snum mezőt (kivételek nélkül). Ez azt jelenti, hogy kétszer írunk egy információt – melyik vevő melyik szállítóhoz van hozzárendelve –, és még több munkát kell végezni, hogy mindkét verzió konzisztens legyen. Ha a fent említett módon nem rendelkezünk idegenkulcs-megszorítással, ez a helyzet különösen problémás lesz, mert minden egyes rendelést manuálisan kell ellenőrizni (a lekérdezéssel együtt), hogy megbizonyosodjon arról, hogy a megfelelő eladó írt jóvá minden egyes értékesítést. Az ilyen típusú információredundancia jelenlétét az adatbázisban denormalizációnak nevezzük, ami egy ideális relációs adatbázisban nem kívánatos, bár a gyakorlatban megengedhető. A demoralizáció bizonyos lekérdezéseket gyorsabbá tehet, mivel az egyetlen táblán lévő lekérdezés mindig lényegesen gyorsabb, mint az összekapcsolásnál végzett lekérdezés.
Mivel a szülőkulcs értékeinek megváltoztatásának kérdése is érintett, a válasz az ANSI definíciója szerint még egyszerűbb, de talán kicsit korlátozottabb: az idegen kulcs értékkel hivatkozott szülőkulcs bármely értéke nem törölhető vagy módosítható. Ez például azt jelenti, hogy nem távolíthat el egy ügyfelet a Vevők táblából, amíg még vannak rendelései a Rendelések táblában. Attól függően, hogy hogyan használja ezeket a táblázatokat, ez kívánatos vagy problémás lehet. Ez azonban minden bizonnyal jobb, mint egy olyan rendszer, amely lehetővé teszi az aktuális rendelésekkel rendelkező vevő eltávolítását, és a Rendelések tábla elhagyását a nem létező ügyfelekre hivatkozva. Ennek a kényszerrendszernek az a lényege, hogy a Rendelések tábla létrehozója a Vevők táblát és az Eladók táblát szülőkulcsként használva jelentős korlátozásokat szabhat a táblákban szereplő műveletekre. Emiatt nem használhat olyan táblát, amelyet nem Ön irányít (azaz nem Ön hozta létre, és nem Ön a tulajdonosa), kivéve, ha a tábla tulajdonosa (alkotója) kifejezetten megadja Önnek a jogot tegye meg (a 22. fejezetben leírtak szerint). Vannak mások is lehetséges cselekvések szülőkulcs-módosítások, amelyek nem részei az ANSI-nak, de néhány kereskedelmi programban megtalálhatók. Ha módosítani vagy törölni szeretné az aktuális szülőkulcs referenciaértékét, alapvetően három lehetőség van:
Előfordulhat, hogy még ezen a három kategórián belül sem kívánja az összes módosítási parancsot így feldolgozni. Az INSERT természetesen lényegtelen. A szülőkulcs új értékeit a táblázatba helyezi, így pillanatnyilag egyiket sem lehet meghívni. Lehetséges azonban, hogy a módosítások törlés nélküli lépcsőzetessé váljanak, és fordítva. A legjobb helyzet az lehet, ha lehetővé teszi a három kategória bármelyikének meghatározását, az UPDATE és DELETE parancsoktól függetlenül. Ezért hivatkozni fogunk a frissítési és törlési effektusokra, amelyek meghatározzák, hogy mi történik, ha UPDATE vagy DELETE parancsot ad ki a szülőkulcson. Ezeket az effektusokat, amelyekről beszéltünk, KORLÁTOZOTT változtatásoknak, CASCADES változtatásoknak és NULL változásoknak nevezzük. A rendszer tényleges képességeinek a szigorú ANSI szabványon belül kell lenniük – a módosítási és törlési hatások, mindkettő automatikusan korlátozott – a fent leírt ideálisabb helyzethez. Szemléltetésképpen bemutatunk néhány példát arra, hogy mit tehet a módosítási és törlési effektusok teljes skálájával. Természetesen az effektusok módosítása és törlése, amelyek nem szabványos szolgáltatások, nem rendelkeznek a szabványos állapotszintaxissal. Az itt használt szintaxist egyszerű megírni, és az effektusok funkciójának illusztrálására szolgál.
A kísérlet teljessége érdekében tegyük fel, hogy van oka megváltoztatni az Értékesítő tábla snum mezőjét abban az esetben, ha az Értékesítő táblánk partíciókat változtat. (Általában nem javasoljuk az elsődleges kulcsok megváltoztatását a gyakorlatban. Ez csak egy újabb ok, amiért a meglévő elsődleges kulcsok nem csinálnak mást, mint elsődleges kulcsként viselkednek: nem szabad megváltozniuk.) Amikor megváltoztatja a szállító számát, az összeset meg szeretné tartani ügyfeleinek. Ha azonban ez az értékesítő elhagyja cégét vagy vállalatát, előfordulhat, hogy nem kívánja eltávolítani ügyfeleit, amikor eltávolítja őt az adatbázisból. Ehelyett meg kell győződnie arról, hogy az ügyfelek valaki máshoz vannak rendelve. Ehhez meg kell adni az UPDATE Cascading effektust, és a DELETE értéket korlátozott hatással. TÁBLÁZAT LÉTREHOZÁSA Ügyfelek (cnum integer NOT NULL ELSŐDLEGES KULCS, cname char(10) NOT NULL, city char(10), értékelés integer, snum integer HIVATKOZÁS Értékesítők, Értékesítők CASCADES FRISSÍTÉSE, Értékesítők RESTRICTED TÖRLÉSE); Ha most megpróbálja eltávolítani a Peel-t a Szállítók táblából, a parancs addig nem lesz érvényes, amíg meg nem változtatja a Hoffman és Clemens ügyfelek snum gender értékét egy másik hozzárendelt szállítóra. Másrészt megváltoztathatja Peel snum mezőjét 1009-re, és Hoffman és Clemens is automatikusan megváltozik.
A harmadik hatás a NULL változások. Előfordul, hogy amikor az eladók elhagyják a céget, az aktuális megrendeléseiket nem ruházzák át másik eladóhoz. Másrészt automatikusan törölni szeretné az összes megrendelést azon ügyfelek számára, akiknek a fiókját törölte. Az eladó vagy az ügyfél számának megváltoztatásával egyszerűen átadhatja azokat neki. Az alábbi példa bemutatja, hogyan hozhat létre Rendelési táblázatot ezekkel az effektusokkal. TÁBLÁZAT LÉTREHOZÁSA Rendelések (onum integer NOT NULL ELSŐDLEGES KULCS, amt decimális, dátum dátum NOT NULL cnum integer NOT NULL REFERENCES Ügyfelek snum integer REFERENCES Értékesítők, Vásárlók FRISSÍTÉSE CASCADES CASCADES, DELETE OF CASCADES, DELETE OF CASCADES, DELETE OF CASCADES, DELETE OF CASCAPEAS, Ügyfélfrissítések ople NULLS); Természetesen a Salespersons táblában NULL változási hatással rendelkező DELETE parancsban a NOT NULL kényszert el kell távolítani az snum mezőből.
2. Bonyolítsuk a problémát. Határozza meg újra a Rendelések táblát a következőképpen: adjon hozzá egy új, előző oszlopot, amelyet minden rendelésnél azonosítani kell, az aktuális vevő előző rendelésének onum mezőjét. Tegye ezt egy idegen kulccsal, amely magára a rendelési táblára hivatkozik. Az idegen kulcsnak hivatkoznia kell az ügyfél cnum mezőjére is, amely valamilyen előírt kapcsolatot biztosít az aktuális rendelés és a hivatkozott között.
(A válaszokat lásd az A. függelékben.)
Ha olyan vagy, mint ennek a kurzusnak a szerzője abban, hogy szeretsz komplexen, különböző szerzők különböző műveiben választ keresni egy téged érdeklő kérdésre, akkor nem lehetett nem észrevenni némi zavart a definíciókban. fő (mester) -> beosztott (részlet) táblázatok. Emlékezzünk vissza, hogy a fő táblát gyakran szülőtáblának, az alárendelt táblát pedig gyermektáblának nevezik.
Ez valószínűleg annak köszönhető, hogy ezeket a definíciókat hogyan értelmezik a helyi és SQL-szerver DBMS-ben.
A helyi DBMS-ben a fő tábla az, amely a fő adatokat tartalmazza, az alárendelt tábla pedig a kiegészítő. Vegyünk például három kapcsolódó táblázatot. Az első az értékesítési adatokat, a második a termékadatokat, a harmadik pedig a vásárlói adatokat tartalmazza:
Rizs. 18.1.
Itt a fő információ az értékesítési táblázatban tárolódik, ezért ez a fő (szülő). A további információkat a termékek és a vásárlók táblázatai tárolják, ami azt jelenti, hogy gyermek. Ez érthető: egy lánynak nem lehet két biológiai anyja, de egy anya képes két lányt világra hozni.
Az SQL adatbázis-kiszolgálók azonban másképpen határozzák meg a kapcsolatokat: amikor egy tábla egyik mezője egy másik tábla mezőjére hivatkozik, az ún. idegen kulcs. És azt a mezőt, amelyre hivatkozik, úgy hívják szülő ill elsődleges kulcs. Az idegen kulccsal rendelkező táblát (hivatkozás egy másik táblában lévő rekordhoz) gyakran gyermektáblának nevezik, és a táblát szülőkulcs- szülő. Már a kapcsolatok meghatározásánál is azt mondják, hogy egy szülőnek csak egy egyedi rekordja lehet, amelyre több rekord is hivatkozhat. gyermek asztal.
Tehát a fenti példában az értékesítési táblának két idegen kulcsa van: a termékazonosító és az ügyfélazonosító. És az ábra jobb oldalán lévő mindkét táblázat rendelkezik szülőkulcs"Azonosító". Mivel egy vásárló vagy termék többször is megjelenhet az értékesítési táblázatban, így kiderül, hogy az ábra jobb oldalán mindkét táblázat szülő, a bal oldali pedig egy gyermek. Mivel most tanulunk InterBase-SQL adatbázis szerver, ezektől a definícióktól fogunk vezérelni a következő előadásokban. Annak érdekében, hogy ne keverjük tovább ezt a zavart, azonnal megegyezünk: gyermek asztal idegen kulcsa ( FOREIGN KEY ) van egy másik táblához.
ELSŐDLEGES KULCS- elsődleges kulcs, az egyik fő kényszertípus az adatbázisban. Az elsődleges kulcs célja egy tábla rekordjának egyedi azonosítása, és egyedinek kell lennie. Elsődleges kulcsok A PRIMARY KEY a szülő (szülő) táblákban található. Ne keverjük össze az elsődleges kulcsot a helyi adatbázisok elsődleges indexeivel, az elsődleges kulcs nem index, hanem megszorítás. Az elsődleges kulcs létrehozásakor InterBase automatikusan létrehoz neki egyedi index. Ha azonban megteremtjük egyedi index, ez nem jön létre elsődleges kulcs megszorításai. Egy táblázatnak csak egy ELSŐDLEGES KULCSJA lehet.
Tegyük fel, hogy van egy táblázatunk az alkalmazottak listájával. A "Vezetéknév" mező ugyanazokat az értékeket (azonos nevű neveket) tartalmazhatja, ezért nem használható elsődleges kulcsként. Ritkán, de vannak névrokonok, akiknek ráadásul ugyanaz a neve. Még ritkább, de vannak teljes névrokonok, így még mindhárom mező „Vezetéknév” + „Keresztnév” + „Patronymic” nem garantálhatja a rekord egyediségét, és nem lehet elsődleges kulcs. Ebben az esetben a kiút, mint korábban, egy mező - azonosító hozzáadása, amely tartalmazza ennek a személynek a sorozatszámát. Az ilyen mezőket általában automatikus növekményessé teszik (a következő előadásokban az auto-növekmény mezők megszervezéséről lesz szó). Így,
elsődleges kulcs - ez egy vagy több mező a táblázatban, amelyek kombinációja minden rekordnál egyedi.
Ha az elsődleges kulcsnak egyetlen oszlopa van (mint ez a leggyakrabban előfordul), akkor az ELSŐDLEGES KULCS specifikátort használják, amikor oszlop meghatározása:
TÁBLÁZAT LÉTREHOZÁSA Prim_1(Stolbec1 INT NOT NULL ELSŐDLEGES KULCS, Stolbec2 VARCHAR(50))
Ha az elsődleges kulcs több oszlopra épül, akkor a specifikátor az összes mező meghatározása után kerül elhelyezésre:
TÁBLÁZAT LÉTREHOZÁSA Prim_2(Stolbec1 INT NEM NULL, Stolbec2 VARCHAR(50) NOT NULL, ELSŐDLEGES KULCS (Stolbec1, Stolbec2))
Amint a példákból látható, az elsődleges kulcs feltétlenül rendelkeznie kell egy NOT NULL oszlop(ok) megszorítással.
EGYEDI- egyedi kulcs. Az EGYEDI MEGÁLLAPÍTÓ megadja, hogy az összes érték adott mező egyedinek kell lennie, ezért az ilyen mezők sem tartalmazhatnak értékeket NULLA. Elmondható, hogy az EGYEDI kulcs az elsődleges kulcs alternatívája, de vannak eltérések. A fő különbség az, hogy csak egy elsődleges kulcsnak kell lennie, míg több egyedi kulcs is lehet. Ezenkívül egy EGYEDI kényszer nem építhető fel ugyanarra az oszlopkészletre, amelyet az ELSŐDLEGES KULCS kényszerhez vagy egy másik EGYEDI kényszerhez használtak. Az egyedi kulcsok, az elsődleges kulcsokhoz hasonlóan, olyan táblákban találhatók, amelyek más táblák szülői.
Egy EGYEDI megszorítással deklarált oszlop, mint az elsődleges kulcs, felhasználható a szülő és a hivatkozási integritás érvényesítésére. gyermek asztalok. Míg az idegen kulcs gyermek asztal az adott mező(k)re fog hivatkozni. Az elsődleges kulcshoz hasonlóan, amikor egyedi kulcsot hoz létre, automatikusan létrejön egy kulcs hozzá. egyedi index. De nem fordítva. Példa egy tábla létrehozására egy elsődleges és két egyedi kulccsal:
TÁBLÁZAT LÉTREHOZÁSA Prim_3(Stolbec1 INT NEM NULL ELSŐDLEGES KULCS, Stolbec2 VARCHAR(50) NEM NULL EGYEDI, Stolbec3 FLOAT NEM NULL EGYEDI)
IDEGEN KULCS- külső kulcs. Ez egy nagyon hatékony eszköz a táblák közötti hivatkozási integritás biztosítására, amely lehetővé teszi nemcsak a megfelelő hivatkozások jelenlétének figyelését, hanem automatikus kezelését is. Az idegen kulcsokat olyan táblák tartalmazzák, amelyek más táblák gyermekei ( Child ). Hivatkozási integritás pontosan egy idegen kulcs biztosítja, amely az elsődleges ill
