Relacionamento de tabelas innoDB – MySQL

[agutoli]

Neste artigo sobre relacionamento innoDB, vou mencionar algumas dicas para deixar seu sistema mais consistente e com melhor desempenho.

1. TABELAS – Crie as tabelas abaixo no seu banco

CREATE  TABLE IF NOT EXISTS `tb_category`(
  `id_category` INT NOT NULL AUTO_INCREMENT ,
  `name_category` VARCHAR(45) NULL ,
  PRIMARY KEY (`id_category`) )
ENGINE = InnoDB

CREATE  TABLE IF NOT EXISTS `tb_product` (
  `id_product` INT NOT NULL AUTO_INCREMENT ,
  `name_product` VARCHAR(45) NULL ,
  `id_category` INT(11) NOT NULL ,
  PRIMARY KEY (`id_product`) )
ENGINE = InnoDB

Obs: Repare que estou utilizando a instrução “ENGINE = InnoDB” que é responsável por criar as tabelas como InnoDB (permite relacionamentos).

2. RELACIONAMENTOS – Para criar os relacionamentos, vamos imaginar as seguintes especificações;

1. a – Usuário não poderá apagar categorias que estiverem sendo utilizadas em outras tabelas (chave estrangeira desta categoria).

O relacionamento ficaria assim;

ALTER TABLE `tb_product`
ADD FOREIGN KEY(`id_category`)
REFERENCES `tb_category`(`id_category`)
--command to delete restrict
ON DELETE RESTRICT
--command to update restrict
ON UPDATE RESTRICT;

1. b – Quando o usuário deletar ou fizer update em uma categoria, as alterações sejam aplicadas em todos os locais que possuir a chave estrangeira deste registro.

O relacionamento ficaria assim;

ALTER TABLE `tb_product`
ADD FOREIGN KEY(`id_category`)
REFERENCES `tb_category`(`id_category`)
--command to delete all registers
ON DELETE CASCADE
--command to update all registers
ON UPDATE CASCADE ;

1. c – Se o usuário apagar uma categoria que estiver sendo utilizada em outra tabela, o sistema deve setar o valor nulo nos campos que existiam este registro (chave da categoria) .

O relacionamento ficaria assim;

ALTER TABLE `tb_product`
ADD FOREIGN KEY(`id_category`)
REFERENCES `tb_category`(`id_category`)
--command to set null value
ON DELETE SET NULL 
--command to set null value
ON UPDATE SET NULL ;

1. d – Se o usuário tentar apagar um registro, o sistema deve desconsiderar a ação.

O relacionamento ficaria assim;

ALTER TABLE `tb_product`
ADD FOREIGN KEY(`id_category`)
REFERENCES `tb_category`(`id_category`)
-- command to cancel action
ON DELETE NO ACTION
-- command to cancel action
ON UPDATE NO ACTION;

REGISTROS – Muito bem, agora que definimos o tipo de ralacionamento ideal, vamos popular as tabelas.

INSERT INTO `tb_category` 
(`id_category`, `name_category`) VALUES
(1, 'Electronics');

INSERT INTO `tb_product` 
(`id_product`, `name_product`, `id_category`) VALUES
(3, 'Tv', 1),
(4, 'DVD Player', 1);

Obs: Primeiramente, estou inserindo pelo menos um registro na tabela categoria, pois se não tiver categoria cadastrada não será possível cadastrar um produto, já que um produto necessita de uma categoria.

4. TESTE – Agora que já temos tabelas,relacionamentos e registros criados, podemos fazer um teste.

1. a – Quando crie o relacionamento, ptei por DELETE RESTRICT e UPDATE RESTRICT e quando tentei apagar uma categoria que estava sendo utilizada em uma outra tabela como chave estrangeira, o MySQL apresentou a exceção abaixo ou seja, funcionou exatamente como eu queria.

Erro

consulta SQL:

DELETE FROM `tb_category` WHERE `tb_category` . `id_category` = 1 LIMIT 1

Mensagens do MySQL : Documentação

#1451 – Cannot delete or update a parent row: a foreign key constraint fails (`test/tb_product`, CONSTRAINT `tb_product_ibfk_1` FOREIGN KEY (`id_category`) REFERENCES `tb_category` (`id_category`))

Então é isso, espero que tenham gostado deste artigo, se alguem tiver dúvidas, por favor postem aqui.

Abraço!!!

[adelsjunior]

Opa beleza?

em primeiro lugar parabéns pelo exemplo simples e objetivo de relacionamento de tabelas mysql.

A opção de ON DELETE SET NULL nao funciona aqui comigo mesmo que eu tenha criado as tabelas com o codigo que você criou, pode me ajudar?

erro:

11:58:14 [ALTER - 0 row(s), 0.000 secs] [Error Code: 1005, SQL State: HY000] Can't create table './abraz/#sql-1194_2064.frm' (errno: 150)

... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 0 warnings, 1 errors]

[agutoli]

Amigo...

Desculpe-me pela resposta demorada, só a vi hoje!

Antes de tentar adicionar os relacionamente execute este comando;

ALTER TABLE `tb_product` CHANGE `id_category` `id_category` INT( 11 )  NOT NULL

Na verdade foi uma falha minha, fiz e não testei... o campo id_category do segundo create está como varchar e na verdade deve ser INT ou seja, sempre os dois campos que são relacionados devem ser iguais.

Errado
-----------------
CREATE  TABLE IF NOT EXISTS `tb_product` (
  `id_product` INT NOT NULL AUTO_INCREMENT ,
  `name_product` VARCHAR(45) NULL ,
  `id_category` VARCHAR(45) NULL ,
  PRIMARY KEY (`id_product`) )
ENGINE = InnoDB

Correto
----------------
CREATE  TABLE IF NOT EXISTS `tb_product` (
  `id_product` INT NOT NULL AUTO_INCREMENT ,
  `name_product` VARCHAR(45) NULL ,
  `id_category` INT(11) NOT NULL ,
  PRIMARY KEY (`id_product`) )
ENGINE = InnoDB