Lição 9.2 · Tempo de leitura: ~9 min

O comando SQL ALTER TABLE altera a estrutura de uma tabela existente sem recriá-la: adiciona, altera, renomeia e remove colunas, e adiciona ou remove restrições. Nesta lição você vai ver a sintaxe de cada alteração no MySQL e no PostgreSQL, por que os dados existentes podem impedir uma alteração e como encontrar e remover uma restrição pelo nome.

ALTER TABLE em SQL: alterando uma tabela existente

Na lição anterior você criou tabelas com CREATE TABLE. Os requisitos mudam: uma tabela precisa de uma nova coluna, de um campo de texto maior ou de uma restrição esquecida. Apagar e recriar a tabela apagaria os dados, por isso a estrutura é alterada no lugar com ALTER TABLE.

Os bancos de exemplo do SQLtest são somente leitura, por isso estes comandos retornam lá um erro de permissão. Os exemplos usam o esquema Sakila e foram verificados no MySQL 8.0 e no PostgreSQL 17.

Sintaxe do ALTER TABLE

ALTER TABLE table_name
    action [, action ...];

A ação (action) diz o que alterar. Um único ALTER TABLE pode ter várias ações separadas por vírgulas. As mais comuns:

Adicionando uma coluna

ALTER TABLE customer
ADD COLUMN loyalty_points INT NOT NULL DEFAULT 0;

Resultado: cada cliente existente recebe loyalty_points = 0; novas linhas recebem 0, a menos que o INSERT informe um valor.

A nova coluna é adicionada no fim da tabela. O MySQL pode colocá-la em outro lugar com AFTER column_name ou FIRST; o PostgreSQL sempre a adiciona no fim.

NOT NULL sem valor padrão

Se a tabela já tem linhas, uma coluna NOT NULL precisa de um valor para elas:

ALTER TABLE customer
ADD COLUMN phone VARCHAR(20) NOT NULL;

Resultado no PostgreSQL: um erro, column "phone" contains null values. O MySQL aceita o comando e preenche as linhas existentes com o valor padrão implícito do tipo: aqui uma string vazia, 0 para números.

Por isso, sempre informe um DEFAULT ao adicionar uma coluna obrigatória a uma tabela com dados, ou adicione a coluna aceitando NULL, preencha-a com UPDATE e só depois a torne NOT NULL.

Alterando uma coluna

Para aumentar a coluna email:

-- MySQL
ALTER TABLE customer
MODIFY COLUMN email VARCHAR(100) NOT NULL;

-- PostgreSQL
ALTER TABLE customer
ALTER COLUMN email TYPE VARCHAR(100);

Os dois bancos tratam o resto da definição da coluna de forma diferente:

• MODIFY no MySQL substitui a definição inteira da coluna. O que você não repetir se perde: ALTER TABLE customer MODIFY active SMALLINT; transforma uma coluna NOT NULL DEFAULT 1 em uma coluna que aceita NULL e não tem valor padrão. Sempre escreva a definição completa: MODIFY active SMALLINT NOT NULL DEFAULT 1.
• ALTER COLUMN no PostgreSQL muda uma propriedade por vez e mantém as outras:

ALTER TABLE customer
    ALTER COLUMN active TYPE INT,
    ALTER COLUMN active SET DEFAULT 1,
    ALTER COLUMN email SET NOT NULL;

O MySQL também tem CHANGE COLUMN old_name new_name definition, que renomeia e redefine uma coluna em um só passo.

Quando os dados não cabem

O banco verifica as linhas existentes em relação à nova definição:

• Reduzir VARCHAR(50) para VARCHAR(10) falha se algum valor for maior: "Data truncated for column 'email'" no MySQL, "value too long for type character varying(10)" no PostgreSQL.
• Adicionar NOT NULL falha se a coluna tiver NULL: "Invalid use of NULL value" no MySQL, "column contains null values" no PostgreSQL.

Corrija os dados primeiro, por exemplo UPDATE customer SET email = '' WHERE email IS NULL;, e repita o ALTER TABLE.

Renomeando e removendo

ALTER TABLE customer RENAME COLUMN email TO email_address;

ALTER TABLE customer DROP COLUMN loyalty_points;

ALTER TABLE customer RENAME TO client;

Resultado: a coluna ganha um novo nome, a coluna loyalty_points é removida junto com os dados e a tabela é renomeada.

RENAME COLUMN funciona no MySQL 8.0, no MariaDB 10.5 e no PostgreSQL; o SQL Server usa o procedimento sp_rename. Renomear não atualiza suas consultas, views e o código da aplicação: tudo o que usa o nome antigo para de funcionar.

DROP COLUMN não pode ser desfeito com ROLLBACK no MySQL, porque o DDL confirma implicitamente (veja a lição 8.4). Faça backup dos dados de que possa precisar.

Adicionando restrições

As restrições do CREATE TABLE podem ser adicionadas depois. Dê um nome a cada uma com CONSTRAINT name, para encontrá-la e removê-la com facilidade:

ALTER TABLE customer
ADD CONSTRAINT uq_customer_email UNIQUE (email);

ALTER TABLE payment
ADD CONSTRAINT chk_payment_amount CHECK (amount >= 0);

ALTER TABLE payment
ADD CONSTRAINT fk_payment_customer
    FOREIGN KEY (customer_id) REFERENCES customer (customer_id);

Uma chave primária é adicionada do mesmo jeito: ALTER TABLE table_name ADD PRIMARY KEY (column_name);.

Os dados existentes podem impedir uma restrição

