ON UPDATE e ON DELETE

[Henrique Barcelos 290]

Galera, beleza???

 

Estou tentando me aprofundar um pouco mais em MySQL e surgiu uma dúvida aqui:

 

No relacionamento das FK's, não entendi muito bem o que fazem as "funções" ON UPDATE e ON DELETE...

 

O que seria ON DELETE CASCADE e RESTRICT?

 

SET NULL, SET DEFAUL, NO ACTION, beleza, ashusahauh, fácil saber o que fazem...

 

A dúvida é com cascade e restrict msm...

 

Já fui lá no manual, mas não entendi muito bem...

 

Agradeço qualquer ajuda...

[Wagner Bianchi 0]

Vamos lá!!

 

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

 

Imagine que você tenha um banco de dados para armazenamento de produtos, clientes e compras de produtos por clientes. Temos nesse modelinho três tabelas InnoDB com integridade referencial entre elas. Bom, quando faço uma venda de um produto, tenho que informar um cliente e um produto que ele comprou para então inserir na tabela de compras, sendo que, caso eu efetive uma venda para um cliente que não existe, o banco de dados terá que inibiar a operação, assim também com produto, caso eu faça uma venda para um cliente de um produto que não tenho na tabela de produtos.

 

DB de exemplo:

 

mysql> desc produto;
+------------+------------------+------+-----+---------+----------------+
| Field	  | Type			 | Null | Key | Default | Extra		  |
+------------+------------------+------+-----+---------+----------------+
| produto_id | int(10) unsigned | NO   | PRI | NULL	| auto_increment |
| nome	   | char(40)		 | YES  |	 | NULL	|				|
+------------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> desc cliente;
+------------+------------------+------+-----+---------+----------------+
| Field	  | Type			 | Null | Key | Default | Extra		  |
+------------+------------------+------+-----+---------+----------------+
| cliente_id | int(10) unsigned | NO   | PRI | NULL	| auto_increment |
| nome	   | char(40)		 | YES  |	 | NULL	|				|
+------------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> desc compras;
+------------+------------------+------+-----+-------------------+-------+
| Field	  | Type			 | Null | Key | Default		   | Extra |
+------------+------------------+------+-----+-------------------+-------+
| cliente_id | int(10) unsigned | NO   | PRI |				   |	   |
| produto_id | int(10) unsigned | NO   | PRI |				   |	   |
| dt		 | timestamp		| YES  |	 | CURRENT_TIMESTAMP |	   |
+------------+------------------+------+-----+-------------------+-------+
3 rows in set (0.00 sec)

Carga de dados:

 

mysql> insert into cliente set nome='JOÃO MINARDES DA SORIA';
Query OK, 1 row affected (0.05 sec)

mysql> insert into cliente set nome='JUCA DA SORIA RUAÇA';
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO produto SET nome='MySQL';
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO produto SET nome='ORACLE';
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO produto SET nome='SQL Server';
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO produto SET nome='Firebird';
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO compras SET cliente_id=1, produto_id=1;
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO compras SET cliente_id=1, produto_id=2;
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO compras SET cliente_id=1, produto_id=3;
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO compras SET cliente_id=1, produto_id=4;
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO compras SET cliente_id=2, produto_id=1;
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO compras SET cliente_id=2, produto_id=2;
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO compras SET cliente_id=2, produto_id=3;
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO compras SET cliente_id=2, produto_id=4;
Query OK, 1 row affected (0.02 sec)

Ok...então, aonde entram os conceitos ON UPDATE e ON DELETE?

 

Imagine que estamos implementando o banco de dados agora. Já temos pronta a tabela de clientes e também a tabela de produto, sendo que so nos falta agora criarmos a tabela de compras (esta que registraremos qual cliente comprou qual produto e em qual data). Nesta tabela teremos duas colunas, uma cliente_id que apontará para a coluna cliente_id na tabela de clientes e outra a coluna produto_id que apontará para a coluna produto_id, na tabela de produtos.

 

Quando atribuirmos a constraint ou restrição FOREIGN KEY, podemos informar também qual será o comportamento dessa retrição, que é justamente o ON DELETE ou ON UPDATE.

 

