I used workbench to implement a database schema, but I'm getting this error when using foreign keys in a certain table.
1215 - Cannot add foreign key constraint
SQL query:
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`)
my 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;
I checked the datatypes and they are all the same, also every foreign key is a primary key in it's original table.
Tables are created in order from top to bottom when you run a SQL script.
You can't create a foreign key that references a table that hasn't been created yet.
You must order the tables so that each table is created before any tables that reference it.
@Rahul wrote:
You need to refer all of the columns designated as primary key.
I agree this is a recommended practice, because otherwise you can create a foreign key where a given row references multiple rows in the parent table. This leads to ambiguous semantics. For instance, can you delete a row in the parent table if there's a row referencing it in the child table, but there's a second row in the parent table that satisfies the reference? This breaks the definition of referential integrity in standard SQL.
Nevertheless, InnoDB allows it. You can make a foreign key that references any left-most subset of columns of any key (unique or non-unique). It's a very bad idea, but InnoDB lets you do it and does not throw an error.
The following is crazy, but it's not an error to InnoDB:
create table foo (a int, b int, key (a, b));
create table bar (a int, foreign key (a) references foo(a));