Cizí klíč - Foreign key

Cizího klíče je sada atributů v tabulce, která se odkazuje na primární klíč jiné tabulky. Cizí klíč spojuje tyto dvě tabulky. Jiný způsob: V kontextu relačních databází je cizí klíč množina atributů podléhajících určitému druhu omezení závislosti na začlenění, konkrétně omezení, které musí n -tice skládající se z atributů cizího klíče v jednom vztahu , R, také existují v nějakém jiném (ne nutně odlišném) vztahu, S, a dále, že tyto atributy musí být také kandidátským klíčem v S. V jednodušších slovech je cizí klíč soubor atributů, které odkazují na kandidátský klíč. Například tabulka s názvem TEAM může mít atribut MEMBER_NAME, což je cizí klíč odkazující na kandidátský klíč PERSON_NAME v tabulce PERSON. Protože je MEMBER_NAME cizí klíč, musí jakákoli hodnota existující jako jméno člena v týmu TEAM existovat také jako jméno osoby v tabulce PERSON; jinými slovy, každý člen TÝMU je také OSOBOU.

souhrn

Tabulka obsahující cizí klíč se nazývá podřízená tabulka a tabulka obsahující klíč kandidáta se nazývá odkazovaná nebo nadřazená tabulka. V relačním modelování a implementaci databáze je kandidátský klíč sada nula nebo více atributů, jejichž hodnoty jsou zaručeně jedinečné pro každou řazenou kolekci (řádek) ve vztahu. Hodnotu nebo kombinaci hodnot atributů kandidátského klíče pro libovolnou řazenou kolekci nelze duplikovat pro žádné jiné řazené kolekce členů v tomto vztahu.

Protože účelem cizího klíče je identifikovat konkrétní řádek odkazované tabulky, je obecně požadováno, aby se cizí klíč rovnal kandidátskému klíči v některém řádku primární tabulky, jinak nemá žádnou hodnotu ( hodnotu NULL .) . Toto pravidlo se nazývá omezení referenční integrity mezi dvěma tabulkami. Protože porušení těchto omezení může být zdrojem mnoha databázových problémů, většina systémů pro správu databází poskytuje mechanismy zajišťující, že každý nenulový cizí klíč odpovídá řádku odkazované tabulky.

Zvažte například databázi se dvěma tabulkami: tabulku ZÁKAZNÍKA, která obsahuje všechna data zákazníků, a tabulku OBJEDNÁVKA, která obsahuje všechny objednávky zákazníků. Předpokládejme, že obchod vyžaduje, aby každá objednávka odkazovala na jednoho zákazníka. Aby se to projevilo v databázi, je do tabulky OBJEDNÁVKA přidán sloupec cizího klíče (např. CUSTOMERID), který odkazuje na primární klíč ZÁKAZNÍKA (např. ID). Protože primární klíč tabulky musí být jedinečný a protože CUSTOMERID obsahuje pouze hodnoty z tohoto pole primárního klíče, můžeme předpokládat, že pokud má hodnotu, CUSTOMERID identifikuje konkrétního zákazníka, který objednávku zadal. To však již nelze předpokládat, pokud tabulka ORDER není aktualizována, když jsou odstraněny řádky tabulky CUSTOMER nebo změněn sloupec ID a práce s těmito tabulkami může být obtížnější. Mnoho databází skutečného světa tento problém řeší „deaktivací“, nikoli fyzickým odstraněním cizích klíčů hlavní tabulky, nebo složitými aktualizačními programy, které v případě potřeby změní všechny odkazy na cizí klíč.

Cizí klíče hrají zásadní roli při návrhu databáze . Jednou z důležitých součástí návrhu databáze je zajistit, aby se vztahy mezi entitami reálného světa odrážely v databázi pomocí odkazů, pomocí cizích klíčů se odkazovat z jedné tabulky do druhé. Další důležitou součástí návrhu databáze je normalizace databáze , ve které jsou tabulky rozděleny a cizí klíče umožňují jejich rekonstrukci.

Více řádků v odkazující (nebo podřízené) tabulce může odkazovat na stejný řádek v odkazované (nebo nadřazené) tabulce. V tomto případě se vztah mezi dvěma tabulkami nazývá vztah jedna k mnoha mezi referenční tabulkou a odkazovanou tabulkou.

Kromě toho může být podřízená a nadřazená tabulka ve skutečnosti stejná tabulka, tj. Cizí klíč odkazuje zpět na stejnou tabulku. Takový cizí klíč je v SQL: 2003 znám jako samoreferenční nebo rekurzivní cizí klíč. V systémech pro správu databází se toho často dosahuje propojením prvního a druhého odkazu na stejnou tabulku.

