Validar CPF no banco de dados: PostgreSQL e MySQL

Validar o CPF no formulário resolve a maior parte dos erros de digitação, mas não impede que um número inválido chegue à tabela por outro caminho: uma importação de planilha, um script de manutenção, um segundo serviço que grava no mesmo banco. Este guia mostra como guardar o CPF, como escrever a validação em PL/pgSQL e em MySQL 8, como transformá-la em restrição e como aplicar essa restrição a uma tabela que já tem dados ruins.

Onde validar: aplicação, banco ou ambos

Na aplicação, a validação existe para o usuário. É ali que você sabe qual campo falhou, em que idioma responder e se o problema foi tamanho, sequência repetida ou dígito verificador. O validador de CPF e as implementações em TypeScript, Python ou PHP fazem esse trabalho com mensagens específicas.

No banco, a validação existe para os dados. Uma restrição declarada na tabela vale para qualquer cliente que escreva nela, inclusive os que você não controla. Ela não dá mensagens amigáveis, só recusa a linha, mas garante que tudo o que está na coluna obedece à regra.

Na prática, a resposta costuma ser ambos, com a mesma regra. Se a aplicação aceita algo que o banco recusa, o usuário vê um erro genérico; se o banco aceita algo que a aplicação recusa, a regra não está sendo aplicada. Por isso a função SQL abaixo segue a implementação de referência: remove pontos, hífen e espaços, exige 11 dígitos, recusa sequências repetidas e confere os dois verificadores.

Coluna: CHAR(11) sem pontuação e índice único

Guarde apenas os 11 dígitos. A pontuação é formatação, e formatação pertence à camada de apresentação: o formatador de CPF ou uma função como formatarCpf reconstroem 529.982.247-25 a partir de 52998224725 quando for preciso exibir.

Motivos para não usar um tipo numérico como BIGINT:

• Zeros à esquerda somem. 012.345.678-90 é um CPF válido, e como número vira 1234567890, com 10 dígitos. Toda leitura passa a exigir LPAD, e basta um lugar esquecer para o dado sair errado.
• CPF não é quantidade. Um identificador que não participa de contas é texto.

CREATE TABLE clientes (
id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
nome   text     NOT NULL,
cpf    char(11) NOT NULL,
CONSTRAINT clientes_cpf_formato CHECK (cpf ~ '^[0-9]{11}$')
);

CREATE UNIQUE INDEX clientes_cpf_key ON clientes (cpf);

O índice único impede o mesmo CPF em dois cadastros e acelera a busca por CPF. Ele depende da coluna normalizada: se 529.982.247-25 e 52998224725 pudessem coexistir, a duplicata passaria.

Se a regra do seu negócio permite cliente sem CPF, deixe a coluna anulável. No PostgreSQL e no MySQL, um índice único aceita várias linhas com NULL, então clientes sem CPF não conflitam entre si.

Função PL/pgSQL cpf_valido

A função recebe texto, aceita a entrada com ou sem pontuação e devolve true ou false. Ela é IMMUTABLE, porque o resultado depende só do argumento, e STRICT, o que faz o PostgreSQL devolver NULL sem executá-la quando o argumento é NULL.

CREATE OR REPLACE FUNCTION cpf_valido(entrada text)
RETURNS boolean
LANGUAGE plpgsql
IMMUTABLE
STRICT
AS $$
DECLARE
d     text;
soma  int;
resto int;
dv1   int;
dv2   int;
BEGIN
-- remove pontos, hífen e espaços; qualquer outro caractere invalida
d := regexp_replace(entrada, '[.[:space:]-]', '', 'g');

IF d !~ '^[0-9]{11}$' THEN
  RETURN false;
END IF;

-- 000.000.000-00 a 999.999.999-99 passam na conta, mas não são aceitos
IF d = repeat(substr(d, 1, 1), 11) THEN
  RETURN false;
END IF;

-- primeiro verificador: 9 dígitos com pesos de 10 a 2
soma := 0;
FOR i IN 1..9 LOOP
  soma := soma + substr(d, i, 1)::int * (11 - i);
END LOOP;
resto := soma % 11;
dv1 := CASE WHEN resto < 2 THEN 0 ELSE 11 - resto END;

IF substr(d, 10, 1)::int <> dv1 THEN
  RETURN false;
END IF;

-- segundo verificador: 10 dígitos com pesos de 11 a 2
soma := 0;
FOR i IN 1..10 LOOP
  soma := soma + substr(d, i, 1)::int * (12 - i);
END LOOP;
resto := soma % 11;
dv2 := CASE WHEN resto < 2 THEN 0 ELSE 11 - resto END;

RETURN substr(d, 11, 1)::int = dv2;
END;
$$;

SELECT cpf_valido('529.982.247-25');  -- true
SELECT cpf_valido('11144477735');     -- true
SELECT cpf_valido('111.111.111-11');  -- false (repetido)
SELECT cpf_valido('529.982.247-26');  -- false (segundo dígito)

