Auto increment w SQL - ID, luki i różnice między bazami

Gdy tworzę tabelę użytkowników, zamówień albo produktów, nie chcę ręcznie pilnować kolejnego numeru identyfikatora. Automatyczne zwiększanie wartości w SQL rozwiązuje ten problem, ale sposób konfiguracji zależy od silnika bazy danych. Pokażę, jak działa mechanizm auto increment w SQL, czym różnią się SQL Server, MySQL i PostgreSQL oraz na jakie pułapki uważać w aplikacjach .NET.

Jak działa automatyczne nadawanie identyfikatorów

Automatycznie zwiększana kolumna przechowuje numer nadawany przez bazę podczas dodawania nowego wiersza. Aplikacja wysyła dane biznesowe, takie jak nazwa produktu czy adres e-mail, a identyfikator powstaje po stronie bazy.

CREATE TABLE Products
(
    Id INT IDENTITY(1, 1) PRIMARY KEY,
    Name NVARCHAR(100) NOT NULL,
    Price DECIMAL(10, 2) NOT NULL
);

W tym przykładzie pierwszy rekord otrzyma numer 1, a każdy następny będzie zwiększany o 1. Parametry IDENTITY(1, 1) oznaczają kolejno wartość początkową i przyrost. W praktyce najczęściej zostawiam domyślne ustawienia, ponieważ ważniejsza od konkretnego numeru jest unikalność klucza.

Warto rozdzielić dwie rzeczy. Mechanizm auto increment generuje wartości, ale dopiero ograniczenie PRIMARY KEY albo UNIQUE wymusza, żeby nie pojawiły się duplikaty. Dlatego sama kolumna z automatycznym przyrostem nie powinna być traktowana jako pełna definicja klucza tabeli.

Podczas dodawania rekordu pomijam kolumnę identyfikatora:

INSERT INTO Products (Name, Price)
VALUES (N'Klawiatura mechaniczna', 299.00);

Baza przydzieli Id automatycznie. To bezpieczniejsze niż obliczanie wartości za pomocą MAX(Id) + 1. Taki wzorzec jest podatny na konflikt, gdy dwóch użytkowników zapisuje dane w tym samym momencie.

Składnia w SQL Server, MySQL i PostgreSQL

Idea jest podobna, ale składnia zależy od systemu. W projektach .NET najczęściej spotykam SQL Server i Azure SQL, jednak MySQL oraz PostgreSQL rozwiązują ten sam problem własnymi mechanizmami.

SQL Server i właściwość IDENTITY

CREATE TABLE Customers
(
    Id BIGINT IDENTITY(1, 1) PRIMARY KEY,
    Email NVARCHAR(320) NOT NULL UNIQUE,
    CreatedAt DATETIME2 NOT NULL
);

Dla małych tabel INT zwykle wystarczy, ponieważ mieści wartości do około 2,1 miliarda. Przy dużych systemach, intensywnym logowaniu zdarzeń albo tabelach telemetrycznych wybieram BIGINT. Zmiana typu po zapisaniu setek milionów rekordów może być kosztowną operacją.

MySQL i AUTO_INCREMENT

CREATE TABLE customers
(
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    email VARCHAR(320) NOT NULL,
    created_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_customers_email (email)
);

W MySQL można zmienić wartość początkową za pomocą opcji tabeli albo polecenia ALTER TABLE. Nie oznacza to jednak, że numery będą zawsze idealnie kolejne. Baza może zarezerwować wartość dla transakcji, która później zakończy się błędem.

PostgreSQL i kolumny identity

CREATE TABLE customers
(
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email VARCHAR(320) NOT NULL UNIQUE,
    created_at TIMESTAMP NOT NULL
);

W PostgreSQL spotkasz także starszy zapis SERIAL, na przykład SERIAL PRIMARY KEY. Nadal działa, lecz w nowych projektach preferuję kolumny identity, bo wyraźniej opisują intencję i są zgodne z nowocześniejszym stylem definiowania schematu.

Różnica między GENERATED ALWAYS a GENERATED BY DEFAULT ma znaczenie przy importach. Pierwszy wariant zasadniczo nie pozwala aplikacji podać własnego identyfikatora, a drugi dopuszcza wartość jawną, gdy jest potrzebna. Przy migracji danych często wybieram drugi wariant albo wykonuję import z użyciem specjalnych opcji konkretnego silnika.

Czy identyfikator zawsze będzie kolejną liczbą

Nie. Automatyczny przyrost oznacza nadawanie następnych wartości, ale nie obietnicę ciągłej numeracji. Jeśli transakcja pobierze numer 15, a później zostanie wycofana, kolejny rekord może otrzymać 16. Numer 15 nie musi wrócić do puli.

Najczęstsze przyczyny luk to:

• rollback transakcji, czyli wycofanie zapisu po przydzieleniu numeru,
• usunięcie istniejącego rekordu,
• równoległe inserty wykonywane przez wielu użytkowników,
• mechanizmy buforowania wartości w silniku bazy,
• ręczne ustawienie identyfikatora podczas importu.

To zwykle nie jest problem. Identyfikator techniczny ma wskazywać rekord, a nie tworzyć numerację księgową. Jeżeli biznes wymaga numerów bez luk, na przykład dla faktur, potrzebny jest osobny, świadomie zaprojektowany mechanizm. Zwykłe IDENTITY lub AUTO_INCREMENT nie spełnia takiego wymagania.

Nie należy też zakładać, że najwyższy identyfikator oznacza najnowszy rekord. Wystarczy opóźniona transakcja albo import starszych danych, aby kolejność numerów różniła się od kolejności zdarzeń. Do sortowania używam osobnej kolumny czasu, na przykład CreatedAt, a nie samego Id.

