Czy dobrą praktyką jest zawsze posiadanie klucza podstawowego liczby całkowitej z automatycznym zwiększaniem?

W moich bazach danych mam tendencję do posiadania klucza podstawowego z automatyczną inkrementacją liczb całkowitych z nazwą iddla każdej tworzonej przeze mnie tabeli, dzięki czemu mam unikalne wyszukiwanie dla każdego konkretnego wiersza.

Czy to jest uważane za zły pomysł? Czy są jakieś wady robienia tego w ten sposób? Czasami mam wiele wskaźników, takich jak id, profile_id, subscriptionsgdzie idjest unikalny identyfikator, profile_idlinki do zagranicznych idz Profiletabeli, itd.

A może istnieją scenariusze, w których nie chcesz dodawać takiego pola?

Posiadanie gwarantowanego unikalnego identyfikatora wiersza nigdy nie jest złym pomysłem. Chyba nie powinnam mówić nigdy - ale chodźmy w przeważającej większości przypadków, to dobry pomysł.

Teoretyczne potencjalne wady obejmują dodatkowy indeks do utrzymania i dodatkowe miejsce do przechowywania. To nigdy nie było wystarczającym powodem, aby nie korzystać z żadnego.

Nie zgadzam się ze wszystkimi wcześniejszymi odpowiedziami. Istnieje wiele powodów, dla których złym pomysłem jest dodanie pola automatycznego przyrostu we wszystkich tabelach.

Jeśli masz tabelę, w której nie ma oczywistych kluczy, pole automatycznego przyrostu wydaje się dobrym pomysłem. W końcu nie chcesz select * from blog where body = '[10000 character string]'. Wolisz select * from blog where id = 42. Twierdziłbym, że w większości tych przypadków tak naprawdę potrzebujesz unikalnego identyfikatora; nie sekwencyjny unikalny identyfikator. Prawdopodobnie chcesz zamiast tego użyć uniwersalnie unikalnego identyfikatora.

W większości baz danych są funkcje generujące losowe unikalne identyfikatory ( uuidw mysql, postgres. newidW mssql). Umożliwiają one generowanie danych do wielu baz danych, na różnych komputerach, w dowolnym momencie, bez połączenia sieciowego między nimi, i nadal łączą dane z zerowymi konfliktami. Umożliwia to łatwiejszą konfigurację wielu serwerów, a nawet centrów danych, na przykład z mikrousługami.

Pozwala to również uniknąć zgadywania adresów URL stron, do których nie powinny mieć dostępu. Jeśli istnieje, https://example.com/user/1263prawdopodobnie https://example.com/user/1262również. Może to pozwolić na automatyzację exploita zabezpieczającego na stronie profilu użytkownika.

Istnieje również wiele przypadków, w których kolumna z płynem jest bezużyteczna, a nawet szkodliwa. Załóżmy, że masz sieć społecznościową. Jest usersstół i friendsstół. Tabela znajomych zawiera dwie kolumny identyfikatora użytkownika i pole automatycznego przyrostu. Chcesz 3się zaprzyjaźnić 5, więc wstawiasz 3,5do bazy danych. Baza danych dodaje identyfikator automatycznego przyrostu i przechowuje 1,3,5. W jakiś sposób użytkownik ponownie 3klika przycisk „dodaj znajomego”. Wstawiasz 3,5ponownie do bazy danych, baza danych dodaje identyfikator automatycznego przyrostu i wstawia 2,3,5. Ale teraz 3i 5są ze sobą przyjaciółmi dwa razy! To marnowanie miejsca, a jeśli się nad tym zastanowić, to kolumna z auto-przyrostem. Wszystko, co musisz zobaczyć, czy aibsą przyjaciółmi to wybrać dla wiersza z tymi dwiema wartościami. Są one razem unikalnym identyfikatorem wiersza. (Można by pewnie chcesz zrobić napisać jakąś logikę, aby upewnić się 3,5i 5,3są deduplikacji).

Nadal istnieją przypadki, w których sekwencyjne identyfikatory mogą być przydatne, na przykład podczas budowania skracacza adresów URL, ale głównie (a nawet przy użyciu skrótu adresów URL) losowo generowany unikalny identyfikator jest tym, czego naprawdę chcesz użyć.

TL; DR: Użyj UUID zamiast automatycznego przyrostu, jeśli nie masz jeszcze unikalnego sposobu identyfikowania każdego wiersza.

Klucze autoincemental mają głównie zalety.

Ale niektóre możliwe wady mogą być:

• Jeśli masz klucz biznesowy, musisz również dodać unikalny indeks do tej kolumny, aby wymusić reguły biznesowe.
• Podczas przesyłania danych między dwiema bazami danych, zwłaszcza gdy dane znajdują się w więcej niż jednej tabeli (tj. Master / detail), nie jest to proste, ponieważ sekwencje nie są synchronizowane między bazami danych i najpierw trzeba utworzyć tabelę równoważności przy użyciu klucz biznesowy jako dopasowanie, aby wiedzieć, który identyfikator z źródłowej bazy danych odpowiada identyfikatorowi w docelowej bazie danych. Nie powinno to jednak stanowić problemu podczas przesyłania danych z / do izolowanych tabel.
• Wiele przedsiębiorstw ma narzędzia raportowania ad hoc, graficzne, typu „wskaż i kliknij”, „przeciągnij i upuść”. Ponieważ autoinkrementalne identyfikatory nie mają znaczenia, tego typu użytkownikom trudno będzie zrozumieć dane poza „aplikacją”.
• Jeśli przypadkowo zmodyfikujesz klucz biznesowy, istnieje szansa, że ​​nigdy nie odzyskasz tego wiersza, ponieważ nie masz już dla ludzi czegoś do zidentyfikowania. To raz spowodowało błąd na platformie BitCoin .
• Niektórzy projektanci dodają identyfikator do tabeli łączenia między dwiema tabelami, gdy PK powinien po prostu składać się z dwóch zagranicznych identyfikatorów. Oczywiście, jeśli tabela łączenia znajduje się między trzema lub więcej tabelami, wówczas autoinkrementalny identyfikator ma sens, ale wtedy musisz dodać unikalny klucz, gdy ma on zastosowanie do kombinacji FK w celu egzekwowania reguł biznesowych.

Oto sekcja artykułu w Wikipedii na temat wad kluczy zastępczych.

Wręcz przeciwnie, nie, NIE musisz zawsze mieć numerycznej PK AutoInc.

Jeśli dokładnie analizujesz swoje dane, często identyfikujesz naturalne klucze w danych. Często dzieje się tak, gdy dane mają istotne znaczenie dla firmy. Czasami PK są artefaktami ze starożytnych systemów, które użytkownicy biznesowi używają jako drugiego języka do opisywania atrybutów swojego systemu. Widziałem na przykład numery VIN pojazdów używane jako podstawowy klucz tabeli „Vehicle” w systemie zarządzania flotą.

Jakkolwiek powstało, JEŚLI masz już unikalny identyfikator, użyj go. Nie twórz drugiego, pozbawionego znaczenia klucza podstawowego; jest to marnotrawstwo i może powodować błędy.

Czasami można użyć AutoInc PK w celu wygenerowania znaczącej wartości dla klienta, np. Numerów polis. Ustawienie wartości początkowej na coś sensownego i stosowanie reguł biznesowych dotyczących zer wiodących itp. Jest to prawdopodobnie podejście „najlepsze z obu światów”.

Jeśli masz małą liczbę wartości, które są względnie statyczne, użyj wartości, które mają sens dla użytkownika systemu. Po co używać 1,2,3, gdy można użyć L, C, H, gdzie L, H i C reprezentują Życie, Samochód i Dom w kontekście „Typu ubezpieczenia”, lub, wracając do przykładu VIN, może skorzystać z „TO „dla Toyoty? Wszystkie samochody Toyata mają numer VIN, który zaczyna się od „DO”. To jedna rzecz, o której użytkownicy nie muszą pamiętać, co zmniejsza prawdopodobieństwo wprowadzenia błędów programistycznych i błędów użytkownika, a nawet może być użytecznym odpowiednikiem pełnego opisu w raportach zarządczych, co upraszcza raporty. pisać i być może szybciej generować.

Dalszy rozwój tego jest prawdopodobnie „mostem za daleko” i generalnie nie polecam go, ale dołączam go dla kompletności i możesz znaleźć dla niego dobre zastosowanie. Oznacza to, że użyj opisu jako klucza podstawowego. Dla szybko zmieniających się danych jest to obrzydliwość. W przypadku bardzo statycznych danych zgłaszanych przez cały czas , być może nie. Po prostu wspominając o tym, więc jest to możliwe.

Używam AutoInc PK, po prostu angażuję swój mózg i najpierw szukam lepszych alternatyw. Sztuka projektowania baz danych polega na tworzeniu czegoś znaczącego, na co można szybko uzyskać zapytanie. Zbyt wiele połączeń utrudnia to.