Tabulka může mít více cizích klíčů a každý cizí klíč může mít jinou nadřazenou tabulku. Každý cizí klíč je vynucen nezávisle databázovým systémem . Kaskádové vztahy mezi tabulkami lze tedy navázat pomocí cizích klíčů.

Cizí klíč je definován jako atribut nebo sada atributů ve vztahu, jehož hodnoty odpovídají primárnímu klíči v jiném vztahu. Syntaxe pro přidání takového omezení do existující tabulky je definována v SQL: 2003, jak je uvedeno níže. Vynechání seznamu sloupců v REFERENCESklauzuli znamená, že cizí klíč bude odkazovat na primární klíč odkazované tabulky. Podobně lze cizí klíče definovat jako součást příkazu CREATE TABLESQL. 

CREATE TABLE child_table (
  col1 INTEGER PRIMARY KEY,
  col2 CHARACTER VARYING(20),
  col3 INTEGER,
  col4 INTEGER,
  FOREIGN KEY(col3, col4) REFERENCES parent_table(col1, col2) ON DELETE CASCADE
)

Pokud je cizí klíč pouze jedním sloupcem, sloupec lze takto označit pomocí následující syntaxe: 

CREATE TABLE child_table (
  col1 INTEGER PRIMARY KEY,
  col2 CHARACTER VARYING(20),
  col3 INTEGER,
  col4 INTEGER REFERENCES parent_table(col1) ON DELETE CASCADE
)

Cizí klíče lze definovat pomocí příkazu uložené procedury . 

sp_foreignkey child_table, parent_table, col3, col4
  • child_table : název tabulky nebo pohledu, který obsahuje definovaný cizí klíč.
  • parent_table : název tabulky nebo pohledu, který má primární klíč, na který se cizí klíč vztahuje. Primární klíč již musí být definován.
  • col3 a col4 : název sloupců, které tvoří cizí klíč. Cizí klíč musí mít alespoň jeden sloupec a nejvýše osm sloupců.

Referenční akce

Hlavní článek: Omezení šíření

Protože systém správy databází vynucuje referenční omezení, musí zajistit integritu dat, pokud mají být řádky v odkazované tabulce odstraněny (nebo aktualizovány). Pokud závislé řádky v referenčních tabulkách stále existují, je třeba tyto odkazy vzít v úvahu. SQL: 2003 určuje 5 různých referenčních akcí, které se mají provádět v takových případech:

KASKÁDA

Kdykoli budou odstraněny (nebo aktualizovány) řádky v nadřazené (odkazované) tabulce, budou odstraněny (nebo aktualizovány) také příslušné řádky podřízené (odkazující) tabulky s odpovídajícím sloupcem cizího klíče. Toto se nazývá kaskádové mazání (nebo aktualizace).

OMEZIT

Hodnotu nelze aktualizovat ani odstranit, pokud existuje řádek v odkazující nebo podřízené tabulce, která odkazuje na hodnotu v odkazované tabulce.

Podobně řádek nelze odstranit, pokud na něj existuje odkaz z odkazující nebo podřízené tabulky.

Abyste lépe porozuměli RESTRICT (a CASCADE), může být užitečné si všimnout následujícího rozdílu, který nemusí být hned jasný. Referenční akce CASCADE upravuje „chování“ samotné (podřízené) tabulky, kde je použito slovo CASCADE. Například ON DELETE CASCADE efektivně říká „Když je odkazovaný řádek odstraněn z jiné tabulky (hlavní tabulky), pak také odstranit ze mě “. Referenční akce RESTRICT však upravuje „chování“ hlavní tabulky, nikoli podřízené tabulky, přestože slovo RESTRICT se objevuje v podřízené tabulce a nikoli v hlavní tabulce! ON ON DELETE RESTRICT tedy efektivně říká: „Když se někdo pokusí odstranit řádek z jiné tabulky (hlavní tabulky), zabraňte vymazání z této jiné tabulky (a samozřejmě také neodstraňujte ze mě, ale to není hlavní bod tady)."

Microsoft SQL 2012 a starší verze RESTRICT nepodporuje.

ŽÁDNÁ AKCE

ŽÁDNÁ AKCE a OMEZENÍ jsou si velmi podobné. Hlavní rozdíl mezi NO ACTION a RESTRICT spočívá v tom, že u NO ACTION se kontrola referenční integrity provádí po pokusu o změnu tabulky. RESTRICT provede kontrolu před pokusem o spuštění příkazu UPDATE nebo DELETE . Obě referenční akce fungují stejně, pokud kontrola referenční integrity selže: Příkaz UPDATE nebo DELETE způsobí chybu.

