Trabalhando com campo SERIAL , SERIAL8, BIGSERIAL

Versão para impressãoEnviar para amigoVersão PDF

Campo do tipo SERIAL são campos de auto-incremento , similar ao IDENTITY do SqlServer.

Em um registro quando não é informado o valor deste campo ou é informado o valor 0 (zero) o banco de dados automaticamente identifica qual é o valor atual da tabela e inclui este valor incrementado de 1 .

Como alternativa ao campo SERIAL, no Informix também pode trabalhar com SEQUENCE.

Existe três tipos de campo SERIAL, onde cada um é a evolução do anterior.

  1. SERIAL
  2. SERIAL8
  3. BIGSERIAL
  4. Características de campos seriais
  5. Exemplos de utilização
    1. Criação da tabela de teste
    2. Inclusão de dado simples
    3. Alterando o valor atual do auto-incremento com ALTER TABLE
    4. Tentativa de reiniciar serial com ALTER TABLE
    5. Valores nulos não são válidos
    6. Demonstração que não é possível ter mais de um campo serial na mesma tabela
    7. Outra demonstração de que não é possível diminuir o valor de auto-incremento
    8. Demonstração de como reiniciar o SERIAL
    9. Como capturar o ultimo SERIAL incluido
    10. Serial com valor interno inválido

SERIAL


Primeiro tipo SERIAL disponível no Informix, baseado no campo INTEGER, utiliza 4 bytes de espaço e pode armazenar valores entre 0 e 2,147,483,647 .

SERIAL8


Primeiro passo de evolução do tipo SERIAL, este foi baseado no campo INT8, utiliza 10 bytes de espaço e pode armazenar valores entre 0 e 9,223,372,036,854,775,807
Este tipo de dado está disponível a partir da versão 9 .

BIGSERIAL


Baseado no BIGINT, estes dois campos possuem exatamente as mesmas características do SERIAL8 e INT8. Conforme descrito nos manuais da IBM, estes campos possuem vantagens de armazenamento e processamento. Devem ser utilizados no lugar dos campos SERIAL8 e INT8.

Este tipo de dado está disponível a partir da versão 11 .