EDYCJA Innym kluczowym przypadkiem, w którym nie jest potrzebny autogenerowany PK, jest przypadek tabel reprezentujących przecięcie dwóch innych tabel. Aby trzymać się analogii samochodu, samochód ma 0..n Akcesoria, każde akcesorium można znaleźć w wielu samochodach. Aby to przedstawić, tworzysz tabelę Car_Accessory zawierającą PK z samochodu i akcesoriów oraz inne istotne informacje o dacie łącza itp.

To, czego nie potrzebujesz (zwykle), to AutoInc PK na tym stole - będzie dostępny tylko za pośrednictwem samochodu „powiedz mi, jakie akcesoria są w tym samochodzie” lub z akcesorium „powiedz im, jakie samochody mają to akcesorium”

Wiele tabel ma już naturalny unikalny identyfikator. Nie dodawaj kolejnej unikalnej kolumny identyfikatora (auto-przyrost lub w inny sposób) do tych tabel. Zamiast tego użyj naturalnego unikalnego identyfikatora. Jeśli dodasz inny unikalny identyfikator, zasadniczo masz nadmiarowość (powielanie lub zależność) w swoich danych. Jest to sprzeczne z zasadami normalizacji. Jeden unikalny identyfikator zależy od drugiego pod względem dokładności. Oznacza to, że muszą być idealnie zsynchronizowany w każdym czasie , w każdym systemie , która zarządza tymi wiersze. To po prostu kolejna niestabilność w integralności danych, której tak naprawdę nie chcesz zarządzać i sprawdzać w perspektywie długoterminowej.

Większość tabel w dzisiejszych czasach tak naprawdę nie potrzebuje bardzo niewielkiego zwiększenia wydajności, które dałaby dodatkowa unikalna kolumna identyfikatora (a czasem nawet obniża wydajność). Zasadniczo w IT należy unikać redundancji jak zarazy! Opieraj się wszędzie tam, gdzie jest to sugerowane. To klątwa. I zwróć uwagę na cytat. Wszystko powinno być tak proste, jak to możliwe, ale nie prostsze. Nie miej dwóch unikalnych identyfikatorów, w których wystarczy, nawet jeśli naturalny wydaje się mniej uporządkowany.

W większych systemach ID jest narzędziem zwiększającym spójność, używaj go niemal wszędzie. W tym kontekście NIE zaleca się używania pojedynczych kluczy podstawowych, ponieważ są one bardzo kosztowne (przeczytaj dlaczego).

Każda reguła ma wyjątek, więc może nie być potrzebny identyfikator autoinkrementacji liczb całkowitych w tabelach pomostowych używanych do eksportowania / importowania oraz w podobnych tabelach jednokierunkowych lub tabelach tymczasowych. Wolisz także GUID zamiast ID w systemach rozproszonych.

Wiele odpowiedzi tutaj sugeruje, że należy wziąć istniejący unikalny klucz. Cóż, nawet jeśli ma 150 znaków? Nie wydaje mi się

Teraz mój główny punkt:

Wygląda na to, że przeciwnicy identyfikatora liczb całkowitych z autoinkrementacją mówią o małych bazach danych zawierających do 20 tabel. Tam mogą sobie pozwolić na indywidualne podejście do każdego stołu.

ALE kiedy masz ERP z ponad 400 tabelami, posiadanie identyfikatora autoinkrementacji liczb całkowitych w dowolnym miejscu (z wyjątkiem przypadków wymienionych powyżej) po prostu ma sens. Nie polegasz na innych unikalnych polach, nawet jeśli są one obecne i zabezpieczone dla wyjątkowości.

• Korzystasz z uniwersalnej konwencji oszczędzającej czas, oszczędzającej wysiłek i łatwej do zapamiętania.
• W większości przypadków JOINtabele nie wymagają sprawdzania kluczy.
• Możesz mieć uniwersalne procedury kodu działające z kolumną autoinkrementacji liczb całkowitych.
• Możesz rozszerzyć swój system o nowe tabele lub wtyczki użytkownika, których wcześniej nie przewidziano, po prostu odwołując się do identyfikatorów istniejących tabel. Są już od samego początku, bez dodatkowych kosztów.

W większych systemach warto zignorować niewielkie zalety tych pojedynczych kluczy podstawowych i w większości przypadków konsekwentnie używać identyfikatora autoinkrementacji liczb całkowitych. Używanie istniejących unikatowych pól jako kluczy podstawowych może oszczędzać niektóre bajty na rekord, ale dodatkowy czas przechowywania lub indeksowania nie stanowi problemu w dzisiejszych silnikach baz danych. W rzeczywistości tracisz znacznie więcej pieniędzy i zasobów na zmarnowany czas deweloperów / opiekunów. Dzisiejsze oprogramowanie powinno być zoptymalizowane pod kątem czasu i wysiłku programistów - jakie podejście ze spójnymi identyfikatorami spełnia się znacznie lepiej.

