Klucz zastępczy - Surrogate key

Kluczem zastępczym (lub klucz syntetyczny , pseudokey , identyfikator podmiotu , klucz bez faktów , czy klucz techniczny ) w bazie danych to unikalny identyfikator dla każdej z jednostki w modelowanego świata lub obiektu w bazie danych. Klucz zastępczy nie pochodzi z danych aplikacji, w przeciwieństwie do klucza naturalnego (lub biznesowego ) .

Definicja

Istnieją co najmniej dwie definicje surogatu:

Surogat (1) – Hall, Owlett i Todd (1976)
Surogat reprezentuje byt w świecie zewnętrznym. Surogat jest wewnętrznie generowany przez system, ale mimo to jest widoczny dla użytkownika lub aplikacji.
Surogat (2) – Wieringa i De Jonge (1991)
Surogat reprezentuje obiekt w samej bazie danych. Surogat jest wewnętrznie generowany przez system i jest niewidoczny dla użytkownika lub aplikacji.

Zastępczy (1), określenie odnosi się do modelu danych , a nie modelu przechowywania i jest stosowany w niniejszym artykule. Patrz Data (1998).

Ważna różnica między kluczem zastępczym a kluczem podstawowym zależy od tego, czy baza danych jest bieżącą bazą danych, czy bazą danych tymczasowych . Ponieważ bieżąca baza danych przechowuje tylko aktualnie ważne dane, istnieje zależność jeden do jednego między substytutem w modelowanym świecie a kluczem podstawowym bazy danych. W takim przypadku surogat może być używany jako klucz podstawowy, co skutkuje określeniem klucza zastępczego . Jednak w tymczasowej bazie danych istnieje relacja „wiele do jednego” między kluczami podstawowymi a substytutem. Ponieważ w bazie danych może znajdować się kilka obiektów odpowiadających jednemu surogatowi, nie możemy użyć surogatu jako klucza podstawowego; wymagany jest inny atrybut, oprócz surogatu, aby jednoznacznie zidentyfikować każdy obiekt.

Chociaż Hall i in. (1976) nic na ten temat nie mówią, inni twierdzili, że surogat powinien mieć następujące cechy:

  • wartość jest unikalna w całym systemie, dlatego nigdy nie zostanie ponownie wykorzystana
  • wartość jest generowana przez system
  • wartością nie może manipulować użytkownik ani aplikacja
  • wartość nie ma znaczenia semantycznego
  • wartość nie jest widoczna dla użytkownika lub aplikacji
  • wartość nie składa się z kilku wartości z różnych dziedzin.

Surogaty w praktyce

W bieżącej bazie danych klucz zastępczy może być kluczem podstawowym , generowanym przez system zarządzania bazą danych i niepochodzącym z żadnych danych aplikacji w bazie danych. Jedynym znaczeniem klucza zastępczego jest działanie jako klucz podstawowy. Możliwe jest również, że klucz zastępczy istnieje oprócz UUID wygenerowanego przez bazę danych (na przykład numer HR dla każdego pracownika inny niż UUID każdego pracownika).

Klucz zastępczy jest często kolejnym numerem (np. „kolumna tożsamości Sybase lub SQL Server ”, PostgreSQL lub Informix serial , Oracle lub SQL Server SEQUENCE albo kolumna zdefiniowana AUTO_INCREMENTw MySQL ). Niektóre bazy danych udostępniają UUID / GUID jako możliwy typ danych dla kluczy zastępczych (np. PostgreSQLUUID lub SQL ServerUNIQUEIDENTIFIER ).

Posiadanie klucza niezależnego od wszystkich innych kolumn izoluje relacje bazy danych od zmian wartości danych lub projektu bazy danych (dzięki czemu baza danych jest bardziej elastyczna ) i gwarantuje unikalność.

W tymczasowej bazie danych konieczne jest rozróżnienie między kluczem zastępczym a kluczem biznesowym . Każdy wiersz miałby zarówno klucz biznesowy, jak i klucz zastępczy. Klucz zastępczy identyfikuje jeden unikalny wiersz w bazie danych, klucz biznesowy identyfikuje jedną unikalną jednostkę modelowanego świata. Jeden wiersz tabeli reprezentuje wycinek czasu, w którym znajdują się wszystkie atrybuty jednostki przez określony czas. Plastry te przedstawiają cały okres życia jednego podmiotu gospodarczego. Na przykład tabela EmployeeContracts może zawierać informacje czasowe w celu śledzenia zakontraktowanych godzin pracy. Klucz biznesowy dla jednej umowy będzie identyczny (nie niepowtarzalny) w obu wierszach, jednak klucz zastępczy dla każdego wiersza jest unikalny.

Klucz zastępczy BusinessKey Imię i nazwisko pracownika Godziny pracy w tygodniu WierszWażnyOd WierszWażny do
1 BOS0120 John Smith 40 2000-01-01 2000-12-31
56 P0000123 Bob Brown 25 1999-01-01 2011-12-31
234 BOS0120 John Smith 35 2001-01-01 2009-12-31

