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)

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *