Como restringir o update de um registro caso ele possua referencias?

Fr4nc0w · Agosto 19, 2009

Dae galera beleza? eu estou precisando restringir um update em um registro que possua referencias em outra tabela NxN.

Exemplo:

Tabela Empresa: id_empresa, nome_empresa.

Tabela Tags:id_tag,nome_tag, id_categoria.

Tabela Categoria: id_categoria, nome_categoria, id_categoria_mae

Tabela empresa_tags: id_tag, id_empresa

Agora oq acontece, se uma tag esta ligada a uma empresa, eu nao posso deixar que a pessoa mude a categoria dela e preciso restringir esse update.

O delete eu consegui impedir, já o update não estou entendendo por que não dá, eu sei que é uma constraint mas não está indo.

Estou usando MYSQL com InnoDB, restrições tem funcionado, mas essa por acaso não.

flw e vlwww !

João Batista Neto · Agosto 19, 2009

eu estou precisando restringir um update em um registro que possua referencias em outra tabela NxN.

Agora oq acontece, se uma tag esta ligada a uma empresa, eu nao posso deixar que a pessoa mude a categoria dela e preciso restringir esse update.

O delete eu consegui impedir, já o update não estou entendendo por que não dá, eu sei que é uma constraint mas não está indo.

Bom amigo, eu tentei reproduzir aqui seu problema, mas não consegui (para mim funcionou :P)

Olha só, criei duas tabelas:

mysql> create table `Empresa` (
    -> `id_empresa` mediumint(8) unsigned not null auto_increment,
    -> `nome_empresa` varchar(50) not null,
    -> primary key( `id_empresa` )
    -> ) engine=InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql> create table `empresa_tags` (
    ->   `id_tag` mediumint(8) unsigned not null auto_increment,
    ->   `id_empresa` mediumint(8) unsigned not null,
    ->   primary key  (`id_tag`),
    ->   key `FKempresas` (`id_empresa`),
    ->   constraint `FKempresas` foreign key (`id_empresa`) references `Empresa` (`id_empresa`) ON DELETE CASCADE ON UPDATE CASCADE
    -> ) engine=InnoDB;
Query OK, 0 rows affected (0.01 sec)

Depois inseri alguns dados nas duas:

mysql> insert into `Empresa`(`nome_empresa`) values('Teste'),('Empresa'),('Outra');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into `empresa_tags`(`id_empresa`) values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

Agora temos a tabela Empresa com:

mysql> select * from `Empresa`;
+------------+--------------+
| id_empresa | nome_empresa |
+------------+--------------+
|          1 | Teste        |
|          2 | Empresa      |
|          3 | Outra        |
+------------+--------------+
3 rows in set (0.01 sec)

e a empresa_tags com:

mysql> select * from `empresa_tags`;
+--------+------------+
| id_tag | id_empresa |
+--------+------------+
|      1 |          1 |
|      2 |          2 |
|      3 |          3 |
+--------+------------+
3 rows in set (0.00 sec)

Ai para testar se está funcionando, fui até a tabela Empresa e mudei o id de um registro:

mysql> update `Empresa` set `id_empresa`=4 where `id_empresa`=3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Agora a empresa com nome Outra tem o id_empresa com o valor 4 e não mais 3, o esperado então é que nossa tabela empresa_tags tenha sido atualizada também, correto ?

mysql> select * from `empresa_tags`;
+--------+------------+
| id_tag | id_empresa |
+--------+------------+
|      1 |          1 |
|      2 |          2 |
|      3 |          4 |
+--------+------------+
3 rows in set (0.00 sec)

Ai está, a atualização na tabela Empresa causou a atualização também na tabela empresa_tags.

Verifique se você definiu o ON DELETE e o ON UPDATE com CASCADE e se não for isso, pode ser interessante você colocar o seu DDL aqui.

Fr4nc0w · Agosto 19, 2009

blzz até ai acabou me ajudando a colocar 1 coisa a mais pra consistencia do banco..mas o caso é o seguinte, essa tag que você ligou a empresa, possui uma categoria, e a ligacao da tag com a categoria é assim:

Como impedir de mudar a categoria da tag, caso essa tag já esteja ligada a alguma empresa na tabela empresa_tag?

vlw flw

Categoria:

CREATE TABLE IF NOT EXISTS `categoria` (
  `id_categoria` int(11) NOT NULL AUTO_INCREMENT,
  `titulo` varchar(80) NOT NULL,
  `categoriamae` int(11) DEFAULT '0',
  PRIMARY KEY (`id_categoria`),
  KEY `fk_pessoa_categoria` (`id_pessoa`),
  KEY `FK_CATEGORIA_CATEGORIAMAE` (`categoriamae`)
) ENGINE=InnoDB;

ALTER TABLE `categoria`
  ADD CONSTRAINT `fk_pessoa_categoria` FOREIGN KEY (`id_pessoa`) REFERENCES `pessoa` (`id_pessoa`) ON UPDATE NO ACTION;

Tag:

CREATE TABLE IF NOT EXISTS `tag` (
  `id_tag` int(11) NOT NULL AUTO_INCREMENT,
  `titulo` varchar(80) NOT NULL,
  `id_categoria` int(11) DEFAULT '0',    
  PRIMARY KEY (`id_tag`),
  KEY `fk_pessoa_tag` (`id_pessoa`),
  KEY `fk_categoria_tag` (`id_categoria`)
) ENGINE=InnoDB;

ALTER TABLE `tag`
  ADD CONSTRAINT `fk_categoria_tag` FOREIGN KEY (`id_categoria`) REFERENCES `categoria` (`id_categoria`),
  ADD CONSTRAINT `fk_pessoa_tag` FOREIGN KEY (`id_pessoa`) REFERENCES `pessoa` (`id_pessoa`);

ALTER TABLE `empresa_tag`
  ADD CONSTRAINT `FK_EMPRESA_EMPRESATAG` FOREIGN KEY (`id_empresa`) REFERENCES `empresa` (`id_empresa`) ON UPDATE CASCADE,
  ADD CONSTRAINT `FK_TAG_EMPRESATAG` FOREIGN KEY (`id_tag`) REFERENCES `tag` (`id_tag`);

João Batista Neto · Agosto 19, 2009

Como impedir de mudar a categoria da tag, caso essa tag já esteja ligada a alguma empresa na tabela empresa_tag?

Você quer mesmo impedir e não causar a atualização em cascata, é isso ?

Matias Rezende · Agosto 19, 2009

Olha este post aqui. Tem uma boa explicação sobre o assunto.

http://forum.imasters.com.br/index.php?/topic/352622-on-update-e-on-delete/

Carlos Eduardo

Fr4nc0w · Agosto 20, 2009

não a atualização em cascata eu queria não deixar atualizar caso tal registro tenha referencias.

Pois qual é o contexto, quando é selecionada uma categoria, são exibidas as tags dessa categoria e dai sim é selecionada a tag e ligada a empresa.

se por acaso trocar a tag de categoria, fica tudo inconsistente, pois a empresa estará em uma categoria e as tags que a empresa possui já não são da categoria que a empresa selecionou.

então preciso impedir que uma tag que foi selecionada, possa ser editada, para evitar que os dados fiquem perdidos.

problema é que não consigo hehehe.

flw

João Batista Neto · Agosto 20, 2009

Pois qual é o contexto, quando é selecionada uma categoria, são exibidas as tags dessa categoria e dai sim é selecionada a tag e ligada a empresa.

se por acaso trocar a tag de categoria, fica tudo inconsistente, pois a empresa estará em uma categoria e as tags que a empresa possui já não são da categoria que a empresa selecionou.

Puxa amigo, acho que ainda não entendi direito, olha só:

Criei a tabela de categorias e inseri dois registros nela:

mysql> create table `Categoria` (
    ->     `id_categoria` mediumint(8) unsigned not null auto_increment,
    ->     `nome_categoria` varchar(30) not null,
    ->     `id_categoria_mae` mediumint(8) unsigned default null,
    ->     primary key(`id_categoria`),
    ->     key `subs`(`id_categoria_mae`)
    -> ) engine=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into `Categoria`(`nome_categoria`) values('Teste'),('Categoria');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

Depois fui até a tabela empresa_tags e criei uma coluna para abrigar a categoria e inseri para todos os registros o valor 1:

mysql> alter table `empresa_tags` add column `id_categoria` mediumint(8) unsigned not null after `id_empresa`,
    -> add index `FKcategorias`(`id_categoria`);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> update `empresa_tags` set `id_categoria`=1;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

