Usé el banco de trabajo para implementar un esquema de base de datos, pero obtengo este error cuando uso claves externas en una tabla determinada.

1215 - No se puede agregar restricción de clave externa

Consulta SQL:

CREATE TABLE IF NOT EXISTS `Gam3ty`.`Frequently_Used_Location` (
`idFrequently_Used_Location` INT NOT NULL,
`User_idUser` INT NOT NULL,
`User_College_idCollege` INT NOT NULL,
PRIMARY KEY (`idFrequently_Used_Location`,`User_idUser`,`User_College_idCollege`),
INDEX `fk_Frequently_Used_Location_User1_idx` (`User_idUser` ASC,`User_College_idCollege` ASC),
CONSTRAINT `fk_Frequently_Used_Location_User1`
FOREIGN KEY (`User_idUser` , `User_College_idCollege`)
REFERENCES `Gam3ty`.`User` (`idUser` , `College_idCollege`)

Mi SQL:

CREATE SCHEMA IF NOT EXISTS `Gam3ty` DEFAULT CHARACTER SET utf8 ;
USE `Gam3ty` ;

-- -----------------------------------------------------
-- Table `Gam3ty`.`Frequently_Used_Location`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Gam3ty`.`Frequently_Used_Location` (
`idFrequently_Used_Location` INT NOT NULL,
`User_idUser` INT NOT NULL,
`User_College_idCollege` INT NOT NULL,
PRIMARY KEY (`idFrequently_Used_Location`, `User_idUser`,          `User_College_idCollege`),
INDEX `fk_Frequently_Used_Location_User1_idx` (`User_idUser` ASC, `User_College_idCollege` ASC),
CONSTRAINT `fk_Frequently_Used_Location_User1`
FOREIGN KEY (`User_idUser` , `User_College_idCollege`)
REFERENCES `Gam3ty`.`User` (`idUser` , `College_idCollege`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Gam3ty`.`Location`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Gam3ty`.`Location` (
`idLocation` INT NOT NULL,
`Frequently_Used_Location_idFrequently_Used_Location` INT NOT NULL,
`Frequently_Used_Location_User_idUser` INT NOT NULL,
`Frequently_Used_Location_User_College_idCollege` INT NOT NULL,
`type` VARCHAR(45) NULL,
PRIMARY KEY (`idLocation`),
INDEX `fk_Location_Frequently_Used_Location1_idx`  (`Frequently_Used_Location_idFrequently_Used_Location` ASC,    `Frequently_Used_Location_User_idUser` ASC, `Frequently_Used_Location_User_College_idCollege` ASC),
CONSTRAINT `fk_Location_Frequently_Used_Location1`
FOREIGN KEY (`Frequently_Used_Location_idFrequently_Used_Location` ,  `Frequently_Used_Location_User_idUser` ,        `Frequently_Used_Location_User_College_idCollege`)
 REFERENCES `Gam3ty`.`Frequently_Used_Location`         (`idFrequently_Used_Location`,  `User_idUser` , `User_College_idCollege`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Gam3ty`.`University`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Gam3ty`.`University` (
`idUniversity` INT NOT NULL,
`Location_idLocation` INT NOT NULL,
`Info` VARCHAR(45) NULL,
PRIMARY KEY (`idUniversity`, `Location_idLocation`),
INDEX `fk_University_Location1_idx` (`Location_idLocation` ASC),
CONSTRAINT `fk_University_Location1`
FOREIGN KEY (`Location_idLocation`)
REFERENCES `Gam3ty`.`Location` (`idLocation`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Gam3ty`.`College`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Gam3ty`.`College` (
`idCollege` INT NOT NULL,
`University_idUniversity` INT NOT NULL,
`Location_idLocation` INT NOT NULL,
`Info` VARCHAR(45) NULL,
`Staff` VARCHAR(45) NULL,
`Department` VARCHAR(45) NULL,
PRIMARY KEY (`idCollege`, `University_idUniversity`, `Location_idLocation`),
INDEX `fk_College_University1_idx` (`University_idUniversity` ASC),
INDEX `fk_College_Location1_idx` (`Location_idLocation` ASC),
CONSTRAINT `fk_College_University1`
FOREIGN KEY (`University_idUniversity`)
REFERENCES `Gam3ty`.`University` (`idUniversity`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_College_Location1`
FOREIGN KEY (`Location_idLocation`)
REFERENCES `Gam3ty`.`Location` (`idLocation`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Gam3ty`.`User`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Gam3ty`.`User` (

`idUser` INT NOT NULL,
`College_idCollege` INT NOT NULL,
`UserName` VARCHAR(45) NOT NULL,
`Password` VARCHAR(45) NOT NULL,
`E-mail` VARCHAR(45) NOT NULL,
`Social_media_accounts` VARCHAR(45) NULL,
`Gender` VARCHAR(45) NOT NULL,
`Job` VARCHAR(45) NOT NULL,
`Tel-num` BIGINT(11) NULL,
`Adress` VARCHAR(45) NULL,
PRIMARY KEY (`idUser`, `College_idCollege`, `UserName`),
INDEX `fk_User_College_idx` (`College_idCollege` ASC),
CONSTRAINT `fk_User_College`
FOREIGN KEY (`College_idCollege`)
REFERENCES `Gam3ty`.`College` (`idCollege`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

Revisé los tipos de datos y son todos iguales, también cada clave externa es una clave principal en su tabla original.

1
ِaliya 13 dic. 2016 a las 21:51

2 respuestas

La mejor respuesta

Las tablas se crean en orden de arriba hacia abajo cuando ejecuta un script SQL.

No puede crear una clave externa que haga referencia a una tabla que aún no se ha creado.

Debe ordenar las tablas para que cada tabla se cree antes que cualquier tabla que haga referencia a ella.


@Rahul escribió:

Debe hacer referencia a todas las columnas designadas como clave principal.

Estoy de acuerdo en que esta es una práctica recomendada, porque de lo contrario, puede crear una clave externa donde una fila determinada haga referencia a múltiples filas en la tabla principal. Esto conduce a una semántica ambigua. Por ejemplo, ¿puede eliminar una fila en la tabla principal si hay una fila que hace referencia a ella en la tabla secundaria, pero hay una segunda fila en la tabla principal que satisface la referencia? Esto rompe la definición de integridad referencial en SQL estándar.

Sin embargo, InnoDB lo permite. Puede crear una clave externa que haga referencia a cualquier subconjunto de columnas de la izquierda de cualquier clave (única o no única). Es una muy mala idea, pero InnoDB te permite hacerlo y no arroja ningún error.

Lo siguiente es una locura, pero no es un error para InnoDB:

create table foo (a int, b int, key (a, b));
create table bar (a int, foreign key (a) references foo(a));
2
Bill Karwin 13 dic. 2016 a las 19:10

Esa es la causa de la tabla Gam3ty.User define la clave principal en 3 columnas como se muestra a continuación, pero solo hace referencia a dos de ellas. lo que crea una dependencia funcional parcial. Debe hacer referencia a todas las columnas designadas como clave principal

CREATE TABLE IF NOT EXISTS `Gam3ty`.`User` (
....
PRIMARY KEY (`idUser`, `College_idCollege`, `UserName`)

Tu tabla de referencia

FOREIGN KEY (`User_idUser` , `User_College_idCollege`)
REFERENCES `Gam3ty`.`User` (`idUser` , `College_idCollege`)
1
Rahul 13 dic. 2016 a las 18:56