Niektórzy projektanci baz danych używają kluczy zastępczych systematycznie, niezależnie od przydatności innych kluczy kandydujących , podczas gdy inni będą używać klucza już obecnego w danych, jeśli taki istnieje.

Niektóre z nazw alternatywnych („klucz generowany przez system”) opisują sposób generowania nowych wartości zastępczych, a nie naturę koncepcji zastępczej.

Podejścia do generowania surogatów obejmują:

Zalety

Stabilność

Klucze zastępcze zwykle nie zmieniają się, gdy wiersz istnieje. Ma to następujące zalety:

  • Aplikacje nie mogą stracić odniesienia do wiersza w bazie danych (ponieważ identyfikator się nie zmienia).
  • Dane klucza podstawowego lub naturalnego można zawsze zmodyfikować, nawet w przypadku baz danych, które nie obsługują aktualizacji kaskadowych powiązanych kluczy obcych .

Zmiany wymagań

Atrybuty, które jednoznacznie identyfikują jednostkę, mogą ulec zmianie, co może unieważnić przydatność kluczy naturalnych. Rozważmy następujący przykład:

Jako klucz naturalny wybierana jest sieciowa nazwa użytkownika pracownika. Po połączeniu z inną firmą należy wstawić nowych pracowników. Niektóre z nowych nazw użytkowników sieci powodują konflikty, ponieważ ich nazwy użytkowników zostały wygenerowane niezależnie (kiedy firmy były oddzielne).

W takich przypadkach zazwyczaj do klucza naturalnego należy dodać nowy atrybut (na przykład kolumnę original_company ). W przypadku klucza zastępczego należy zmienić tylko tabelę, która definiuje klucz zastępczy. W przypadku kluczy naturalnych wszystkie tabele (i ewentualnie inne powiązane oprogramowanie), które używają klucza naturalnego, będą musiały ulec zmianie.

Niektóre domeny problemowe nie identyfikują jednoznacznie odpowiedniego klucza naturalnego. Klucze zastępcze unikają wybierania klucza naturalnego, który może być nieprawidłowy.

Wydajność

Klucze zastępcze są zwykle kompaktowymi typami danych, takimi jak czterobajtowa liczba całkowita. Dzięki temu baza danych może szybciej przeszukiwać pojedynczą kolumnę klucza niż wiele kolumn. Co więcej, nieredundantny rozkład kluczy powoduje, że wynikowy indeks b-drzewa jest całkowicie zrównoważony. Klucze zastępcze są również tańsze w łączeniu (mniej kolumn do porównania) niż klucze złożone .

Zgodność

Podczas korzystania z kilku systemów tworzenia aplikacji bazodanowych, sterowników i systemów mapowania obiektowo-relacyjnego , takich jak Ruby on Rails lub Hibernate , znacznie łatwiej jest użyć kluczy zastępczych liczb całkowitych lub GUID dla każdej tabeli zamiast kluczy naturalnych w celu obsługi bazy danych- operacje niezależne od systemu i mapowanie obiekt-wiersz.

Jednolitość

Gdy każda tabela ma jednolity klucz zastępczy, niektóre zadania można łatwo zautomatyzować, pisząc kod w sposób niezależny od tabeli.

Walidacja

Możliwe jest zaprojektowanie par klucz-wartość zgodnych z dobrze znanym wzorcem lub strukturą, które można automatycznie zweryfikować. Na przykład klucze, które mają być używane w jakiejś kolumnie jakiejś tabeli, mogą być zaprojektowane tak, aby „wyglądały inaczej niż” te, które są przeznaczone do użycia w innej kolumnie lub tabeli, co upraszcza wykrywanie błędów aplikacji, w których klucze zostały zagubione. Jednak ta cecha kluczy zastępczych nigdy nie powinna być używana do sterowania logiką samych aplikacji, ponieważ naruszałoby to zasady normalizacji bazy danych .

Niedogodności

Odłączenie

Wartości wygenerowanych kluczy zastępczych nie mają związku z rzeczywistym znaczeniem danych przechowywanych w rzędzie. Podczas sprawdzania wiersza zawierającego odwołanie do klucza obcego do innej tabeli za pomocą klucza zastępczego, znaczenie wiersza klucza zastępczego nie może być odróżnione od samego klucza. Każdy klucz obcy musi być połączony, aby zobaczyć powiązany element danych. Jeśli odpowiednie ograniczenia bazy danych nie zostały ustawione lub dane zaimportowane ze starszego systemu, w którym nie zastosowano integralności referencyjnej , możliwe jest posiadanie wartości klucza obcego, która nie odpowiada wartości klucza podstawowego i dlatego jest nieprawidłowa. (W związku z tym CJ Date uważa brak znaczenia kluczy zastępczych za zaletę).