Zbędne projekty nie są dobrą praktyką. To znaczy - nie jest dobrą praktyką, aby zawsze mieć automatyczny przyrost klucza podstawowego, gdy nie jest potrzebny.

Zobaczmy przykład, w którym nie jest potrzebny.

Masz tabelę artykułów - ma ona int klucz podstawowy idi kolumnę varchar o nazwie title.

Masz również tabelę pełną kategorii artykułów - idklucz główny int, varchar name.

Jeden wiersz w tabeli artykułów zawiera id5 i title „Jak gotować gęś z masłem”. Chcesz połączyć ten artykuł z następującymi wierszami w tabeli kategorii: „Ptactwo” ( id : 20), „Gęś” ( id : 12), „Gotowanie” ( id : 2), „Masło” (id: 9) .

Teraz masz 2 tabele: artykuły i kategorie. Jak tworzysz relacje między nimi?

Możesz mieć tabelę z 3 kolumnami: id (klucz podstawowy), article_id (klucz obcy), category_id (klucz obcy). Ale teraz masz coś takiego:

| id | a_id | c_id |
| 1 | 5 | 20 |
| 2 | 5 | 12 |
| 3 | 5 | 2 |

Lepszym rozwiązaniem jest posiadanie klucza podstawowego złożonego z 2 kolumn.

| a_id | c_id |
| 5 | 20 |
| 5 | 12 |
| 5 | 2 |

Można to osiągnąć, wykonując:

create table articles_categories (
  article_id bigint,
  category_id bigint,
  primary key (article_id, category_id)
) engine=InnoDB;

Innym powodem, dla którego nie należy używać liczb całkowitych automatycznego przyrostu, jest użycie UUID dla klucza podstawowego.

UUID są z definicji unikalne, co pozwala osiągnąć to samo, co przy użyciu unikatowych liczb całkowitych. Mają także swoje dodatkowe zalety (i wady) w stosunku do liczb całkowitych. Na przykład dzięki identyfikatorowi UUID wiesz, że niepowtarzalny ciąg, do którego się odwołujesz, wskazuje na konkretny rekord danych; jest to przydatne w przypadkach, gdy nie masz 1 centralnej bazy danych lub gdy aplikacje mają możliwość tworzenia rekordów danych w trybie offline (a następnie przesłania ich do bazy danych w późniejszym terminie).

W końcu nie musisz myśleć o kluczach podstawowych jako o czymś. Musisz myśleć o nich jako o funkcji, którą pełnią. Dlaczego potrzebujesz kluczy podstawowych? Aby móc jednoznacznie zidentyfikować określone zestawy danych z tabeli za pomocą pola, które nie zostanie zmienione w przyszłości. Czy potrzebujesz do tego konkretnej kolumny id, czy możesz oprzeć tę unikalną identyfikację na innych (niezmiennych) danych?

A może istnieją scenariusze, w których nie chcesz dodawać takiego pola?

Pewnie.

Przede wszystkim istnieją bazy danych, które nie mają żadnych autoinkrementów (np. Oracle, co z pewnością nie jest jednym z najmniejszych konkurentów w okolicy). To powinna być pierwsza wskazówka, że ​​nie wszyscy je lubią lub nie potrzebują.

Co ważniejsze, zastanów się, czym tak naprawdę jest identyfikator - jest to podstawowy klucz do twoich danych. Jeśli masz tabelę z innym kluczem podstawowym, nie potrzebujesz identyfikatora i nie powinieneś go mieć. Na przykład tabela (EMPLOYEE_ID, TEAM_ID)(gdzie każdy pracownik może być jednocześnie w kilku zespołach) ma jasno zdefiniowany klucz podstawowy składający się z tych dwóch identyfikatorów. Dodanie IDkolumny autowzrostu , która jest również kluczem podstawowym dla tej tabeli, nie miałoby żadnego sensu. Teraz masz za sobą 2 klucze podstawowe, a pierwsze słowo w „kluczu podstawowym” powinno dać ci wskazówkę, że naprawdę powinieneś mieć tylko jeden.

Zwykle używam kolumny „tożsamość” (liczba całkowita z automatyczną inkrementacją) podczas definiowania nowych tabel dla „długowiecznych” danych (rekordy, które spodziewam się wstawić raz i przechowywać w nieskończoność, nawet jeśli ostatecznie zostaną „usunięte logicznie” przez ustawienie pola bitowego ).

Jest kilka sytuacji, o których mogę myśleć, gdy nie chcesz ich używać, z których większość sprowadza się do scenariuszy, w których jedna tabela na jednej instancji bazy danych nie może być wiarygodnym źródłem nowych wartości identyfikatora:

• Gdy przyrostowe identyfikatory byłyby zbyt dużą informacją dla potencjalnego napastnika. Użycie kolumny tożsamości dla usług danych „publicznych” sprawia, że ​​jesteś podatny na „problem niemieckiego czołgu”; jeśli istnieje identyfikator 10234, to znaczy, że istnieje rekord 10233, 10232 itd., z powrotem do co najmniej rekordu 10001, a następnie łatwo jest sprawdzić rekord 1001, 101 i 1, aby dowiedzieć się, gdzie rozpoczęła się twoja kolumna tożsamości. Identyfikatory GUID V4 złożone głównie z danych losowych przerywają to przyrostowe zachowanie zgodnie z projektem, dlatego tylko dlatego, że istnieje jeden identyfikator GUID, identyfikator GUID utworzony przez zwiększenie lub zmniejszenie bajtu identyfikatora GUID niekoniecznie istnieje, co utrudnia atakującemu korzystanie z usługi do pobierania pojedynczych rekordów jako narzędzie zrzutu. Istnieją inne środki bezpieczeństwa, które mogą lepiej ograniczyć dostęp, ale to pomaga.
• W tabelach odsyłaczy M: M. To coś w rodzaju gimme, ale widziałem to już wcześniej. Jeśli istnieje relacja wiele do wielu między dwiema tabelami w bazie danych, rozwiązaniem podstawowym jest tabela odsyłaczy zawierająca kolumny kluczy obcych odwołujące się do PK każdej tabeli. PK tej tabeli powinien praktycznie zawsze być kluczem złożonym z dwóch kluczy obcych, aby uzyskać wbudowane zachowanie indeksu i zapewnić unikalność odwołań.
• Jeśli planujesz dużo wstawiać i usuwać na tym stole. Prawdopodobnie największą wadą kolumn tożsamości jest dodatkowa obręcz, przez którą musisz przejść, wstawiając wiersze z innej tabeli lub zapytania, w których chcesz zachować wartości klucza oryginalnej tabeli. Musisz włączyć „wstawianie tożsamości” (jednak odbywa się to w DBMS), a następnie ręcznie upewnić się, że wstawiane klucze są unikalne, a następnie po zakończeniu importu musisz ustawić licznik tożsamości w metadane tabeli do maksymalnej obecnej wartości. Jeśli ta operacja zdarza się często na tym stole, rozważ inny schemat PK.
• Do tabel rozproszonych.Kolumny tożsamości świetnie sprawdzają się w przypadku baz danych z pojedynczą instancją, par przełączania awaryjnego i innych scenariuszy, w których jedna instancja bazy danych jest jedynym organem dla całego schematu danych w danym momencie. Jednak jest tylko tyle, że możesz przejść i nadal mieć jeden komputer wystarczająco szybki. Wysyłka dziennika replikacji lub transakcji może zapewnić dodatkowe kopie tylko do odczytu, ale istnieje także ograniczenie skali tego rozwiązania. Wcześniej czy później będziesz potrzebować dwóch lub więcej instancji serwera obsługujących wstawianie danych, a następnie synchronizujących się ze sobą. Kiedy taka sytuacja się pojawi, będziesz potrzebować pola GUID zamiast pola przyrostowego, ponieważ większość DBMS jest wstępnie skonfigurowana do używania części identyfikatorów GUID, które generują jako identyfikatora specyficznego dla instancji, a następnie generowania pozostałej części losowo lub przyrostowo. W obu przypadkach,
• Gdy musisz wymusić unikalność w wielu tabelach w bazie danych.Na przykład w systemach księgowych zarządzanie Księgą Główną (z wierszem dla każdego kredytu lub obciążenia każdego konta, które kiedykolwiek miało miejsce, więc robi się bardzo duże bardzo szybko) jako sekwencja tabel reprezentujących jeden miesiąc kalendarzowy / rok. Następnie można utworzyć widoki, aby połączyć je w celu raportowania. Logicznie rzecz biorąc, to wszystko jest jednym bardzo dużym stołem, ale rozdrobnienie go ułatwia prace konserwacyjne DB. Przedstawia jednak problem zarządzania wstawieniami w wielu tabelach (umożliwiając rozpoczęcie rejestrowania transakcji w następnym miesiącu przy jednoczesnym zamykaniu ostatniego) bez kończenia na duplikatach kluczy. Ponownie, GUID zamiast kolumn liczb całkowitych tożsamości są rozwiązaniem, ponieważ DBMS jest zaprojektowany do generowania ich w naprawdę unikalny sposób,

