MySQL Workbench Error Code: 1215. Cannot add foreign key constraint

Question

I keep coming across this error message each time I try to push my code to a server.

I've checked the data types of the foreign and primary keys and made sure they've matched but i still can't find out the issue with the following tables:

    -- -----------------------------------------------------
-- Table `testdb`.`Photo`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `testdb`.`Photo` (
  `nonprofit_photo_link` VARCHAR(255) NOT NULL DEFAULT 'empty',
  `campaign_photo_link` VARCHAR(255) NOT NULL DEFAULT 'empty',
  `photo_id` VARCHAR(40) NOT NULL,
  `company_id` INT NOT NULL,
  `date_created` DATETIME NOT NULL DEFAULT NOW(),
  PRIMARY KEY (`nonprofit_photo_link`, `campaign_photo_link`),
  INDEX `company_id_idx` (`company_id` ASC),
  CONSTRAINT `company_id`
    FOREIGN KEY (`company_id`)
    REFERENCES `testdb`.`Company` (`company_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `testdb`.`Campaign`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `testdb`.`Campaign` (
  `campaign_id` INT NOT NULL AUTO_INCREMENT,
  `campaign_name` VARCHAR(100) NOT NULL,
  `time_created` DATETIME NOT NULL DEFAULT NOW(),
  `time_start` DATETIME NOT NULL,
  `time_end` DATETIME NOT NULL,
  `total_goal` DOUBLE(9,2) NOT NULL,
  `description` TEXT NULL,
  `category` VARCHAR(255) NULL,
  `tax_deductable` ENUM('yes', 'no') NULL DEFAULT 'no',
  `campaign_sponsorship` VARCHAR(100) NOT NULL,
  `phone_number` VARCHAR(12) NULL,
  `street_address` VARCHAR(255) NULL,
  `city` VARCHAR(255) NOT NULL,
  `state` VARCHAR(2) NOT NULL,
  `zip_code` INT(11) NULL,
  `amazon_link` TEXT NULL,
  `amazon_quantity` INT NULL,
  `total_donated` DOUBLE NOT NULL DEFAULT 0,
  `donation_remainder` DOUBLE NULL,
  `photo_link` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`campaign_id`),
  INDEX `photo_link_idx` (`photo_link` ASC),
  CONSTRAINT `photo_link`
    FOREIGN KEY (`photo_link`)
    REFERENCES `testdb`.`Photo` (`campaign_photo_link`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `testdb`.`Nonprofit`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `testdb`.`Nonprofit` (
  `nonprofit_id` INT NOT NULL AUTO_INCREMENT,
  `nonprofit_photo_link` VARCHAR(255) NOT NULL,
  `state` VARCHAR(2) NOT NULL,
  `nonprofit_name` VARCHAR(100) NOT NULL,
  `street_address` VARCHAR(255) NULL,
  `zip_code` INT(11) NULL,
  `number_of_employees` INT NULL,
  `type_of_business` VARCHAR(255) NULL,
  `number_of_donors` INT NOT NULL DEFAULT 0,
  `social_handle` VARCHAR(255) NULL,
  `city` VARCHAR(255) NOT NULL,
  INDEX `photo_link_idx` (`nonprofit_photo_link` ASC),
  PRIMARY KEY (`nonprofit_id`),
  CONSTRAINT `nonprofit_photo_link`
    FOREIGN KEY (`nonprofit_photo_link`)
    REFERENCES `testdb`.`Photo` (`nonprofit_photo_link`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

Error Code: 1215. Cannot add foreign key constraint

0.047 sec

schema is a keyword , you would need to create a database with some other name say my_schema . Also, add Photo table also . — tom, Jun 14 '17 at 20:55
I had just put schema there in lieu of the schema's actual name; so that wasn't the issue for the error. I also have included the Photo table. — Steven Omeis, Jun 14 '17 at 21:45
try this command __SHOW ENGINE INNODB STATUS;__ and edit the question with the LATEST FOREIGN KEY ERROR section in its output . — tom, Jun 14 '17 at 22:28

tom · Answer 1 · 2017-06-14T23:50:36.083

The error is because of the fact that in Photo table you have a composite primary key and you are trying to make only campaign_photo_link as a foreign key i.e not composite foreign key which caused this error in Campaign table.

Similar is the case with Nonprofit table .

I do this and all tables get generated -

 CREATE TABLE IF NOT EXISTS `testdb`.`Company` (
         `company_id` INT NOT NULL AUTO_INCREMENT,
          PRIMARY KEY (`company_id`))
 ENGINE = InnoDB;


  CREATE TABLE IF NOT EXISTS `testdb`.`Photo` (
        `campaign_photo_link` VARCHAR(255) NOT NULL DEFAULT 'empty',
        `nonprofit_photo_link` VARCHAR(255) NOT NULL DEFAULT 'empty',
        `photo_id` VARCHAR(40) NOT NULL,
        `company_id` INT NOT NULL,
        `date_created` DATETIME NOT NULL DEFAULT NOW(),
        PRIMARY KEY (`nonprofit_photo_link`, `campaign_photo_link`),
        INDEX `company_id_idx` (`company_id` ASC),
        CONSTRAINT `company_id`
        FOREIGN KEY (`company_id`)
        REFERENCES `testdb`.`Company` (`company_id`)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION)
  ENGINE = InnoDB;

  CREATE TABLE IF NOT EXISTS `testdb`.`Campaign` (
      `campaign_id` INT NOT NULL AUTO_INCREMENT,
      `campaign_name` VARCHAR(100) NOT NULL,
      `time_created` DATETIME NOT NULL DEFAULT NOW(),
      `time_start` DATETIME NOT NULL,
      `time_end` DATETIME NOT NULL,
      `total_goal` DOUBLE(9,2) NOT NULL,
      `description` TEXT NULL,
      `category` VARCHAR(255) NULL,
      `tax_deductable` ENUM('yes', 'no') NULL DEFAULT 'no',
      `campaign_sponsorship` VARCHAR(100) NOT NULL,
      `phone_number` VARCHAR(12) NULL,
      `street_address` VARCHAR(255) NULL,
      `city` VARCHAR(255) NOT NULL,
      `state` VARCHAR(2) NOT NULL,
      `zip_code` INT(11) NULL,
      `amazon_link` TEXT NULL,
      `amazon_quantity` INT NULL,
      `total_donated` DOUBLE NOT NULL DEFAULT 0,
      `donation_remainder` DOUBLE NULL,
      `np_photo_link` VARCHAR(255) NOT NULL DEFAULT 'empty',
      `cp_photo_link` VARCHAR(255) NOT NULL DEFAULT 'empty',
       PRIMARY KEY (`campaign_id`),
       INDEX `photo_link_idx` (`cp_photo_link` ASC),
       CONSTRAINT `FK_photo_link`
       FOREIGN KEY (`np_photo_link` , `cp_photo_link`)
       REFERENCES `testdb`.`Photo` (`nonprofit_photo_link`, 
       `campaign_photo_link`)
           ON DELETE NO ACTION
           ON UPDATE NO ACTION)
  ENGINE = InnoDB;


  CREATE TABLE IF NOT EXISTS `testdb`.`Nonprofit` (
      `nonprofit_id` INT NOT NULL AUTO_INCREMENT,
      `state` VARCHAR(2) NOT NULL,
      `nonprofit_name` VARCHAR(100) NOT NULL,
      `street_address` VARCHAR(255) NULL,
      `zip_code` INT(11) NULL,
      `number_of_employees` INT NULL,
      `type_of_business` VARCHAR(255) NULL,
      `number_of_donors` INT NOT NULL DEFAULT 0,
      `social_handle` VARCHAR(255) NULL,
      `city` VARCHAR(255) NOT NULL,
      `np_photo_link` VARCHAR(255) NOT NULL DEFAULT 'empty',
      `cp_photo_link` VARCHAR(255) NOT NULL DEFAULT 'empty',
      INDEX `photo_link_idx` (`np_photo_link` ASC),
      PRIMARY KEY (`nonprofit_id`),
      CONSTRAINT `nonprofit_photo_link`
      FOREIGN KEY (`np_photo_link` , `cp_photo_link`)
      REFERENCES `testdb`.`Photo` (`nonprofit_photo_link`, 
        `campaign_photo_link`)
          ON DELETE NO ACTION
          ON UPDATE NO ACTION)
 ENGINE = InnoDB;

Output in mysql command line -

  mysql> show tables;
  +------------------+
  | Tables_in_testdb |
  +------------------+
  | Campaign         |
  | Company          |
  | Nonprofit        |
  | Photo            |
  +------------------+

4 rows in set (0.01 sec)

 mysql> select * from Nonprofit;
 Empty set (0.00 sec)

 mysql> desc Nonprofit;
 +---------------------+--------------+------+-----+---------+----------------+
 | Field               | Type         | Null | Key | Default | Extra          |
 +---------------------+--------------+------+-----+---------+----------------+
 | nonprofit_id        | int(11)      | NO   | PRI | NULL    | auto_increment |
 | state               | varchar(2)   | NO   |     | NULL    |                |
 | nonprofit_name      | varchar(100) | NO   |     | NULL    |                |
 | street_address      | varchar(255) | YES  |     | NULL    |                |
 | zip_code            | int(11)      | YES  |     | NULL    |                |
 | number_of_employees | int(11)      | YES  |     | NULL    |                |
 | type_of_business    | varchar(255) | YES  |     | NULL    |                |
 | number_of_donors    | int(11)      | NO   |     | 0       |                |
 | social_handle       | varchar(255) | YES  |     | NULL    |                |
 | city                | varchar(255) | NO   |     | NULL    |               

 | np_photo_link       | varchar(255) | NO   | MUL | empty   |                |

 | cp_photo_link       | varchar(255) | NO   |     | empty   |                |
 +---------------------+--------------+------+-----+---------+--------
 --------+
 12 rows in set (0.00 sec)

 mysql> desc Company;
 +------------+---------+------+-----+---------+----------------+
 | Field      | Type    | Null | Key | Default | Extra          |
 +------------+---------+------+-----+---------+----------------+
 | company_id | int(11) | NO   | PRI | NULL    | auto_increment |
 +------------+---------+------+-----+---------+----------------+
 1 row in set (0.00 sec)

 mysql> desc Photo;
 +----------------------+--------------+------+-----+-------------------+-------+
 | Field                | Type         | Null | Key | Default           | Extra |
 +----------------------+--------------+------+-----+-------------------+-------+
 | campaign_photo_link  | varchar(255) | NO   | PRI | empty             |       |
 | nonprofit_photo_link | varchar(255) | NO   | PRI | empty             |       |
 | photo_id             | varchar(40)  | NO   |     | NULL              |       |
 | company_id           | int(11)      | NO   | MUL | NULL              |       |
 | date_created         | datetime     | NO   |     | CURRENT_TIMESTAMP |       |
 +----------------------+--------------+------+-----+-------------------+-------+
 5 rows in set (0.00 sec)

Summary: I have added composite primary key in both tables.It will be upto you whether it will be fine for your use case or not.

You can read Creating a Composite Foreign Key

Also , check How Can I Create a Foreign Key from Composite Primary Key

Let me know, if it helps !

Thanks

@Steven, please upvote this answer if you find it useful – tom Jun 21 '17 at 18:03 — tom, Jun 21 '17 at 18:03

MySQL Workbench Error Code: 1215. Cannot add foreign key constraint

1 Answers1