Acompanhe a execução com 529.982.247-25. Depois da limpeza, d vale 52998224725, que tem 11 dígitos e não é repetição do 5. No primeiro laço, o peso é 11 - i, de 10 (para i = 1) até 2 (para i = 9), e a soma chega a 295. O resto de 295 por 11 é 9, então dv1 é 2, que bate com o décimo caractere. No segundo laço, os pesos vão de 11 a 2 sobre os dez primeiros dígitos e a soma é 347; o resto é 6 e dv2 é 5, igual ao último caractere. A função devolve true.

Com 111.444.777-35, as somas são 162 (resto 8, dv1 = 3) e 204 (resto 6, dv2 = 5): true. Com 529.982.247-26, o segundo verificador calculado é 5 e o informado é 6: false. E 111.111.111-11 nem chega ao cálculo, porque é igual a repeat('1', 11).

CHECK constraint no PostgreSQL

Com a função criada, a restrição de formato vira uma restrição completa:

ALTER TABLE clientes
DROP CONSTRAINT clientes_cpf_formato,
ADD CONSTRAINT clientes_cpf_valido
  CHECK (cpf ~ '^[0-9]{11}$' AND cpf_valido(cpf));

INSERT INTO clientes (nome, cpf) VALUES ('Teste', '52998224725');  -- ok
INSERT INTO clientes (nome, cpf) VALUES ('Teste', '52998224726');
-- ERROR: new row for relation "clientes" violates check constraint "clientes_cpf_valido"

A expressão regular continua na restrição porque a função aceita pontuação e a coluna não deve aceitar. Assim, cpf_valido serve tanto para auditar dados brutos quanto para proteger a coluna normalizada.

Uma CHECK só recusa a linha quando a expressão é falsa; com NULL, a linha passa. Numa coluna anulável, é o comportamento desejado para clientes sem CPF.

Função em MySQL 8

O MySQL não tem PL/pgSQL, mas a mesma lógica cabe em uma função armazenada. Ela é declarada DETERMINISTIC e NO SQL; com o log binário ligado, o MySQL recusa funções sem esse tipo de declaração, a menos que log_bin_trust_function_creators esteja ativo.

DELIMITER //

CREATE FUNCTION cpf_valido(entrada VARCHAR(32))
RETURNS BOOLEAN
DETERMINISTIC
NO SQL
BEGIN
DECLARE d VARCHAR(32);
DECLARE soma INT DEFAULT 0;
DECLARE resto INT;
DECLARE dv1 INT;
DECLARE dv2 INT;
DECLARE i INT DEFAULT 1;

IF entrada IS NULL THEN
  RETURN NULL;
END IF;

SET d = REPLACE(REPLACE(REPLACE(entrada, '.', ''), '-', ''), ' ', '');

IF d NOT REGEXP '^[0-9]{11}$' THEN
  RETURN FALSE;
END IF;

IF d = REPEAT(LEFT(d, 1), 11) THEN
  RETURN FALSE;
END IF;

WHILE i <= 9 DO
  SET soma = soma + CAST(SUBSTRING(d, i, 1) AS SIGNED) * (11 - i);
  SET i = i + 1;
END WHILE;
SET resto = soma % 11;
SET dv1 = IF(resto < 2, 0, 11 - resto);

IF CAST(SUBSTRING(d, 10, 1) AS SIGNED) <> dv1 THEN
  RETURN FALSE;
END IF;

SET soma = 0;
SET i = 1;
WHILE i <= 10 DO
  SET soma = soma + CAST(SUBSTRING(d, i, 1) AS SIGNED) * (12 - i);
  SET i = i + 1;
END WHILE;
SET resto = soma % 11;
SET dv2 = IF(resto < 2, 0, 11 - resto);

RETURN CAST(SUBSTRING(d, 11, 1) AS SIGNED) = dv2;
END //

DELIMITER ;

SELECT cpf_valido('529.982.247-25'), cpf_valido('111.444.777-35'),
     cpf_valido('111.111.111-11'), cpf_valido('529.982.247-26');
-- 1, 1, 0, 0

Pesos e somas são os mesmos da versão PostgreSQL. A limpeza usa REPLACE encadeado, que remove exatamente ponto, hífen e espaço.

Por que não dá para usar a função em um CHECK

O MySQL aplica CHECK constraints desde a versão 8.0.16, mas a expressão de uma CHECK não pode chamar funções armazenadas nem funções definidas pelo usuário. Uma restrição como CHECK (cpf_valido(cpf)) é recusada na criação da tabela. A expressão regular, por ser uma função nativa determinística, é permitida, então a checagem de formato pode ficar na tabela:

CREATE TABLE clientes (
id   BIGINT AUTO_INCREMENT PRIMARY KEY,
nome VARCHAR(200) NOT NULL,
cpf  CHAR(11) NOT NULL,
CONSTRAINT clientes_cpf_formato CHECK (cpf REGEXP '^[0-9]{11}$'),
UNIQUE KEY clientes_cpf_key (cpf)
);

DELIMITER //

CREATE TRIGGER clientes_cpf_bi BEFORE INSERT ON clientes
FOR EACH ROW
BEGIN
IF NOT cpf_valido(NEW.cpf) THEN
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'CPF inválido';
END IF;
END //

-- crie também clientes_cpf_bu, idêntico, com BEFORE UPDATE

DELIMITER ;

Cada trigger do MySQL responde a um único evento, por isso é preciso um segundo, igual, para UPDATE. Se NEW.cpf for NULL, a função devolve NULL, NOT NULL continua NULL e o IF não dispara, o mesmo comportamento de uma CHECK com valor nulo.

Migração de dados existentes com CPFs inválidos

Aplicar a restrição em uma tabela nova é trivial. O cenário comum é outro: uma coluna antiga, com CPFs em formatos variados, alguns sem o zero à esquerda e alguns simplesmente errados. Um ADD CONSTRAINT comum falharia na primeira linha inválida e, numa tabela grande, ainda manteria um bloqueio exclusivo enquanto verifica todas as linhas.

O PostgreSQL resolve isso com NOT VALID: a restrição passa a valer para escritas novas imediatamente, sem verificar o que já existe. Depois da limpeza, VALIDATE CONSTRAINT confere as linhas antigas com um bloqueio que não impede leituras nem escritas.

-- Tabela legada: cpf varchar, com formatos variados
-- 1. Escritas novas passam a ser verificadas; as antigas ainda não
ALTER TABLE clientes
ADD CONSTRAINT clientes_cpf_valido
CHECK (cpf ~ '^[0-9]{11}$' AND cpf_valido(cpf)) NOT VALID;

-- 2. Normaliza a pontuação, só onde o resultado é válido
UPDATE clientes
SET cpf = regexp_replace(cpf, '[.[:space:]-]', '', 'g')
WHERE cpf ~ '[.[:space:]-]'
AND cpf_valido(regexp_replace(cpf, '[.[:space:]-]', '', 'g'));

-- 3. Recupera zeros perdidos, se a coluna já foi numérica
UPDATE clientes
SET cpf = lpad(cpf, 11, '0')
WHERE cpf ~ '^[0-9]{9,10}$'
AND cpf_valido(lpad(cpf, 11, '0'));

-- 4. Separa o que sobrou para análise e limpa a coluna
CREATE TABLE clientes_cpf_quarentena AS
SELECT id, cpf AS cpf_original, now() AS registrado_em
FROM clientes
WHERE NOT (cpf ~ '^[0-9]{11}$' AND cpf_valido(cpf));

UPDATE clientes
SET cpf = NULL
WHERE NOT (cpf ~ '^[0-9]{11}$' AND cpf_valido(cpf));

-- 5. Verifica as linhas antigas
ALTER TABLE clientes VALIDATE CONSTRAINT clientes_cpf_valido;

Alguns pontos sobre a sequência:

• Enquanto a restrição está NOT VALID, linhas antigas inválidas não podem ser atualizadas. O PostgreSQL verifica a CHECK na nova versão de toda linha alterada, mesmo que o UPDATE mude outra coluna. Por isso os passos 2 e 3 filtram pelo resultado válido: sem esse filtro, uma única linha irrecuperável faria o comando inteiro falhar.
• O passo 3 só faz sentido se você sabe que a coluna já foi numérica. Completar com zeros um valor que alguém digitou incompleto transforma um erro em um CPF possivelmente de outra pessoa. Na dúvida, mande esses casos para a quarentena.
• O passo 4 exige coluna anulável. Se a coluna é NOT NULL, decida com a área de negócio o que fazer com esses cadastros antes de validar. Não substitua CPFs inválidos por números gerados: o cadastro passaria a apontar para um número que pode pertencer a outra pessoa.
• A normalização pode criar duplicatas. 529.982.247-25 e 52998224725 viram o mesmo valor. Crie o índice único depois de conferir com GROUP BY cpf HAVING count(*) > 1, e em tabelas grandes use CREATE UNIQUE INDEX CONCURRENTLY.

No MySQL não existe NOT VALID, mas o caminho é parecido: crie os triggers primeiro, para estancar a entrada de dados ruins, faça a limpeza com UPDATE filtrados pela função e só então troque o tipo da coluna e adicione a CHECK de formato. Antes de qualquer UPDATE em massa, guarde uma cópia da coluna original.

Para popular ambientes de teste depois da migração, use números fictícios do gerador de CPF em lote em vez de copiar a produção. O guia sobre CPF em massa de teste e LGPD explica por quê.