Istnieją obejścia, które pozwalają na użycie kolumn tożsamości w takich sytuacjach, jak mam nadzieję, wspomniałem, ale w większości z nich przejście z kolumny liczby całkowitej tożsamości na identyfikator GUID jest prostsze i rozwiązuje problem w sposób bardziej kompletny.

Klucz inkrementowany automatycznie (tożsamość) jest dobrym pomysłem, z wyjątkiem tego, że nie ma znaczenia poza kontekstem bazy danych i bezpośrednimi klientami tej bazy danych. Na przykład, jeśli przesyłasz i przechowujesz niektóre dane w innej bazie danych, a następnie kontynuujesz zapisywanie różnych danych do obu tabel bazy danych, identyfikatory będą się różnić - tzn. Dane o identyfikatorze 42 w jednej bazie danych niekoniecznie będą pasować do danych z identyfikatorem 42 w drugim.

Biorąc to pod uwagę, jeśli konieczne jest, aby nadal móc jednoznacznie identyfikować wiersze poza bazą danych (a często tak jest), musisz mieć inny klucz do tego celu. Wystarczy starannie wybrany klucz biznesowy, ale często będziesz musiał znaleźć się w pozycji dużej liczby kolumn wymaganych do zagwarantowania wyjątkowości. Inną techniką jest posiadanie kolumny Id jako klucza podstawowego klastrowanego z automatycznym przyrostem oraz kolejnej kolumny unikalnego identyfikatora (guid) jako nieklastrowego unikalnego klucza, w celu unikatowej identyfikacji wiersza, gdziekolwiek istnieje na świecie. W tym przypadku nadal masz klucz z auto-inkrementacją, ponieważ wydajniejsze jest klastrowanie i indeksowanie klucza z auto-inkrementacją niż w przypadku guid.

Jednym z przypadków, w których możesz nie chcieć klucza automatycznego zwiększania, byłaby tabela wiele do wielu, w której klucz podstawowy jest złożeniem kolumn Id dwóch innych tabel (nadal możesz mieć tutaj klucz automatycznego zwiększania, ale ja nie widzę sensu).

Kolejnym pytaniem jest typ danych klucza automatycznie zwiększanego. Korzystanie z Int32 daje duży, ale stosunkowo ograniczony zakres wartości. Osobiście często używam kolumn bigint dla identyfikatora, aby praktycznie nigdy nie musieć się martwić, że zabraknie wartości.

Gdy inne osoby opowiedziały się za zwiększaniem klucza podstawowego, stworzę go dla GUID:

• Gwarantuje to, że jest wyjątkowy
• Możesz mieć o jedną krótszą podróż do bazy danych dla danych w swojej aplikacji. (Na przykład w przypadku tabeli typów można zapisać identyfikator GUID w aplikacji i użyć go do pobrania rekordu. Jeśli używasz tożsamości, musisz zapytać bazę danych według nazwy, a widziałem wiele aplikacji, które robią to, aby uzyskać PK a później ponownie go zapyta, aby uzyskać pełne informacje).
• Jest to przydatne do ukrywania danych. www.domain.com/Article/2 Informuje, że masz tylko dwa artykuły, podczas gdy www.domain.com/article/b08a91c5-67fc-449f-8a50-ffdf2403444a nic mi nie mówi.
• Możesz łatwo łączyć rekordy z różnych baz danych.
• MSFT używa GUID do identyfikacji.

Edycja: zduplikowany punkt

Zasadą dobrego projektu jest to, że każdy stół powinien mieć niezawodny sposób na jednoznaczną identyfikację rzędu. Chociaż do tego właśnie służy klucz podstawowy, nie zawsze wymaga on istnienia klucza podstawowego. Dodanie klucza podstawowego do każdej tabeli nie jest złą praktyką, ponieważ zapewnia unikalną identyfikację wiersza, ale może być niepotrzebne.

Aby zachować niezawodne relacje między wierszami dwóch lub więcej tabel, musisz to zrobić za pomocą kluczy obcych, stąd potrzeba kluczy podstawowych w co najmniej niektórych tabelach. Dodanie klucza podstawowego do każdej tabeli ułatwia rozszerzenie projektu bazy danych, gdy przychodzi czas na dodanie nowych tabel lub relacji do istniejących danych. Planowanie z wyprzedzeniem jest zawsze dobrą rzeczą.

