MySQL Criando tabelas com Chaves Estrangeiras dando errno: 150

Eu estou tentando criar uma tabela no MySQL com duas foreign keys, que referenciam as chaves primárias em 2 outras tabelas, mas estou recebendo um erro de errno: 150 e ele não irá criar a tabela.

Aqui está o SQL para todas as 3 tabelas:

CREATE TABLE role_groups ( `role_group_id` int(11) NOT NULL `AUTO_INCREMENT`, `name` varchar(20), `description` varchar(200), PRIMARY KEY (`role_group_id`) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS `roles` ( `role_id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50), `description` varchar(200), PRIMARY KEY (`role_id`) ) ENGINE=InnoDB; create table role_map ( `role_map_id` int not null `auto_increment`, `role_id` int not null, `role_group_id` int not null, primary key(`role_map_id`), foreign key(`role_id`) references roles(`role_id`), foreign key(`role_group_id`) references role_groups(`role_group_id`) ) engine=InnoDB; 

Qualquer ajuda seria muito apreciada.

    Eu tive o mesmo problema com ALTER TABLE ADD FOREIGN KEY .

    Depois de uma hora, descobri que essas condições devem ser satisfeitas para não receber o erro 150:

    1. A tabela pai deve existir antes de você definir uma chave estrangeira para referenciá-la. Você deve definir as tabelas na ordem correta: Tabela pai primeiro e depois a tabela Filho. Se ambas as tabelas fizerem referência uma à outra, você deverá criar uma tabela sem restrições FK, criar a segunda tabela e adicionar a restrição FK à primeira tabela com ALTER TABLE .

    2. As duas tabelas devem suportar restrições de chave estrangeira, ou seja, ENGINE=InnoDB . Outros mecanismos de armazenamento silenciosamente ignoram as definições de chave estrangeira, para que não retornem nenhum erro ou aviso, mas a restrição FK não é salva.

    3. As colunas referenciadas na tabela pai devem ser as colunas mais à esquerda de uma chave. Melhor se a chave no pai for PRIMARY KEY ou UNIQUE KEY .

    4. A definição FK deve referenciar as colunas PK na mesma ordem que a definição PK. Por exemplo, se o FK REFERENCES Parent(a,b,c) então o PK do Pai não deve ser definido nas colunas na ordem (a,c,b) .

    5. As colunas PK na tabela pai devem ter o mesmo tipo de dados que as colunas FK na tabela filho. Por exemplo, se uma coluna PK na tabela Parent for UNSIGNED , certifique-se de definir UNSIGNED para a coluna correspondente no campo Child table.

      Exceção: o comprimento das seqüências de caracteres pode ser diferente. Por exemplo, VARCHAR(10) pode referenciar VARCHAR(20) ou vice-versa.

    6. Qualquer (s) coluna (s) FK do tipo string deve ter o mesmo conjunto de caracteres e agrupamento que a (s) coluna (s) PK correspondente (s).

    7. Se já houver dados na tabela Child, todos os valores nas colunas FK deverão corresponder a um valor na (s) coluna (s) PK da tabela pai. Verifique isso com uma consulta como:

       SELECT COUNT(*) FROM Child LEFT OUTER JOIN Parent ON Child.FK = Parent.PK WHERE Parent.PK IS NULL; 

      Isso deve retornar zero (0) valores não correspondentes. Obviamente, esta consulta é um exemplo genérico; você deve replace seus nomes de tabelas e nomes de coluna.

    8. Nem a tabela pai nem a tabela filho podem ser uma tabela TEMPORARY .

    9. Nem a tabela pai nem a tabela filho podem ser uma tabela PARTITIONED .

    10. Se você declarar um FK com a opção ON DELETE SET NULL , as colunas FK deverão ser anuláveis.

    11. Se você declarar um nome de restrição para uma chave estrangeira, o nome da restrição deverá ser exclusivo em todo o esquema, não apenas na tabela na qual a restrição está definida. Duas tabelas podem não ter sua própria restrição com o mesmo nome.

    Espero que isto ajude.

    A mensagem “errno 150” genérica do MySQL “ significa que uma restrição de chave estrangeira não foi formada corretamente ”. Como você provavelmente já sabe se está lendo esta página, a mensagem de erro genérica “errno: 150” é realmente inútil. Contudo:

    Você pode obter a mensagem de erro real executando SHOW ENGINE INNODB STATUS; e, em seguida, procurando o mais LATEST FOREIGN KEY ERROR na saída.

    Por exemplo, esta tentativa de criar uma restrição de chave estrangeira:

     CREATE TABLE t1 (id INTEGER); CREATE TABLE t2 (t1_id INTEGER, CONSTRAINT FOREIGN KEY (t1_id) REFERENCES t1 (id)); 

    falha com o erro Can't create table 'test.t2' (errno: 150) . Isso não diz nada a ninguém além do que é um problema de chave estrangeira. Mas corra SHOW ENGINE INNODB STATUS; e dirá:

     ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 130811 23:36:38 Error in foreign key constraint of table test/t2: FOREIGN KEY (t1_id) REFERENCES t1 (id)): Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint. 

    Diz que o problema é que não é possível encontrar um índice. SHOW INDEX FROM t1 mostra que não existem índices para a tabela t1 . Corrija isso, digamos, definindo uma chave primária em t1 e a restrição de chave estrangeira será criada com sucesso.

    Certifique-se de que as propriedades dos dois campos que você está tentando vincular a uma restrição sejam exatamente as mesmas.

    Freqüentemente, a propriedade “não assinada” em uma coluna de ID o surpreende.

     ALTER TABLE `dbname`.`tablename` CHANGE `fieldname` `fieldname` int(10) UNSIGNED NULL; 

    Qual é o estado atual do seu database quando você executa este script? Está completamente vazio? Seu SQL roda muito bem ao criar um database a partir do zero, mas o erro 150 geralmente tem a ver com descartar e recriar tabelas que fazem parte de uma chave estrangeira. Tenho a sensação de que você não está trabalhando com um database 100% novo e novo.

    Se você está errando quando “fonte” -ing seu arquivo SQL, você deve ser capaz de executar o comando “SHOW ENGINE INNODB STATUS” do prompt do MySQL imediatamente após o comando “source” para ver informações de erro mais detalhadas.

    Você pode querer verificar a input manual também:

    Se você recriar uma tabela que foi descartada, ela deverá ter uma definição que esteja em conformidade com as restrições de chave estrangeira que fazem referência a ela. Ele deve ter os nomes e os tipos de coluna corretos e deve ter índices nas chaves referenciadas, conforme indicado anteriormente. Se estes não estiverem satisfeitos, o MySQL retorna o número de erro 1005 e se refere ao erro 150 na mensagem de erro. Se o MySQL reportar um número de erro 1005 a partir de uma instrução CREATE TABLE e a mensagem de erro se referir ao erro 150, a criação da tabela falhou porque uma restrição de chave estrangeira não foi formada corretamente.

    – Manual de referência do MySQL 5.1 .

    Para as pessoas que estão visualizando esse segmento com o mesmo problema:

    Existem muitas razões para se obter erros como este. Para uma lista bastante completa de causas e soluções de erros de chave estrangeira no MySQL (incluindo aquelas discutidas aqui), confira este link:

    Erros de Chave Estrangeira do MySQL e Errno 150

    Para outros que encontrarem essa input do SO pelo Google: Certifique-se de que você não está tentando fazer uma ação SET NULL em uma coluna estrangeira (a ser) definida como “NOT NULL”. Isso causou grande frustração até que me lembrei de fazer um CHECK-INNODB STATUS.

    Definitivamente não é o caso, mas achei esse erro bastante comum e não óbvio. O alvo de uma FOREIGN KEY pode não ser PRIMARY KEY . A resposta que se torna útil para mim é:

    Uma CHAVE ESTRANGEIRA deve sempre ser apontada para um campo true PRIMARY KEY da outra tabela.

     CREATE TABLE users( id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(40)); CREATE TABLE userroles( id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, FOREIGN KEY(user_id) REFERENCES users(id)); 

    Como apontado por @andrewdotn, a melhor maneira é ver o erro detalhado ( SHOW ENGINE INNODB STATUS; ) em vez de apenas um código de erro.

    Uma das razões pode ser que um índice já exista com o mesmo nome, pode estar em outra tabela. Como prática, recomendo prefixar o nome da tabela antes do nome do índice para evitar tais colisões. por exemplo, em vez de idx_userId use idx_userActionMapping_userId .

    Dica útil, use SHOW WARNINGS; depois de tentar sua consulta CREATE e você receberá o erro, bem como o aviso mais detalhado:

      ---------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------------------------------------- -------------------------------------------------------------------------------------------- ---------------+ | Warning | 150 | Create table 'fakeDatabase/exampleTable' with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns. | | Error | 1005 | Can't create table 'exampleTable' (errno:150) | +---------+------+-------------------------------------------------------------------------- -------------------------------------------------------------------------------------------- ---------------+ 

    Então, neste caso, hora de recriar minha tabela!

    Por favor, certifique-se primeiro de que

    1. você está usando tabelas InnoDB.
    2. O campo FOREIGN KEY tem o mesmo tipo e comprimento (!) que o campo de origem.

    Eu tive o mesmo problema e consertei. Eu tinha INT não assinado para um campo e apenas inteiro para outro campo.

    Isso geralmente está acontecendo quando você tenta o arquivo de origem no database existente. Solte todas as tabelas primeiro (ou o próprio database). E, em seguida, arquivo de origem com SET foreign_key_checks = 0; no começo e SET foreign_key_checks = 1; no fim.

    Eu encontrei outro motivo que isso falha … nomes de tabela com distinção entre maiúsculas e minúsculas.

    Para esta definição de tabela

     CREATE TABLE user ( userId int PRIMARY KEY AUTO_INCREMENT, username varchar(30) NOT NULL ) ENGINE=InnoDB; 

    Esta definição de tabela funciona

     CREATE TABLE product ( id int PRIMARY KEY AUTO_INCREMENT, userId int, FOREIGN KEY fkProductUser1(userId) REFERENCES **u**ser(userId) ) ENGINE=InnoDB; 

    Considerando que este falha

     CREATE TABLE product ( id int PRIMARY KEY AUTO_INCREMENT, userId int, FOREIGN KEY fkProductUser1(userId) REFERENCES User(userId) ) ENGINE=InnoDB; 

    O fato de que funcionou no Windows e falhou no Unix me levou algumas horas para descobrir. Espero que ajude alguém.

    MySQL Workbench 6.3 para Mac OS.

    Problema: errno 150 na tabela X ao tentar fazer Engenharia Direta em um diagrama DB, 20 de 21 foram bem-sucedidos, 1 falhou. Se os FKs na tabela X forem excluídos, o erro será movido para uma tabela diferente que não estava falhando antes.

    Mudou todos os mecanismos de tabelas para myISAM e funcionou muito bem.

    insira a descrição da imagem aqui

    Também vale a pena verificar se você não está operando acidentalmente no database errado. Este erro ocorrerá se a tabela externa não existir. Por que o MySQL tem que ser tão enigmático?

    Certifique-se de que as foreign keys não estejam listadas como exclusivas no pai. Eu tive esse mesmo problema e resolvi-o demarcando-o como não único.

    No meu caso, foi devido ao fato de que o campo que era um campo de chave estrangeira tinha um nome muito longo, ie. foreign key (some_other_table_with_long_name_id) . Tente mais baixo. Mensagem de erro é um pouco enganadora nesse caso.

    Além disso, como @Jon mencionou anteriormente – as definições de campo devem ser as mesmas (atente para o subtipo unsigned ).

    Quando a restrição de chave forign é baseada no tipo varchar , então, além da lista fornecida por marv-el a coluna de destino deve ter uma restrição exclusiva.

    (Notas laterais grandes demais para um comentário)

    Não há necessidade de um ID AUTO_INCREMENT em uma tabela de mapeamento; livre-se disso.

    Mude a PRIMARY KEY para (role_id, role_group_id) (em qualquer ordem). Isso fará com que os accesss sejam mais rápidos.

    Como você provavelmente deseja mapear ambas as direções, adicione também um INDEX com essas duas colunas na ordem oposta. (Não há necessidade de torná-lo UNIQUE .)

    Mais dicas: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta

    Eu encontrei o mesmo problema, mas eu verifiquei que não tinha a tabela pai. Por isso, apenas edito a migration dos pais em frente à migration infantil. Apenas faça isso.