Jak definovat relace mezi tabulkami v accessové databázi

Původní číslo KB: 304466

Souhrn

Tento článek popisuje, jak definovat relace v databázi aplikace Microsoft Access. Článek obsahuje následující témata:

• Co jsou relace mezi tabulkami?
• Druhy relací mezi tabulkami
  • Relace 1:N
  • Relace M:N
  • Relace 1:1
• Definování relací mezi tabulkami
  • Jak definovat relaci 1:N nebo 1:1
  • Definování relace M:N
• Referenční integrita
• Kaskádové aktualizace a odstranění
• Typy spojení

Co jsou relace mezi tabulkami?

Relace v relační databázi umožňují zabránit redundantním datům. Pokud například navrhujete databázi, která bude sledovat informace o knihách, můžete mít tabulku s názvem "Názvy", ve které se ukládají informace o každé knize, jako je název knihy, datum vydání a vydavatel. Existují také informace, které můžete chtít uložit o vydavateli, jako je telefonní číslo vydavatele, adresa a PSČ. Pokud byste všechny tyto informace uložili do tabulky Názvy, telefonní číslo vydavatele by se duplikovalo pro každý titul, který vydavatel vytiskne.

Lepším řešením je uložit informace o vydavateli pouze jednou do samostatné tabulky, kterou budeme nazývat "Vydavatelé". Potom byste do tabulky Nadpisy umístili ukazatel, který odkazuje na položku v tabulce Vydavatelé.

Abyste měli jistotu, že data zůstanou synchronizovaná, můžete mezi tabulkami vynutit referenční integritu. Referenční relace integrity pomáhají zajistit, aby informace v jedné tabulce odpovídaly informacím v jiné tabulce. Každý název v tabulce Názvy musí být například přidružený ke konkrétnímu vydavateli v tabulce Vydavatelé. Název nelze přidat do databáze pro vydavatele, který v databázi neexistuje.

Logické relace v databázi umožňují efektivně dotazovat data a vytvářet sestavy.

Druhy relací mezi tabulkami

Relace funguje tak, že porovnává data v klíčových sloupcích, obvykle ve sloupcích (nebo polích), které mají v obou tabulkách stejný název. Ve většině případů relace spojuje primární klíč nebo sloupec jedinečného identifikátoru pro každý řádek z jedné tabulky do pole v jiné tabulce. Sloupec v druhé tabulce se označuje jako cizí klíč. Pokud například chcete sledovat prodej každého názvu knihy, vytvoříte relaci mezi sloupcem primárního klíče (nazvěme ho title_ID) v tabulce "Názvy" a sloupcem v tabulce "Prodej", který má název title_ID. Sloupec title_ID v tabulce Sales je cizí klíč.

Mezi tabulkami existují tři druhy relací. Druh vytvořené relace závisí na tom, jak jsou definovány související sloupce.

Relace 1:N

Nejběžnějším typem relace je relace 1:N. V tomto typu relace může mít řádek v tabulce A mnoho odpovídajících řádků v tabulce B. Řádek v tabulce B ale může mít pouze jeden odpovídající řádek v tabulce A. Například tabulky Vydavatelé a Názvy mají relaci 1:N. To znamená, že každý vydavatel vytváří mnoho titulů. Každý titul ale pochází pouze od jednoho vydavatele.

Relace 1:N se vytvoří, pokud je primárním klíčem pouze jeden ze souvisejících sloupců nebo má jedinečné omezení.

V okně relace v Accessu je strana primárního klíče relace 1:N označená číslem 1. Strana cizího klíče relace je označena symbolem nekonečna.

Relace M:N