Imagine que você deseja excluir o cliente "JOÃO MINARDES DA SORIA" da tabela de clientes e este já lhe comprou vários produtos, o que colaborou para que a tabela de compras tenha várias linhas com o ID deste cliente. Quando você exclui o JOÃO da tabela, você poderá solicitar que o SGBD siga as regras que foram configuradas para a FOREIGN KEY que foi criada em cliente_id da tabela de compras, que podem ser as seguintes:

 

ON DELETE CASCADE: quando se deleta uma registro na tabela PAI, por exemplo, ao excluir o ID de JOÃO da tabela de clientes, todos os registros na tabela de compras também serão automaticamente excluídos, CASO SUA RESTRIÇÃO TENHA SIDO CRIADA COM ESTA OPÇÃO:

 

mysql> ALTER TABLE compras ADD FOREIGN KEY (cliente_id) REFERENCES cliente(cliente_id) ON DELETE CASCADE ON UPDATE CASCADE;
Query OK, 8 rows affected (0.14 sec)
Records: 8  Duplicates: 0  Warnings: 0

Exemplo do CASCADE:

 

mysql> SELECT * FROM compras;
+------------+------------+---------------------+
| cliente_id | produto_id | dt				  |
+------------+------------+---------------------+
|		  1 |		  1 | 2009-07-08 16:22:20 |
|		  1 |		  2 | 2009-07-08 16:22:22 |
|		  1 |		  3 | 2009-07-08 16:22:24 |
|		  1 |		  4 | 2009-07-08 16:22:27 |
|		  2 |		  1 | 2009-07-08 16:22:36 |
|		  2 |		  2 | 2009-07-08 16:22:39 |
|		  2 |		  3 | 2009-07-08 16:22:41 |
|		  2 |		  4 | 2009-07-08 16:22:43 |
+------------+------------+---------------------+
8 rows in set (0.00 sec)

mysql> DELETE FROM cliente WHERE cliente_id=1;
Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM compras;
+------------+------------+---------------------+
| cliente_id | produto_id | dt				  |
+------------+------------+---------------------+
|		  2 |		  1 | 2009-07-08 16:22:36 |
|		  2 |		  2 | 2009-07-08 16:22:39 |
|		  2 |		  3 | 2009-07-08 16:22:41 |
|		  2 |		  4 | 2009-07-08 16:22:43 |
+------------+------------+---------------------+
4 rows in set (0.00 sec)

Com SET NULL, ao invés de excluir os registros da tabela FILHA, no caso, a tabela compras, ele setaria os valores aonde o cliente_id é igual a 1 como NULL, caso a coluna aceitasse valores NULL que não é este caso.

 

SET DEFAULT, teria quase o mesmo comportamento, somente que setaria o valor DEFAULT da coluna, valor este que fora definido quando da criação da tabela.

 

E o NO ACTION é o valor padrão quando não são fornecidas as propriedades. Ela não deleta um registro na tabela PAI se ele fizer parte da FOREIGN KEY na tabela FILHA.

 

Happy MySQL'ing!! http://forum.imasters.com.br/public/style_emoticons/default/clap.gif

[Henrique Barcelos 290]

Simplesmente esclarecedor...

 

Duvida, cade você hein???? :D

 

Eu tava pra justamente pesquisar um modo de como ao excluir um dado de uma tabela, excluir também os linkados a ele por foreign keys, mas aí fui inventar de usar o DBDesigner Fork e vi essas opões lá e pintou a dúvida... bom que matei 2 coelhos com uma cajadada só :D...

 

Só + uma pergunta: isso só funciona com tabelas InnoDB ou funciona com MYISAM tbm???

 

Valeu aí Wagner...

 

Bota essa na conta B) http://forum.imasters.com.br/public/style_emoticons/default/clap.gif

[Wagner Bianchi 0]

Somente com tabelas controladas pelos Storage Engines InnoDB e Falcon.

MyISAM não tem integridade referencial.

 

Happy MySQL'ing camarada!! http://forum.imasters.com.br/public/style_emoticons/default/clap.gif

[João Batista Neto 448]

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