IDENTITY – autoinkrementacja kolumny w SQL Server

W SQL Server  autoinkrementację  pola ustawia się za pomocą IDENTITY. Najczęściej stosuje się tą klauzulę w celu inkremetacji kolumny z kluczem podstawowym. Za pomocą SQL Server Management Studio możemy tę czynność  wykonać wizualnie w następujący sposób. Podczas tworzenia tabeli, zaznaczamy kolumnę przechowującą wartość typu int. We właściwościach rozwijamy węzeł Identity Secification. Dla Is Identity z listy wybieramy Yes.

Zapisujemy  tabelę.

Od tej pory możemy pomijać wpisywanie id wprost do tabeli. Pomijanie tej kolumny spowoduje automatyczne zwiększanie o 1 wartości kolumny.

insert into tab(nazwa) values ('element1');
insert into tab(nazwa) values ('element2');

A oto zawartość tabeli:

A co się stanie, gdy nie wymienimy kolumn, a po value podamy w miejsce id wartość NULL. Są serwery baz danych, które pozwalają na wykonanie takiego zapytania, automatycznie wstawiając w miejsce null inkrementowaną wartość id.

insert into tab values (null, 'element3');

W wyniku otrzymamy błąd:

An explicit value for the identity column in table 'tab' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Jak widać SQL Server nie pozwala na takie działanie.

Czy autoinkrementacja musi rozpoczynać się od 1 i zwiększać o 1? Oczywiście, że nie. Wystarczy dokonanie odpowiedniej zmiany podczas tworzenia tabeli we właściwościach  zaznaczonej kolumny.

W powyższym przykładzie wartość startowa będzie wynosiła 100, a kolejna będzie powiększana o 5.

Utworzenie tabeli z autoinkrementacją możemy wykonać także za pomocą zapytania SQL.

create table tab 
(
  id int PRIMARY KEY IDENTITY,
  nazwa varchar(50) not null
);

Samo IDENTITY oznacza rozpoczęcie wartości od 1 i zwiększanie o 1.
Gdy chcemy, aby wartość startowa wynosiła 100, a kolejna była zwiększana o 5, musimy po IDENTITY podać w nawiasach te dane. Pierwsza jest wartością startową, druga po przecinku to wartość o którą będzie zwiększana ostatnia wartość.

create table tab 
(
  id int PRIMARY KEY IDENTITY(100,5),
  nazwa varchar(50) not null
);

Gdy ustawienia IDENTITY będziemy chcieli zmienić w SQL Server Management Studio np. z (1,1) na (100,5), podczas zapisu nazwy tabeli, pojawi się okno ostrzegawcze o próbie wykonania niedozwolonego kroku.

Napotkać możemy na jeszcze inny problem. Wyobraźmy sobie, że musieliśmy usunąć jeden z rekordów tabeli. Okazało się jednak, iż jest on z powrotem niezbędny.  Musimy więc wstawić konkretną wartość id mimo ustawionej inkrementacji:

insert into tab values( 12,'element3');

W odpowiedzi otrzymamy informację:

An explicit value for the identity column in table 'tab' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Powyższa informacja zawiera nie tylko ostrzeżenie, ale także podpowiedź na rozwiązanie problemu – polecenie SET IDENTITY_INSERT.
Po IDENTITY_INSERT podajemy nazwę bazy i schematu oraz nazwę tabeli i ON, jeśli chcemy włączyć opcję lub OFF, gdy ją chcemy wyłączyć. Oprócz tego w poleceniu insert musimy wprost wymienić nazwy kolumn. Tak więc najpierw ustawiamy dla tabeli tab opcję IDENTITY_INSERT na ON.

SET IDENTITY_INSERT BazaTest.dbo.tab ON;

Następnie modyfikujemy polecenie insert:

insert into BazaTest.dbo.tab(id, nazwa) values( 12,'element3');

Włączenie opcji powoduje zezwolenie na wstawianie dowolnej wartości dla id.
Teraz możemy wykonać wcześniejsze polecenie.

Jednak po wykonaniu polecenia należy natychmiast wyłączyć opcję IDENTITY_INSERT.

SET IDENTITY_INSERT BazaTest.dbo.tab OFF;

A czy włączona opcja IDENTITY_INSERT pozwoli nam na wstawienie wartości klucza o wartości istniejącej już w tabeli? Oczywiście, że nie. Zabezpiecza nas przed tym klucz podstawowy założony na kolumnie.

Violation of PRIMARY KEY constraint 'PK__tab__3213E83F13A189C3'. Cannot insert duplicate key in object 'dbo.tab'. The duplicate key value is (12).