Pokud vlastníte podnik, musíte se ve svém podnikání setkat s hodnotou a potřebou dat. Mít prostředky pro ukládání a manipulaci s databázemi přidává podniku větší hodnotu.
Databáze jsou organizovány podle konkrétní konvence a umožňují vám strukturovat data ve spojeních, čímž se dostáváme k relačním databázím, které jsou jako forma správy dat přijímány od 70. let 20. století. A na dnešním trhu jsou preferovány relační databáze pro jejich schopnosti při manipulaci s daty.
I když existuje mnoho dostupných relačních databází, MySQL se dostalo na přední místo a podle Statista je od ledna 2022 světová dvojka.
Na serveru SQL jsou omezení předdefinovaná pravidla a omezení vynucená v jednom nebo více sloupcích; jsou svázány s hodnotami ve sloupci a pomáhají udržovat integritu, přesnost a spolehlivost dat zadaných sloupců.
Jednoduše řečeno, do sloupce se úspěšně vloží pouze data, která splňují omezující pravidlo. Operace vložení se ukončí, pokud data nesplňují kritéria.
Tento příspěvek předpokládá, že jste se setkali s relačními databázemi, konkrétně s MySQL, a těšíte se na posílení svých znalostí v doméně. Nakonec se podělím o několik tipů pro interakci s omezeními cizího klíče.
Table of Contents
Primární klíčová omezení – rekapitulace
Tabulka v SQL zahrnuje sloupec nebo několik obsahující klíčové hodnoty, které přesně označují každý řádek v systémech. Sloupec nebo sloupce s názvem primární klíč (PK) tabulky mají roli vynucování integrity entity tabulky. Omezení primárního klíče zaručují jedinečná data a jsou často definována ve sloupci identity.
Po zadání omezení primárního klíče pro vaši tabulku databázový stroj automaticky stanoví jedinečnost dat generováním jedinečných indexů pro každý z primárních sloupců. Primární klíče nabízejí extrémní výhodu při použití v dotazech tím, že poskytují rychlý přístup k datům.
Pokud jsou omezení primárního klíče definována ve více sloupcích, označuje se jako složený nebo složený primární klíč. A v tomto případě může každý sloupec primárního klíče obsahovat duplicitní hodnoty. Kombinované hodnoty ze všech sloupců v primárním klíči však musí být jedinečné.
Dobrým příkladem je případ, kdy máte tabulku se sloupci `id`, `names` a `age`. Když definujete omezení primárního klíče na kombinaci „id“ a „names“, můžete mít duplicitní instance hodnot „id“ nebo „names“. Přesto musí být každá kombinace jedinečná, aby se předešlo duplicitním řádkům. Takže můžete mít záznamy s `id=1` a `jméno=Walter` a `věk-22 “a `id=1`, `name=Henry` a `age=27`, ale nemůžete mít jiné záznamy s `id=1` a `name=Walter`, protože kombinace není jedinečná.
Zde je několik základních aspektů, které je třeba znát:
Omezení cizích klíčů – přehled
Cizí klíč (FK) zahrnuje sloupec nebo kombinaci několika sloupců používaných k vytvoření a svázání propojení mezi dvěma tabulkami a spravuje data, která mají být uložena v tabulce cizího klíče.
Odkaz na cizí klíč znamená vytvoření spojení mezi dvěma tabulkami; když na sloupec nebo sloupce obsahující primární klíč pro jinou tabulku odkazuje sloupec nebo sloupce v jiné tabulce.
Ve scénáři odkazu na cizí klíč se vytvoří spojení mezi dvěma tabulkami, když na sloupec nebo sloupce obsahující primární klíče v tabulce odkazují sloupce v jiné tabulce.
V praktickém případě můžete mít tabulku Sales.SalesOrderHeader s cizím klíčem propojeným s jinou tabulkou Prodej.Osoba, protože mezi prodejci a prodejními objednávkami existuje logický vztah.
Zde se SalesPersonID ve sloupci SalesOrderHeader mísí se sloupcem primárního klíče tabulky SalesPerson. Cizí klíč tabulky SalesPerson je sloupec SalesPersonID v SalesOrderHeader.
Tento vztah definuje pravidlo: Hodnota SalesPersonID nemůže být ve vaší tabulce SalesOrderHeader, pokud v tabulce SalesPerson neexistuje.
Tabulka může odkazovat až na 253 dalších sloupců a tabulek jako cizí klíče, alternativně nazývané odchozí odkazy. Od roku 2016 zvýšil SQL server počet tabulek a sloupců, na které můžete odkazovat v jedné tabulce, známé také jako příchozí odkazy, z 253 na 10 000. Tento nárůst však přichází s některými omezeními:
Jaké jsou výhody cizích klíčů?
Jak již bylo zmíněno dříve, omezení cizího klíče hrají zásadní roli při zabezpečení integrity a konzistence dat v relační databázi. Zde je rozpis důvodů, proč jsou omezení cizích klíčů nezbytná.
Indexy omezení cizího klíče
Omezení cizího klíče nevytváří automaticky odpovídající indexy jako primární. Můžete ručně vytvořit indexy pro omezení cizího klíče; 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 ze souvisejících tabulek v dotazech pomocí přiřazování sloupců vázaných na omezení. Indexy pomáhají databázi najít přidružená data v cizí tabulce.
- Pokud změníte omezení primárního klíče, budou zkontrolována s cizími v souvisejících tabulkách.
Není povinné vytvářet indexy. Stále můžete kombinovat data ze dvou tabulek bez určení omezení primárního a cizího klíče. Přidání omezení cizího klíče však optimalizuje tabulky a kombinuje je v dotazu, který splňuje kritéria použití klíčů. Pokud změníte omezení primárního klíče, budou zkontrolována s cizími v souvisejících.
Tipy pro vytvoření omezení cizího klíče v SQL
Spekulacím jste již věnovali značný čas; odpověděl proč. Přesuňme své zaměření a zúžme jej na taktiku vytváření omezení cizích klíčů; odpovědět jak.
Pole „Zahraniční klíč“ v tabulce odkazuje na „Primární klíč“ jiného klíče. Tabulka s primárním klíčem je vaše nadřazená tabulka. A tabulka s cizím klíčem se nazývá podřízená tabulka. Pojďme se ponořit.
Vytvoření cizího klíče při vytváření tabulky
Při vytváření tabulky můžete také vytvořit omezení cizího klíče pro zachování referenční integrity. Jak na to:
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‘ s primárním celočíselným klíčem ‚order_id‘, dalším celým číslem ‚customer_id‘ a datem ‚order_date‘. V tomto případě je omezení FOREIGN KEY přidáno do sloupce ‚customer_id‘ a odkazuje na ‚customer_id‘ ve vaší tabulce ‚customers‘.
Vytvoření cizího klíče po vytvoření tabulky
Předpokládejme, že jste již vytvořili tabulku a chcete přidat omezení cizího klíče; použijte ve svém kódu příkaz `ALTER TABLE`. Podívejte se na fragment kódu níže.
ALTER TABLE orders ADD FOREIGN KEY (customer_id) REFERENCES customers(customer_id);
V tomto případě jste přidali omezení cizího klíče ‚customer_id‘ do tabulky ‚objednávky‘, abyste odkazovali na sloupec ‚customer_id‘ v tabulce ‚customers‘.
Vytvoření cizího klíče bez kontroly existujících dat
Když do tabulky přidáte omezení cizího klíče, databáze automaticky zkontroluje existující data, aby byla zajištěna konzistence s omezením. Pokud však víte, že data jsou konzistentní a chcete přidat omezení bez kontroly konzistence, postupujte takto.
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 konkrétní případ je užitečný v konkrétních případech. Například, když máte velká data a chcete dokončit proces ověření.
Vytvoření cizího klíče pomocí DELETE/UPDATE
Při vytváření omezení cizího klíče můžete řídit akci, která se má provést v případech, kdy je odkazovaný řádek aktualizován nebo odstraněn. V tomto případě používáte kaskádová omezení referenční integrity k diktování akcí, které mají být provedeny. Obsahují:
#1. ŽÁDNÁ AKCE
Stejně jako u mnoha jiných databází je pravidlo ‚NO ACTION‘ výchozím chováním při vytváření omezení cizího klíče. To znamená, že se při odstranění nebo aktualizaci odkazovaného řádku neprovede žádná akce.
Database Engine vyvolá chybu, pokud je porušeno omezení cizího klíče. To se však nedoporučuje, protože to může vést k problémům s referenční integritou, protože je třeba vynutit omezení cizího klíče. Zde je příklad, jak na to:
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. KASKÁDA
Pravidlo ‚CASCADE‘ je další možností pro akce ‚ON DELETE‘ a ‚ON UPDATE‘ při vytváření omezení cizího klíče. Když je zaveden, znamená to, že kdykoli je aktualizován nebo odstraněn řádek v nadřazených tabulkách, odpovídajícím způsobem se aktualizují nebo odstraní odkazované řádky. Tato technika je účinná při zachování 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 byste měli být opatrní, protože může způsobit nežádoucí následky, pokud se nepoužívá opatrně. Měli byste se vyhnout náhodnému smazání příliš velkého množství dat nebo vytváření cyklických odkazů. Proto tuto možnost používejte pouze v případě potřeby a opatrně.
Pro používání CASCADE platí určitá pravidla:
- Nemůžete zadat CASCADE, pokud je sloupec časového razítka součástí cizího nebo odkazovaného klíče.
- Pokud má vaše tabulka spouštěč INSTEAD OF DELETE, nemůžete zadat ON DELETED CASCADE.
- Nemůžete zadat ON UPDATE CASCADE, pokud má vaše tabulka spouštěč MÍSTO AKTUALIZACE.
#3. SET NULL
Když odstraníte nebo aktualizujete odpovídající řádek v nadřazené tabulce, všechny hodnoty tvořící cizí klíč budou nastaveny na hodnotu null. Toto omezující pravidlo vyžaduje, aby sloupce cizího klíče mohly být spouštěny s možnou hodnotou Null, a nelze je zadat pro tabulky s aktivačními událostmi INSTEAD OF UPDATE. Zde je příklad, jak na to.
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řípadě jste nastavili sloupec cizího klíče ‚customer_id‘ v tabulce „objednávky“ na hodnotu null, pokud je odpovídající řádek v tabulce „customers“ odstraněn nebo aktualizován.
#4. NASTAVIT VÝCHOZÍ
Zde nastavujete všechny hodnoty, které činí cizí klíč výchozím za předpokladu, že je odkazovaný řádek v nadřazené tabulce aktualizován nebo odstraněn.
Toto omezení se spustí, pokud mají všechny sloupce cizího klíče výchozí definice. Pokud má sloupec hodnotu null, jeho výchozí hodnota je nastavena na NULL. Všimněte si, že tuto volbu nelze zadat pro tabulky se spouštěči 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řípadě jste nastavili ‚customer_id‘ v tabulce „objednávky“ na výchozí hodnotu, ke které dojde, když je odpovídající řádek v tabulce „zákazníci“ smazán nebo aktualizován.
Závěrečná slova
V této příručce jste si zopakovali omezení primárního klíče a zabrousili do omezení cizího klíče. Také jste se setkali s několika technikami vytváření omezení cizího klíče. A i když existuje mnoho způsobů, jak vytvořit omezení cizího klíče, tento příspěvek tyto metody odhalil.
A doufat, že jste pochopili nové techniky; nejste omezeni na jejich kombinování. Například metody omezení CASCADE, SET NULL, SET DEFAULT a NO ACTION lze kombinovat na tabulkách s referenčními vztahy.
Pokud vaše tabulka nenarazí na ŽÁDNOU AKCI, vrátí se k jiným omezujícím pravidlům. V ostatních případech může akce DELETE spustit kombinaci těchto pravidel a pravidlo NO ACTION bude spuštěno jako poslední.
Dále se podívejte na cheat sheet SQL.