CODEWITHSUNDEEP
mysqldatabase
Anim Rahman
Anim Rahman

Reputation: 67

cant add foreign key constraint, error 1215 in mysql workbench.

So this is my code, I cannot proceed because I am always getting that foreign key constraint error. I have checked the data type they are consistent. Where am I going wrong?. All the problem persists where foreign keys are required. i have just posted two tables here.

-- -----------------------------------------------------
-- Table `44376936`.`AccountType`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `44376936`.`AccountType` ;

CREATE TABLE IF NOT EXISTS `44376936`.`AccountType` (
  `AccountTypeID` float NOT NULL,
  `AccountTypeName` VARCHAR(45) NULL,
  `AccountTypeDesc` VARCHAR(45) NULL,
  `AccountTypeInterestRate` FLOAT NULL,
  `AccountTypeServiceFee` FLOAT NULL,
  PRIMARY KEY (`AccountTypeID`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `44376936`.`Account`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `44376936`.`Account` ;

CREATE TABLE IF NOT EXISTS `44376936`.`Account` (
  `AccountBSB` INT NOT NULL,
  `AccountNumber` INT NOT NULL,
  `AccountCurrentBalance` FLOAT NULL,
  `AccountType_AccountTypeID` Float NOT NULL,
  PRIMARY KEY (`AccountBSB`, `AccountNumber`, `AccountType_AccountTypeID`),
  INDEX `fk_Account_AccountType_idx` (`AccountType_AccountTypeID` ASC),
  CONSTRAINT `fk_Account_AccountType`
    FOREIGN KEY (`AccountType_AccountTypeID`)
    REFERENCES `44376936`.`AccountType` (`AccountTypeID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

Upvotes: 0

Views: 49

Answers (1)

Thomas G
Thomas G

Reputation: 10206

I can reproduce

It comes from this instruction :

REFERENCES `44376936`.`AccountType` (`AccountTypeID`)

and the problem is the database name, probably because it begins with a number and not a letter.

This works :

REFERENCES `AccountType` (`AccountTypeID`)

So get rid of the database name. If you are running this without being on the 44376936 database, execute this instruction at the beginning of your script :

USE `44376936`;

Rextester example

Upvotes: 1

Related Questions