Deletando e alterando dados considerando chaves estrangeiras

Então, a gente já sabe o que são chaves estrangeiras (FOREIGN KEY), como elas funcionam e até já praticou criando tabelas usando elas. Mas e aí, o que fazer quando chega a hora de deletar dados ou alterar registros que estão relacionados? Agora vamos ver como funciona a exclusão e alteração de dados junto com as chaves estrangeiras, e quais são as manhas desse processo.

Pensa que o banco de dados é tipo um castelo de cartas. Se você tira uma carta, pode derrubar tudo. É aí que entram as chaves estrangeiras, que evitam que a gente "quebre" o banco ao deletar dados relacionados. Bora entender como isso funciona.

O que acontece se tentar deletar dados?

Quando tem uma chave estrangeira na tabela, o SGBD verifica se o registro está ligado a outras tabelas. Se estiver, tentar deletar pode causar um erro de integridade de dados. Pra não ter surpresa, dá pra definir antes o que fazer com a chave estrangeira. Essas ações são configuradas com as opções ON DELETE.

Configurando o comportamento com ON DELETE

Você pode escolher uma dessas regras pra chave estrangeira na hora de criar ela:

1. ON DELETE CASCADE: Deletar um registro na tabela pai automaticamente deleta todos os registros relacionados na tabela filha.
2. ON DELETE SET NULL: Em vez de deletar os registros relacionados, a chave estrangeira deles vira NULL.
3. ON DELETE SET DEFAULT: O valor da chave estrangeira vira o valor padrão.
4. ON DELETE RESTRICT (comportamento padrão): Não dá pra deletar o registro se tiver registros relacionados, e vai dar erro.
5. ON DELETE NO ACTION: Quase igual ao RESTRICT, mas a checagem de integridade só acontece no fim da transação.

Exemplo: Exclusão em cascata ON DELETE CASCADE

-- Tabela de clientes
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

-- Tabela de pedidos
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id) ON DELETE CASCADE,
    order_date DATE NOT NULL
);

-- Inserindo dados
INSERT INTO customers (name) VALUES ('Ivan Ivanov');
INSERT INTO orders (customer_id, order_date) VALUES (1, '2023-10-01');

-- Deletando cliente
DELETE FROM customers WHERE customer_id = 1;

-- Conferindo o que rolou na tabela orders
SELECT * FROM orders; -- Nenhum registro, todos foram deletados em cascata!

Quando a gente deleta um registro da tabela customers, o PostgreSQL automaticamente deleta todos os pedidos desse cliente na tabela orders.

Exemplo: Definindo referências como NULL ON DELETE SET NULL

-- Tabela de pedidos com nova regra de comportamento
CREATE TABLE orders_with_null (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id) ON DELETE SET NULL,
    order_date DATE NOT NULL
);

-- Inserindo dados
INSERT INTO orders_with_null (customer_id, order_date) VALUES (1, '2023-10-01');

-- Deletando cliente
DELETE FROM customers WHERE customer_id = 1;

-- Conferindo a tabela orders_with_null
SELECT * FROM orders_with_null;

Resultado:

Com ON DELETE SET NULL a gente consegue manter os pedidos, mas "desvincular" eles do cliente que não existe mais.

Alterando dados considerando chaves estrangeiras

Além de deletar, mudar dados nas tabelas pai também pode afetar os registros relacionados. Por exemplo, o que acontece se o cliente muda o customer_id? É aí que entra a opção ON UPDATE.

Configurando o comportamento com ON UPDATE

Dá pra tratar mudanças na tabela pai usando essas estratégias:

1. ON UPDATE CASCADE: mudar o valor da chave estrangeira na tabela pai automaticamente atualiza nas tabelas filhas.
2. ON UPDATE SET NULL: o valor da chave estrangeira nas tabelas filhas vira NULL.
3. ON UPDATE SET DEFAULT: vira o valor padrão.
4. ON UPDATE RESTRICT: não pode mudar o valor da chave estrangeira se tiver registros relacionados.
5. ON UPDATE NO ACTION: a checagem fica pro fim da transação.

Exemplo: Atualização em cascata ON UPDATE CASCADE

CREATE TABLE customers_with_cascade (
    customer_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE orders_with_cascade (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers_with_cascade(customer_id) ON UPDATE CASCADE,
    order_date DATE NOT NULL
);

-- Inserindo dados
INSERT INTO customers_with_cascade (name) VALUES ('Ivan Ivanov');
INSERT INTO orders_with_cascade (customer_id, order_date) VALUES (1, '2023-10-01');

-- Mudando customer_id
UPDATE customers_with_cascade SET customer_id = 100 WHERE customer_id = 1;

-- Conferindo a tabela orders_with_cascade
SELECT * FROM orders_with_cascade;

Resultado:

Quando muda o customer_id, o PostgreSQL atualiza ele automaticamente na tabela orders_with_cascade.

Praticando: Tabela enrollments

Bora lembrar do nosso exemplo com estudantes students e cursos courses. Vamos trabalhar com a tabela de inscrições em cursos enrollments e configurar o controle de exclusão de dados.

-- Tabela de estudantes
CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

-- Tabela de cursos
CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    title TEXT NOT NULL
);

-- Tabela intermediária de inscrições
CREATE TABLE enrollments (
    student_id INT REFERENCES students(student_id) ON DELETE CASCADE,
    course_id INT REFERENCES courses(course_id) ON DELETE CASCADE,
    PRIMARY KEY (student_id, course_id)
);

-- Inserindo dados
INSERT INTO students (name) VALUES ('Aleksei Petrov');
INSERT INTO courses (title) VALUES ('Matematika');
INSERT INTO enrollments (student_id, course_id) VALUES (1, 1);

-- Deletando estudante
DELETE FROM students WHERE student_id = 1;

-- Conferindo a tabela enrollments
SELECT * FROM enrollments; -- Vazio! O registro foi deletado automaticamente.

Erros comuns e como evitar

Um erro bem comum é tentar deletar um registro da tabela pai sem configurar direito o comportamento das chaves estrangeiras. Por exemplo, se você não colocar nada pra ON DELETE, o padrão vai ser RESTRICT, e isso vai dar erro.

Lembra também que usar operações em cascata (CASCADE) demais pode dar ruim. Tipo, você pode acabar deletando mais dados do que queria.

Pra evitar esses problemas, segue essas dicas:

• Sempre pensa bem no comportamento de ON DELETE e ON UPDATE, de acordo com a lógica do seu app.
• Pra operações importantes, faz consultas de checagem antes de alterar ou deletar.
• Usa transações pra poder desfazer as mudanças se der ruim.

2

Tarefa

SQL SELF, nível 20, lição 3

Bloqueado

Remoção com restrição

Remoção com restrição

Abrir

2

Tarefa

SQL SELF, nível 20, lição 3

Bloqueado

Exclusão em cascata

Exclusão em cascata

Abrir