Zgodnie z podstawową zasadą (być może twardą zasadą) wartość klucza podstawowego nigdy nie powinna się zmieniać przez cały okres jego użytkowania. Mądrze jest założyć, że dowolne dane biznesowe z rzędu mogą ulec zmianie w trakcie ich użytkowania, więc wszelkie dane biznesowe będą kiepskim kandydatem na klucz podstawowy. Dlatego coś abstrakcyjnego, na przykład liczba całkowita z automatyczną inkrementacją, jest często dobrym pomysłem. Jednak automatycznie zwiększane liczby całkowite mają swoje ograniczenia.

Jeśli twoje dane będą miały tylko żywotność w bazie danych, automatycznie zwiększane liczby całkowite są w porządku. Ale, jak wspomniano w innych odpowiedziach, jeśli kiedykolwiek chcesz, aby twoje dane były udostępniane, synchronizowane lub w inny sposób miały życie poza bazą danych, automatycznie zwiększane liczby całkowite powodują słabe klucze podstawowe. Lepszym wyborem będzie przewodnik (aka uuid „uniwersalnie unikalny identyfikator”).

Pytanie i wiele odpowiedzi pomijają ważny punkt, w którym wszystkie klucze naturalne dla każdej tabeli znajdują się wyłącznie w schemacie logicznym bazy danych, a wszystkie klucze zastępcze dla każdej tabeli znajdują się wyłącznie w schemacie fizycznym bazy danych. inne odpowiedzi omawiają wyłącznie względne korzyści wynikające z liczby całkowitej w porównaniu z kluczami zastępczymi GUID, bez omawiania powodów, dla których klucze zastępcze są właściwie używane i kiedy.

BTW: Unikajmy używania źle zdefiniowanego i nieprecyzyjnego klucza podstawowego . Jest to artefakt przedrelacyjnych modeli danych, który najpierw został (nierozsądnie) przyjęty do modelu relacyjnego, a następnie z powrotem do domeny fizycznej przez różnych dostawców RDBMS. Jego użycie służy jedynie do pomieszania semantyki.

Należy zauważyć z modelu relacyjnego, że aby schemat logiczny bazy danych był w pierwszej normalnej formie , każda tabela musi mieć widoczny dla użytkownika zestaw pól, zwany kluczem naturalnym, który jednoznacznie identyfikuje każdy wiersz tabeli. W większości przypadków taki naturalny klucz można łatwo zidentyfikować, ale czasami trzeba go zbudować, czy to jako pole przerywacza remisu, czy w inny sposób. Jednak taki skonstruowany klucz jest zawsze widoczny dla użytkownika i dlatego zawsze znajduje się w logicznym schemacie bazy danych.

W przeciwieństwie do tego, każdy klucz zastępczy w tabeli znajduje się wyłącznie w fizycznym schemacie bazy danych (i dlatego musi zawsze, zarówno ze względów bezpieczeństwa, jak i dla zachowania integralności bazy danych, być całkowicie niewidoczny dla użytkowników bazy danych). Jedynym powodem wprowadzenia klucza zastępczego jest rozwiązanie problemów związanych z wydajnością w fizycznym utrzymaniu i korzystaniu z bazy danych; niezależnie od tego, czy są to sprzężenia, replikacja, wiele źródeł sprzętowych danych lub inne.

Ponieważ jedynym powodem wprowadzenia klucza zastępczego jest wydajność, załóżmy, że chcemy, aby był wydajny. Jeśli chodzi o dołączenie problemu z wydajnością, to koniecznie chcemy, aby nasz klucz zastępczy był tak wąski, jak to tylko możliwe (bez przeszkadzania sprzętowi, tak więc zwykle brakuje krótkich liczb całkowitych i bajtów). Wydajność łączenia zależy od minimalnej wysokości indeksu, więc 4-bajtowa liczba całkowita jest naturalnym rozwiązaniem. Jeśli problemem z wydajnością jest szybkość wstawiania, naturalnym rozwiązaniem może być również 4-bajtowa liczba całkowita (w zależności od wewnętrznych elementów RDBMS). Jeśli problemem z wydajnością dla tabeli jest replikacja lub wiele źródeł danych niż jakaś inna technologia klucza zastępczego , może to być GUID lub dwuczęściowy klucz (identyfikator hosta + liczba całkowita). Osobiście nie jestem ulubieńcem GUID, ale są one wygodne.

