Co to jest klucz obcy?

Klucz obcy (FOREIGN KEY) to kolumna lub zestaw kolumn w jednej tabeli, która odnosi się do klucza głównego w innej tabeli. W kontekście baz danych relacyjnych, klucz obcy podlega ograniczeniu zależności włączenia, które wymaga, aby krotki składające się z atrybutów klucza obcego w jednej relacji (R) istniały również w innej (niekoniecznie odrębnej) relacji (S). Ponadto atrybuty te muszą być również kluczem kandydującym w S. Innymi słowy, klucz obcy to zestaw atrybutów, które odwołują się do klucza kandydującego.

W BigQuery, klucze obce są używane do tworzenia relacji między tabelami, podobnie jak w innych systemach zarządzania bazami danych (DBMS). Pomagają one zapewnić integralność danych, gwarantując, że wartości w kolumnie klucza obcego istnieją w tabeli referencyjnej. W BigQuery klucze obce są implementowane jako ograniczenia, które można definiować podczas tworzenia tabeli lub później za pomocą polecenia ALTER TABLE.

Zastosowanie klucza obcego

Klucze obce mają szerokie zastosowanie w kontekście BigQuery, w tym:

• Zapewnienie integralności danych: Klucze obce zapobiegają wprowadzaniu błędnych danych, gwarantując, że wartości w kolumnie klucza obcego istnieją w tabeli referencyjnej.
• Ułatwianie łączenia tabel: Klucze obce ułatwiają łączenie tabel, umożliwiając szybkie i efektywne pobieranie powiązanych danych.
• Wspieranie analizy danych: Klucze obce umożliwiają analizowanie danych z różnych tabel w sposób spójny i dokładny.
• Poprawa wydajności zapytań: Klucze obce mogą być indeksowane, co przyspiesza wykonywanie zapytań, zwłaszcza w przypadku dużych zestawów danych.

Przykład użycia w BigQuery

Załóżmy, że mamy dwie tabele: „Klienci” i „Zamówienia”. Tabela „Klienci” zawiera informacje o klientach, a tabela „Zamówienia” zawiera informacje o zamówieniach złożonych przez klientów. Tabela „Zamówienia” zawiera kolumnę „id_klienta”, która odnosi się do kolumny „id” w tabeli „Klienci”.

CREATE TABLE Klienci (
  id INT64 PRIMARY KEY,
  imie STRING,
  nazwisko STRING
);

CREATE TABLE Zamowienia (
  id INT64 PRIMARY KEY,
  id_klienta INT64,
  data_zamowienia DATE,
  wartosc FLOAT64,
  FOREIGN KEY (id_klienta) REFERENCES Klienci(id)
);

W tym przykładzie kolumna „id_klienta” w tabeli „Zamówienia” jest kluczem obcym, który odnosi się do klucza głównego „id” w tabeli „Klienci”. Oznacza to, że każda wartość w kolumnie „id_klienta” musi istnieć w kolumnie „id” w tabeli „Klienci”.

Możemy teraz użyć klucza obcego do łączenia tabel i pobierania informacji o klientach i ich zamówieniach:

SELECT
    k.imie,
    k.nazwisko,
    z.data_zamowienia,
    z.wartosc
  FROM
    `projekt.dataset.Klienci` AS k
    INNER JOIN `projekt.dataset.Zamowienia` AS z ON k.id = z.id_klienta;

Najczęstsze błędy i sposoby ich unikania

Najczęstsze błędy związane z kluczami obcymi w BigQuery to:

• Próba wstawienia wartości do kolumny klucza obcego, która nie istnieje w tabeli referencyjnej. Aby uniknąć tego błędu, należy upewnić się, że wartość w kolumnie klucza obcego istnieje w tabeli referencyjnej przed wstawieniem danych.
• Próba zaktualizowania wartości w kolumnie klucza obcego, która spowodowałaby naruszenie ograniczenia klucza obcego. Aby uniknąć tego błędu, należy upewnić się, że nowa wartość istnieje w tabeli referencyjnej przed zaktualizowaniem danych.
• Próba usunięcia wiersza z tabeli referencyjnej, który jest używany jako wartość klucza obcego w innej tabeli. Aby uniknąć tego błędu, należy usunąć lub zaktualizować wiersze w tabeli zależnej przed usunięciem wiersza z tabeli referencyjnej.

BigQuery oferuje różne opcje obsługi naruszeń ograniczeń klucza obcego, takie jak CASCADE, RESTRICT, NO ACTION, SET NULL i SET DEFAULT. Wybór odpowiedniej opcji zależy od konkretnego scenariusza i wymagań biznesowych.

Optymalizacje i najlepsze praktyki

Aby zoptymalizować zapytania z użyciem klucza obcego w BigQuery, należy:

• Indeksować kolumny klucza obcego. Indeksy przyspieszają wyszukiwanie danych i łączenie tabel.
• Używać odpowiednich typów danych. Wybór odpowiedniego typu danych dla kolumn klucza obcego może poprawić wydajność zapytań.
• Unikać nadmiernego użycia kluczy obcych. Zbyt wiele kluczy obcych może spowolnić wykonywanie zapytań.

Porównanie z innymi dialektami SQL

Klucze obce są obsługiwane w większości dialektów SQL, w tym MySQL, PostgreSQL i Oracle. Podstawowa funkcjonalność jest podobna, ale mogą istnieć niewielkie różnice w składni i opcjach obsługi naruszeń ograniczeń klucza obcego. Na przykład w MySQL można użyć opcji ON DELETE CASCADE, aby automatycznie usunąć wiersze w tabeli zależnej, gdy wiersz w tabeli referencyjnej zostanie usunięty.