Nas colunas SMALLINT, INT e BIGINT com atributo de identidade, o atribui automaticamente valores inteiros incrementados para a coluna. Os atributos da coluna de identidade se comportam como os outros valores padrão, ou seja, quando a instrução de inserção não especifica o valor para a coluna, o fornece automaticamente o valor. Entretanto, o valor não é uma constante; o incrementa automaticamente o valor padrão na hora da inserção.

A palavra chave IDENTITY somente pode ser especificada quando o tipo de dado associado à coluna for um dos seguintes tipos inteiros exatos.

• SMALLINT
• INT
• BIGINT

Existem dois tipos de coluna de identidade no : aquelas que são sempre geradas (GENERATED ALWAYS), e aquelas que são geradas por padrão (GENERATED BY DEFAULT).

GENERATED ALWAYS

Uma coluna de identidade que é sempre gerada, incrementa o valor padrão em todas as inserções e armazena o valor incrementado na coluna. Ao contrário dos outros valores padrão, não é possível inserir diretamente um valor ou atualizar uma coluna de identidade que é sempre gerada. Por isso, deve ser especificada a palavra chave DEFAULT ao fazer inserção na coluna de identidade, ou não incluir a coluna de identidade na lista de colunas na inserção. Por exemplo: CREATE TABLE SAUDAÇÕES ( I INT GENERATED ALWAYS AS IDENTITY, CH CHAR(50)); INSERT INTO SAUDAÇÕES VALUES (DEFAULT, 'alô'); INSERT INTO SAUDAÇÕES(CH) VALUES ('bom dia'); Os valores gerados automaticamente na coluna de identidade GENERATED ALWAYS são únicos. A criação de uma coluna de identidade não cria um índice para a coluna.

GENERATED BY DEFAULT

Uma coluna de identidade gerada por padrão, somente incrementa e utiliza o valor padrão nas inserções quando não é fornecido nenhum valor explícito. Ao contrário da colunas sempre geradas, pode ser especificado um valor na instrução de inserção a ser utilizado no lugar do valor padrão gerado.

Para utilizar o valor gerado, deve ser especificada a palavra chave DEFAULT ao inserir na coluna de identidade, ou não incluir a coluna de identidade na lista de colunas da inserção. Para especificar um valor, este deve ser incluído na instrução de inserção. Por exemplo: CREATE TABLE SAUDAÇÕES ( I INT GENERATED BY DEFAULT AS IDENTITY, CH CHAR(50)); -- especificar o valor "1": INSERT INTO SAUDAÇÕES VALUES (1, 'olá'); -- usar o padrão gerado INSERT INTO SAUDAÇÕES VALUES (DEFAULT, 'salut'); -- usar o padrão gerado INSERT INTO SAUDAÇÕES(CH) VALUES ('bonjour'); Deve ser observado que ao contrário das colunas GENERATED ALWAYS, as colunas GENERATED BY DEFAULT não garantem unicidade. Portanto, no exemplo acima as linhas olá e salut possuem o valor de identidade igual a "1", porque a coluna gerada começa por "1" e o valor especificado pelo usuário também é "1". Para não permitir duplicidade, especialmente ao carregar ou importar dados, a tabela deve ser criada utilizando um valor para START WITH correspondente ao primeiro valor de identidade que o sistema deve atribuir. Para verificar esta condição e não permiti-la, pode ser utilizada uma chave primária ou restrição de unicidade na coluna de identidade GENERATED BY DEFAULT.

Por padrão, o valor inicial da coluna de identidade é 1, e o valor do incremento é 1. Podem ser especificados valores diferentes do padrão para o valor inicial e para o incremento ao definir a coluna utilizando as palavras chave STARTS WITH e INCREMENT BY. Se for especificado um número negativo para o valor do incremento, o decrementará o valor a cada inserção. Se o valor for 0, ou positivo, o incrementará o valor a cada inserção.

Os valores máximos e mínimos permitidos nas colunas de identidade são determinados pelo tipo de dado da coluna. A tentativa de inserir um valor fora da faixa de valores suportados pelo tipo de dado lança uma exceção. Tipo de dado Valor máximo Valor mínimo SMALLINT 32.767 (java.lang.Short.MAX_VALUE) -32.768 (java.lang.Short.MIN_VALUE) INT 2.147.483.647 (java.lang.Integer.MAX_VALUE) -2.147.483.648 (java.lang.Integer.MIN_VALUE) BIGINT 9.223.372.036.854.775.807 (java.lang.Long.MAX_VALUE) -9.223.372.036.854.775.808 (java.lang.Long.MIN_VALUE)

Os valores gerados automaticamente na coluna de identidade são únicos. Deve ser utilizada uma restrição de chave primária ou de unicidade para garantir a unicidade. A criação da coluna de identidade não cria um índice para a coluna.

A função IDENTITY_VAL_LOCAL é uma função não determinística que retorna o valor atribuído mais recentemente a uma coluna de identidade. Consulte para obter mais informações.

O mantém o último valor incrementado para a coluna no cache. Também armazena o próximo valor incrementado para a coluna no disco na coluna AUTOINCREMENTVALUE da tabela do sistema SYS.SYSCOLUMNS. Desfazer a transação não desfaz este valor, portanto transações desfeitas podem deixar "espaços" nos valores inseridos automaticamente na coluna de identidade. O se comporta desta maneira para evitar bloqueio na linha de SYS.SYSCOLUMNS pela duração da transação, e para manter a simultaneidade alta.

Colunas de identidadee gatilhos Quando acontece uma inserção em uma instrução-SQL-engatilhada, o valor inserido pela instrução-SQL-engatilhada na coluna de identidade é disponível a partir de ConnectionInfo somente dentro do código do gatilho. O código do gatilho também pode ver o valor inserido pela instrução que fez o gatilho disparar. Entretanto, a instrução que fez o gatilho disparar não pode ver o valor inserido pela instrução-SQL-engatilhada na coluna de identidade. Da mesma maneira, os gatilhos podem ser aninhados (ou recursivos). Uma instrução SQL pode fazer o gatilho T1 disparar. T1, por sua vez, executa uma instrução SQL que faz o gatilho T2 disparar. Se tanto T1 quanto T2 inserem linhas em uma tabela que faz o inserir em uma coluna de identidade, o gatilho T1 não poderá ver o valor produzido pela inserção feita por T2, mas T2 poderá ver o valor produzido pela inserção feita por T1. Cada nível de aninhamento pode ver os valores incrementados gerados por si próprio e pelos níveis de aninhamento anteriores, por todo o percurso até a instrução SQL de nível superior que deu início aos gatilhos recursivos. Somente podem existir 16 níveis de recursividade de gatilho.