Jak wprowadzić wartość w kolumnę IDENTITY? Opcja IDENTITY_INSERT.

Identity to dobry sposób na to by SQL Serwer zwolnił nas z konieczności wymyślania klucza podstawowego. Serwer sam będzie generował kolejne numery dla nowo wstawianych rekordów, więc każdy rekord będzie miał niezależny i unikalny numer, gdyby tylko nie to, że kolumna Identity nie musi być unikalna….

Przyjrzyjmy się tabeli:

CREATE TABLE dbo.Test
(
 Id int IDENTITY,
 Name VARCHAR(30)
);
GO

Zazwyczaj wstawiasz tu rekordy wykorzystując polecenie INSERT w postaci:

INSERT dbo.Test VALUES (’Record #1′);

lub

INSERT dbo.Test (Name) VALUES (’Record #2′);

A gdybyś spróbował wstawić rekord poleceniem

INSERT dbo.Test (Id, Name) VALUES (1, 'Record #3′);

to otrzymasz komunikat o błędzie:

Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'Test’ when IDENTITY_INSERT is set to OFF.

Można jednak sprawić, aby mimo tego, że kolumna ma typ Identity wstawić do niej określoną wartość! Zobacz:

SET IDENTITY_INSERT dbo.Test ON;

Od tej pory sytuacja się odwróciła. Teraz aby wstawić rekord musisz podać ID. Zadziała więc polecenie:

INSERT dbo.Test (Id, Name) VALUES (1, 'Record #3′);

ale polecenie

INSERT dbo.Test VALUES (’Record #1′);

skończy się błędem:

Msg 545, Level 16, State 1, Line 1
Explicit value must be specified for identity column in table 'Test’ either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.

 Możliwość wstawiania samodzielnie wartości do kolumny Identity została włączona tylko na skalę pojedyńczej sesji użytkownika. Inni użytkownicy pracują z tabelą w trybie IDENTITY_INSERT ON.

Więcej. Tryb ten możesz na raz włączyć tylko dla jednej tabeli w sersji.
Gdyby spróbować założyć drugą tabelę:

CREATE TABLE dbo.Test2
(
 Id int IDENTITY,
 Name VARCHAR(30)
);

i chcieć na niej wyłączyć tryb IDENTITY_INSERT:

SET IDENTITY_INSERT dbo.Test2 ON;

to otrzymasz błąd:

Msg 8107, Level 16, State 1, Line 2
IDENTITY_INSERT is already ON for table 'Testy.dbo.Test’. Cannot perform SET operation for table 'dbo.Test2′.

Wyłączenie trybu IDENTITY_INSERT wykonasz poleceniem:

SET IDENTITY_INSERT dbo.Test OFF;

Na dodatek zauważ, że wskutek ręcznego wstawiania IDENTITY doporowadziliśmy do sytuacji, że dane w kolumnie ID nie są uniklane. Jeżeli więc chcesz, aby kolumna oparta o IDENTITY miała wartości unikalne i pełniła funkcję klucza podstawowego, należy połączyć ją z CONTRAINT PRIMARY KEY. Unikalność zapewnisz także budując indeks uniklany lub nakładając CONTRAINT UNIQUE.

Jeżeli temat Cię zainteresował, to zapraszam na szkolenia Microsoft w Opolu, Katowicach i Warszawie do autoryzowanego ośrodka szkoleniowego bit Polska na szkolenie 6231 Maintaining a Microsoft SQL Server 2008 Database .

Komentarze są wyłączone