Dlaczego miałaby się zmieniać wartość klucza podstawowego?

Niedawno badałem koncepcję ROWGUID i natknąłem się na to pytanie. Ta odpowiedź dała wgląd, ale doprowadziła mnie do innej dziury królika ze wzmianką o zmianie wartości klucza podstawowego.

Rozumiałem zawsze, że klucz podstawowy powinien być niezmienny, a moje wyszukiwanie od czasu przeczytania tej odpowiedzi dostarczyło tylko odpowiedzi, które odzwierciedlają to samo, co najlepsza praktyka.

W jakich okolicznościach wartość klucza podstawowego musiałaby zostać zmieniona po utworzeniu rekordu?

Jeśli używasz nazwiska osoby jako klucza podstawowego, a jej nazwisko się zmieniło, musisz zmienić klucz podstawowy. To jest to, co ON UPDATE CASCADEsłuży do ponieważ zasadniczo kaskadach puch zmianę do wszystkich powiązanych tabel, które mają zagranicznych kluczowych relacji do klucza podstawowego.

Na przykład:

USE tempdb;
GO

CREATE TABLE dbo.People
(
    PersonKey VARCHAR(200) NOT NULL
        CONSTRAINT PK_People
        PRIMARY KEY CLUSTERED
    , BirthDate DATE NULL
) ON [PRIMARY];

CREATE TABLE dbo.PeopleAKA
(
    PersonAKAKey VARCHAR(200) NOT NULL
        CONSTRAINT PK_PeopleAKA
        PRIMARY KEY CLUSTERED
    , PersonKey VARCHAR(200) NOT NULL
        CONSTRAINT FK_PeopleAKA_People
        FOREIGN KEY REFERENCES dbo.People(PersonKey)
        ON UPDATE CASCADE
) ON [PRIMARY];

INSERT INTO dbo.People(PersonKey, BirthDate)
VALUES ('Joe Black', '1776-01-01');

INSERT INTO dbo.PeopleAKA(PersonAKAKey, PersonKey)
VALUES ('Death', 'Joe Black');

SELECTPrzeciwko obu tabelach:

SELECT *
FROM dbo.People p
    INNER JOIN dbo.PeopleAKA pa ON p.PersonKey = pa.PersonKey;

Zwroty:

Jeśli zaktualizujemy PersonKey kolumnę i ponownie uruchom SELECT:

UPDATE dbo.People
SET PersonKey = 'Mr Joe Black'
WHERE PersonKey = 'Joe Black';

SELECT *
FROM dbo.People p
    INNER JOIN dbo.PeopleAKA pa ON p.PersonKey = pa.PersonKey;

widzimy:

Patrząc na plan powyższej UPDATEinstrukcji, wyraźnie widzimy, że obie tabele są aktualizowane za pomocą pojedynczej instrukcji aktualizacji na podstawie klucza obcego zdefiniowanego jakoON UPDATE CASCADE :

^{kliknij obraz powyżej, aby zobaczyć go bardziej wyraziście}

Na koniec wyczyścimy nasze tabele tymczasowe:

DROP TABLE dbo.PeopleAKA;
DROP TABLE dbo.People;

Preferowany ¹ sposobem na wykonanie tego przy użyciu kluczy zastępczych byłoby:

USE tempdb;
GO

CREATE TABLE dbo.People
(
    PersonID INT NOT NULL IDENTITY(1,1)
        CONSTRAINT PK_People
        PRIMARY KEY CLUSTERED
    , PersonName VARCHAR(200) NOT NULL
    , BirthDate DATE NULL
) ON [PRIMARY];

CREATE TABLE dbo.PeopleAKA
(
    PersonAKAID INT NOT NULL IDENTITY(1,1)
        CONSTRAINT PK_PeopleAKA
        PRIMARY KEY CLUSTERED
    , PersonAKAName VARCHAR(200) NOT NULL
    , PersonID INT NOT NULL
        CONSTRAINT FK_PeopleAKA_People
        FOREIGN KEY REFERENCES dbo.People(PersonID)
        ON UPDATE CASCADE
) ON [PRIMARY];

INSERT INTO dbo.People(PersonName, BirthDate)
VALUES ('Joe Black', '1776-01-01');

INSERT INTO dbo.PeopleAKA(PersonID, PersonAKAName)
VALUES (1, 'Death');

SELECT *
FROM dbo.People p
    INNER JOIN dbo.PeopleAKA pa ON p.PersonID = pa.PersonID;

UPDATE dbo.People
SET PersonName = 'Mr Joe Black'
WHERE PersonID = 1;

Dla kompletności plan instrukcji aktualizacji jest bardzo prosty i wykazuje jedną zaletę zastępowania kluczy, mianowicie tylko jeden wiersz wymaga aktualizacji, a nie każdy wiersz zawierający klucz w scenariuszu z kluczem naturalnym:

SELECT *
FROM dbo.People p
    INNER JOIN dbo.PeopleAKA pa ON p.PersonID = pa.PersonID;

DROP TABLE dbo.PeopleAKA;
DROP TABLE dbo.People;

Dane wyjściowe z dwóch SELECTpowyższych instrukcji są następujące:

Zasadniczo wynik jest w przybliżeniu taki sam. Jedną z głównych różnic jest to, że szeroki naturalny klucz nie jest powtarzany w każdej tabeli, w której występuje klucz obcy. W moim przykładzie używam VARCHAR(200)kolumny do przechowywania nazwiska osoby, co wymaga użycia znakuVARCHAR(200) wszędzie . Jeśli istnieje wiele wierszy i wiele tabel zawierających klucz obcy, spowoduje to powstanie dużej ilości zmarnowanej pamięci. Uwaga: nie mówię o marnowaniu miejsca na dysku, ponieważ większość ludzi twierdzi, że miejsce na dysku jest tak tanie, że jest zasadniczo wolne. Pamięć jest jednak droga i zasługuje na pielęgnację. Użycie 4-bajtowej liczby całkowitej dla klucza pozwoli zaoszczędzić dużo pamięci, biorąc pod uwagę średnią długość nazwy wynoszącą około 15 znaków.

Styczne z pytaniem o to, jak i dlaczego klucze mogą się zmieniać, jest pytanie o to, dlaczego wybierać klucze naturalne zamiast kluczy zastępczych, co jest interesującym i być może ważniejszym pytaniem, szczególnie tam, gdzie wydajność jest celem projektowym. Zobacz moje pytanie tutaj na ten temat.

^{1 - http://weblogs.sqlteam.com/mladenp/archive/2009/10/06/Why-I-prefer-surrogate-keys-instead-of-natural-keys-in.aspx}

Chociaż możesz używać klucza, który jest naturalny i / lub zmienny jako PK, z mojego doświadczenia wynika, że ​​prowadzi to do problemów, którym często można zapobiec, stosując PK, który spełnia następujące warunki:

 Guaranteed Unique, Always Exists, Immutable, and Concise.

Na przykład wiele firm w USA próbuje używać numerów ubezpieczenia społecznego jako osobistych numerów identyfikacyjnych (i PK) w swoich systemach. Następnie napotykają następujące problemy - błędy wprowadzania danych prowadzące do wielu rekordów, które muszą zostać naprawione, ludzie, którzy nie mają SSN, ludzie, których SSN jest zmieniany przez rząd, ludzie, którzy mają zduplikowane SSN.

Widziałem każdy z tych scenariuszy. Widziałem także firmy, które nie chciały, aby ich klienci byli „tylko liczbą”, co oznaczało, że ich PK to „pierwszy + środkowy + ostatni + DOB + zip” lub podobne bzdury. Chociaż dodali wystarczającą liczbę pól, aby niemal zagwarantować unikalność, ich zapytania były przerażające, a aktualizacja któregokolwiek z tych pól oznaczała ściganie problemów z spójnością danych.

Z mojego doświadczenia wynika, że ​​PK generowane przez samą bazę danych jest prawie zawsze lepszym rozwiązaniem.

Polecam ten artykuł w celu uzyskania dodatkowych wskazówek: http://www.agiledata.org/essays/keys.html

Klucz podstawowy może zostać zmieniony podczas synchronizacji. Może tak być w przypadku odłączonego klienta, który synchronizuje dane z serwerem w określonych odstępach czasu.

Kilka lat temu pracowałem nad systemem, w którym wszystkie dane zdarzeń na lokalnej maszynie miały ujemne identyfikatory wierszy, takie jak -1, -2 itd. Gdy dane zostały zsynchronizowane z serwerem, identyfikator wiersza na serwerze został zastosowany do klient. Powiedzmy, że następny wiersz Id na serwerze to 58. Wtedy -1 będzie 58, -2 59 i tak dalej. Ta zmiana identyfikatora wiersza byłaby kaskadowa do wszystkich potomnych rekordów FK na komputerze lokalnym. Mechanizm ten wykorzystano również do ustalenia, które rekordy były wcześniej synchronizowane.

Nie twierdzę, że był to dobry projekt, ale jest to przykład zmiany klucza podstawowego w czasie.

Każdy projekt, który wymaga PRIMARY KEYregularnej zmiany, jest przepisem na katastrofę. Jedynym dobrym powodem do zmiany byłoby połączenie dwóch wcześniej oddzielnych baz danych.

Jak wskazał @MaxVernon, mogą wystąpić sporadyczne zmiany - następnie użyj ON UPDATE CASCADE, chociaż większość systemów obecnie używa identyfikatora jako surogatu PRIMARY KEY.

Puryści tacy jak Joe Celko i Fabian Pascal (strona, którą warto odwiedzić) nie zgadzają się z użyciem kluczy zastępczych, ale myślę, że przegrali tę konkretną bitwę.

Stabilność jest pożądaną właściwością klucza, ale jest względną rzeczą, a nie bezwzględną regułą. W praktyce często przydatna jest zmiana wartości kluczy. W kategoriach relacyjnych dane można rozpoznać tylko po ich (super) kluczach. Wynika z tego, że jeśli w danej tabeli jest tylko jeden klucz, wówczas różnica między A) zmianą wartości klucza, lub B) zastąpieniem zestawu wierszy w tabeli jakimś podobnym lub innym zestawem wierszy zawierającym inne wartości kluczy, jest zasadniczo kwestia semantyki zamiast logiki.