Jinými slovy, když je v referenční tabulce proveden příkaz UPDATE nebo DELETE pomocí referenční akce NO ACTION, DBMS na konci provádění příkazu ověří, že není porušen žádný z referenčních vztahů. To se liší od OMEZENÍ, které na začátku předpokládá, že operace poruší omezení. Pomocí NO ACTION mohou spouště nebo sémantika samotného příkazu přinést koncový stav, ve kterém nebudou do doby, kdy je omezení konečně zkontrolováno, narušeny žádné vztahy s cizími klíči, což umožní úspěšné dokončení příkazu.

SET NULL, SET DEFAULT

Obecně platí, že akce provedená DBMS pro SET NULL nebo SET DEFAULT je stejná pro ON DELETE nebo ON UPDATE: hodnota příslušných referenčních atributů se změní na NULL pro SET NULL a na zadanou výchozí hodnotu pro SET DEFAULT .

Spouště

Referenční akce jsou obecně implementovány jako implicitní spouště (tj. Spouštěče se systémově generovanými názvy, často skryté). Jako takové se na ně vztahují stejná omezení jako na uživatelem definované spouště a jejich pořadí provádění ve srovnání s jinými spouštěči může být nutné považováno; v některých případech může být nutné nahradit referenční akci ekvivalentním uživatelem definovaným spouštěčem, aby byl zajištěn správný příkaz provedení, nebo obejít omezení mutační tabulky.

Při izolaci transakcí se objevuje další důležité omezení : vaše změny v řádku nemusí být možné plně kaskádovat, protože na řádek odkazují data, která vaše transakce „nevidí“, a proto na ně nelze kaskádovat. Příklad: zatímco se vaše transakce pokouší přečíslovat zákaznický účet, souběžná transakce se pokouší vytvořit novou fakturu pro stejného zákazníka; zatímco pravidlo CASCADE může opravit všechny řádky faktury, které vaše transakce může vidět, aby byly konzistentní s přečíslovanými řadami zákazníků, nedosáhne na jinou transakci, aby tam opravila data; protože databáze nemůže zaručit konzistentní data, když se obě transakce potvrdí, jedna z nich bude nucena vrátit se zpět (často na základě zásady „kdo dřív přijde, je dřív na řadě“.) 

CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));

CREATE TRIGGER ins_sum BEFORE INSERT ON account
     FOR EACH ROW SET @sum = @sum + NEW.amount;

Příklad

Jako první příklad pro ilustraci cizích klíčů předpokládejme, že databáze účtů obsahuje tabulku s fakturami a každá faktura je spojena s konkrétním dodavatelem. Údaje o dodavateli (například jméno a adresa) jsou uloženy v samostatné tabulce; každému dodavateli je přiděleno „číslo dodavatele“ k jeho identifikaci. Každý záznam na faktuře má atribut obsahující číslo dodavatele dané faktury. Potom je ‚dodavatelské číslo‘ primárním klíčem v tabulce Dodavatel. Cizí klíč v tabulce Faktury ukazuje na tento primární klíč. Relační schéma je následující. Primární klíče jsou vyznačeny tučně a cizí klíče kurzívou. 

 Supplier (SupplierNumber, Name, Address)
 Invoice (InvoiceNumber, Text, SupplierNumber)

Odpovídající prohlášení Jazyk definice dat je následující. 

CREATE TABLE Supplier (
  SupplierNumber INTEGER NOT NULL,
  Name           VARCHAR(20) NOT NULL,
  Address        VARCHAR(50) NOT NULL,
  CONSTRAINT supplier_pk PRIMARY KEY(SupplierNumber),
  CONSTRAINT number_value CHECK(SupplierNumber > 0)
)

CREATE TABLE Invoice (
  InvoiceNumber  INTEGER NOT NULL,
  Text           VARCHAR(4096),
  SupplierNumber INTEGER NOT NULL,
  CONSTRAINT invoice_pk PRIMARY KEY(InvoiceNumber),
  CONSTRAINT inumber_value CHECK (InvoiceNumber > 0),
  CONSTRAINT supplier_fk
    FOREIGN KEY(SupplierNumber) REFERENCES Supplier(SupplierNumber)
    ON UPDATE CASCADE ON DELETE RESTRICT
)

Viz také

Reference

externí odkazy