tipos - MySQL no puede agregar restricción de clave externa

no se puede agregar o actualizar una fila secundaria falla una restricción de clave externa

Question

Así que estoy tratando de agregar restricciones de clave foránea a mi base de datos como un requisito del proyecto y funcionó la primera o la segunda vez en diferentes tablas, pero tengo dos tablas en las que aparece un error al intentar agregar restricciones de clave foránea. El mensaje de error que recibo es:

ERROR 1215 (HY000): No se puede agregar una restricción de clave externa

Este es el SQL que estoy usando para crear las tablas, las dos tablas ofensivas son Patient y 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;

Answer 1

NOTA: Las siguientes tablas se tomaron de algún sitio cuando estaba realizando I + D en la base de datos. Así que la convención de nomenclatura no es adecuada.

Para mí, el problema era que mi tabla principal tenía un conjunto de caracteres diferente al de la que estaba creando.

Tabla de Padres (PRODUCTOS)

products | CREATE TABLE `products` (
  `productCode` varchar(15) NOT NULL,
  `productName` varchar(70) NOT NULL,
  `productLine` varchar(50) NOT NULL,
  `productScale` varchar(10) NOT NULL,
  `productVendor` varchar(50) NOT NULL,
  `productDescription` text NOT NULL,
  `quantityInStock` smallint(6) NOT NULL,
  `buyPrice` decimal(10,2) NOT NULL,
  `msrp` decimal(10,2) NOT NULL,
  PRIMARY KEY (`productCode`),
  KEY `productLine` (`productLine`),
  CONSTRAINT `products_ibfk_1` FOREIGN KEY (`productLine`) REFERENCES `productlines` (`productLine`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Tabla infantil que tuvo un problema (PRICE_LOGS)

price_logs | CREATE TABLE `price_logs` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `productCode` varchar(15) DEFAULT NULL,
  `old_price` decimal(20,2) NOT NULL,
  `new_price` decimal(20,2) NOT NULL,
  `added_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `productCode` (`productCode`),
  CONSTRAINT `price_logs_ibfk_1` FOREIGN KEY (`productCode`) REFERENCES `products` (`productCode`) ON DELETE CASCADE ON UPDATE CASCADE
);

MODIFICADO A

price_logs | CREATE TABLE `price_logs` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `productCode` varchar(15) DEFAULT NULL,
  `old_price` decimal(20,2) NOT NULL,
  `new_price` decimal(20,2) NOT NULL,
  `added_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `productCode` (`productCode`),
  CONSTRAINT `price_logs_ibfk_1` FOREIGN KEY (`productCode`) REFERENCES `products` (`productCode`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 

Answer 2

¡Tuve un error similar con dos claves externas para diferentes tablas pero con los mismos nombres de clave! He cambiado el nombre de las claves y el error desapareció.

Answer 3

Asegúrese de que ambas tablas estén en formato InnoDB. Incluso si uno está en formato MyISAM, entonces, la restricción de clave externa no funcionará.

Además, otra cosa es que, ambos campos deben ser del mismo tipo. Si uno es INT, entonces el otro también debería ser INT. Si uno es VARCHAR, el otro también debería ser VARCHAR, etc.

Answer 4

Compruebe la firma en ambas columnas de la tabla. Si la columna de la tabla de referencia está FIRMADA, la columna de la tabla de referencia también debe estar FIRMADA.

Answer 5

Compruebe las siguientes reglas:

• Primero verifica si los nombres son correctos para los nombres de tablas

• ¿Segundo tipo de datos correcto dado a la clave externa?

Answer 6

Confirme que la codificación de caracteres y la intercalación de las dos tablas es la misma.

En mi caso, una de las tablas usaba utf8 y la otra usaba latin1 .

Tuve otro caso en el que la codificación era la misma pero la clasificación era diferente. Uno utf8_general_ci el otro utf8_unicode_ci

Puede ejecutar este comando para establecer la codificación y la intercalación de una tabla.

ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Espero que esto ayude a alguien.

Answer 7

En mi caso, hubo un error de sintaxis que no fue notificado explícitamente por la consola MySQL al ejecutar la consulta. Sin embargo, la sección LATEST FOREIGN KEY ERROR del comando SHOW ENGINE INNODB STATUS informó,

  Syntax error close to:

  REFERENCES`role`(`id`) ON DELETE CASCADE) ENGINE = InnoDB DEFAULT CHARSET = utf8

Tuve que dejar un espacio en blanco entre las REFERENCES y el role para que funcione.

Answer 8

Enfrenté el problema y pude resolverlo asegurándome de que los tipos de datos coincidieran exactamente.

Estaba usando SequelPro para agregar la restricción y estaba configurando la clave principal como no firmada por defecto.

Answer 9

Había establecido un campo como "Sin firmar" y otro no. Una vez que puse ambas columnas en Sin firmar funcionó.

Answer 10

Intente usar el mismo tipo de claves primarias - int (11) - en las claves externas - smallint (5) - también.

¡Espero eso ayude!

Answer 11

Mi solución es quizás un poco vergonzosa y cuenta la razón por la que a veces deberías mirar lo que tienes frente a ti en lugar de estas publicaciones :)

Había dirigido un ingeniero de avanzada antes, lo cual falló, lo que significaba que mi base de datos ya tenía algunas tablas, luego estaba sentado tratando de corregir fallas en las contraseñas de claves externas para asegurarme de que todo fuera perfecto, pero se topó con el Tablas creadas previamente, por lo que no prevaleció.

Answer 12

Para encontrar el error específico ejecute esto:

SHOW ENGINE INNODB STATUS;

Y busque en la sección LATEST FOREIGN KEY ERROR .

El tipo de datos para la columna secundaria debe coincidir exactamente con la columna principal. Por ejemplo, dado que medicalhistory.MedicalHistoryID es una INT , Patient.MedicalHistory también debe ser una INT , no SMALLINT .

Además, debe ejecutar el set foreign_key_checks=0 consultas set foreign_key_checks=0 antes de ejecutar el DDL para que pueda crear las tablas en un orden arbitrario en lugar de tener que crear todas las tablas primarias antes que las tablas secundarias relevantes.

Answer 13

Para establecer una CLAVE EXTRANJERA en la Tabla B, debe establecer una CLAVE en la tabla A.

En la tabla A: id INDEX ( id )

Y luego en la tabla B,

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

Answer 14

Tengo el mismo error. La causa en mi caso fue:

1. Creé una copia de seguridad de una base de datos a través de phpmyadmin copiando toda la base de datos.
2. Creé una nueva base de datos con el mismo nombre que la antigua db y la había seleccionado.
3. Comencé un script SQL para crear tablas y datos actualizados.
4. Me dieron el error También cuando deshabilité foreign_key_checks. Aunque la base de datos estaba completamente vacía.

La causa fue: desde que utilicé phpmyadmin para crear algunas claves externas en la base de datos cuyo nombre se cambió, las claves externas se crearon con un prefijo de nombre de base de datos pero el prefijo de nombre de base de datos no se actualizó. Así que todavía había referencias en la copia de seguridad-db apuntando a la db recién creada.

Answer 15

Tuve el mismo problema y la solución fue muy simple. Solución: las claves externas declaradas en la tabla no deben establecerse como no nulas.

referencia: si especifica una acción SET NULL, asegúrese de no haber declarado las columnas en la tabla secundaria como NOT NULL. ( ref )

Answer 16

Tuve este mismo problema, luego corregí el nombre del motor como Innodb en las tablas primarias y secundarias y corregí el nombre del campo de referencia. CLAVE c_id ( c_id ) REFERENCIAS x9o_parent_table ( c_id )
entonces funciona bien y las tablas están instaladas correctamente. Esto será uso completo para alguien.

Answer 17

Tuve un error similar al crear una clave externa en una tabla de Muchos a Muchos, donde la clave principal consistía en 2 claves externas y otra columna normal. Solucioné el problema corrigiendo el nombre de la tabla a la que se hace referencia, es decir, la empresa, como se muestra en el código corregido a continuación:

create table company_life_cycle__history -- (M-M)
(
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
);

Answer 18

Tuvo un error similar, pero en mi caso faltaba declarar el pk como auto_increment.

Por si acaso puede ser de ayuda para cualquiera.

Answer 19

Una causa adicional de este error es cuando sus tablas o columnas contienen keywords reservadas:

A veces uno se olvida de estos.

Answer 20

• El motor debe ser el mismo, por ejemplo, InnoDB
• El tipo de datos debe ser el mismo, y con la misma longitud. por ejemplo VARCHAR (20)
• El conjunto de caracteres Columna Columnas debe ser el mismo. por ejemplo, utf8
  Vigilancia: incluso si sus tablas tienen la misma intercalación, las columnas podrían tener una diferente.
• Único : la clave externa debe referirse al campo que es único (generalmente clave principal) en la tabla de referencia.