Integridade Referencial no MySQL

[walace 1]

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

[Wagner Web 0]

Eu também havia lido esta matéria no site do SQL Magazine. Mesmo como um programador jr., recomendo totalmente! ;) []'s

[ligianelc 0]

Cara que matéria ótima, isso era tudo que eu tava precisando saber... valeu!!!

http://forum.imasters.com.br/public/style_emoticons/default/clap.gif

[xcross 0]

Só uma dúvida:Já tenho uma tabela pronta no MySQL do tipo MyISAM e quero trocá-la para o tipo innoDB, estou usando o phpMyAdmin 2.6.1-pl3 , entro nas operações da tabela aonde se troca o tipo, mas só tem disponível:- MyISAM- Heap- ISAM- MergeComo prossigo?Obrigado

[thiagocodu 0]

http://forum.imasters.com.br/public/style_emoticons/default/clap.gif Parabéns pelo artigo!

Essa matéria vai me ajudar muito na minha formação, creio que isso ocorre tb com muita gente, principalmente que está começando como eu...

Valeu mesmo! http://forum.imasters.com.br/public/style_emoticons/default/thumbsup.gif

[cristiano de oliveira farias 0]

^_^ é possível alterar o tipo da tabela com o comando SQL:ALTER TABLE nome_da_tabela TYPE INNODBmesmo que ela possua conteúdo.

[Macinux 0]

Só uma dúvida:

Já tenho uma tabela pronta no MySQL do tipo MyISAM e quero trocá-la para o tipo innoDB, estou usando o phpMyAdmin 2.6.1-pl3 , entro nas operações da tabela aonde se troca o tipo, mas só tem disponível:

 

- MyISAM

- Heap

- ISAM

- Merge

 

Como prossigo?

 

Obrigado

<{POST_SNAPBACK}>

Então rapaiz,

 

se você estiver fazendo da seguinte maneira...

 

entrou no phpmyadmin clicou na base no lado esquerdo....

depois clicou na tabela no lado esquerdo logo abaixo do nome do banco...

depois na área central escolheu a opção operações(operations caso seu phpmyadmin esteja em inglês) e na opção Tipo de tabela (table type) não apararece o tipo InnoDB......

 

O que acontece é o seguinte, se você estiver hospedado em um provedor você deve verificar com ele se o mysql dele foi configurado para suportar o InnoDB senão, no way honey!!! e também se a versão dele do phpmyadmin deixa você trabalhar com as tabelas em InnoDB. Caso seu banco esteja em máquina local verifique a sua instalação e a versão do phpmyadmin e também do mysql.

 

Espero que ajude!!!

 

abraços. http://forum.imasters.com.br/public/style_emoticons/default/thumbsup.gif

[davistome 1]

Grande explicação..........valeu mesmo......quem esta aprendendo nem sabe que isso existe!...srsrObrigado!

[PHPSistemas 0]

Otima materia, muito bom o post, só fiquei com uma dúvida... Como ficaria o backup no mysql usando refêrencia? um projeto complexo ficaria quase impossivel o backup de apenas 1 tabela seguida de restauração?Forte abraço a todos... e obrigado!

[Prog 183]

Quando você passa a ter integridade referencial, as informações de uma tabela passam a depender uma das outras, então, você deve ter uma ordem para fazer a restauração desses dados, para manter a integridade, se esta ordem não for estabelecida, sempre que for tentar restaurar, vai dar erro de integridade.

 

Com relação ao tipo de tabelas InnoDB, vale saber que recentemente a empresa responsável por este tipo de tabela do MySQL foi adiquirida pela Oracle, ela comenta que dará continuidade ao projeto como Open Source, mas não comenta sobre licenças ou coisas do genero, esperamos que permaneça da forma que esta.

 

Leia mais em:

http://www.oracle.com/innodb/

[TuzinAr3_ 0]

Tiro todas minha duvidas sobre a integridade...

