Bazy danych

Własność IDENTITY_INSERT (T-SQL)

Opis własności INSERT_IDENTITY języka baz danych T-SQL z przykładami kodu. W artykule pokazano sposób modyfikacji pól automatycznie numerowanych IDENTITY.

***

Sładnia

SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON | OFF }

gdzie

database_name – nazwa bazy danych (opcjonalnie)
schema_name – nazwa schematu bazy danych (opcjonalnie)
table – nazwa tabeli bazy danych (wymagane)

Pola tabeli typu IDENTITY nie pozwalają na wpisywanie do nich wartości poleceniem INSERT INTO, ponieważ są numerowane automatycznie. Jest to sytuacja typowa.

Dla przykładowej tabeli utworzonej kwerendą:

CREATE TABLE Names(
   ID INT IDENTITY NOT NULL PRIMARY KEY, 
   Name VARCHAR(20) NOT NULL
)

podczas dodawania kolejnych rekordów podajemy tylko wartości pola Name. Podawanie wartości pola ID byłoby błędem.

INSERT INTO Names (Name) VALUES ('Tomek')
INSERT INTO Names (Name) VALUES ('Marta')
INSERT INTO Names (Name) VALUES ('Ewa')
INSERT INTO Names (Name) VALUES ('Piotr')
INSERT INTO Names (Name) VALUES ('Anna')

Po wykonaniu poleceń powyżej otrzymujemy tabelę:

00_tabela_names.png

Po usunięciu z tabeli rekordu z numerem 4 poleceniem:

DELETE Names WHERE ID=4

otrzymujemy:

01_tabela_names_2.png

Może się zdarzyć, że usunięcie rekordu było efektem błędu użytkownika i trzeba ten błąd naprawić. Wtedy możemy się posłużyć własnością IDENTITY_INSERT w sposób następujący:

SET IDENTITY_INSERT Names ON
INSERT INTO Names (ID, Name) VALUES (4, 'Piotr') 
SET IDENTITY_INSERT Names OFF

Zwróćmy uwagę na to, że nazwa i wartość pola ID zostały podane.

Inny przykład to czasami stosowane zakładanie nowych baz danych dla kolejnych lat. Wtedy większość tabel jest tworzona na nowo i od nowego roku pola typu IDENTITY są numerowane od 1, ale być może część tabel trzeba pozostawić z niezmienionymi wartościami kluczy ze względu na istniejące relacje np. 1:n

02_tabele_w_relacji_1n.png

Mamy dwie tabele powiązane relacją 1:n przez pole IdCompany (kolor żółty).

Zakładając bazę danych na nowy rok musimy zachować wartości klucza głównego IdCompany (zakładamy, że typu IDENTITY) w tabeli Companies (Firmy) i klucza obcego w IdCompanies w tabeli Products (Produkty).

O ile klucz obcy IdCompany w tabeli Products nie wymaga szczególnego postępowania, o tyle klucz główny IdCompany w tabeli Companies tego wymaga.

Załóżmy że nowa baza danych wraz z pustymi tabelami została utworzona. Poprzednia baza nazywała się DB2017, a nazwa nowej bazy to DB2018.

Wykonujemy kopiowanie danych kwerendą:

SET IDENTITY_INSERT DB2018.dbo.Companies ON
INSERT INTO DB2018.dbo.Companies (IdCompany, Company)
SELECT IdCompany, Company FROM DB2017.dbo.Companies
SET IDENTITY_INSERT DB2018.dbo.Companies OFF

Uwaga: Nazwy pól musimy podać wymieniając je na liście. Nie można użyć znaku * (gwiazdka) w odniesieniu do wszystkich odczytywanych pól.

Bazy danych

0 komentarzy