Ai então criei a restrição para a categoria:

mysql> alter table `empresa_tags` add constraint `FKcategorias` foreign key `FKcategorias`(`id_categoria`)
    ->     references `Categoria`(`id_categoria`)
    ->     ON DELETE CASCADE
    ->     ON UPDATE CASCADE;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

Bom, agora a tabela empresa_tags ficou assim:

mysql> show create table `empresa_tags`;
CREATE TABLE `empresa_tags` (
  `id_tag` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `id_empresa` mediumint(8) unsigned NOT NULL,
  `id_categoria` mediumint(8) unsigned NOT NULL,
  PRIMARY KEY (`id_tag`),
  KEY `FKempresas` (`id_empresa`),
  KEY `FKcategorias` (`id_categoria`),
  CONSTRAINT `FKcategorias` FOREIGN KEY (`id_categoria`) REFERENCES `Categoria` (`id_categoria`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FKempresas` FOREIGN KEY (`id_empresa`) REFERENCES `Empresa` (`id_empresa`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |

Agora o caso:

1. É escolhida uma categoria:

mysql> select * from `Categoria`;
+--------------+----------------+------------------+
| id_categoria | nome_categoria | id_categoria_mae |
+--------------+----------------+------------------+
|            1 | Teste          |             NULL |
|            2 | Categoria      |             NULL |
+--------------+----------------+------------------+
2 rows in set (0.00 sec)

Escolhemos a Teste, vamos pegar as tags dessa categoria:

mysql> select
    ->     t.*
    -> from
    ->     `empresa_tags` t
    -> left join
    ->     `Categoria` c
    -> on
    ->     c.`nome_categoria` = 'Teste'
    -> where
    ->     t.`id_categoria` = c.`id_categoria`;
+--------+------------+--------------+
| id_tag | id_empresa | id_categoria |
+--------+------------+--------------+
|      1 |          1 |            1 |
|      2 |          2 |            1 |
|      3 |          4 |            1 |
+--------+------------+--------------+
3 rows in set (0.00 sec)

Selecionando todo mundo:

mysql> select
    ->     e.`nome_empresa`,
    ->     c.`nome_categoria`,
    ->     t.`id_tag`
    -> from
    ->     `empresa_tags` t
    -> left join
    ->     `Empresa` e
    -> on
    ->     e.`id_empresa` = t.`id_empresa`
    -> left join
    ->     `Categoria` c
    -> on
    ->     ( c.`id_categoria` = t.`id_categoria` ) AND ( c.`nome_categoria` = 'Teste' )
    -> where
    ->     t.`id_categoria` = c.`id_categoria`
    -> order by
    ->     e.`nome_empresa`;
+--------------+----------------+--------+
| nome_empresa | nome_categoria | id_tag |
+--------------+----------------+--------+
| Empresa      | Teste          |      2 |
| Outra        | Teste          |      3 |
| Teste        | Teste          |      1 |
+--------------+----------------+--------+
3 rows in set (0.01 sec)

Agora vamos mudar a Categoria:

mysql> update `Categoria` set `id_categoria`=4 where `id_categoria`=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Fazemos a consulta novamente:

mysql> select
    ->     t.*
    -> from
    ->     `empresa_tags` t
    -> left join
    ->     `Categoria` c
    -> on
    ->     c.`nome_categoria` = 'Teste'
    -> where
    ->     t.`id_categoria` = c.`id_categoria`;
+--------+------------+--------------+
| id_tag | id_empresa | id_categoria |
+--------+------------+--------------+
|      1 |          1 |            4 |
|      2 |          2 |            4 |
|      3 |          4 |            4 |
+--------+------------+--------------+
3 rows in set (0.01 sec)

Mesmo mudando a categoria nossas referências continuam sólidas:

mysql> select
    ->     e.`nome_empresa`,
    ->     c.`nome_categoria`,
    ->     t.`id_tag`
    -> from
    ->     `empresa_tags` t
    -> left join
    ->     `Empresa` e
    -> on
    ->     e.`id_empresa` = t.`id_empresa`
    -> left join
    ->     `Categoria` c
    -> on
    ->     ( c.`id_categoria` = t.`id_categoria` ) AND ( c.`nome_categoria` = 'Teste' )
    -> where
    ->     t.`id_categoria` = c.`id_categoria`
    -> order by
    ->     e.`nome_empresa`;
+--------------+----------------+--------+
| nome_empresa | nome_categoria | id_tag |
+--------------+----------------+--------+
| Empresa      | Teste          |      2 |
| Outra        | Teste          |      3 |
| Teste        | Teste          |      1 |
+--------------+----------------+--------+
3 rows in set (0.01 sec)

Matias Rezende · Agosto 20, 2009

Vou mover para o Mysql, porque a dúvida é de lá.

Tópico Movido

PHP http://forum.imasters.com.br/public/style_emoticons/default/seta.gif Mysql

Fr4nc0w · Agosto 20, 2009

é que existe uma tabela: tag.

e o id_categoria está na tabela tage nao na tabela empresa_tag.

chegando em casa posto algo mais completo aqui..

agora to saindo do trabalho..

flw

Fr4nc0w · Agosto 21, 2009

Não consegui por pra editar o post...mas vai logo abaixo:

Preciso impedir uma pessoa de trocar a categoria de uma tag que ja está ligada a alguma empresa.

Todas as tabelas do q eu falei:


CREATE TABLE IF NOT EXISTS `categoria` (
  `id_categoria` int(11) NOT NULL AUTO_INCREMENT,
  `titulo` varchar(80) NOT NULL,
  `categoriamae` int(11) DEFAULT '0',
  PRIMARY KEY (`id_categoria`),
  KEY `fk_pessoa_categoria` (`id_pessoa`),
  KEY `FK_CATEGORIA_CATEGORIAMAE` (`categoriamae`)
) ENGINE=InnoDB ;
ALTER TABLE `categoria`
  ADD CONSTRAINT `fk_pessoa_categoria` FOREIGN KEY (`id_pessoa`) REFERENCES `pessoa` (`id_pessoa`) ON UPDATE NO ACTION;

CREATE TABLE IF NOT EXISTS `empresa` (
  `id_empresa` int(11) NOT NULL AUTO_INCREMENT,
  `nome` varchar(100) NOT NULL,  
  `id_categoria` int(11) DEFAULT NULL,
  PRIMARY KEY (`id_empresa`),
  KEY `fk_categoria_empresa` (`id_categoria`)
) ENGINE=InnoDB;
ALTER TABLE `empresa`
  ADD CONSTRAINT `fk_categoria_empresa` FOREIGN KEY (`id_categoria`) REFERENCES `categoria` (`id_categoria`) ON UPDATE NO ACTION,
  ADD CONSTRAINT `fk_pessoa_empresa` FOREIGN KEY (`id_pessoa`) REFERENCES `pessoa` (`id_pessoa`) ON UPDATE NO ACTION,
  ADD CONSTRAINT `fk_prioridade_empresa` FOREIGN KEY (`id_prioridade`) REFERENCES `prioridade` (`id_prioridade`) ON UPDATE NO ACTION;


CREATE TABLE IF NOT EXISTS `empresa_tag` (
  `id_tag` int(11) NOT NULL,
  `id_empresa` int(11) NOT NULL,
  PRIMARY KEY (`id_tag`,`id_empresa`),
  KEY `FK_EMPRESA_EMPRESATAG` (`id_empresa`)
) ENGINE=InnoDB;
ALTER TABLE `empresa_tag`
  ADD CONSTRAINT `empresa_tag_ibfk_1` FOREIGN KEY (`id_tag`) REFERENCES `tag` (`id_tag`),
  ADD CONSTRAINT `FK_EMPRESA_EMPRESATAG` FOREIGN KEY (`id_empresa`) REFERENCES `empresa` (`id_empresa`) ON UPDATE CASCADE;

CREATE TABLE IF NOT EXISTS `tag` (
  `id_tag` int(11) NOT NULL AUTO_INCREMENT,
  `titulo` varchar(80) NOT NULL,  
  `id_categoria` int(11) DEFAULT '0',
  PRIMARY KEY (`id_tag`),
  KEY `fk_pessoa_tag` (`id_pessoa`),
  KEY `fk_categoria_tag` (`id_categoria`)
) ENGINE=InnoDB;
ALTER TABLE `tag`
  ADD CONSTRAINT `tag_ibfk_2` FOREIGN KEY (`id_pessoa`) REFERENCES `pessoa` (`id_pessoa`) ON UPDATE CASCADE,
  ADD CONSTRAINT `tag_ibfk_1` FOREIGN KEY (`id_categoria`) REFERENCES `categoria` (`id_categoria`) ON UPDATE CASCADE;

Motta · Agosto 21, 2009

Uma trigger me parece a melhor solução.

http://imasters.com.br/artigo/8123/mysql/mysql_-_triggers/imprimir/

Fr4nc0w · Agosto 21, 2009

eu pensei em trigger mesmo antes, pois vi que só tem a ver com o ID da tabela referênciada e não como os dados como um todo.

fazendo aqui não foi dificil criar o raciocinio para montar a trigger, mas quero saber agora como impedir o update:

CREATE TRIGGER verifica_tag_empresa ON tag
	FOR EACH ROW
 		BEGIN
 		       	SELECT COUNT(id_tag) INTO @id_tag FROM tag  WHERE id_tag = @id_tag;
                        //o resultado de count foi pro @id_tag?
		IF (@cliente_id = 0)
			// OQ POR AQUI AGORA??? SINCERAMENTE NÃO TENHO IDÉIA DO QUE FAZER PARA RESTRINGIR.
		END IF;
	END

Motta · Agosto 21, 2009

Deve haver na linguagem alguma forma de criar um erro é só disparar um erro.

Fr4nc0w · Agosto 21, 2009

Não estou conseguindo editar meu post, =(

mas uma correção da trigger acima.

Delimiter//
CREATE TRIGGER verifica_tag_empresa BEFORE UPDATE ON tag
	FOR EACH ROW
 		BEGIN
 		       	SELECT COUNT(id_empresa) INTO @tag_id FROM empresa_tag WHERE id_tag = @id_tag;
		IF (@tag_id > 0) THEN
   	            ????????/
		END IF;
	END
END//

ai cheguei a uma conclusão, talvez seja melhor usar procedure não? levando em conta que TRIGGER não pode passar nenhuma mensagem, e eu preciso enviar uma mensagem avisando que não pode ser alterada.

vlw e flw!

Lucas Renan · Agosto 22, 2009

não sei se é a melhor solução, e não sei se eu entendi direito..

mas você poderia criar uma procedure,

dentro dela, você faz um SELECT COUNT() para verificar se existem registros (consequentemente relacionamentos)

se não houver você altera, insere, sei lah

Arquivado

Como restringir o update de um registro caso ele possua referencias?

Recommended Posts

Fr4nc0w 1

Compartilhar este post

Link para o post

Compartilhar em outros sites

João Batista Neto 448

Compartilhar este post

Link para o post

Compartilhar em outros sites

Fr4nc0w 1

Compartilhar este post

Link para o post

Compartilhar em outros sites

João Batista Neto 448

Compartilhar este post

Link para o post

Compartilhar em outros sites

Matias Rezende 50

Compartilhar este post

Link para o post

Compartilhar em outros sites

Fr4nc0w 1

Compartilhar este post

Link para o post

Compartilhar em outros sites

João Batista Neto 448

Compartilhar este post

Link para o post

Compartilhar em outros sites

Matias Rezende 50

Compartilhar este post

Link para o post

Compartilhar em outros sites

Fr4nc0w 1

Compartilhar este post

Link para o post

Compartilhar em outros sites

Fr4nc0w 1

Compartilhar este post

Link para o post

Compartilhar em outros sites

Motta 645

Compartilhar este post

Link para o post

Compartilhar em outros sites

Fr4nc0w 1

Compartilhar este post

Link para o post

Compartilhar em outros sites

Motta 645

Compartilhar este post

Link para o post

Compartilhar em outros sites

Fr4nc0w 1

Compartilhar este post

Link para o post

Compartilhar em outros sites

Lucas Renan 2

Compartilhar este post

Link para o post

Compartilhar em outros sites

Javascript - Calcular Valores Monetário em Tempo Real

JavaScript - Exibir logo dentro da Tabela html

PHP+Codeigniter - Implementar o Input CÓDIGO ou ID do Produto

Fóruns

Tempo Real

Informação importante