[Marcos Vinicius Albuquerque 0]

Muito bom esse artigo, mas veja:alem dessas tabelas eu tenhoTABELA = ALUNO, CURSO, SEMESTRE, DISCIPLINA, NOTAS DA REFERIDA DISCIPLINA, FALTAS DA UMA OUTRA REFERIDA DISCIPLINA. COMO EU FAÇO PRA RELACIONAR TUDO ISSO? ESTOU SEM DORMIR JÁ... OBRIGADO... Se puder fazer um create table pra mim com os INNODB eu me viro dai pra frente.. eu não sei como fazer a estrutura disso... e relacionar o ALUNO que tem o ID x,y,z estar matriculardo no determinado curso, no determinado semestre que contem as determinadas disciplinas, que o mesmo ID do aluno tem tais notas em tais disciplina e tem x,y,z faltas nas respequitivas disciplina.... Se alguem puder me dar uma luz de por onde começar..... ex:CRIE TABELAS ASSIM OU ASSADO, FAÇA PAGINAS ASSIM OU ASSADO.... FAÇA UM TESTE .... VEJA COMO FUNCIONA E PRONTO AGORA MANDA BALA.... OBRIGADO.. E DEUS ABENÇOE OS QUE AJUDAREM...!

[cyrusbr 0]

como faço uma consulta nesse banco retornando o alun e suas notas por exemplo ?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;

[Eclesiastes 2]

SELECT aluno.nome, notas.nota FROM aluno INNER JOIN notas ON notas.aluno_id = aluno.id

http://www.sqlmagazine.com.br/Colunistas/P...pos_join_PI.asp

[Wagner Bianchi 0]

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.

Olá pessoal,

 

Somente para complementar os conceitos apresentados na matéria que abre este tópico, não é somente o table type ou engine InnoDB que implementa a Integridade Referencial. Ainda temos uma outra engine que também implementa que é o Berkeley Database, mais conhecido no meio por BDB.

 

Este engine está disponível no mysql (para usuários de mysql no windows, no binário mysqld-max), no linux é instalado por default, dependendo de como foi instalado.

 

As diferenças entre o InnoDB é o BDB são basicamente o nível de lock nas tabelas. O InnoDB bloqueia a linha e o BDB bloqueia a coluna. Tabelas BDB são representadas em disco por um arquivo ".frm", assim como tabelas InnoDB e um arquivo que é o diferencial, com extensão ".db", que armazena informações de índices e dados. Os índices e dados no InnoDB são armazenados dentro do tablespace.

 

Integridade Referencial: só poderá ser cadastrado no campo que é foreign key, aquilo que tem na primary key, ou seja, não podemos ter itens da nota fiscal para uma nota fiscal ainda não cadastrada, ok? http://forum.imasters.com.br/public/style_emoticons/default/thumbsup.gif

 

Vale salientar que, mesmo com o bloqueio em nível de domínio/coluna, é possível termos, também com tabelas BDB, deadlocks.

 

Um abraço a todos e espero ter contrinuído.

 

=> Vem aí o FALCON, uma novidade prevsita para o MySQL 6.0, saibam mais clicando aqui!

 

http://forum.imasters.com.br/public/style_emoticons/default/thumbsup.gif

[JCSPADER 0]

Não estou conseguindo implementar com perfeição, poderiam me ajudar?

 

 

Regras de Negocio:

-> Nega a exclusão em dispon_armario se contiver registros em dispon_caixa;

-> Exclui em cascata os registros de dispon_cliente se o registro de dispon_caixa for excluido;

-> Nao permite adicionar registro em dispon_cliente sem referencia de dispon_caixa;

-> Nao permite adicionar registro em dispon_caixa sem referencia de dispon_armario

[PauloRob 1]

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

Muito obrigado pela força, esse esclarecimento foi muito útil para mim pois sou iniciante em mysql e estou iniciando minha construção de base de dados, obrigado mais uma vez e parabéns pela iniciativa.