Jak vytvořit omezení cizího klíče v SQL

autor:
Tweet
Share
Share
Pin

Pro firmy je klíčové pracovat s daty. Schopnost ukládat a efektivně spravovat databáze představuje značnou konkurenční výhodu.

Databáze se řídí specifickými pravidly a umožňují strukturovat data do propojených celků. V tomto kontextu se dostáváme k relačním databázím, které se staly preferovaným způsobem správy dat už od 70. let minulého století. I dnes jsou relační databáze pro svou flexibilitu při práci s daty nejrozšířenější.

Přestože existuje mnoho relačních databází, MySQL se stala jednou z nejpopulárnějších. Podle statistik společnosti Statista byla v lednu 2022 druhou nejpoužívanější databází na světě.

V SQL databázích existují takzvaná omezení (constraints), která jsou předdefinovanými pravidly pro jeden nebo více sloupců. Tato omezení jsou vázána na hodnoty ve sloupci a pomáhají udržovat celistvost, přesnost a spolehlivost dat.

Jednoduše řečeno, do sloupce lze vložit pouze data, která splňují dané omezení. V případě, že data kritéria nesplňují, operace vložení se přeruší.

Předpokládá se, že máte již určité zkušenosti s relačními databázemi, konkrétně s MySQL, a chcete si dále prohloubit své znalosti. V tomto článku se podíváme na omezení primárního a cizího klíče a nabídneme tipy pro práci s nimi.

Zopakování omezení primárního klíče

Každá tabulka v SQL obsahuje jeden nebo více sloupců, které obsahují klíčové hodnoty, které jednoznačně identifikují každý řádek. Sloupec nebo skupina sloupců nazvaná primární klíč (PK) zajišťuje celistvost dat v tabulce. Omezení primárního klíče garantují unikátnost dat a jsou často definována ve sloupci s identifikačními čísly.

Po definování omezení primárního klíče databázový systém automaticky vytváří jedinečné indexy pro každý sloupec označený jako primární klíč, což zaručuje rychlý přístup k datům.

Pokud je omezení primárního klíče definováno pro více sloupců, nazývá se složený (composite) primární klíč. V takovém případě může každý jednotlivý sloupec primárního klíče obsahovat duplicitní hodnoty, ale kombinace hodnot ve všech sloupcích primárního klíče musí být jedinečná.

Příkladem může být tabulka se sloupci `id`, `names` a `age`. Když definujete omezení primárního klíče na kombinaci `id` a `names`, mohou existovat duplicitní hodnoty v `id` nebo `names`, ale každá kombinace těchto hodnot musí být jedinečná, aby se předešlo duplicitním záznamům. Například můžete mít záznamy s `id=1`, `name=Walter`, `age=22` a `id=1`, `name=Henry`, `age=27`, ale nesmí existovat další záznam s `id=1` a `name=Walter`, protože tato kombinace není jedinečná.

Zde je několik základních bodů, které je dobré znát:

  • Tabulka může mít pouze jedno omezení primárního klíče.
  • Primární klíče nesmí přesáhnout 16 sloupců a celkovou délku 900 znaků.
  • Indexy vytvářené primárními klíči mohou zvětšit celkový počet indexů v tabulce. Počet clusterovaných indexů v tabulce je omezen na 1, zatímco počet neclusterovaných indexů je omezen na 999.
  • Pokud pro omezení primárního klíče není určen clusterovaný nebo neclusterovaný index, automaticky se použije clusterovaný index.
  • Všechny sloupce zahrnuté v omezení primárního klíče by měly být definovány jako NOT NULL. Pokud tomu tak není, všechny sloupce propojené v omezení se automaticky nastaví na NOT NULL.
  • Pokud jsou primární klíče definovány na sloupci uživatelského typu CLR (Common Language Runtime), implementace tohoto typu musí podporovat binární řazení.

Přehled omezení cizího klíče

Cizí klíč (FK) je sloupec nebo kombinace sloupců, které se používají k vytvoření a správě vztahů mezi dvěma tabulkami. Cizí klíč odkazuje na primární klíč jiné tabulky a kontroluje hodnoty, které se v něm nacházejí.

Odkaz cizího klíče vytváří propojení mezi dvěma tabulkami, kdy sloupec (nebo sloupce) v jedné tabulce odkazuje na primární klíč (sloupce) v jiné tabulce.

V praxi to znamená, že se vytváří vztah mezi dvěma tabulkami, kdy sloupce v jedné tabulce odkazují na primární klíč v jiné tabulce.

Například tabulka `Sales.SalesOrderHeader` může mít cizí klíč, který odkazuje na tabulku `Sales.Person`, protože mezi prodejci a objednávkami existuje logický vztah.

