MySQL – Como adicionar chave estrangeira em tabela nova ou existente

TL; DR

Com adicionar chave estrangeira em uma tabela nova:

CREATE TABLE child (ID INT PRIMARY KEY, parent_ID INT, FOREIGN KEY my_fk (parent_id) REFERENCES parent(ID) ON UPDATE NO ACTION ON DELETE NO ACTION);

Com adicionar chave estrangeira em uma tabela existente:

ALTER TABLE child ADD FOREIGN KEY my_fk (parent_id) REFERENCES parent(ID) ON UPDATE NO ACTION ON DELETE NO ACTION;

MySQL tem a habilidade the forçar um registro a existir em uma tabela pai quando você está adicionando / editando ou deletando um registro na tabela filho, isso é chamada de Chave Estrangeira ou Foreign Key.

Como podemos ver acima, você pode tanto criar uma tabela com FK desde o começo ou adicionar ela após a tabela já ter sido criada. Além da sintaxe que refere a coluna na tabela filho e o nome da tabela e coluna na tabela pai que vamos referenciar, é possível controlar o comportamento da tabela pai quando se modificar algum registro que está referenciado na tabela filho. Isso é controlada com os parametros ON UPDATE e ON DELETE. As restrições são as seguintes:

Vamos utilizar a tabela abaixo como exemplo:

mysql> SELECT * FROM parent;
+----+
| ID |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql> SELECT * FROM child;
+----+-----------+
| ID | parent_ID |
+----+-----------+
|  1 |         1 |
+----+-----------+
1 row in set (0.00 sec)

• RESTRICT or NO ACTION – Comportamento padrão caso seja omitido  ON UPDATE or ON DELETE. Isso quer dizer que quando seja feito alguma alteração na tabela pai, e ela tenha um registro na tabela filho apontado para ela, essa operação (UPDATE ou DELETE) vai ser bloqueada:

  mysql> UPDATE parent SET ID = 2 WHERE ID = 1;
  ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_ID`) REFERENCES `parent` (`ID`))
  

  Mesmo não recomendado, em casos extremos, podemos desabilitar esse check que o MySQL faz usando o comando abaixo:

  SET foreign_key_checks=0;

  Tenha em mente que desabilitar o check faz com que não tenha sentido algum ter a chave estrangeira na tabela, considere remover!

• SET DEFAULT – É reconhecido como uma sintaxe válida, mas é interpredado como RESTRICT.
• CASCADE – Qualquer ação na tabela pai, vai ser replicada para os registro referenciados na tabela filho:

  mysql> UPDATE parent SET ID = 2 WHERE ID = 1;
  Query OK, 1 row affected (0.01 sec)
  Rows matched: 1  Changed: 1  Warnings: 0
  
  mysql> SELECT * FROM parent;
  +----+
  | ID |
  +----+
  |  2 |
  +----+
  1 row in set (0.00 sec)
  
  mysql> SELECT * FROM child;
  +----+-----------+
  | ID | parent_ID |
  +----+-----------+
  |  1 |         2 |
  +----+-----------+
  1 row in set (0.00 sec)

• SET NULL – Qualquer alteração na tabela pai, vai resetar a coluna na tabela filho para NULL (tenha certeza que a coluna não esteja definada como NOT NULL):

  mysql> UPDATE parent SET ID = 2 WHERE ID = 1;
  Query OK, 1 row affected (0.01 sec)
  Rows matched: 1  Changed: 1  Warnings: 0
  
  mysql> SELECT * FROM parent;
  +----+
  | ID |
  +----+
  |  2 |
  +----+
  1 row in set (0.01 sec)
  
  mysql> SELECT * FROM child;
  +----+-----------+
  | ID | parent_ID |
  +----+-----------+
  |  1 |      NULL |
  +----+-----------+
  1 row in set (0.00 sec)