Sdílet prostřednictvím

Omezení primárního a cizího klíče

  • Článek

platí pro: SQL Server 2016 (13.x) a novější verze Azure SQL DatabaseAzure SQL Managed InstanceSQL Database v Microsoft Fabric

Primární klíče a cizí klíče jsou dva typy omezení, která je možné použít k vynucení integrity dat v tabulkách SQL Serveru. Jedná se o důležité databázové objekty.

Omezení primárního klíče

Tabulka obvykle obsahuje sloupec nebo kombinaci sloupců, které obsahují hodnoty, které jednoznačně identifikují každý řádek v tabulce. Tento sloupec nebo sloupce se nazývá primární klíč (PK) tabulky a vynucuje integritu entity tabulky. Vzhledem k tomu, že omezení primárního klíče zaručují jedinečná data, jsou často definovaná ve sloupci identity.

Když pro tabulku zadáte omezení primárního klíče, databázový stroj vynutí jedinečnost dat tím, že automaticky vytvoří jedinečný index pro sloupce primárního klíče. Tento index také umožňuje rychlý přístup k datům při použití primárního klíče v dotazech. Pokud je omezení primárního klíče definováno ve více než jednom sloupci, mohou být hodnoty duplikovány v jednom sloupci, ale každá kombinace hodnot ze všech sloupců v definici omezení primárního klíče musí být jedinečná.

Jak je znázorněno na následujícím obrázku, sloupce ProductID a VendorID v tabulce Purchasing.ProductVendor tvoří omezení složeného primárního klíče pro tuto tabulku. Tím zajistíte, že každý řádek v tabulce ProductVendor má jedinečnou kombinaci ProductID a VendorID. Tím zabráníte vložení duplicitních řádků.

Diagram řádků v tabulce pro složené omezení PRIMÁRNÍHO KLÍČE

  • Tabulka může obsahovat pouze jedno omezení primárního klíče.
  • Primární klíč nesmí překročit 16 sloupců a celkovou délku klíče 900 bajtů.
  • Index vygenerovaný omezením primárního klíče nemůže způsobit, že počet indexů v tabulce překročí 999 neclusterovaných indexů a 1 clusterovaný index.
  • Pokud pro omezení primárního klíče není zadaný cluster nebo neclusterovaný, použije se clusterovaný, pokud v tabulce není žádný clusterovaný index.
  • Všechny sloupce definované v rámci omezení primárního klíče musí být definovány jako not null. Pokud není zadána nulovatelnost, všechny sloupce, které se účastní omezení primárního klíče, mají nastavenu nulovatelnost na "není null".
  • Pokud je primární klíč definován ve sloupci typu definovaném uživatelem CLR, musí implementace typu podporovat binární řazení.

Omezení cizího klíče

Cizí klíč (FK) je sloupec nebo kombinace sloupců, které slouží k vytvoření a vynucení propojení mezi daty ve dvou tabulkách, aby bylo možné řídit data, která lze uložit v tabulce cizího klíče. V odkazu na cizí klíč se vytvoří propojení mezi dvěma tabulkami, když sloupec nebo sloupce, které obsahují hodnotu primárního klíče pro jednu tabulku, odkazuje sloupec nebo sloupce v jiné tabulce. Tento sloupec se stane cizím klíčem v druhé tabulce.

Tabulka Sales.SalesOrderHeader má například vazbu pomocí cizího klíče na tabulku Sales.SalesPerson, protože mezi prodejními objednávkami a prodejci existuje logická vazba. Sloupec SalesPersonID v tabulce SalesOrderHeader odpovídá sloupci primárního klíče tabulky SalesPerson. Sloupec SalesPersonID v tabulce SalesOrderHeader je cizí klíč v tabulce SalesPerson. Vytvořením této relace cizího klíče nelze hodnotu pro SalesPersonID vložit do tabulky SalesOrderHeader, pokud ještě v tabulce SalesPerson neexistuje.

