MySQL não pode adicionar restrição de chave estrangeira

Então, estou tentando adicionar restrições de chave estrangeira ao meu database como um requisito do projeto e funcionou pela primeira vez ou duas em tabelas diferentes, mas tenho duas tabelas nas quais recebo um erro ao tentar adicionar as restrições de chave estrangeira. A mensagem de erro que recebo é:

ERROR 1215 (HY000): Não é possível adicionar restrição de chave estrangeira

Este é o SQL que estou usando para criar as tabelas, as duas tabelas ofensivas são Patient e Appointment .

 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=1; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES'; CREATE SCHEMA IF NOT EXISTS `doctorsoffice` DEFAULT CHARACTER SET utf8 ; USE `doctorsoffice` ; -- ----------------------------------------------------- -- Table `doctorsoffice`.`doctor` -- ----------------------------------------------------- DROP TABLE IF EXISTS `doctorsoffice`.`doctor` ; CREATE TABLE IF NOT EXISTS `doctorsoffice`.`doctor` ( `DoctorID` INT(11) NOT NULL AUTO_INCREMENT , `FName` VARCHAR(20) NULL DEFAULT NULL , `LName` VARCHAR(20) NULL DEFAULT NULL , `Gender` VARCHAR(1) NULL DEFAULT NULL , `Specialty` VARCHAR(40) NOT NULL DEFAULT 'General Practitioner' , UNIQUE INDEX `DoctorID` (`DoctorID` ASC) , PRIMARY KEY (`DoctorID`) ) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; -- ----------------------------------------------------- -- Table `doctorsoffice`.`medicalhistory` -- ----------------------------------------------------- DROP TABLE IF EXISTS `doctorsoffice`.`medicalhistory` ; CREATE TABLE IF NOT EXISTS `doctorsoffice`.`medicalhistory` ( `MedicalHistoryID` INT(11) NOT NULL AUTO_INCREMENT , `Allergies` TEXT NULL DEFAULT NULL , `Medications` TEXT NULL DEFAULT NULL , `ExistingConditions` TEXT NULL DEFAULT NULL , `Misc` TEXT NULL DEFAULT NULL , UNIQUE INDEX `MedicalHistoryID` (`MedicalHistoryID` ASC) , PRIMARY KEY (`MedicalHistoryID`) ) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; -- ----------------------------------------------------- -- Table `doctorsoffice`.`Patient` -- ----------------------------------------------------- DROP TABLE IF EXISTS `doctorsoffice`.`Patient` ; CREATE TABLE IF NOT EXISTS `doctorsoffice`.`Patient` ( `PatientID` INT unsigned NOT NULL AUTO_INCREMENT , `FName` VARCHAR(30) NULL , `LName` VARCHAR(45) NULL , `Gender` CHAR NULL , `DOB` DATE NULL , `SSN` DOUBLE NULL , `MedicalHistory` smallint(5) unsigned NOT NULL, `PrimaryPhysician` smallint(5) unsigned NOT NULL, PRIMARY KEY (`PatientID`) , UNIQUE INDEX `PatientID_UNIQUE` (`PatientID` ASC) , CONSTRAINT `FK_MedicalHistory` FOREIGN KEY (`MEdicalHistory` ) REFERENCES `doctorsoffice`.`medicalhistory` (`MedicalHistoryID` ) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `FK_PrimaryPhysician` FOREIGN KEY (`PrimaryPhysician` ) REFERENCES `doctorsoffice`.`doctor` (`DoctorID` ) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `doctorsoffice`.`Appointment` -- ----------------------------------------------------- DROP TABLE IF EXISTS `doctorsoffice`.`Appointment` ; CREATE TABLE IF NOT EXISTS `doctorsoffice`.`Appointment` ( `AppointmentID` smallint(5) unsigned NOT NULL AUTO_INCREMENT , `Date` DATE NULL , `Time` TIME NULL , `Patient` smallint(5) unsigned NOT NULL, `Doctor` smallint(5) unsigned NOT NULL, PRIMARY KEY (`AppointmentID`) , UNIQUE INDEX `AppointmentID_UNIQUE` (`AppointmentID` ASC) , CONSTRAINT `FK_Patient` FOREIGN KEY (`Patient` ) REFERENCES `doctorsoffice`.`Patient` (`PatientID` ) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `FK_Doctor` FOREIGN KEY (`Doctor` ) REFERENCES `doctorsoffice`.`doctor` (`DoctorID` ) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `doctorsoffice`.`InsuranceCompany` -- ----------------------------------------------------- DROP TABLE IF EXISTS `doctorsoffice`.`InsuranceCompany` ; CREATE TABLE IF NOT EXISTS `doctorsoffice`.`InsuranceCompany` ( `InsuranceID` smallint(5) NOT NULL AUTO_INCREMENT , `Name` VARCHAR(50) NULL , `Phone` DOUBLE NULL , PRIMARY KEY (`InsuranceID`) , UNIQUE INDEX `InsuranceID_UNIQUE` (`InsuranceID` ASC) ) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `doctorsoffice`.`PatientInsurance` -- ----------------------------------------------------- DROP TABLE IF EXISTS `doctorsoffice`.`PatientInsurance` ; CREATE TABLE IF NOT EXISTS `doctorsoffice`.`PatientInsurance` ( `PolicyHolder` smallint(5) NOT NULL , `InsuranceCompany` smallint(5) NOT NULL , `CoPay` INT NOT NULL DEFAULT 5 , `PolicyNumber` smallint(5) NOT NULL AUTO_INCREMENT , PRIMARY KEY (`PolicyNumber`) , UNIQUE INDEX `PolicyNumber_UNIQUE` (`PolicyNumber` ASC) , CONSTRAINT `FK_PolicyHolder` FOREIGN KEY (`PolicyHolder` ) REFERENCES `doctorsoffice`.`Patient` (`PatientID` ) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `FK_InsuranceCompany` FOREIGN KEY (`InsuranceCompany` ) REFERENCES `doctorsoffice`.`InsuranceCompany` (`InsuranceID` ) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE = InnoDB; USE `doctorsoffice` ; SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; 

Para encontrar o erro específico, execute isto:

 SHOW ENGINE INNODB STATUS; 

E veja a seção mais LATEST FOREIGN KEY ERROR .

O tipo de dados para a coluna filho deve corresponder exatamente à coluna pai. Por exemplo, como o medicalhistory.MedicalHistoryID é um INT , Patient.MedicalHistory também precisa ser um INT , não um SMALLINT .

Além disso, você deve executar o set foreign_key_checks=0 consultas set foreign_key_checks=0 antes de executar o DDL para poder criar as tabelas em uma ordem arbitrária em vez de precisar criar todas as tabelas pai antes das tabelas set foreign_key_checks=0 relevantes.

Eu defini um campo como “Não assinado” e outro não. Depois de definir ambas as colunas para Unsigned, funcionou.

  • O mecanismo deve ser o mesmo, por exemplo, InnoDB
  • O tipo de dados deve ser o mesmo e com o mesmo tamanho. por exemplo VARCHAR (20)
  • O charset das Colunas de Agrupamento deve ser o mesmo. por exemplo, utf8
    Watchout: Mesmo que suas tabelas tenham o mesmo Collation, as colunas ainda poderão ter uma diferente.
  • Exclusivo – Chave estrangeira deve se referir ao campo que é exclusivo (geralmente chave primária) na tabela de referência.

Tente usar o mesmo tipo de suas chaves primárias – int (11) – nas foreign keys – smallint (5) – também.

Espero que ajude!

Confirme se a codificação e o agrupamento de caracteres das duas tabelas é o mesmo.

No meu caso, uma das tabelas estava usando o utf8 e a outra usando o latin1 .

Eu tive outro caso em que a codificação era a mesma, mas o agrupamento era diferente. Um utf8_general_ci o outro utf8_unicode_ci

Você pode executar este comando para definir a codificação e o agrupamento para uma tabela.

 ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci; 

Espero que isso ajude alguém.

Para definir uma FOREIGN KEY na Tabela B, você deve definir uma KEY na tabela A.

Na tabela A: id ÍNDICE ( id )

E então na tabela B,

 CONSTRAINT `FK_id` FOREIGN KEY (`id`) REFERENCES `table-A` (`id`) 

Por favor, verifique se ambas as tabelas estão no formato InnoDB. Mesmo se um estiver no formato MyISAM, a restrição de chave estrangeira não funcionará.

Além disso, outra coisa é que ambos os campos devem ser do mesmo tipo. Se um é INT, então o outro também deve ser INT. Se um é VARCHAR, o outro também deve ser VARCHAR, etc.

Verifique as seguintes regras:

  • Primeiro, verifica se os nomes são indicados para nomes de tabelas

  • Segundo tipo de dado direito dar a chave estrangeira?

Eu tive o mesmo problema e a solução foi muito simples. Solução: as foreign keys declaradas na tabela não devem ser definidas como nulas.

reference: Se você especificar uma ação SET NULL, certifique-se de não ter declarado as colunas na tabela filha como NOT NULL. ( ref )

Enfrentei o problema e consegui resolvê-lo, certificando-me de que os tipos de dados correspondiam exatamente.

Eu estava usando o SequelPro para adicionar a restrição e estava tornando a chave primária como não assinada por padrão.

Eu tive um erro semelhante ao criar uma chave estrangeira em uma tabela Muitos para Muitos, em que a chave primária consistia em duas foreign keys e outra coluna normal. Corrigi o problema corrigindo o nome da tabela referenciada, ou seja, empresa, conforme mostrado no código corrigido abaixo:

 create table company_life_cycle__history -- (MM) ( company_life_cycle_id tinyint unsigned not null, Foreign Key (company_life_cycle_id) references company_life_cycle(id) ON DELETE CASCADE ON UPDATE CASCADE, company_id MEDIUMINT unsigned not null, Foreign Key (company_id) references company(id) ON DELETE CASCADE ON UPDATE CASCADE, activity_on date NOT NULL, PRIMARY KEY pk_company_life_cycle_history (company_life_cycle_id, company_id,activity_on), created_on datetime DEFAULT NULL, updated_on datetime DEFAULT NULL, created_by varchar(50) DEFAULT NULL, updated_by varchar(50) DEFAULT NULL ); 

Eu tive erro semelhante com duas foreign keys para tabelas diferentes, mas com os mesmos nomes de chaves! Eu renomei chaves e o erro desapareceu)

Tive um erro semelhante, mas no meu caso eu estava ausente para declarar o pk como auto_increment.

Apenas no caso de ser útil para qualquer um

Eu tenho o mesmo erro. A causa no meu caso foi:

  1. Eu criei um backup de um database via phpmyadmin copiando todo o database.
  2. Eu criei um novo database com o mesmo nome que o database antigo tinha selecionado.
  3. Eu comecei um script SQL para criar tabelas e dados atualizados.
  4. Eu recebi o erro. Além disso, quando desativei foreign_key_checks. Embora o database estivesse completamente vazio.

A causa foi: Desde que eu usei o phpmyadmin para criar algumas foreign keys no database renomeado – as foreign keys foram criadas com um prefixo de nome de database, mas o prefixo do nome do database não foi atualizado. Portanto, ainda havia referências no backup-db apontando para o db recém-criado.

Minha solução é talvez um pouco embaraçosa e conta a história de por que você deve, às vezes, olhar para o que tem à sua frente, em vez desses posts 🙂

Eu tinha corrido um engenheiro avançado antes, o que falhou, então isso significava que meu database já tinha algumas tabelas, então eu estava sentado tentando corrigir falhas de chave estrangeiras tentando ter certeza de que tudo estava perfeito, mas ele se deparou com o tabelas criadas anteriormente, por isso não prevaleceu.

Verifique a assinatura em ambas as colunas da sua tabela. Se a coluna da tabela de referência for ASSINADA, a coluna da tabela referenciada também deverá ser ASSINADA.

Uma causa adicional desse erro é quando suas tabelas ou colunas contêm palavras – chave reservadas:

Às vezes, alguém esquece isso.

Eu tive esse mesmo problema, em seguida, eu corrigi o nome do mecanismo como Innodb em tabelas pai e filho e corrigido o nome do campo de referência FOREIGN KEY ( c_id ) REFERENCES x9o_parent_table ( c_id )
então funciona bem e as tabelas estão instaladas corretamente. Isso será usado integralmente para alguém.