V relaci M:N může mít řádek v tabulce A mnoho odpovídajících řádků v tabulce B a naopak. Takovou relaci vytvoříte definováním třetí tabulky, která se nazývá spojovací tabulka. Primární klíč spojovací tabulky se skládá z cizích klíčů z tabulky A i z tabulky B. Například tabulka "Authors" a "Title" mají relaci M:N, která je definována relací 1:N z každé z těchto tabulek k tabulce TitleAuthors. Primárním klíčem tabulky TitleAuthors je kombinace sloupce au_ID (primární klíč tabulky Autorů) a sloupce title_ID (primární klíč tabulky Title).

Relace 1:1

V relaci 1:1 nemůže mít řádek v tabulce A více než jeden odpovídající řádek v tabulce B a naopak. Relace 1:1 se vytvoří, pokud oba související sloupce jsou primárními klíči nebo mají jedinečná omezení.

Tento druh relace není běžný, protože většina informací, které se tímto způsobem týkají, by byla v jedné tabulce. Relaci 1:1 můžete použít k provedení následujících akcí:

• Rozdělí tabulku s mnoha sloupci.
• Z bezpečnostních důvodů izolujte část tabulky.
• Ukládejte krátkodobá data, která by se dala snadno odstranit odstraněním tabulky.
• Uložte informace, které se vztahují pouze na podmnožinu hlavní tabulky.

V Accessu je strana primárního klíče relace 1:1 označená symbolem klíče. Strana cizího klíče je také označena symbolem klíče.

Definování relací mezi tabulkami

Když vytvoříte relaci mezi tabulkami, nemusí mít související pole stejné názvy. Související pole však musí mít stejný datový typ, pokud pole primárního klíče není polem automatického číslo. Pole Automatické číslo s polem Číslo lze spárovat pouze v případě, že je vlastnostFieldSize obou odpovídajících polí stejná. Můžete například porovnat pole Automatické číslo a pole Číslo, pokud je vlastnostFieldSize obou polí dlouhé celé číslo. I když jsou obě odpovídající pole číselná, musí mít stejné nastaveníFieldSizeproperty.

Jak definovat relaci 1:N nebo 1:1

Pokud chcete vytvořit relaci 1:N nebo 1:1, postupujte takto:

1. Zavřete všechny tabulky. Relace mezi otevřenými tabulkami nelze vytvářet ani měnit.

2. V aplikaci Access 2002 nebo Access 2003 postupujte takto:

   1. Stisknutím klávesy F11 přepněte do okna Databáze.
   2. V nabídce Nástroje klikněte na Relace.

   V Accessu 2007, Accessu 2010 nebo Accessu 2013 klikněte na Relace ve skupině Zobrazit nebo skrýt na kartě Databázové nástroje .

3. Pokud jste v databázi ještě nedefinovali žádné relace, zobrazí se automaticky dialogové okno Zobrazit tabulku . Pokud chcete přidat tabulky, které chcete propojit, ale dialogové okno Zobrazit tabulku se nezobrazí, klikněte v nabídce Relace na zobrazit tabulku.

4. Poklikejte na názvy tabulek, které chcete propojit, a potom zavřete dialogové okno Zobrazit tabulku . Pokud chcete vytvořit relaci mezi tabulkou a sebou samým, přidejte tuto tabulku dvakrát.

5. Přetáhněte pole, které chcete propojit, z jedné tabulky do souvisejícího pole v druhé tabulce. Pokud chcete přetáhnout více polí, stiskněte klávesu Ctrl, klikněte na jednotlivá pole a potom je přetáhněte.

   Ve většině případů přetáhnete pole primárního klíče (toto pole se zobrazuje tučným písmem) z jedné tabulky do podobného pole (toto pole má často stejný název), které se v druhé tabulce nazývá cizí klíč.

6. Zobrazí se dialogové okno Upravit relace . Ujistěte se, že názvy polí, které se zobrazují v těchto dvou sloupcích, jsou správné. V případě potřeby můžete názvy změnit.

   V případě potřeby nastavte možnosti relace. Pokud potřebujete mít informace o konkrétní položce v dialogovém okně Upravit relace , klikněte na tlačítko otazníku a potom klikněte na položku. (Tyto možnosti budou podrobně vysvětleny dále v tomto článku.)

