Restrições FOREIGN KEY

A partir da versão 3.23.43b, o InnoDB disponibiliza restrições de chaves estrangeiras. O InnoDB é o primeiro tipo de tabela da MySQL, que permite definir restrições de chaves estrangeiras para guardar a integridade dos seus dados.

A sintaxe da definição das restriçõess de chaves estrangeiras no InnoDB:

[CONSTRAINT [symbol]] FOREIGN KEY (index_col_name, ...)
 REFERENCES nome_tabela (index_nome_coluna, ...)
 [ON DELETE {CASCADE | SET NULL | NO ACTION
 | RESTRICT}]
 [ON UPDATE {CASCADE | SET NULL | NO ACTION
 | RESTRICT}]

Ambas as tabelas devem ser do tipo InnoDB, na tabela deve existir um índice onde as colunas de chaves estrangeiras listadas como as PRIMEIRAS colunas e na tabela indicada deve haver um índice onde as colunas indicadas são listadas como as PRIMEIRAS colunas e na mesma ordem. O InnoDB não cria índices automaticamente em chaves estrangeiras para chaves referênciadas: você tem que criá-las explicitamente. Os índices são necessários para verificação de chaves estrangeiras para ser rápido e não exigir a varredura da tabela.

Colunas correspondentes nas chaves estrangeiras e a chave referenciada devem ter tipos de dados internos parecidos dentro do InnoDB para que possam ser comparados sem uma conversão de tipo. O tamanho e a sinalização de tipos inteiros devem ser o mesmo. O tamanho do tipos string não precisam ser o mesmo. Se você especificar uma ação SET NULL, esteja certo de que você não declarou as colunas na tabela filha como NOT NULL.

Se o MariaDB retornar o erro de número 1005 de uma instrução CREATE TABLE, e a string de mensagem de erro se referir ao errno 150, então a criação da tabela falhou porque um restrição de chaves estrangeiras não foi formada corretamente. Similarmente, se uma ALTER TABLE falhar e se referir ao errno 150, sgnifica que um definição de chave estrangeira foi formada incorretamente na tabela alterada. A partir da versão 4.0.13, você pode usar SHOW INNODB STATUS para ver uma explicação detalhada do ultimo erro de chave estrangeira do InnoDB no servidor.

A partir de versão 3.23.50, InnoDB não verifica restrições de chaves estrangeiras naqueles valores de chaves estrangeiras ou chaves referênciadas que contenham uma coluna NULL.

Um desvio do padrão SQL: se na tabela pai existirem diversos registros têm o mesmo valor de chave referência, então o InnoDB atua na verificação da chave estrangeira como o outro registro pai como se o mesmo valor de chave não existisse. Por exemplo, se você tiver definido uma restrição de tipo RESTRICT, e existir um registro filho com diversos registros pais, o InnoDB não permite a deleção de qualquer um dos registros pais.

A partir da versão 3.23.50, você também pode associar a cláusula ON DELETE CASCADE ou ON DELETE SET NULL com a restrição de chave estrangeira. Opções correspondentes do ON UPDATE estão disponíveis a partir da versão 4.0.8. Se ON DELETE CASCADE for especificado, e um registro na tabela pai for deletado, então o InnoDB automaticamente também deleta todos aqueles registros na tabela filha cujos valores de chaves estrangeiras são iguais ao valor da chave referênciada no registro pai Se ON DELETE SET NULL for especificado, os registros filhos são automaticamente atualizados e assim as colunas na chave estrangeira são definidas com o valor NULL do SQL.

Um desvio dos padrões SQL: se ON UPDATE CASCADE ou ON UPDATE SET NULL retornam para atualizar a MESMA TABELA que ja tenha sido atualizada durante o processo cascata, ele atua como RESTRICT. Isto é para prevenirloops infinitos resultantes de atualizações em cascata. Um ON DELETE SET NULL auto referêncial, por outro lado, funciona desde a versão 4.0.13. ON DELETE CASCADE auto referêncial já está funcionando.

Um exemplo:

CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB;
CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id),
 FOREIGN KEY (parent_id) REFERENCES parent(id)
 ON DELETE SET NULL
) TYPE=INNODB;

Um exemplo complexo:

CREATE TABLE product (category INT NOT NULL, id INT NOT NULL,
 price DECIMAL,
 PRIMARY KEY(category, id)) TYPE=INNODB;
CREATE TABLE customer (id INT NOT NULL,
 PRIMARY KEY (id)) TYPE=INNODB;
CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT,
 product_category INT NOT NULL,
 product_id INT NOT NULL,
 customer_id INT NOT NULL,
 PRIMARY KEY(no),
 INDEX (product_category, product_id),
 FOREIGN KEY (product_category, product_id)
 REFERENCES product(category, id)
 ON UPDATE CASCADE ON DELETE RESTRICT,
 INDEX (customer_id),
 FOREIGN KEY (customer_id)
 REFERENCES customer(id)) TYPE=INNODB;

A partir da versão 3.23.50 o InnoDB lhe permite adicionar novas restriçoões de chaves estrangeiras a uma tabela.

ALTER TABLE seunomedetabela ADD [CONSTRAINT [symbol]] FOREIGN KEY (...) REFERENCES anothertablename(...)
[on_delete_and_on_update_actions]

Lembre-se de criar os índices necessários primeiro.

A partir da versão 4.0.13, o InnoDB suporta

ALTER TABLE suatabela DROP FOREIGN KEY id_chave_estrangeira_gerada_internamente

Você tem que usar SHOW CREATE TABLE para daterminar as id's de chaves estrangeiras geradas internamente quando você apaga uma chave estrangeira.

Na versão anterior a 3.23.50 do InnoDB, ALTER TABLE ou CREATE INDEX não devem ser usadas em conexões com tabelas que têm restrições de chaves estrangeiras ou que são referênciadas em restrições de chaves estrangeiras: Qualquer ALTER TABLE remove todas as restrições de chaves estrangeiras definidas na tabela. Você não deve utilizar ALTER TABLE para tabela referenciadas também, mas utilizar DROP TABLE e CREATE TABLE para modifcar o esquema. Quando o MariaDB faz um ALTER TABLE ele pode usar internamente RENAME TABLE, e isto irá confundir a restrição de chave estrangeira que se refere a tabela. Uma instrução CREATE INDEX é processada no MariaDB como um ALTER TABLE, e estas restrições também se aplicam a ele.

Ao fazer a verificação de chaves estrangeiras, o InnoDB define o bloqueio a nivel de linhas compartilhadas em registros filhos e pais que ele precisa verificar. O InnoDB verifica a restrição de chaves estrangeiras imediatamente: a verificação não é aplicada no commit da transaçao.

Se você quiser ignorar as restrições de chaves estrangeiras durante, por exemplo um operação LOAD DATA, você pode fazer SET FOREIGN_KEY_CHECKS=0.

O InnoDB lhe permite apagar qualquer tabela mesmo que ela quebre a restrição de chaves estrangeira que referencia a tabela. Ao apagar um tabela restrição que é definida na instrução create também é apagada.

Se você recriar uma tabela que foi apagada, ela deve ter uma definição de acordo com a restrição de chaves estrangeiras que faz referência a ela. Ela deve ter os nomes e tipos de colunas corretor e deve ter os índices na chave referenciada como indicado acima. Se esta condição não for satisfeita, o MariaDB retornará o erro de número 1005 e se refere ao errno 150 na string de mensagem de erro.

A partir da versão 3.23.50 o InnoDB retorna da definição de chave estrangeira de uma tabela quando você chama

SHOW CREATE TABLE seunometabela

Assim o mysqldump também produz as difinições de tabelas corretas no arquivo dump e não se esquece das chaves estrangeiras.

Você também pode listar as restrições de chaves estrangeiras de uma tabela T com

SHOW TABLE STATUS FROM seubancodedados LIKE 'T'

As restrições de chaves estrangeiras são listadas no comentário da tabela impresso na saída.

Retornar