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).