Tag Archive | MySql

Erros na criação de Foreign Key’s no MySql

MySqlOlha eu aqui de novo…. continuando o meu projeto de remodelagem do banco de dados do sistema que irei desenvolver daqui pra frente me deparei com vários erros ao tentar criar minhas foreign keys… então vou listá-los aqui para explicar tudo que descobri e quem sabe ajudar você colega desenvolvedor que está batendo cabeça num erro não muito claro que o MySql te oferece… tem horas que chega a cansar a beleza…

Erro 1:


alter table [tabela1] add constraint fk_tabela1_x_tabela2 foreign key (idTabela1) references tabela2 (idTabela2) on delete restrict on update cascade;
[Err] 1005 - Can't create table 'basedados.#sql-11e0_4f' (errno: 150)

Solução 1:

ERRNO 150… o que é!?

No meu caso o problema foi o seguinte… não sei porque cargas d’água que em algumas tabelas os campos que seriam a minha foreign key estava com o UNSIGNED ligado. E o campo referenciado (primary key da outra tabela) logicamente não era UNSIGNED.

Para quem não sabe, o UNSIGNED define que um campo inteiro ou numérico não pode receber valores negativos (somente valores sem sinal).

Ou seja, ao criar integridade referencial, os campos envolvidos devem ser UNSIGNED ou SIGNED. Se forem divergentes o MySql retornará o ERRNO 150.

Para acertar, eu tive que fazer um modify na coluna que seria minha FK em cada tabela para retirar o UNSIGNED:

#se a definição do seu campo é algo mais ou menos assim
campo1 int(6) UNSIGNED NOT NULL AUTO_INCREMENT;

#repita-o somente tirando o UNSIGNED
alter table tabela1 modify campo1 int(6) NOT NULL AUTO_INCREMENT;

Erro 2:


[SQL] alter table [tabela1] add constraint fk_tabela1_x_tabela2 foreign key (idTabela1) references tabela2 (idTabela2) on delete restrict on update cascade;
[Err] 1452 - Cannot add or update a child row: a foreign key constraint fails (`basedados`.`#sql-11e0_4f`, CONSTRAINT `fk_tabela1_x_tabela2` FOREIGN KEY (`idTabela1`) REFERENCES `tabela2` (`idTabela2`) ON UPDATE CASCADE)

Solução 2:

Que que ocorre nesse ERR 1452?!?!?!

Esse erro ocorre não só na criação da foreign key, mas pode acontecer também quando a foreign key já existir na tabela e você estiver atualizando ou inserindo dados nesta tabela. No caso, esse erro está indicando que constraint foreign key falhou…. Mas como falhou!?!?

Muito simples… a informação do campo da foreign key não é encontrada na tabela2 (a tabela que possui a primary key referenciada). No meu caso, como estou criando as relações em uma base que já existe e já possui informações cadastradas, significa dizer que na tabela1 que eu estou tentando criar a foreign key eu tenho uma ou mais linhas que estão referenciando uma chave primária que não existe na minha ‘tabela2’ (a tabela referenciada).

Se a foreign key já existir e você receber esse erro, significará que você está editando ou inserindo um registro e passando um valor para o campo da foreign key que não possui um registro na tabela2 referenciada (tabela da primary key).

Na minha situação, como minhas tabelas já possuem registros e eu não posso simplesmente limpar a tabela toda, tive que fazer sql’s que deletassem os registros que estão referenciando informações inválidas. Ficou assim mais ou menos:


delete a from tabela1 a left join tabela2 b on b.idTabela2 = a.idTabela2 where b.idTabela2 is null;

Ou seja, faço um select com left join nas minhas duas tabelas e deleto as linhas que vierem com as informações da tabela2 nulas.

Erro 3:

[SQL]
alter table orcamento add constraint fk_orcamento_x_seguradora foreign key (codSeguradora) references seguradora (codSeguradora) on delete restrict on update cascade;
[Err] 1005 - Can't create table 'tiseg.#sql-11e0_4f' (errno: 121)

Solução 3:

E o ERRNO 121??

Mais simples ainda… a sua foreign key já existe!!! Pule para seu próximo comando…. =)

Anúncios

Foreign Key’s no MySql

Hoje passei por uma situação interessante. Estou fazendo a remodelagem de um banco de dados MySql de um sistema que estou trabalhando atualmente. Como o banco é um pouco grande (cerca de 100 tabelas) e está muito mal organizado e sem referenciação nenhuma, resolvi começar pela organização das foreign keys para melhor visualizar o meu modelo relacional.

Porém caí na situação em que eu rodava um comando de criação de foreign key e ela simplesmente não aparecia na minha tabela referente do bd. Pesquiso dali, pesquiso daqui, descobri uma coisa muito interessante:

O ‘problema’ estava acontecendo por causa dos tipos das minhas tabelas…. Isso mesmo, as tabelas do MySql podem ser de vários tipos. Aqui no meu MySql 5.0.51a tenho os seguintes tipos: Memory, MyISAM, Blackhole, MRG_MYISAM, CSV, ARCHIVE e InnoDB.

Por padrão, tabelas criadas no MySql e sem definição do tipo, são criadas sob o tipo MyISAM. Este tipo de tabela não permite a utilização de transações e nem de foreign keys.

Este era o meu problema, as minhas tabelas eram do tipo MyISAM. Então eu tive que convertê-las para o tipo INNODB que me permite gerar foreign keys. Para alterar fiz o seguinte sql:

alter table [tabela] type = INNODB;

O tipo InnoDB prove ao MySql um mecanismo de armazenamento seguro com transações com commit, rollback e recuperação em caso de falhas. O bloqueio é realizado a nível de registro (row locking) ao contrário do MyISAM que faz o bloqueio por tabela (table locking). Isto faz aumentar a performance e concorrência de multi usuários [ele foi desenvolvido para obter o máximo de performance ao processar grande volume de dados].

O InnoDB armazena as tabelas e índices em um espaço de tabela, o qual pode consistir de vários arquivos, diferentemente do MyISAM onde cada tabela é armazenada em um arquivo separado.

Além disso tudo, ainda é o primeiro tipo que suporta restrições Foreign Key…. \o/

%d blogueiros gostam disto: