Integridade Referencial no MySQL

Como essa é uma dúvida constante no fórum, resolvi postar esta matéria muito interessando que está no sqlmagazine.com.br (veja nota no final do post)

Implementando Integridade Referencial no MySQL

No artigo "Trabalhando com os vários tipos de tabelas do MySQL", vimos que no MySQL é possível escolher o formato de armazenamento da tabela no momento da sua criação, o que dá a flexibilidade de optar por um ou outro tipo de tabela, dependendo do tipo de aplicação a ser desenvolvida. Existem determinados recursos do SGBD (Sistema Gerenciador de Banco de Dados) que estão diretamente

relacionados ao tipo de tabela escolhido, tais como, controle de transação, níveis de lock e integridade referencial. Neste artigo vamos exemplificar como definir regras de integridades no MySQL.

Para trabalharmos com integridade referencial, isto é, para adicionarmos restrições de integridade (constraints) às chaves estrangeiras, é necessário criar as tabelas como InnoDB. Este recurso está disponível somente para este tipo de tabela, embora seja possível definir chaves estrangeiras e restrições outros tipos de tabelas por razões de compatibilidade. O detalhe é que neste caso, estas definições terão o efeito apenas de documentação, ou seja, o MySQL não respeitará os constraints definidos.

O InnoDB implementa as restrições de integridade CASCADE, RESTRICT, SET NULL e SET DEFAULT. No primeiro caso, ao se remover um registro da tabela referenciada pela chave estrangeira os registros relacionados àquele removido serão eliminados em todas as tabelas relacionadas. O RESTRICT não permite a remoção de registros que possuam relacionamentos em outras tabelas. Os dois últimos atribuem os valores DEFAULT ou NULL para as chaves estrangeiras cujos registros relacionados foram excluídos. O exemplo abaixo ilustra algumas tabelas que utilizam regras de integridade:

CREATE TABLE aluno (

 id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

 nome CHAR(30) NOT NULL

) TYPE=InnoDB;

 

CREATE TABLE cursos (

 id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

 nome CHAR(30) NOT NULL

) TYPE=InnoDB;

 

CREATE TABLE notas (

 aluno_id INT NOT NULL,

 cursos_id INT NOT NULL,

 date DATE NOT NULL,

 nota DOUBLE NOT NULL,

 PRIMARY KEY(aluno_id, cursos_id, date),

 INDEX i2 (cursos_id),

 FOREIGN KEY (aluno_id) REFERENCES aluno(id) ON DELETE CASCADE,

 FOREIGN KEY (cursos_id) REFERENCES cursos(id) ON DELETE RESTRICT

) TYPE=InnoDB;

 

No exemplo existem 3 tabelas: aluno, que armazena os alunos de uma faculdade; a tabela cursos que contém as disciplinas ministradas e a tabela notas com os pontos dos alunos em todos os cursos freqüentados por eles. No modelo é possível que um curso possua várias avaliações em datas distintas. Neste caso, foram criadas as tabelas como tipo InnoDB (TYPE=InnoDB), para que as regras de integridade sejam respeitadas. As regras definidas foram: um CASCADE para aluno, isto é, se for removido um registro da tabela de aluno, todas as suas notas serão removidas automaticamente. No caso da tabela de cursos, não será possível remover um curso que possua notas cadastradas para ele. Além da restrição ON DELETE, o InnoDB permite também o ON UPDATE, que aplica as restrições no caso de atualizações dos campos ralacionados entre as tabelas.

 

É importante ressaltar que o FOREIGN KEY não cria automaticamente um índice na(s) coluna(s) referenciada(s). Assim, é necessário criar explicitamente um índice nas colunas que serão chaves estrangeiras. No exemplo, a coluna aluno_id já é um índice, visto que esta é o primeiro campo da chave primária da tabela. Como cursos_id não é o primeiro campo de nenhuma chave, foi adicionado o índice i2 para esta chave estrangeira. Caso não seja criado o índice nas chaves estrangeiras, o MySQL exibirá o erro "ERROR 1005: Can't create table './test/notas.frm' (errno: 150)", onde o erro significa que há uma definição incorreta das chaves estrangeiras.

Esta matéria foi publicada em http://www.sqlmagazine.com.br por Eber M. Duarte