Bardziej interesującym przykładem jest tabela z wieloma kluczami, w której wartości jednego lub więcej z tych kluczy mogą wymagać zmiany w stosunku do innych wartości kluczy. Weźmy przykład tabeli pracowników z dwoma kluczami: LoginName i numer identyfikacyjny. Oto przykładowy wiersz z tej tabeli:

+---------+--------+
|LoginName|BadgeNum|
+---------+--------+
|ZoeS     |47832   |
+---------+--------+

Jeśli ZoeS straci swoją odznakę, może przydzielono jej nową i otrzyma nowy numer odznaki:

+---------+--------+
|LoginName|BadgeNum|
+---------+--------+
|ZoeS     |50282   |
+---------+--------+

Później może zdecydować o zmianie nazwy logowania:

+---------+--------+
|LoginName|BadgeNum|
+---------+--------+
|ZSmith   |50282   |
+---------+--------+

Obie kluczowe wartości uległy zmianie - względem siebie. Zauważ, że niekoniecznie robi to różnicę, która z nich jest uważana za „podstawową”.

W praktyce „niezmienność”, tzn. Absolutnie nigdy nie zmieniająca wartości, jest nieosiągalna lub przynajmniej niemożliwa do zweryfikowania. W zakresie, w jakim zmiana w ogóle robi różnicę, najbezpieczniejszym rozwiązaniem jest prawdopodobnie założenie, że dowolny klucz (lub dowolny atrybut) może wymagać zmiany.

Co ciekawe, powiązane pytanie dotyczące rodzaju ROWGUID zapewnia własny przypadek użycia: gdy masz sprzeczne klucze podstawowe w bazach danych, które wymagają synchronizacji. Jeśli masz dwie bazy danych, które wymagają uzgodnienia i używają sekwencji dla kluczy podstawowych, będziesz chciał zmienić jeden z kluczy, aby mógł pozostać unikalny.

W idealnym świecie tak się nigdy nie stanie. Na początek użyjesz identyfikatorów GUID dla kluczy podstawowych. Jednak realistycznie może nie być nawet rozproszonej bazy danych na początku projektowania, a konwersja jej na GUID mogła być wysiłkiem, który został potraktowany priorytetowo poniżej, ponieważ została uznana za większy wpływ niż wdrożenie aktualizacji klucza. Może się to zdarzyć, jeśli masz dużą bazę kodu, która zależy od kluczy liczb całkowitych i wymagałaby poważnej zmiany, aby przekonwertować na GUID. Istnieje również fakt, że rzadkie identyfikatory GUID (identyfikatory GUID, które nie są bardzo blisko siebie, co zdarza się, jeśli są generowane losowo tak, jak należy), mogą również powodować problemy z niektórymi rodzajami indeksów, co oznacza, że ​​chcesz uniknąć używania jako klucze podstawowe (wspomniane przez Byrona Jonesa ).

Jednym z możliwych scenariuszy jest założenie, że masz partnerów, którzy mają unikalny identyfikator i wiesz, że nie będą się powielać między partnerami, ponieważ mają unikalną postać początkową. Partnerzy ładują dane do tabeli głównej. Tam przetwarzane są rekordy, a następnie przypisywany identyfikator główny. Użytkownicy potrzebują dostępu do rekordów, gdy tylko zostaną załadowane, nawet jeśli nie zostały jeszcze przetworzone. Chcesz, aby identyfikator główny opierał się na przetwarzanym zamówieniu i nie zawsze będziesz przetwarzał w kolejności, w której rekordy zostały załadowane. Wiem trochę sfabrykowany.

Wyobraź sobie sytuację, w której ktoś wybrał numer ubezpieczenia społecznego (NIN) jako klucz podstawowy, a operator w jakiś sposób wstawia wiersz z niewłaściwym numerem NIN. Po wstawieniu wartości istnieją dwa sposoby skorygowania błędu:

1. Usuń błędny zapis i wstaw nowy
2. Zaktualizuj wartość do poprawnej i użyj opcji Kaskada aktualizacji, jeśli w tej kolumnie występuje ograniczenie integralności referencyjnej