Usuwanie rekordów i ponowne używanie numerów

Po usunięciu rekordu o numerze 8 baza zazwyczaj nie przydzieli ponownie wartości 8. Taka luka jest normalna i pomaga uniknąć niejednoznaczności w logach, cache oraz integracjach. Ręczne „naprawianie” numeracji często powoduje więcej szkód niż korzyści.

Jeśli identyfikatory mają znaczenie tylko wewnątrz systemu, pozostawiam luki bez ingerencji. Gdy numer jest widoczny dla klienta, dodaję osobny numer zamówienia albo publiczny identyfikator, zamiast zmieniać zasady działania klucza głównego.

Jak pobrać wygenerowane ID w aplikacji .NET

Samo dodanie wiersza to dopiero połowa zadania. Aplikacja często musi znać wygenerowany identyfikator, żeby zapisać elementy podrzędne, na przykład pozycje zamówienia. Najbezpieczniej pobrać go w ramach tego samego polecenia, a nie wykonywać osobne zapytanie typu SELECT MAX(Id).

SQL Server

INSERT INTO Orders (CustomerId, CreatedAt)
OUTPUT INSERTED.Id
VALUES (@CustomerId, SYSUTCDATETIME());

Klauzula OUTPUT INSERTED.Id zwraca identyfikator właśnie dodanego rekordu. W kodzie korzystającym z ADO.NET mogę odczytać tę wartość przez ExecuteScalar. To rozwiązanie dobrze działa również wtedy, gdy wielu użytkowników zapisuje dane jednocześnie.

MySQL

INSERT INTO orders (customer_id, created_at)
VALUES (?, UTC_TIMESTAMP());

SELECT LAST_INSERT_ID();

LAST_INSERT_ID() jest powiązane z połączeniem, więc aplikacja nie powinna rozdzielać tych operacji między różne sesje. W bibliotekach .NET, takich jak konektory MySQL, często można odczytać tę wartość także z właściwości obiektu komendy.

Przeczytaj również: JOIN w MySQL bez błędów. Kiedy użyć INNER, LEFT i CROSS JOIN?

PostgreSQL

INSERT INTO orders (customer_id, created_at)
VALUES ($1, CURRENT_TIMESTAMP)
RETURNING id;

PostgreSQL pozwala zwrócić identyfikator bez dodatkowego zapytania dzięki RETURNING. Przy bardziej rozbudowanych operacjach można przenieść część logiki do procedury składowanej, ale warto świadomie zdecydować, gdzie ma znajdować się reguła biznesowa. Pomocne rozróżnienie między funkcją a procedurą opisuję w materiale o funkcjach SQL.

W Entity Framework Core zwykle nie muszę pisać ręcznie zapytania zwracającego numer. Po SaveChanges() właściwość klucza encji jest uzupełniana przez dostawcę bazy. Mimo to podczas diagnozowania problemów sprawdzam wygenerowany SQL, bo konfiguracja klucza w modelu musi odpowiadać rzeczywistemu schematowi.

Kiedy automatyczny numer nie wystarcza

Klucz liczbowy jest szybki, mały i wygodny w relacjach, dlatego dla wielu tabel pozostaje moim pierwszym wyborem. Nie używam go jednak bezrefleksyjnie jako identyfikatora pokazywanego w adresie URL albo przesyłanego między niezależnymi systemami.

W systemie rozproszonym dwa serwisy mogą wygenerować tę samą liczbę, jeśli każdy korzysta z własnej tabeli. W takiej sytuacji lepiej sprawdzi się UUID albo centralna usługa przydzielająca zakresy. Trzeba jednak pamiętać, że UUID zajmuje więcej miejsca w indeksach i może pogorszyć lokalność danych, szczególnie gdy jest generowany losowo.

Osobny temat stanowią migracje. Importując dane z innego systemu, mogę chwilowo podawać własne identyfikatory, ale po zakończeniu operacji trzeba sprawdzić, czy generator nie pozostaje za najwyższą wartością w tabeli. W przeciwnym razie kolejny insert może zakończyć się konfliktem klucza.

W tabelach przechowujących zdarzenia często zapisuję zarówno automatyczny Id, jak i czas UTC. Późniejsze raporty oraz filtrowanie zakresów dat powinny korzystać z kolumny czasu i odpowiednich funkcji silnika, na przykład z DATEADD w SQL Serverze, a nie z założenia, że większy numer oznacza późniejsze zdarzenie.

Nie przesadzam też z typem danych. BIGINT daje duży zapas, ale zwiększa rozmiar kluczy i indeksów względem INT. Dla tabeli, która realistycznie nie przekroczy kilkuset milionów wierszy, INT może być bardziej praktycznym wyborem. Decyzję podejmuję na podstawie tempa przyrostu danych, a nie samego faktu, że większy typ wygląda bezpieczniej.

Dobry klucz zaczyna się od właściwego założenia

Automatyczne zwiększanie wartości sprawdza się świetnie jako techniczny klucz główny. W SQL Server użyję IDENTITY, w MySQL AUTO_INCREMENT, a w PostgreSQL najczęściej kolumny IDENTITY. We wszystkich przypadkach pomijam identyfikator podczas zwykłego inserta i pobieram wygenerowaną wartość mechanizmem konkretnej bazy.

Najważniejsza decyzja nie dotyczy jednak samej składni. Trzeba ustalić, czy numer ma być wyłącznie wewnętrznym identyfikatorem, czy również numerem widocznym dla użytkownika, elementem integracji albo numeracją podlegającą kontroli biznesowej. Gdy odpowiem sobie na to pytanie na początku projektu, unikam później kosztownych migracji i prób naprawiania luk, które od początku były całkowicie normalne.