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

>

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;

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;

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;

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.

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`);

>
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 ?

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

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

>
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;

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`);

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;

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

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`;

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`;

Agora vamos mudar a Categoria:

mysql> update `Categoria` set `id_categoria`=4 where `id_categoria`=1;

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`;

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`;

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

é 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

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;

Uma trigger me parece a melhor solução.

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

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

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

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

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!

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