Colunas Identity - Parte-II - Exemplos práticos

Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Para efetuar o download você precisa estar logado. Clique aqui para efetuar o login
Confirmar voto
0
 (2)  (0)

No segundo artigo da série, Paulo Ribeiro no mostra, na prática, como utilizar as colunas do tipo identity.

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á!

Leia todos artigos da série

 
Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Ficou com alguma dúvida?