Podsumowując, nie wszystkie tabele będą wymagać klucza zastępczego (dowolnego typu); należy ich używać tylko wtedy, gdy zostanie to uznane za konieczne do wykonania rozważanej tabeli. Bez względu na to, jaką preferujesz technologię zastępczą, przed dokonaniem wyboru dokładnie zastanów się nad rzeczywistymi potrzebami stołu; zmiana surogatu na kluczową technologię dla stołu będzie męczącą pracą. Dokumentuj kluczowe wskaźniki wydajności dla tabeli, aby Twoi następcy zrozumieli dokonane wybory.

Przypadki specjalne

1. Jeśli wymagania biznesowe wymagają sekwencyjnej numeracji transakcji do celów audytu (lub innych), to pole nie jest kluczem zastępczym; jest to naturalny klucz (z dodatkowymi wymaganiami). Z dokumentacji automatyczna liczba całkowita generuje tylko klucze zastępcze , więc znajdź inny mechanizm do jej wygenerowania. Oczywiście niezbędny będzie jakiś monitor, a jeśli pozyskujesz transakcje z wielu witryn, jedna witryna będzie wyjątkowa , ponieważ jest wyznaczoną witryną hosta dla monitora.

2. Jeśli Twój stół nigdy nie będzie miał więcej niż około stu rzędów, wówczas wysokość indeksu nie ma znaczenia; każdy dostęp będzie przez skanowanie tabeli. Jednak porównania ciągów na długich ciągach będą nadal znacznie droższe niż porównanie 4-bajtowej liczby całkowitej i droższe niż porównanie GUID.

3. Tabela wartości kodu wpisywana w polu kodu char (4) powinna być tak samo wydajna jak tabela z 4-bajtową liczbą całkowitą. Chociaż nie mam na to dowodu, często używam tego założenia i nigdy nie miałem powodu, aby to przekręcać.

Nie tylko nie jest to dobra praktyka, w rzeczywistości jest opisana jako anty-wzór w książce Billa Karwina SQL Antipatterns.

Nie każda tabela potrzebuje pseudoklucza - klucza podstawowego o dowolnej wartości, a nie czegoś, co ma wartość semantyczną dla modelu - i nie ma powodu, aby zawsze go wywoływać id.

Jest to dość uniwersalne - w przeciwnym razie należy zweryfikować, czy klucz jest rzeczywiście unikalny. Można to zrobić, patrząc na wszystkie pozostałe klucze ... co byłoby czasochłonne. Posiadanie klucza przyrostowego staje się kosztowne, ponieważ liczba rekordów zbliża się do wartości przepełnienia klucza.

Zazwyczaj zmieniam wskaźniki na bardziej oczywiste nazwy pól, takie jak ref_{table}lub podobny pomysł.

Jeśli zewnętrzne wskazanie rekordu nie jest konieczne, nie potrzebujesz identyfikatora.

Nie powiedziałbym, że zawsze należy to zrobić. Mam tutaj stolik bez unikalnego klucza - i on go nie potrzebuje. To dziennik kontroli. Nigdy nie będzie aktualizacji, zapytania zwrócą wszystkie zmiany do tego, co jest rejestrowane, ale jest to najlepsze, co można rozsądnie zrobić, aby zdefiniować nieprawidłową zmianę. (Gdyby kod mógł go w pierwszej kolejności zabronić!)

Automatyczny licznik przyrostu klucza podstawowego nie jest dobrym pomysłem. Dzieje się tak, ponieważ przed wstawieniem danych musisz wrócić do bazy danych, aby znaleźć następny klucz i zwiększyć go o jeden.

Biorąc to pod uwagę, ogólnie używałbym wszystkiego, co baza danych może zapewnić dla klucza podstawowego, zamiast mieć go jako część aplikacji.

Pozwalając na natywną bazę danych, może zagwarantować, że klucz będzie unikalny pod względem potrzeb.

Oczywiście nie wszystkie bazy danych obsługują to. W takim przypadku zazwyczaj używam tabeli przechowującej kluczowe zestawy i używam wysokich i niskich zakresów zarządzanych w aplikacji. Jest to najbardziej wydajne rozwiązanie, jakie znalazłem, ponieważ otrzymujesz zakres 10000 liczb i automatycznie inkrementujesz je w instancji aplikacji. Inna instancja aplikacji może wybrać inny zestaw liczb do pracy. Potrzebny jest wystarczająco duży prymityw klucza podstawowego, taki jak 64-bitowy.

Identyfikatory UUID, których nie używam jako kluczy podstawowych, ponieważ koszt ich zbudowania i przechowywania jest znacznie wyższy niż zwiększenie długiej wartości o jeden. UUID nadal radzą sobie z paradoksem urodzinowym, ponieważ teoretycznie może powstać duplikat.