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)