V tomto případě se sloupec `SalesPersonID` v tabulce `SalesOrderHeader` shoduje se sloupcem primárního klíče v tabulce `SalesPerson`. Cizím klíčem v tabulce `SalesOrderHeader` je tedy sloupec `SalesPersonID`.

Tento vztah definuje pravidlo, že hodnota `SalesPersonID` v tabulce `SalesOrderHeader` nemůže existovat, pokud neexistuje odpovídající hodnota v tabulce `SalesPerson`.

Tabulka může odkazovat na až 253 dalších sloupců a tabulek jako cizí klíče, což se také nazývá odchozí odkazy. Od roku 2016 SQL server zvýšil počet tabulek a sloupců, na které lze odkazovat v jedné tabulce, takzvané příchozí odkazy, z 253 na 10 000. Toto navýšení však má svá omezení:

  • Odkazy cizího klíče přesahující 253 jsou dostupné pouze pro operace DELETE DML. MERGE a UPDATE nejsou podporovány.
  • Tabulky, které na sebe odkazují pomocí cizího klíče, mají maximálně 253 odkazů.
  • Pro indexy sloupcových úložišť, tabulky optimalizované pro paměť a tabulky dělených cizích klíčů jsou odkazy omezeny na 253.

Jaké jsou výhody cizích klíčů?

Jak již bylo zmíněno, omezení cizího klíče hrají důležitou roli při zabezpečení celistvosti a konzistence dat v relační databázi. Podívejme se na konkrétní důvody, proč jsou omezení cizích klíčů nezbytná.

  • Referenční integrita: Omezení cizího klíče zajišťuje, že každý záznam v tabulce odkazující na primární klíč odpovídá existujícímu záznamu v tabulce s primárním klíčem. To zajišťuje konzistenci dat mezi tabulkami.
  • Prevence osamocených záznamů: Pokud smažete záznam z tabulky s primárním klíčem, omezení cizího klíče zajistí, že se smažou i odpovídající záznamy v tabulce s cizím klíčem. Tím se zabrání vzniku nekonzistentních dat v databázi.
  • Zvýšení výkonu: Omezení cizích klíčů mohou pomoci urychlit vyhledávání dat, protože databázový systém může optimalizovat dotazy na základě vztahů mezi tabulkami.

Indexy omezení cizího klíče

Omezení cizího klíče automaticky nevytváří odpovídající indexy jako primární klíč. Indexy pro omezení cizího klíče můžete vytvořit manuálně. Je to výhodné z následujících důvodů:

  • Sloupce cizího klíče se často používají v kritériích spojení při kombinování dat z příbuzných tabulek. Indexy pomáhají databázi rychleji vyhledat odpovídající data.
  • Pokud změníte omezení primárního klíče, budou zkontrolována s odpovídajícími omezeními cizího klíče v souvisejících tabulkách.

Vytvoření indexu pro cizí klíč není povinné. Stále můžete kombinovat data ze dvou tabulek bez nastavení omezení primárního a cizího klíče. Přidání omezení cizího klíče však optimalizuje tabulky a umožňuje efektivnější kombinování dat v dotazech. Dále, při změnách primárního klíče se automaticky kontrolují i cizí klíče.

Tipy pro vytvoření omezení cizího klíče v SQL

Dosud jsme se zabývali teorií a důvody používání omezení cizích klíčů. Nyní se podíváme na konkrétní metody jejich vytváření v SQL.

Pole „Cizí klíč“ v tabulce odkazuje na „Primární klíč“ jiné tabulky. Tabulka s primárním klíčem je nadřazená tabulka a tabulka s cizím klíčem je podřízená tabulka.

Vytvoření cizího klíče při vytváření tabulky

Omezení cizího klíče můžete vytvořit již při vytváření tabulky. To je jeden ze způsobů, jak zachovat referenční integritu. Zde je příklad:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Výše uvedený kód vytvoří tabulku nazvanou `orders`, která má primární klíč `order_id` typu celé číslo, cizí klíč `customer_id` typu celé číslo a datum `order_date`. V tomto případě se omezení `FOREIGN KEY` aplikuje na sloupec `customer_id` a odkazuje na `customer_id` v tabulce `customers`.

Vytvoření cizího klíče po vytvoření tabulky

Pokud již tabulku máte a potřebujete do ní přidat omezení cizího klíče, použijte příkaz `ALTER TABLE`. Zde je příklad:

ALTER TABLE orders
ADD FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

V tomto případě se omezení cizího klíče `customer_id` přidá do tabulky `orders`, čímž se odkazuje na sloupec `customer_id` v tabulce `customers`.

Vytvoření cizího klíče bez kontroly existujících dat

Při přidávání omezení cizího klíče do tabulky databáze automaticky kontroluje, zda jsou existující data v souladu s omezením. Pokud však víte, že data jsou v pořádku, a chcete přidat omezení bez ověřování, můžete to udělat následovně:

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
NOT VALIDATE;

Příkaz `NOT VALIDATE` říká databázi, aby nekontrolovala existující data. Tento postup je užitečný například v případech, kdy máte velká data a chcete urychlit proces přidání omezení.

Vytvoření cizího klíče pomocí DELETE/UPDATE

Při vytváření omezení cizího klíče můžete nastavit, co se má stát, když se aktualizuje nebo odstraní záznam v tabulce s primárním klíčem. K tomuto účelu se používají kaskádová omezení referenční integrity. Mezi akce patří:

#1. NO ACTION

Stejně jako v mnoha jiných databázích je `NO ACTION` výchozí chování při vytváření omezení cizího klíče. To znamená, že při smazání nebo aktualizaci řádku v nadřazené tabulce se neprovede žádná akce.

Databázový systém zobrazí chybu, pokud dojde k porušení omezení cizího klíče. Tato možnost se nedoporučuje, protože by mohla vést k problémům s integritou dat. Zde je příklad:

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION;

#2. CASCADE

Pravidlo `CASCADE` je další možnost pro akce `ON DELETE` a `ON UPDATE` při vytváření omezení cizího klíče. Pokud je definováno, znamená to, že když je řádek v nadřazené tabulce aktualizován nebo smazán, odpovídající řádky v podřízené tabulce se automaticky aktualizují nebo smažou. Tato technika je užitečná pro udržení referenční integrity. Zde je příklad:

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
ON DELETE CASCADE
ON UPDATE CASCADE;

Při používání tohoto pravidla buďte opatrní, protože může vést k nechtěným následkům, pokud ho nepoužíváte rozvážně. Je potřeba se vyhnout situacím, kdy omylem smažete příliš velké množství dat nebo vytvoříte cyklické reference. Používejte tuto možnost pouze v případě potřeby a opatrně.

Pro používání `CASCADE` platí určitá pravidla:

  • Nemůžete použít `CASCADE`, pokud je sloupec s časovým razítkem součástí cizího nebo odkazovaného klíče.
  • Pokud má vaše tabulka aktivační událost `INSTEAD OF DELETE`, nemůžete použít `ON DELETED CASCADE`.
  • Nemůžete použít `ON UPDATE CASCADE`, pokud má vaše tabulka aktivační událost `INSTEAD OF UPDATE`.

#3. SET NULL

Když je smazán nebo aktualizován odpovídající řádek v nadřazené tabulce, všechny hodnoty cizího klíče v podřízené tabulce se nastaví na `NULL`. Aby toto omezení fungovalo, musí sloupce cizího klíče umožňovat hodnoty `NULL` a nelze ho použít u tabulek s událostí `INSTEAD OF UPDATE`. Zde je příklad:

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
ON DELETE SET NULL
ON UPDATE SET NULL

V tomto příkladu je sloupec cizího klíče `customer_id` v tabulce `orders` nastaven na hodnotu `NULL`, pokud je odpovídající řádek v tabulce `customers` smazán nebo aktualizován.

#4. SET DEFAULT

Zde se všechny hodnoty tvořící cizí klíč nastaví na výchozí hodnotu, pokud se aktualizuje nebo smaže odkazovaný řádek v nadřazené tabulce.

Toto omezení funguje, pokud mají všechny sloupce cizího klíče definovanou výchozí hodnotu. Pokud sloupec umožňuje `NULL`, je jeho výchozí hodnotou `NULL`. Tuto možnost nelze použít pro tabulky s aktivační událostí `INSTEAD OF UPDATE`. Zde je příklad:

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
ON DELETE SET DEFAULT
ON UPDATE SET DEFAULT;

Ve výše uvedeném příkladu je sloupec `customer_id` v tabulce `orders` nastaven na výchozí hodnotu, když se odpovídající řádek v tabulce `customers` smaže nebo aktualizuje.

Závěrečná slova

V tomto článku jsme si zopakovali omezení primárního klíče a probrali omezení cizího klíče. Také jsme se podívali na několik metod vytváření omezení cizího klíče. Ačkoli existuje mnoho způsobů, jak vytvořit omezení cizího klíče, v tomto článku jsme se zaměřili na nejběžnější metody.

Doufáme, že jste získali nové znalosti a nezapomeňte, že nejste omezeni pouze jednou metodou. Například metody `CASCADE`, `SET NULL`, `SET DEFAULT` a `NO ACTION` lze kombinovat v tabulkách s referenčními vztahy.

Pokud vaše tabulka nenarazí na `NO ACTION`, platí pro ni ostatní omezující pravidla. V opačném případě, akce `DELETE` může spustit kombinaci těchto pravidel a `NO ACTION` se provede až jako poslední.

Podívejte se také na další užitečné informace o SQL.