MySQL error when running script "Cannot add Foreign Key Constraint"

Question

I created a schema with MySQL Workbench, exported it as a script, and ran the script on my MySQL local server. Everything worked, except the 'member' table. When it creates this table, I get Error 1215: Cannot Add Foreign Key Constraint.

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


CREATE TABLE IF NOT EXISTS `endor`.`activity` (
  `activityid` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  PRIMARY KEY (`activityid`))
ENGINE = InnoDB;


CREATE TABLE IF NOT EXISTS `endor`.`resource` (
  `resourceid` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `location` VARCHAR(45) NULL,
  `time_checkedout` VARCHAR(45) NULL,
  `activity_activityid` INT NULL,
  PRIMARY KEY (`resourceid`),
  INDEX `fk_resource_activity1_idx` (`activity_activityid` ASC),
  CONSTRAINT `fk_resource_activity1`
    FOREIGN KEY (`activity_activityid`)
    REFERENCES `endor`.`activity` (`activityid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;



CREATE TABLE IF NOT EXISTS `endor`.`treasury` (
  `treasuryid` INT NOT NULL AUTO_INCREMENT,
  `treasurer_name` VARCHAR(45) NULL,
  PRIMARY KEY (`treasuryid`))
ENGINE = InnoDB;



CREATE TABLE IF NOT EXISTS `endor`.`account` (
  `accountid` INT NOT NULL AUTO_INCREMENT,
  `type` VARCHAR(45) NULL,
  `balance` INT NULL,
  `treasury_treasuryid` INT NULL,
  PRIMARY KEY (`accountid`),
  INDEX `fk_account_treasury1_idx` (`treasury_treasuryid` ASC),
  CONSTRAINT `fk_account_treasury1`
    FOREIGN KEY (`treasury_treasuryid`)
    REFERENCES `endor`.`treasury` (`treasuryid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


CREATE TABLE IF NOT EXISTS `endor`.`member` (
  `memberid` INT NOT NULL AUTO_INCREMENT,
  `fname` VARCHAR(45) NULL,
  `lname` VARCHAR(45) NULL,
  `email` VARCHAR(45) NULL,
  `type` VARCHAR(45) NULL,
  `resource_resourceid` INT NULL,
  `resource_activity_activityid` INT NULL,
  `treasury_treasuryid` INT NULL,
  `account_accountid` INT NULL,
  PRIMARY KEY (`memberid`),
  INDEX `fk_member_resource1_idx` (`resource_resourceid` ASC,                   `resource_activity_activityid` ASC),
  INDEX `fk_member_treasury1_idx` (`treasury_treasuryid` ASC),
  INDEX `fk_member_account1_idx` (`account_accountid` ASC),
  CONSTRAINT `fk_member_resource1`
    FOREIGN KEY (`resource_resourceid` , `resource_activity_activityid`)
    REFERENCES `endor`.`resource` (`resourceid` , `activity_activityid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_member_treasury1`
    FOREIGN KEY (`treasury_treasuryid`)
    REFERENCES `endor`.`treasury` (`treasuryid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_member_account1`
    FOREIGN KEY (`account_accountid`)
    REFERENCES `endor`.`account` (`accountid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;



CREATE TABLE IF NOT EXISTS `endor`.`accounts_payable` (
  `apid` INT NOT NULL AUTO_INCREMENT,
  `treasury_treasuryid` INT NULL,
  PRIMARY KEY (`apid`),
  INDEX `fk_accounts_payable_treasury1_idx` (`treasury_treasuryid` ASC),
  CONSTRAINT `fk_accounts_payable_treasury1`
    FOREIGN KEY (`treasury_treasuryid`)
    REFERENCES `endor`.`treasury` (`treasuryid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;



CREATE TABLE IF NOT EXISTS `endor`.`accounts_receivable` (
  `arid` INT NOT NULL AUTO_INCREMENT,
  `treasury_treasuryid` INT NULL,
  PRIMARY KEY (`arid`),
  INDEX `fk_accounts_receivable_treasury1_idx` (`treasury_treasuryid` ASC),
  CONSTRAINT `fk_accounts_receivable_treasury1`
    FOREIGN KEY (`treasury_treasuryid`)
    REFERENCES `endor`.`treasury` (`treasuryid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

Possible duplicate of [MySQL Cannot Add Foreign Key Constraint](http://stackoverflow.com/questions/15534977/mysql-cannot-add-foreign-key-constraint) — miken32, Dec 09 '15 at 21:41

score 0 · Answer 1 · answered Dec 09 '15 at 21:45

It appears the FK activity_activityid is being referenced through resource instead of activity.

CONSTRAINT `fk_member_resource1`
FOREIGN KEY (`resource_resourceid` , `resource_activity_activityid`)
REFERENCES `endor`.`resource` (`resourceid` , `activity_activityid`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,

Try changing the above to:

 CONSTRAINT `fk_member_resource1`
FOREIGN KEY (`resource_resourceid`)
REFERENCES `endor`.`resource` (`resourceid`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
 CONSTRAINT `fk_member_activity1`
FOREIGN KEY (`activity_activityid`)
REFERENCES `endor`.`activity` (`activityid`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,

score 0 · Accepted Answer · answered Dec 09 '15 at 21:45

You are trying to create this foreign key:

CONSTRAINT `fk_member_resource1`
  FOREIGN KEY (`resource_resourceid` , `resource_activity_activityid`)
  REFERENCES `endor`.`resource` (`resourceid` , `activity_activityid`)

but the primary key of endor.resource is just (resourceid). Your foreign key should not reference non-key fields.

Moreover, you should consider whether table endor.member needs column resource_activity_activityid at all. If this is required to be the same as the activity_activity_id column of the referenced resource row, then your schema is denormalized by its presence. If you leave it in, then you leave open the possibility that the member and resource will disagree about the appropriate activity id.

Sometimes databases are intentionally denormalized, typically for performance reasons, but I don't at this point see any justification for that here.

MySQL error when running script "Cannot add Foreign Key Constraint"

2 Answers2