Tabulka může odkazovat na maximálně 253 dalších tabulek a sloupců jako cizí klíče (odchozí odkazy). SQL Server 2016 (13.x) zvyšuje limit počtu dalších tabulek a sloupců, které můžou odkazovat na sloupce v jedné tabulce (příchozí odkazy), od 253 do 10 000. (Vyžaduje alespoň 130 úrovní kompatibility.) Zvýšení má následující omezení:

  • Více než 253 odkazů na cizí klíč se podporuje pouze pro operace DELETE DML. UPDATE a operace MERGE se nepodporují.

  • Tabulka s odkazem na cizí klíč je stále omezena na 253 odkazů na cizí klíč.

  • Není možné mít více než 253 referencí cizího klíče pro indexy columnstore, tabulky optimalizované pro paměť, Stretch Database nebo dělené tabulky cizích klíčů.

    Důležitý

    Funkce Stretch Database je v SQL Serveru 2022 (16.x) a Azure SQL Database zastaralá. Tato funkce bude odebrána v budoucí verzi databázového stroje. Nepoužívejte tuto funkci v nové vývojové práci a naplánujte úpravu aplikací, které tuto funkci aktuálně používají.

Indexy omezení cizího klíče

Na rozdíl od omezení primárního klíče se vytvořením omezení cizího klíče automaticky nevytvoří odpovídající index. Ruční vytvoření indexu pro cizí klíč je však často užitečné z následujících důvodů:

  • Sloupce cizího klíče se často používají v kritériích spojení, když se data ze souvisejících tabulek zkombinují v dotazech tak, že se shodují se sloupcem nebo sloupci v omezení cizího klíče jedné tabulky s primárním nebo jedinečným klíčovým sloupcem nebo sloupci v druhé tabulce. Index umožňuje databázovému stroji rychle najít související data v tabulce cizího klíče. Vytvoření tohoto indexu se ale nevyžaduje. Data ze dvou souvisejících tabulek se dají kombinovat i v případě, že mezi tabulkami nejsou definována žádná omezení primárního klíče nebo cizího klíče, ale relace cizího klíče mezi dvěma tabulkami znamená, že tyto dvě tabulky jsou optimalizované tak, aby se zkombinovaly v dotazu, který jako kritéria používá klíče.

  • Změny omezení primárního klíče se kontrolují s omezeními cizího klíče v souvisejících tabulkách.

Referenční integrita

I když hlavním účelem omezení cizího klíče je řídit data, která lze uložit v tabulce cizího klíče, řídí také změny dat v tabulce primárního klíče. Pokud je například řádek prodejce odstraněn z tabulky Sales.SalesPerson a ID prodejce se používá pro prodejní objednávky v tabulce Sales.SalesOrderHeader, relační integrita mezi těmito dvěma tabulkami je poškozena; Odstraněné prodejní objednávky prodejce jsou osamocené v tabulce SalesOrderHeader bez odkazu na data v tabulce SalesPerson.

Omezení cizího klíče brání této situaci. Omezení vynucuje referenční integritu tím, že zaručuje, že změny nelze provést u dat v tabulce primárního klíče, pokud tyto změny zneplatní propojení s daty v tabulce cizího klíče. Pokud se pokusíte odstranit řádek v tabulce primárního klíče nebo změnit hodnotu primárního klíče, akce selže, když odstraněná nebo změněná hodnota primárního klíče odpovídá hodnotě v omezení cizího klíče jiné tabulky. Chcete-li úspěšně změnit nebo odstranit řádek v omezení cizího klíče, musíte nejprve odstranit data cizího klíče v tabulce cizího klíče nebo změnit data cizího klíče v tabulce cizího klíče, která propojuje cizí klíč s různými daty primárního klíče.

Kaskádová referenční integrita

