Colunas Identity Parte-II
Exemplos práticos
por Paulo Ribeiro
Colunas com a propriedade identity são utilizadas para geração de números inteiros auto-incrementáveis, sendo bastante úteis na construção de relacionamentos como alvo de primary-keys. O objetivo dessa matéria será fornecer exemplos práticos da utilização de colunas identity, assunto que foi iniciado em Colunas Identity Parte I: para que servem e como criar.
Nos exemplos dessa matéria serão utilizadas duas tabelas: pedido e item_pedido, cuja estrutura está relacionada na Listagem 1.
create table pedido
(
id_pedido int not null identity (1,1)
,dt_emissao smalldatetime not null
,id_emitente int not null
)
go
create table item_pedido
(
id_item_pedido int not null identity (1,1)
,id_pedido int not null
,cod_produto int not null
,qtde int not null
,vlr_unitario dec(8,2) not null
,vlr_descto_unitario dec(8,2) not null
)
alter table pedido add constraint pk_pedido primary key clustered (id_pedido)
alter table item_pedido add constraint pk_item_pedido primary key nonclustered (id_item_pedido)
alter table item_pedido add constraint fk_item_pedido_pedido foreign key (id_pedido) references pedido (id_pedido)
alter table item_pedido add constraint un_item_pedido unique clustered (id_pedido,cod_produto)
go
Listagem 1. Script para criação das tabelas pedido e item_pedido
Como inserir linhas em tabelas que possuem colunas identity
Como a propriedade identity gera um número auto-incrementável, não se deve atribuir valores para colunas identity no comando de inserção. Se tentássemos atribuir um valor para a coluna id_pedido – que possui a propriedade identity habilitada – durante uma inserção na tabela pedido, seríamos impedidos pela exception 8101 (ver Listagem 2)
insert into pedido
values (1,'2005-09-01',204)
----------------------------------------------------------------------------------------------------
Server: Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'pedido' can only be specified when a column list is used and IDENTITY_INSERT is ON.
Listagem 2. Erro ao tentar atribuir um valor para coluna identity
Para que o comando INSERT na tabela pedido seja concluído com sucesso, vamos alterar a linha de comando especificando todas as colunas - exceto id_pedido (ver Listagem 3).
insert into pedido (dt_emissao, id_emitente)
values ('2005-09-01',204)
----------------------------------------------------------------------------------------------------
(1 row(s) affected)
Listagem 3. Inserindo linhas em tabelas que possuem colunas identity
Bem, agora que o pedido foi inserido com sucesso vamos a uma nova questão: qual foi o número do pedido gerado automaticamente pela propriedade identity no comando INSERT da Listagem 3?
Consultando o valor gerado na coluna identity com @@IDENTITY
A variável de ambiente @@IDENTITY armazena o último identity gerado na sessão. Assim, se quiséssemos prosseguir com o cadastramento do item de pedido (tabela item_pedido), precisaríamos consultar o valor de @@IDENTITY para que pudéssemos informá-lo no cadastramento do item (ver Listagem 4).
set NOCOUNT on
declare @id_pedido int
--inserindo um pedido (a coluna id_pedido nao foi informada)
insert into pedido (dt_emissao, id_emitente)
values ('2005-09-01',204)
-- resgatando o numero do pedido
select @id_pedido = @@IDENTITY
-- inserindo o detalhe do pedido
insert into item_pedido (id_pedido, cod_produto, qtde, vlr_unitario, vlr_descto_unitario)
values (@id_pedido,101014, 5, 10.50, 1.00)
----------------------------------------------------------------------------------------------------
id_pedido dt_emissao id_emitente
----------- ------------------------------------------------------ -----------
2 2005-09-01 00:00:00 204
id_item_pedido id_pedido cod_produto qtde vlr_unitario vlr_descto_unitario
-------------- ----------- ----------- ----------- ------------ -------------------
1 2 101014 5 10.50 1.00
Listagem 4. Consultando o valor identity após inserção com @@IDENTITY
Se excluirmos linhas da tabela pedido, estaremos criando “buracos” na numeração de pedidos. Dependendo da situação, pode ser necessário inserções pontuais, especificando-se a numeração do pedido que se deseja incluir.
Como atualizar manualmente colunas identity durante a inserção
Se inserirmos os pedidos 1,2,3,4,5 e depois excluirmos o pedido 3, esse número não será reutilizado por outro pedido: a numeração ficará com uma “falha” no pedido que foi excluído. Par incluir outro pedido com o mesmo id do pedido excluído (=3), teremos que desabilitar a propriedade identity no comando de inserção com o comando SET IDENTITY_INSERT ON. O “desligamento” da propriedade identity ficará ativo apenas na sessão, permanecendo assim até que a sessão seja finalizada ou o comando SET IDENTITY_INSERT OFF seja executado. (ver Listagem 5).
set nocount ON
-- inserindo pedidos 3,4 e 5
insert into pedido (dt_emissao,id_emitente) values ('2005-09-02',205)
select id_pedido=@@identity
insert into pedido (dt_emissao,id_emitente) values ('2005-09-03',206)
select id_pedido=@@identity
insert into pedido (dt_emissao,id_emitente) values ('2005-09-04',207)
select id_pedido=@@identity
go
-- excluindo pedido 3
delete from pedido where id_pedido=3
go
-- ligando a propriedade identity_insert
set IDENTITY_INSERT pedido ON
-- incluindo pedidos
insert into pedido (id_pedido,dt_emissao,id_emitente)
values (3,'2005-09-05',208) -- incluindo outro pedido com id_pedido=3
insert into pedido (id_pedido,dt_emissao,id_emitente)
values (100,'2005-09-06',209) -- incluindo id_pedido=100
-- desligando a propriedade identity_insert
set IDENTITY_INSERT pedido OFF
-- incluindo pedidos com a propriedade identity habilitada
insert into pedido (dt_emissao,id_emitente)
values ('2005-09-07',210)
-- consultando a tabela pedido
select * from pedido
go
----------------------------------------------------------------------------------------------------
id_pedido
----------------------------------------
3
id_pedido
----------------------------------------
4
id_pedido
----------------------------------------
5
id_pedido dt_emissao id_emitente
----------- ------------------------------------------------------ -----------
1 2005-09-01 00:00:00 204
2 2005-09-01 00:00:00 204
3 2005-09-05 00:00:00 208
4 2005-09-03 00:00:00 206
5 2005-09-04 00:00:00 207
100 2005-09-06 00:00:00 209
101 2005-09-07 00:00:00 210
Listagem 5. Desativando a propriedade identity
Note que durante o período em que a propriedade identity estava desabilitada, foram inseridos os pedidos de 3 e 100. Ao habilitar novamente o identity e efetuar nova inserção, repare que a numeração de pedidos foi automaticamente ajustada considerando a inserção manual; esse é um detalhe importante e que deve ser observado.
Conclusão
Colunas identity possuem regras que devem ser respeitadas nos comandos de inserção e atualização; essas regras informam basicamente como devem ser tratatas alterações nesse tipo de coluna. Na terceira e última parte dessa matéria aprenderemos a corrigir o valor de colunas identity, falaremos também sobre a utilização da função IDENTITY (sim, aqui é função e não propriedade!) com SELECT INTO.
Até lá!
