Controle de integridade do banco de dados
Outra coisa importante a saber sobre bancos de dados é CONSTRAINS. Com a ajuda de restrições, você pode controlar as alterações de dados em suas tabelas e manter sua integridade e consistência.
O que é consistência de dados quando falamos de banco de dados?
Vamos pegar nossa loja online com tabelas de funcionários, produtos e tarefas . Já sabemos que pode haver tarefas na tabela de tarefas que não são atribuídas a ninguém: o employee_id dessas linhas é NULL.
Mas o que acontece se houver uma entrada na tabela de tarefas com um employee_id igual a, digamos, 115? Afinal, não temos esse funcionário. Não temos um funcionário com id = 115 na tabela de funcionários. Ao mesmo tempo, um link para um funcionário com esse id está na tabela de tarefas. Este é um exemplo de inconsistência de dados .
Então, como reconciliamos esses dados? O ideal seria que o servidor SQL, com qualquer alteração de dados, controlasse todas essas nuances. E existe essa oportunidade, chama-se FOREIGN_KEY.
Se alguma coluna em sua tabela contiver não apenas números, mas também linhas de identificação de outra tabela, isso poderá ser especificado explicitamente.
Adicionando uma CHAVE ESTRANGEIRA
Essa chave pode ser adicionada à tabela no estágio de sua criação e depois, usando ALTER TABLE. O formato não é fundamentalmente diferente. Apresentaremos as duas opções.
A forma geral de tal chave/regra é:
FOREIGN KEY (column)
REFERENCES table(column)
Vamos adicionar esta chave/regra à tabela de tarefas para garantir que todos os Employees_ids da tabela se refiram a uma entrada existente na tabela de funcionários. Este script ficará assim:
ALTER TABLE task
ADD FOREIGN KEY (employee_id)
REFERENCES employee(id)
E se decidirmos adicionar essa regra na hora de criar a tabela de tarefas, o código ficaria assim:
CREATE TABLE task (
id INT,
name VARCHAR(100),
employee_id INT,
deadline DATE,
PRIMARY KEY (id),
FOREIGN KEY (employee_id)
REFERENCES employee(id)
);
A propósito, há situações em que a string a que nos referimos possui uma chave composta exclusiva: por exemplo, “Nome e ano de nascimento” ou “productCatogoryId e productId”. Então FOREIGN KEY pode ser escrito assim:
FOREIGN KEY (our_column1, our_column2)
REFERENCES table(their_column1, their_column2)
FOREIGN KEY e mudança de dados
Agora imagine uma situação em que decidimos atualizar alguns dados na tabela de funcionários e nosso id de funcionário foi alterado. O que acontecerá com os dados na tabela de tarefas? Isso mesmo, eles se tornarão irrelevantes e a integridade de nosso banco de dados será violada.
Para evitar que isso aconteça, você pode instruir o SQL Server a alterar o employee_id de todas as linhas em todas as tabelas que se referem a esse id alterado específico quando o id na tabela de funcionários for alterado.
Esses scripts são chamados OnUpdate e OnDelete . O que fazer se o ID do registro mudar e o que fazer se o registro for excluído?
Com a remoção, nem tudo é tão simples. Se você tiver objetos dependentes representados por strings no banco de dados que se referem uns aos outros, uma ampla variedade de cenários de comportamento é possível ao excluir um objeto.
Digamos que excluímos um usuário do site, o que significa que devemos excluir toda a sua correspondência pessoal. Mas é improvável que devamos remover todos os seus comentários públicos.
Ou um funcionário se demite. Seria estranho se ele desistisse e ao mesmo tempo todas as tarefas atribuídas a ele desaparecessem do banco de dados. Mas se eles tivessem permanecido nomeados não por ele, também teria acabado mal. É mais correto fazer com que o funcionário possa pedir demissão após redistribuir todas as suas tarefas para outras pessoas.
Aqui está como podemos descrever esses cenários usando FOREIGN KEY. A forma geral de tal chave/regra é:
FOREIGN KEY (column)
REFERENCES table(column)
[ON DELETE reference_option]
[ON UPDATE reference_option]
O que fazer em caso de exclusão (ON DELETE) ou alteração (ON UPDATE) de registros? No total, podem existir 5 opções para o SQL Server atuar em cada uma dessas situações:
| # | opção_de_referência | Explicação |
|---|---|---|
| 1 | RESTRINGIR | Desativar ação se referências de string forem encontradas |
| 2 | CASCATA | Alterar id em linhas dependentes |
| 3 | DEFINIR NULO | Definir id em linhas dependentes como NULL |
| 4 | SEM AÇÃO | Nada para fazer |
| 5 | DEFINIR PADRÃO x | Definir id em coletores dependentes para x |
Veja como podemos modificar nossa tabela de tarefas:
ALTER TABLE task
ADD FOREIGN KEY (employee_id)
REFERENCES employee(id)
ON UPDATE CASCADE
ON DELETE RESTRICT;
O que está escrito aqui:
ON UPDATE CASCADE : Se a chave id na tabela de funcionários for alterada, altere também o employee_id na tabela de tarefas que a referencia.
ON DELETE RESTRICT : Se uma linha estiver sendo excluída da tabela de funcionários e for referenciada na tabela de tarefas, evite que a linha seja excluída da tabela de funcionários.