Pomocí kaskádových omezení referenční integrity můžete definovat akce, které databázový systém provede, když se uživatel pokusí odstranit nebo aktualizovat klíč, na který existující cizí klíče ukazují. Lze definovat následující kaskádové akce.

  • NO ACTION

    Databázový stroj vyvolá chybu a akce odstranění nebo aktualizace na řádku v nadřazené tabulce se vrátí zpět.

  • CASCADE

    Odpovídající řádky se aktualizují nebo odstraní v odkazující tabulce při aktualizaci nebo odstranění daného řádku v nadřazené tabulce. CASCADE nelze zadat, pokud je sloupec časového razítka součástí cizího klíče nebo odkazovaného klíče. ON DELETE CASCADE nelze zadat pro tabulku, která má aktivační událost INSTEAD OF DELETE. ON UPDATE CASCADE nelze zadat pro tabulky, které mají aktivační události INSTEAD OF UPDATE.

  • SET NULL

    Všechny hodnoty, které tvoří cizí klíč, jsou nastaveny na NULL při aktualizaci nebo odstranění odpovídajícího řádku v nadřazené tabulce. Aby bylo možné toto omezení provést, musí mít sloupce cizího klíče hodnotu null. Nelze specifikovat pro tabulky, které mají triggery INSTEAD OF UPDATE.

  • SET DEFAULT

    Všechny hodnoty, které tvoří cizí klíč, jsou nastaveny na výchozí hodnoty, pokud je odpovídající řádek v nadřazené tabulce aktualizován nebo odstraněn. Aby bylo toto omezení provedeno, musí mít všechny sloupce cizího klíče výchozí definice. Pokud je sloupec s možnou hodnotou null a neexistuje žádná explicitní výchozí hodnota, NULL se stane implicitní výchozí hodnotou sloupce. U tabulek, které mají triggery INSTEAD OF UPDATE, není možné zadat.

CASCADE, SET NULL, SET DEFAULTa NO ACTION lze kombinovat u tabulek, které mají vzájemně referenční relace. Pokud databázový server narazí na NO ACTION, zastaví související akce CASCADE, SET NULLa SET DEFAULT a vrátí je zpět. Když příkaz DELETE způsobí kombinaci CASCADE, SET NULL, SET DEFAULTnebo NO ACTION akcí, všechny CASCADE, SET NULLa SET DEFAULT akce se použijí před tím, než databázový stroj zkontroluje všechny NO ACTION.

Spouště a kaskádové referenční akce

Kaskádové referenční akce aktivují triggery AFTER UPDATE nebo AFTER DELETE následujícím způsobem:

  • Nejprve se provedou všechny kaskádové referenční akce, které byly přímo způsobeny původním DELETE nebo UPDATE.

  • Pokud jsou v ovlivněných tabulkách definované nějaké AFTER triggery, aktivují se tyto triggery po provedení všech kaskádových akcí. Tyto triggery se aktivují v opačném pořadí kaskádové akce. Pokud v jedné tabulce existuje více triggerů, aktivují se v náhodném pořadí, pokud pro tabulku neexistuje vyhrazený první nebo poslední trigger. Toto pořadí je určeno pomocí sp_settriggerorder.

  • Pokud více kaskádových řetězců pochází z tabulky, která byla přímým cílem UPDATE nebo DELETE akce, pořadí, ve kterém tyto řetězy aktivují příslušné triggery, není zadáno. Jeden řetěz však vždy aktivuje všechny své spouštěče, než začne aktivovat jiný řetěz.

  • Spouštěč AFTER v tabulce, která je přímým cílem akce UPDATE nebo DELETE, se aktivuje bez ohledu na to, jestli jsou ovlivněny nějaké řádky. V tomto případě nejsou kaskádovými ovlivněny žádné další tabulky.

  • Pokud některý z předchozích triggerů provádí operace UPDATE nebo DELETE v jiných tabulkách, mohou tyto akce spustit sekundární kaskádové řetězy. Tyto sekundární řetězy se zpracovávají pro každou operaci UPDATE nebo DELETE vždy po aktivaci všech spouštěčů ve všech primárních řetězech. Tento proces se může rekurzivně opakovat pro následné operace UPDATE nebo DELETE.

  • Provádění operací CREATE, ALTER, DELETEnebo jiných operací DDL (Data Definition Language) uvnitř triggerů může způsobit aktivaci triggerů DDL. To může následně provádět operace DELETE nebo UPDATE, které spouští další kaskádové řetězy a triggery.

  • Pokud se vygeneruje chyba uvnitř konkrétního kaskádového referenčního řetězce akcí, vyvolá se chyba, v tomto řetězu se neaktivují žádné aktivační události AFTER a operace DELETE nebo UPDATE, která vytvořila řetěz, se vrátí zpět.

  • Tabulka s triggerem INSTEAD OF nemůže mít také klauzuli REFERENCES, která určuje kaskádovou akci. Trigger AFTER v tabulce, na kterou cílí kaskádová akce, ale může spustit příkaz INSERT, UPDATEnebo DELETE v jiné tabulce nebo zobrazení, které aktivuje trigger INSTEAD OF definovaný u daného objektu.

Související obsah