Uma nova restrição vale para as linhas que já estão na tabela. Se alguma linha a violar, o comando inteiro falha:

Encontre as linhas problemáticas com um SELECT antes de adicionar a restrição. Por exemplo, pagamentos que referenciam um cliente inexistente:

SELECT p.payment_id, p.customer_id
FROM payment AS p
LEFT JOIN customer AS c ON c.customer_id = p.customer_id
WHERE c.customer_id IS NULL;

Resultado: as linhas órfãs que impediriam a chave estrangeira. Corrija-as ou remova-as e depois adicione a restrição.

Encontrando e removendo restrições

Para remover uma restrição, você precisa do nome dela. Se nenhum nome foi dado, o banco gerou um, como payment_ibfk_1 no MySQL ou payment_customer_id_fkey no PostgreSQL. A view information_schema.table_constraints lista essas restrições:

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM information_schema.table_constraints
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'payment'
ORDER BY CONSTRAINT_TYPE, CONSTRAINT_NAME;

Resultado no Sakila: fk_payment_customer, fk_payment_rental e fk_payment_staff (FOREIGN KEY) e PRIMARY (PRIMARY KEY). No PostgreSQL, filtre por table_schema = 'public' em vez de DATABASE().

Depois, remova-a:

-- PostgreSQL, SQL Server and MySQL 8.0.19+
ALTER TABLE payment DROP CONSTRAINT fk_payment_customer;

-- MySQL: type-specific forms
ALTER TABLE payment DROP FOREIGN KEY fk_payment_customer;
ALTER TABLE payment DROP CHECK chk_payment_amount;
ALTER TABLE customer DROP INDEX uq_customer_email;

No MySQL, uma restrição UNIQUE é um índice, por isso é removida com DROP INDEX.

ALTER TABLE em tabelas grandes

Em uma tabela com milhões de linhas, algumas alterações reescrevem a tabela inteira e bloqueiam gravações enquanto rodam. Adicionar uma coluna que aceita NULL ou uma coluna com valor padrão constante é rápido nas versões atuais: o MySQL 8.0 faz isso instantaneamente (ALGORITHM=INSTANT), assim como o PostgreSQL 11 e posteriores. Mudar o tipo de uma coluna, adicionar uma chave estrangeira a uma tabela grande ou remover uma coluna em versões antigas pode demorar bastante.

Em bancos de produção, teste a alteração de esquema em uma cópia antes e execute-a quando a carga estiver baixa.

Principais pontos:

• ALTER TABLE altera uma tabela existente no lugar: colunas, restrições e o nome da tabela.
• Adicione colunas obrigatórias a tabelas com dados usando DEFAULT, ou preencha-as antes de definir NOT NULL.
• No MySQL, MODIFY substitui a definição inteira da coluna; repita NOT NULL e DEFAULT, senão eles se perdem.
• As linhas existentes precisam atender ao novo tipo, ao NOT NULL ou à restrição; caso contrário, o comando falha.
• Dê nomes às restrições; encontre as sem nome em information_schema.table_constraints e remova-as com DROP CONSTRAINT.
• No MySQL, ALTER TABLE confirma implicitamente e não pode ser desfeito.

Perguntas Frequentes

Como adicionar uma coluna a uma tabela existente em SQL?

Use ALTER TABLE table_name ADD COLUMN column_name data_type;, por exemplo ALTER TABLE customer ADD COLUMN loyalty_points INT NOT NULL DEFAULT 0;. As linhas existentes recebem o valor padrão, ou NULL se a coluna não tiver valor padrão.

Como mudar o tipo de dados de uma coluna em SQL?

No MySQL, ALTER TABLE customer MODIFY COLUMN email VARCHAR(100) NOT NULL; com a definição completa da coluna. No PostgreSQL, ALTER TABLE customer ALTER COLUMN email TYPE VARCHAR(100);. A alteração falha se os valores existentes não couberem no novo tipo.

Como adicionar uma chave estrangeira a uma tabela existente?

ALTER TABLE payment ADD CONSTRAINT fk_payment_customer FOREIGN KEY (customer_id) REFERENCES customer (customer_id);. Todo customer_id existente em payment precisa existir em customer, senão o comando falha.

Como remover uma restrição se não sei o nome dela?

Procure-a em information_schema.table_constraints para a tabela e execute ALTER TABLE table_name DROP CONSTRAINT constraint_name;. No MySQL, chaves estrangeiras também podem ser removidas com DROP FOREIGN KEY e restrições de unicidade com DROP INDEX.

Perguntas de entrevista

Qual é a diferença entre MODIFY COLUMN no MySQL e ALTER COLUMN no PostgreSQL?

MODIFY COLUMN substitui a definição completa da coluna, então os atributos que você não repetir, como NOT NULL ou DEFAULT, são removidos. O ALTER COLUMN do PostgreSQL muda uma propriedade, como o tipo ou o valor padrão, e mantém o resto.

Por que adicionar NOT NULL ou UNIQUE a uma tabela existente pode falhar?

A restrição é verificada nas linhas que já estão na tabela. NOT NULL falha se houver um valor NULL, UNIQUE falha com duplicados e a chave estrangeira falha com valores que não existem na tabela referenciada. Limpe os dados primeiro.

Como adicionar uma coluna obrigatória a uma tabela grande em produção?

Adicioná-la com um DEFAULT constante, o que é uma alteração rápida de metadados no MySQL 8.0 e no PostgreSQL 11+; ou adicioná-la aceitando NULL, preenchê-la em lotes com UPDATE e depois definir NOT NULL. Testar em uma cópia e executar com carga baixa.