Características de campos seriais

  • Não são campos unique (de valores únicos), para garantir que não tenham valores duplicados é necessário criar uma constraint unique .
  • Seu valor de auto-incremento será sempre o próximo valor a partir do ultimo *maior* valor inserido ou redefinido através de um ALTER TABLE.
  • Ao atingir o valor limite do SERIAL o próximo valor é automaticamente reiniciado do valor 1 (um) ou do próximo valor incluido explicitamente.
  • Não é possível reiniciar ou diminuir o valor de auto-incremento através do comando ALTER TABLE, para reiniciar o valor é necessário utilizar a regra de reinicio do contador quando atinge o limite do tipo de dado. Ver exemplos mais abaixo.
  • Ao zerar uma tabela com o comando TRUNCATE o valor do serial não é alterado.
  • Os valores incluídos e seqüencia não sofrem efeitos de transações (rollbacks), ou seja, mesmo que uma transação seja cancelada, o valo de auto-incremento se mantém.
  • Para recuperar o valor do ultimo serial incluído :
  • Para identificar qual o valor atual de um campo serial , pode utilizar o comando oncheck -pt [banco:tabela] ou o algum dos SQLs
    SELECT serialv FROM sysmaster:sysptnhdr WHERE partnum = (SELECT partnum 
     FROM sysmaster:systabnames WHERE tabname= 'nome_tabela' AND
          sysmaster:systabnames.dbsname='nome_bancodados')
    
    SELECT cur_serial4, cur_bigserial, cur_serial8 FROM sysmaster:sysactptnhdr
    WHERE partnum = (SELECT partnum FROM sysmaster:systabnames WHERE tabname= 'nome_tabela' 
    AND sysmaster:systabnames.dbsname='nome_bancodados' ;
    

  • Quando o serial é reiniciado com um INSERT seu valor interno ultrapassa o limite do tipo (int, int8, bigint), transformando seu valor *internamente* em um valor "inválido" , que pode ser visto com o comando oncheck ou através do SQL. Observe que o valor do campo serial trabalha apenas com a parte positiva do tipo de dado, quando é reiniciado, como o contador continua a incrementar ele passa internamente para um valor negativo. Para evitar problemas futuros é altamente recomendável que o este valor seja corrigido manualmente, incluindo novamente um novo registro com o valor atual do campo. Veja no final do exemplo abaixo como faze-lo
  • Não é permitido valor NULO.
  • Não é possível adicionar diretamente um campo serial em uma tabela que já possui dados.
    Para isso é necessário adicionar um campo como INTEGER/INT8/BIGINT, adicionar dados a todas as linhas da tabela e então realizar um ALTER TABLE MODIFY [tabela] ( [campo] SERIAL/SERIAL8/BIGSERIAL ).
    Veja neste artigo do BRIUG como fazer.

Exemplos de utilização


Segue uma sequencia de comandos exemplificando várias situações e comportamento do campo SERIAL

Criação da tabela de teste

$ cat teste.sql | dbaccess -e teste_db database selected. create table tab_teste ( cod int , campo_serial serial); table created.

Inclusão de dado simples

insert into tab_teste(cod) values (1); 1 row(s) inserted. insert into tab_teste(cod) values (2); 1 row(s) inserted. select * from tab_teste; cod campo_serial 1 1 2 2 insert into tab_teste(cod, campo_serial) values (3,22); 1 row(s) inserted. select * from tab_teste; cod campo_serial 1 1 2 2 3 22 insert into tab_teste(cod, campo_serial) values (4,22); 1 row(s) inserted. insert into tab_teste(cod, campo_serial) values (5,18); 1 row(s) inserted. select * from tab_teste; cod campo_serial 1 1 2 2 3 22 4 22 5 18

Alterando o valor atual do auto-incremento com ALTER TABLE

alter table tab_teste modify ( campo_serial serial(100)); Table altered. insert into tab_teste(cod) values (6); 1 row(s) inserted. insert into tab_teste(cod) values (7); 1 row(s) inserted. select * from tab_teste; cod campo_serial 1 1 2 2 3 22 4 22 5 18 6 100 7 101

Tentativa de reiniciar serial com ALTER TABLE

alter table tab_teste modify ( campo_serial serial(1)); Table altered. insert into tab_teste(cod) values (8); 1 row(s) inserted. insert into tab_teste(cod, campo_serial) values (9,0); 1 row(s) inserted.

Valores nulos não são válidos

insert into tab_teste(cod, campo_serial) values (10,null); 391: Cannot insert a null into column (tab_teste.campo_serial). Error in line 1 Near character position 56 select * from tab_teste; cod campo_serial 1 1 2 2 3 22 4 22 5 18 6 100 7 101 8 102 9 103

Demonstração que não é possível ter mais de um campo serial na mesma tabela

alter table tab_teste add ( campo2_serial bigserial) ; 287: Cannot add serial column (campo2_serial) to table. Error in line 1 Near character position 51

Outra demonstração de que não é possível diminuir o valor de auto-incremento

delete from tab_teste where cod = 9 ; 1 row(s) deleted. insert into tab_teste(cod, campo_serial) values (11,0); 1 row(s) inserted. insert into tab_teste(cod, campo_serial) values (12,0); 1 row(s) inserted. insert into tab_teste(cod, campo_serial) values (13,0); 1 row(s) inserted. select * from tab_teste; cod campo_serial 1 1 2 2 3 22 4 22 5 18 6 100 7 101 8 102 11 104 12 105 13 106 alter table tab_teste modify ( campo_serial serial(1)); Table altered. delete from tab_teste where cod in (12,13) ; 2 row(s) deleted. insert into tab_teste(cod, campo_serial) values (14,0); 1 row(s) inserted. select * from tab_teste; cod campo_serial 1 1 2 2 3 22 4 22 5 18 6 100 7 101 8 102 11 104 14 107 delete from tab_teste where cod in (12,13,14) ; 1 row(s) deleted. alter table tab_teste modify ( campo_serial serial(105)); Table altered. insert into tab_teste(cod, campo_serial) values (15,0); 1 row(s) inserted. select * from tab_teste; cod campo_serial 1 1 2 2 3 22 4 22 5 18 6 100 7 101 8 102 11 104 15 108 delete from tab_teste ; 10 row(s) deleted. alter table tab_teste modify ( campo_serial serial(1)); Table altered. insert into tab_teste(cod, campo_serial) values (16,0); 1 row(s) inserted.

Demonstração de como reiniciar o SERIAL

insert into tab_teste(cod, campo_serial) values (17, 2147483647); 1 row(s) inserted. insert into tab_teste(cod, campo_serial) values (18,2); 1 row(s) inserted. insert into tab_teste(cod, campo_serial) values (19,3); 1 row(s) inserted. insert into tab_teste(cod, campo_serial) values (20,4); 1 row(s) inserted. insert into tab_teste(cod, campo_serial) values (21,10); 1 row(s) inserted. insert into tab_teste(cod, campo_serial) values (22,15); 1 row(s) inserted. insert into tab_teste(cod, campo_serial) values (23,0); 1 row(s) inserted. insert into tab_teste(cod, campo_serial) values (24,0); 1 row(s) inserted. insert into tab_teste(cod, campo_serial) values (25,0); 1 row(s) inserted. insert into tab_teste(cod, campo_serial) values (26, 2147483647); 1 row(s) inserted. insert into tab_teste(cod, campo_serial) values (27,0); 1 row(s) inserted. insert into tab_teste(cod, campo_serial) values (28,0); 1 row(s) inserted. insert into tab_teste(cod, campo_serial) values (29,0); 1 row(s) inserted.

Como capturar o ultimo SERIAL incluido

select dbinfo('serial8') as serial8, dbinfo('bigserial') as bigserial, dbinfo('sqlca.sqlerrd1') as serial from sysmaster:sysdual; serial8 bigserial serial 0 0 3 select * from tab_teste; cod campo_serial 16 109 17 2147483647 18 2 19 3 20 4 21 10 22 15 23 16 24 17 25 18 26 2147483647 27 1 28 2 29 3

Serial com valor interno inválido

SELECT cur_serial4, cur_bigserial, cur_serial8 FROM sysmaster:sysactptnhdr WHERE partnum = (SELECT partnum FROM sysmaster:systabnames WHERE tabname= 'tab_teste' AND sysmaster:systabnames.dbsname='teste1'); cur_serial4 cur_bigserial cur_serial8 -2147483644 1 1 insert into tab_teste(cod, campo_serial) values (30,10); 1 row(s) inserted. insert into tab_teste(cod, campo_serial) values (31,0); 1 row(s) inserted. SELECT cur_serial4, cur_bigserial, cur_serial8 FROM sysmaster:sysactptnhdr WHERE partnum = (SELECT partnum FROM sysmaster:systabnames WHERE tabname= 'tab_teste' AND sysmaster:systabnames.dbsname='teste1'); cur_serial4 cur_bigserial cur_serial8 12 1 1 1 row(s) retrieved. drop table tab_teste; Table dropped. Database closed.

0
Ainda não votado
Sua avaliação: Nenhum

SQL alternativo para identificar o serial

Este select foi copiado de uma mensagem na lista do IIUG

select trim(dbsname)||'.'||trim(tabname) as tabname,
serialv as serial, cur_serial8 as serial8, cur_bigserial as big_serial
from sysptnhdr P, systabnames T
where P.partnum = T.partnum
and P.partnum = P.lockid 

Comentar

O conteúdo deste campo é privado não será exibido ao público.
  • Endereços de páginas de internet e emails viram links automaticamente.
  • Você pode usar tags BBCode no texto.
  • Tags HTML permitidas: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd>

Mais informações sobre as opções de formatação

CAPTCHA
Este teste é para bloquear programas automatizados e previnir spams
CAPTCHA de Imagem
Digite o texto exibido na imagem.