7. Kliknutím na Vytvořit vytvořte relaci.

8. Opakujte kroky 4 až 7 pro každou dvojici tabulek, které chcete propojit.

   Když zavřete dialogové okno Upravit relace , Access se zeptá, jestli chcete rozložení uložit. Ať už rozložení uložíte nebo neuložíte, relace, které vytvoříte, se uloží do databáze.

   Poznámka

   Relace můžete vytvářet nejen v tabulkách, ale i v dotazech. Referenční integrita se však u dotazů nevynucuje.

Definování relace M:N

Pokud chcete vytvořit relaci M:N, postupujte takto:

1. Vytvořte dvě tabulky, které budou mít relaci M:N.

2. Vytvořte třetí tabulku. Toto je spojovací tabulka. Do spojovací tabulky přidejte nová pole, která mají stejné definice jako pole primárního klíče z každé tabulky, kterou jste vytvořili v kroku 1. Ve spojovací tabulce fungují pole primárního klíče jako cizí klíče. Do spojovací tabulky můžete přidat další pole stejně jako do jakékoli jiné tabulky.

3. Ve spojovací tabulce nastavte primární klíč tak, aby zahrnoval pole primárního klíče z ostatních dvou tabulek. Například ve spojovací tabulce TitleAuthors by se primární klíč skládají z polí OrderID a ProductID .

   Poznámka

   Pokud chcete vytvořit primární klíč, postupujte takto:

   1. Otevřete tabulku v návrhovém zobrazení.

   2. Vyberte pole, která chcete definovat jako primární klíč. Pokud chcete vybrat jedno pole, klikněte na volič řádků pro požadované pole. Pokud chcete vybrat více polí, podržte stisknutou klávesu Ctrl a potom klikněte na volič řádků pro každé pole.

   3. V Accessu 2002 nebo Accessu 2003 klikněte na panelu nástrojů na Primární klíč .

      V Accessu 2007 klikněte na Primární klíč ve skupině Nástroje na kartě Návrh .

      Poznámka

      Pokud chcete, aby se pořadí polí v primárním klíči s více poli liší od pořadí polí v tabulce, klikněte na tlačítko Indexy na panelu nástrojů, aby se zobrazilo dialogové okno Indexy, a potom přeuspořádejte názvy polí pro index s názvem PrimaryKey.

4. Definujte relaci 1:N mezi každou primární tabulkou a spojovací tabulkou.

Referenční integrita

Referenční integrita je systém pravidel, který Access používá k zajištění, že relace mezi záznamy v souvisejících tabulkách jsou platné a že související data omylem neodstraníte nebo nezměníte. Referenční integritu můžete nastavit, pokud jsou splněny všechny následující podmínky:

• Odpovídající pole z primární tabulky je primární klíč nebo má jedinečný index.
• Související pole mají stejný datový typ. Existují dvě výjimky. Pole Automatické číslo může souviset s polem Číslo, které má FieldSize nastavení vlastnosti Dlouhé celé číslo, a pole FieldSizeAutomatické číslo s nastavením vlastnosti Replikační ID může souviset s polem Číslo, které má FieldSize nastavení vlastnosti ID replikace.
• Obě tabulky patří do stejné databáze Accessu. Pokud jsou tabulky propojené, musí to být tabulky ve formátu Accessu a musíte otevřít databázi, ve které jsou uložené, a nastavit referenční integritu. Referenční integritu nelze vynutit u propojených tabulek z databází v jiných formátech.

Při použití referenční integrity platí následující pravidla:

• Do pole cizího klíče související tabulky nelze zadat hodnotu, která neexistuje v primárním klíči primární tabulky. Do cizího klíče však můžete zadat hodnotu Null. To určuje, že záznamy spolu nesouvisejí. Například nemůžete mít objednávku přiřazenou zákazníkovi, který neexistuje. Pokud ale zadáte hodnotu Null do pole CustomerID , můžete vytvořit objednávku, která není nikomu přiřazena.
• Záznam z primární tabulky nelze odstranit, pokud v související tabulce existují odpovídající záznamy. Pokud jsou zaměstnanci přiřazené objednávky v tabulce Objednávky, nemůžete například odstranit záznam zaměstnance z tabulky Zaměstnanci.
• Hodnotu primárního klíče v primární tabulce nelze změnit, pokud tento záznam obsahuje související záznamy. Id zaměstnance například nemůžete změnit v tabulce Zaměstnanci, pokud jsou v tabulce Objednávky přiřazené objednávky danému zaměstnanci.

Kaskádové aktualizace a odstranění

U relací, ve kterých se vynucuje referenční integrita, můžete určit, jestli má Access automaticky kaskádovitě aktualizovat nebo kaskádovitě odstraňovat související záznamy. Pokud nastavíte tyto možnosti, povolí se operace odstranění a aktualizace, kterým by obvykle zabránila pravidla referenční integrity. Když odstraníte záznamy nebo změníte hodnoty primárního klíče v primární tabulce, access provede potřebné změny souvisejících tabulek, aby se zachovala referenční integrita.

Pokud při definování relace zaškrtnete políčko Kaskádová aktualizace souvisejících polí , microsoft Access při každé změně primárního klíče záznamu v primární tabulce automaticky aktualizuje primární klíč na novou hodnotu ve všech souvisejících záznamech. Pokud například změníte ID zákazníka v tabulce Zákazníci, pole Id zákazníka v tabulce Objednávky se automaticky aktualizuje pro každou z objednávek daného zákazníka, aby nedošlo k přerušení relace. Access kaskáduje aktualizace, aniž by se zobrazovaly zprávy.

Pokud při definování relace zaškrtnete políčko Kaskádové odstranění souvisejících záznamů , Access při každém odstranění záznamů v primární tabulce automaticky odstraní související záznamy v související tabulce. Pokud například odstraníte záznam zákazníka z tabulky Zákazníci, všechny objednávky zákazníka se automaticky odstraní z tabulky Objednávky. (To zahrnuje záznamy v tabulce Podrobnosti objednávky, které souvisejí se záznamy "Orders".) Když odstraníte záznamy z formuláře nebo datového listu, když je zaškrtnuté políčko Kaskádové odstranění souvisejících záznamů , Access vás upozorní, že související záznamy mohou být také odstraněny. Když ale odstraníte záznamy pomocí odstraňovacího dotazu, Access automaticky odstraní záznamy v souvisejících tabulkách bez zobrazení upozornění.

Typy spojení

Existují tři typy spojení. Můžete je vidět na následujícím snímku obrazovky:

Možnost 1 definuje vnitřní spojení. Vnitřní spojení je spojení, ve kterém se záznamy ze dvou tabulek zkombinují ve výsledcích dotazu pouze v případě, že hodnoty ve spojených polích splňují zadanou podmínku. V dotazu je výchozím spojením vnitřní spojení, které vybírá záznamy pouze v případě, že se hodnoty ve spojených polích shodují.

Možnost 2 definuje levé vnější spojení. Levé vnější spojení je spojení, ve kterém se do výsledků dotazu přidají všechny záznamy z levé strany operace LEFT JOIN v příkazu SQL dotazu, a to i v případě, že ve spojování pole nejsou žádné odpovídající hodnoty z tabulky na pravé straně.

Možnost 3 definuje pravé vnější spojení. Pravé vnější spojení je spojení, ve kterém se do výsledků dotazu přidají všechny záznamy z pravé strany operace RIGHT JOIN v příkazu SQL dotazu, a to i v případě, že ve spojování pole z tabulky na levé straně nejsou žádné odpovídající hodnoty.