Curso SQL Server — Edição Definitiva

Módulo 7 — PRIMARY KEY e IDENTITY (auto‑incremento)

Garanta unicidade com PRIMARY KEY e gere identificadores automáticos com IDENTITY. Entenda clustered vs nonclustered, PK composta, reseed e como capturar o último ID com segurança.

Objetivo

Aprender a criar chaves primárias simples ou compostas, usar IDENTITY com segurança, entender clustered vs nonclustered, capturar IDs recém inseridos e tratar casos de reseed.

Em geral, prefira chaves substitutas (surrogate keys) simples do tipo INT IDENTITY para PK, exceto quando a chave natural for estável e pequena.

PRIMARY KEY (inline)

O que é / Para que serve: Definir a PK diretamente na coluna ao criar a tabela (coluna única).

Sintaxe: Coluna TIPO PRIMARY KEY

Como funciona: Cria uma restrição de unicidade e um índice único. Se não existir índice clustered, a PK costuma ser criada como clustered por padrão.

Implicações & boas práticas: • Nomeie a constraint em projetos maiores (CONSTRAINT PK_Tabela). • Evite colunas voláteis como PK.

CREATE TABLE dbo.Categorias (
  CategoriaID INT PRIMARY KEY,
  Nome NVARCHAR(60) NOT NULL
);

PRIMARY KEY (nomeada/composta)

O que é / Para que serve: Criar PK com nome e/ou múltiplas colunas.

Sintaxe: CONSTRAINT PK_Tabela PRIMARY KEY (Col1 [, Col2 ...])

Como funciona: Permite definir PK composta, útil em relações de detalhe (ex.: (PedidoID, ItemSeq)).

Implicações & boas práticas: • PK composta aumenta a largura das chaves nos índices/relacionamentos. • Prefira surrogate key simples quando possível.

CREATE TABLE dbo.ItensPedido (
  PedidoID INT NOT NULL,
  ItemSeq  INT NOT NULL,
  ProdutoID INT NOT NULL,
  CONSTRAINT PK_ItensPedido PRIMARY KEY (PedidoID, ItemSeq)
);

Clustered vs Nonclustered (visão rápida)

-- PK clustered nomeada
CREATE TABLE dbo.Clientes (
  ClienteID INT NOT NULL,
  Nome NVARCHAR(100) NOT NULL,
  CONSTRAINT PK_Clientes PRIMARY KEY CLUSTERED (ClienteID)
);
-- Índice adicional nonclustered para buscar por Nome
CREATE INDEX IX_Clientes_Nome ON dbo.Clientes(Nome);

IDENTITY (auto‑incremento)

O que é / Para que serve: Gerar valores automáticos por linha inserida.

Sintaxe: INT IDENTITY([seed],[increment])

Como funciona: O SQL mantém um contador por tabela e atribui o próximo valor no INSERT.

Implicações & boas práticas: • Lacunas são normais (ROLLBACK/falhas). • Não use ID como sequência sem buracos. • Para inserir valores manuais, use SET IDENTITY_INSERT.

CREATE TABLE dbo.Produtos (
  ProdutoID INT IDENTITY(1,1) PRIMARY KEY,
  Nome NVARCHAR(100) NOT NULL
);
INSERT INTO dbo.Produtos (Nome) VALUES (N'Mouse'),(N'Teclado');
SELECT * FROM dbo.Produtos;

SET IDENTITY_INSERT

O que é / Para que serve: Permitir inserir valores explícitos na coluna IDENTITY (ex.: migrações).

Sintaxe: SET IDENTITY_INSERT Tabela ON; -- depois OFF

Como funciona: Com ON, você pode informar manualmente o valor do IDENTITY; após encerrar a migração, desabilite.

Implicações & boas práticas: • Só uma tabela com IDENTITY_INSERT ON por sessão. • Garante integridade ao migrar dados legados.

SET IDENTITY_INSERT dbo.Produtos ON;
INSERT INTO dbo.Produtos (ProdutoID, Nome) VALUES (100,'Produto Migrado');
SET IDENTITY_INSERT dbo.Produtos OFF;

DBCC CHECKIDENT (RESEED)

O que é / Para que serve: Reiniciar/ajustar o contador do IDENTITY.

Sintaxe: DBCC CHECKIDENT('Tabela', RESEED, novo_seed);

Como funciona: Ajusta o ponto de partida para a próxima inserção (próximo = seed+increment).

Implicações & boas práticas: • Use com cautela em produção. • Após TRUNCATE, o contador volta ao seed inicial.

DBCC CHECKIDENT ('dbo.Produtos', RESEED, 1000); -- próxima linha será 1001

SCOPE_IDENTITY()

O que é / Para que serve: Obter o último valor IDENTITY gerado no mesmo escopo.

Sintaxe: SELECT SCOPE_IDENTITY();

Como funciona: Retorna o valor gerado pela última inserção com IDENTITY no mesmo escopo e sessão.

Implicações & boas práticas: • Mais seguro que @@IDENTITY (que pode capturar valores de triggers). • IDENT_CURRENT('Tabela') ignora escopo/sessão.

INSERT INTO dbo.Produtos (Nome) VALUES (N'Webcam');
DECLARE @NovoID INT = SCOPE_IDENTITY();
SELECT @NovoID AS ProdutoGerado;

Armadilhas comuns & como evitar

Exercícios práticos

  1. Crie dbo.Clientes com ClienteID INT IDENTITY(1,1) PRIMARY KEY e Nome NVARCHAR(100) NOT NULL. Insira 3 clientes e liste com o ID gerado.
  2. Use SCOPE_IDENTITY() para capturar o ID do último cliente inserido e em seguida inserir um endereço relacionado.
  3. Ative IDENTITY_INSERT para inserir um cliente com ID específico (ex.: 1000). Desative depois.
  4. Faça DBCC CHECKIDENT para reseedar a tabela para 2000 e confirme o próximo ID.

Referências oficiais & leituras recomendadas