Aby wykryć takie błędy, należy wykonać zapytanie, które używa lewego sprzężenia zewnętrznego między tabelą z kluczem obcym a tabelą z kluczem podstawowym, pokazując oba pola klucza oprócz pól wymaganych do rozróżnienia rekordu; wszystkie nieprawidłowe wartości kluczy obcych będą miały kolumnę klucza podstawowego jako NULL. Konieczność przeprowadzenia takiego sprawdzenia jest tak powszechna, że ​​program Microsoft Access faktycznie udostępnia kreatora „Znajdź niedopasowane zapytanie”, który generuje odpowiedni kod SQL po przejściu użytkownika przez okno dialogowe. (Ręczne tworzenie takich zapytań nie jest jednak zbyt trudne). Zapytania „Znajdź niedopasowane” są zwykle stosowane jako część procesu czyszczenia danych podczas dziedziczenia starszych danych.

Klucze zastępcze są nienaturalne w przypadku eksportowanych i udostępnianych danych. Szczególną trudnością jest to, że tabele z dwóch identycznych schematów (na przykład schematu testowego i schematu programistycznego) mogą zawierać rekordy, które są równoważne w sensie biznesowym, ale mają różne klucze. Można to złagodzić, NIE eksportując kluczy zastępczych, z wyjątkiem danych przejściowych (najwyraźniej w przypadku wykonywania aplikacji, które mają połączenie „na żywo” z bazą danych).

Gdy klucze zastępcze zastępują klucze naturalne, integralność referencyjna specyficzna dla domeny zostanie naruszona. Na przykład w tabeli głównej klientów ten sam klient może mieć wiele rekordów pod osobnymi identyfikatorami klientów, nawet jeśli klucz naturalny (kombinacja nazwy klienta, daty urodzenia i adresu e-mail) byłby unikalny. Aby zapobiec kompromisom, NIE wolno zastępować klucza naturalnego tabeli: musi on być zachowany jako ograniczenie unikatowe , które jest zaimplementowane jako unikatowy indeks w kombinacji pól z kluczem naturalnym.

Optymalizacja zapytań

Relacyjne bazy danych zakładają, że do klucza podstawowego tabeli zastosowano unikalny indeks . Unikalny indeks służy dwóm celom: (i) wymuszenie integralności jednostki, ponieważ dane klucza podstawowego muszą być unikatowe w wierszach oraz (ii) szybkie wyszukiwanie wierszy podczas zapytania. Ponieważ klucze zastępcze zastępują atrybuty identyfikujące tabelę — klucz naturalny — i ponieważ atrybuty identyfikujące prawdopodobnie są tymi, których dotyczy zapytanie, optymalizator zapytań jest zmuszony do wykonania pełnego skanowania tabeli podczas wykonywania prawdopodobnych zapytań. Rozwiązaniem problemu pełnego skanowania tabeli jest zastosowanie indeksów do identyfikujących atrybutów lub ich zestawów. Tam, gdzie takie zestawy same są kluczem kandydującym , indeks może być indeksem unikalnym.

Te dodatkowe indeksy zajmą jednak miejsce na dysku i spowolnią wstawianie i usuwanie.

Normalizacja

Klucze zastępcze mogą skutkować zduplikowanymi wartościami w dowolnych kluczach naturalnych . Aby zapobiec duplikacji, należy zachować rolę kluczy naturalnych jako ograniczeń jednoznacznych podczas definiowania tabeli za pomocą instrukcji SQL CREATE TABLE lub instrukcji ALTER TABLE ...ADD CONSTRAINT, jeśli ograniczenia są dodawane po namyśle.

Modelowanie procesów biznesowych

Ponieważ klucze zastępcze są nienaturalne, podczas modelowania wymagań biznesowych mogą pojawić się wady. Wymagania biznesowe, opierając się na kluczu naturalnym, muszą być następnie przetłumaczone na klucz zastępczy. Strategia polega na wyraźnym rozróżnieniu między modelem logicznym (w którym nie występują klucze zastępcze) a fizyczną implementacją tego modelu, aby zapewnić, że model logiczny jest poprawny i dość dobrze znormalizowany, oraz aby zapewnić, że model fizyczny jest poprawna implementacja modelu logicznego.

Nieumyślne ujawnienie

Informacje zastrzeżone mogą zostać ujawnione, jeśli klucze zastępcze są generowane sekwencyjnie. Odejmując wcześniej wygenerowany klucz sekwencyjny od ostatnio wygenerowanego klucza sekwencyjnego, można poznać liczbę wierszy wstawionych w tym okresie. Może to ujawnić na przykład liczbę transakcji lub nowych rachunków w danym okresie. Na przykład zobacz problem z niemieckim czołgiem .

Jest kilka sposobów na rozwiązanie tego problemu:

  • zwiększyć numer sekwencyjny o losową kwotę;
  • wygeneruj losowy klucz, taki jak UUID .

Nieumyślne założenia

Sekwencyjnie generowane klucze zastępcze mogą sugerować, że zdarzenia o wyższej wartości klucza wystąpiły po zdarzeniach o niższej wartości. Niekoniecznie jest to prawdą, ponieważ takie wartości nie gwarantują sekwencji czasowej, ponieważ wstawianie może się nie powieść i pozostawić luki, które mogą zostać wypełnione w późniejszym czasie. Jeśli chronologia jest ważna, data i godzina muszą być rejestrowane oddzielnie.

Zobacz też

Bibliografia

Cytaty

Źródła