mysql foreign-keys mysql-workbench foreign-key-relationship

#1215 - Cannot add foreign key constraint

I have this SQL query:

-- MySQL Script generated by MySQL Workbench
-- 11/26/14 13:26:51
-- Model: Hashtigator database layout    Version: 1.0
-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema local_sysDB
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `local_sysDB` ;

-- -----------------------------------------------------
-- Schema local_sysDB
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `local_sysDB` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
SHOW WARNINGS;
USE `local_sysDB` ;

-- -----------------------------------------------------
-- Table `local_sysDB`.`accounts`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `local_sysDB`.`accounts` ;

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `local_sysDB`.`accounts` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `password` VARCHAR(255) NULL,
  `email` VARCHAR(255) NULL COMMENT 'email must be unique',
  `role` TINYINT(1) NULL DEFAULT 2 COMMENT '1 = admin, 2 = user\nDefault role is 2 (user)',
  `active` TINYINT(1) NULL DEFAULT 0 COMMENT 'accounts should manually be activated',
  `created` TIMESTAMP NULL DEFAULT NOW(),
  `appId` VARCHAR(255) NULL DEFAULT NULL,
  `appSecret` VARCHAR(255) NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `accounts_email` (`email` ASC),
  UNIQUE INDEX `accounts_appKeys` (`appId` ASC, `appSecret` ASC))
ENGINE = InnoDB
AUTO_INCREMENT = 1
ROW_FORMAT = COMPRESSED;

SHOW WARNINGS;

-- -----------------------------------------------------
-- Table `local_sysDB`.`hashtags`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `local_sysDB`.`hashtags` ;

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `local_sysDB`.`hashtags` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `hashtag` VARCHAR(255) NULL COMMENT 'hashtag must be unique. Must be saved without #',
  `accountId` INT NULL,
  `startTracking` DATETIME NULL COMMENT 'When tracking of the hashtag start',
  `endTracking` DATETIME NULL COMMENT 'When tracking of the hashtag ends',
  `trackingChannels` TINYTEXT NULL COMMENT 'JSON string with all channels that should be tracked',
  `created` TIMESTAMP NULL DEFAULT NOW(),
  PRIMARY KEY (`id`),
  UNIQUE INDEX `hashtags_id` (`id` ASC),
  UNIQUE INDEX `hashtags_hashtag` (`hashtag` ASC),
  INDEX `hashtags_accountId_idx` (`accountId` ASC),
  CONSTRAINT `hashtags_accountId`
    FOREIGN KEY (`accountId`)
    REFERENCES `local_sysDB`.`accounts` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

SHOW WARNINGS;

-- -----------------------------------------------------
-- Table `local_sysDB`.`apiKeys`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `local_sysDB`.`apiKeys` ;

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `local_sysDB`.`apiKeys` (
  `hashtagId` INT NOT NULL,
  `accountId` INT NULL,
  `clientId` VARCHAR(255) NULL,
  `clientSecret` VARCHAR(255) NULL,
  `subscriptionId` CHAR(8) NULL COMMENT 'used for instagram subscription',
  `subsribtionStatus` TINYINT(1) NULL COMMENT '0 = unsubscribed, 1 = subscribed',
  PRIMARY KEY (`hashtagId`),
  UNIQUE INDEX `apiKeys_unique` (`accountId` ASC, `hashtagId` ASC),
  CONSTRAINT `apiKeys_accountId`
    FOREIGN KEY (`accountId`)
    REFERENCES `local_sysDB`.`accounts` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `apiKeys_hashtagId`
    FOREIGN KEY (`hashtagId`)
    REFERENCES `local_sysDB`.`hashtags` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

SHOW WARNINGS;

-- -----------------------------------------------------
-- Table `local_sysDB`.`posts`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `local_sysDB`.`posts` ;

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `local_sysDB`.`posts` (
  `hashtagId` INT NOT NULL,
  `postId` INT UNSIGNED NOT NULL,
  `media` TINYINT(1) NULL,
  `duplicate` TINYINT(1) NULL,
  `sensitive` TINYINT(1) NULL,
  `source` CHAR(10) NULL,
  PRIMARY KEY (`hashtagId`, `postId`),
  INDEX `posts_hashtagId` (`hashtagId` ASC, `postId` ASC))
ENGINE = InnoDB;

SHOW WARNINGS;

-- -----------------------------------------------------
-- Table `local_sysDB`.`posts_instagram`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `local_sysDB`.`posts_instagram` ;

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `local_sysDB`.`posts_instagram` (
  `hashtagId` INT NOT NULL,
  `type` CHAR(8) NULL,
  `filter` VARCHAR(45) NULL,
  `caption` TEXT NULL,
  `userId` INT UNSIGNED NULL,
  `created` INT UNSIGNED NULL,
  `postId` INT UNSIGNED NOT NULL,
  `urlToPost` VARCHAR(255) NULL,
  `media` VARCHAR(255) NULL COMMENT 'First encountered media of COALESCE',
  `media1` VARCHAR(255) NULL COMMENT 'Media can be image or video',
  `media2` VARCHAR(255) NULL,
  `media3` VARCHAR(255) NULL,
  `media4` VARCHAR(255) NULL,
  PRIMARY KEY (`hashtagId`, `postId`),
  CONSTRAINT `posts_instagram_post`
    FOREIGN KEY (`postId` , `hashtagId`)
    REFERENCES `local_sysDB`.`posts` (`postId` , `hashtagId`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

SHOW WARNINGS;

-- -----------------------------------------------------
-- Table `local_sysDB`.`posts_twitter`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `local_sysDB`.`posts_twitter` ;

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `local_sysDB`.`posts_twitter` (
  `hashtagId` INT NOT NULL,
  `coordinates` POINT NULL,
  `created` INT UNSIGNED NULL,
  `filterlevel` VARCHAR(45) NULL,
  `postId` INT UNSIGNED NOT NULL,
  `language` CHAR(11) NULL,
  `sensitive` TINYINT(1) NULL,
  `retweeted` TINYINT(1) NULL,
  `text` CHAR(140) NULL,
  `truncated` TINYINT(1) NULL,
  `userId` INT UNSIGNED NULL,
  `username` CHAR(15) NULL,
  `userFullname` CHAR(20) NULL,
  `media` VARCHAR(255) NULL COMMENT 'First encountered media of COALESCE',
  `media1` VARCHAR(255) NULL,
  `media2` VARCHAR(255) NULL,
  `media3` VARCHAR(255) NULL,
  `media4` VARCHAR(255) NULL,
  PRIMARY KEY (`hashtagId`, `postId`),
  INDEX `posts_coordinates` (`coordinates` ASC)  COMMENT 'should be SPATIAL INDEX',
  CONSTRAINT `posts_twitter_post`
    FOREIGN KEY (`hashtagId` , `postId`)
    REFERENCES `local_sysDB`.`posts` (`hashtagId` , `postId`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

SHOW WARNINGS;

-- -----------------------------------------------------
-- Table `local_sysDB`.`apiRequests`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `local_sysDB`.`apiRequests` ;

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `local_sysDB`.`apiRequests` (
  `id` INT NOT NULL,
  `accountId` INT NULL,
  `ip` INT UNSIGNED NULL,
  `requestUrl` VARCHAR(255) NULL,
  `test` TINYINT(1) NULL DEFAULT 0 COMMENT 'if API request was made during a test. 1 = true, 0 = false',
  `created` TIMESTAMP NULL DEFAULT NOW(),
  PRIMARY KEY (`id`),
  INDEX `apiRequests_accountId_idx` (`accountId` ASC),
  CONSTRAINT `apiRequests_accountId`
    FOREIGN KEY (`accountId`)
    REFERENCES `local_sysDB`.`accounts` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

SHOW WARNINGS;

-- -----------------------------------------------------
-- Table `local_sysDB`.`hashtagLimit`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `local_sysDB`.`hashtagLimit` ;

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `local_sysDB`.`hashtagLimit` (
  `accountId` INT NOT NULL,
  `hashtagId` INT NOT NULL,
  `postLimit` TINYINT NOT NULL,
  PRIMARY KEY (`accountId`),
  UNIQUE INDEX `hashtagLimit_unique` (`hashtagId` ASC, `accountId` ASC),
  CONSTRAINT `hashtagLimit_accountId`
    FOREIGN KEY (`accountId`)
    REFERENCES `local_sysDB`.`accounts` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `hashtagLimit_hashtagId`
    FOREIGN KEY (`hashtagId`)
    REFERENCES `local_sysDB`.`hashtags` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

SHOW WARNINGS;

-- -----------------------------------------------------
-- Table `local_sysDB`.`profanity`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `local_sysDB`.`profanity` ;

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `local_sysDB`.`profanity` (
  `id` INT NOT NULL,
  `filter` VARCHAR(255) NULL COMMENT 'Must be unique',
  `active` TINYINT(1) NULL DEFAULT 1 COMMENT '1 = active, 0  not active',
  PRIMARY KEY (`id`),
  UNIQUE INDEX `profanity_filter` (`filter` ASC))
ENGINE = InnoDB;

SHOW WARNINGS;

-- -----------------------------------------------------
-- Table `local_sysDB`.`accountProfanity`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `local_sysDB`.`accountProfanity` ;

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `local_sysDB`.`accountProfanity` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `accountId` INT NULL,
  `filter` VARCHAR(255) NULL,
  `created` TIMESTAMP NULL DEFAULT NOW(),
  PRIMARY KEY (`id`),
  INDEX `accountProfanity_id` (`accountId` ASC),
  CONSTRAINT `accountProfanity_accountId`
    FOREIGN KEY (`accountId`)
    REFERENCES `local_sysDB`.`accounts` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

SHOW WARNINGS;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

I get this error:

Error
SQL query:



CREATE TABLE IF NOT EXISTS `local_sysDB`.`posts_instagram` (
  `hashtagId` INT NOT NULL,
  `type` CHAR(8) NULL,
  `filter` VARCHAR(45) NULL,
  `caption` TEXT NULL,
  `userId` INT UNSIGNED NULL,
  `created` INT UNSIGNED NULL,
  `postId` INT UNSIGNED NOT NULL,
  `urlToPost` VARCHAR(255) NULL,
  `media` VARCHAR(255) NULL COMMENT 'First encountered media of COALESCE',
  `media1` VARCHAR(255) NULL COMMENT 'Media can be image or video',
  `media2` VARCHAR(255) NULL,
  `media3` VARCHAR(255) NULL,
  `media4` VARCHAR(255) NULL,
  PRIMARY KEY (`hashtagId`, `postId`),
  CONSTRAINT `posts_instagram_post`
    FOREIGN KEY (`postId` , `hashtagId`)
    REFERENCES `local_sysDB`.`posts` (`postId` , `hashtagId`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;
MySQL said: Documentation

#1215 - Cannot add foreign key constraint

I can't figure out what's wrong. I'd like to automatically update posts_twitter or posts_instagram when something in posts changes. I ued mysql workbench to create this query, but I don't get any errors in mysql workbench.

Solution

The SHOW WARNINGS statements in your SQL snippet show the following when pasting the snippet into the command line client:

| Warning |  150 | Create table 'local_sysDB/posts_instagram' with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns.
| Error   | 1005 | Can't create table `local_sysDB`.`posts_instagram` (errno: 150 "Foreign key constraint is incorrectly formed")
| Warning | 1215 | Cannot add foreign key constraint

The key part is "There is no index in the referenced table where the referenced columns appear as the first columns."

The foreign key definition has:

CONSTRAINT `posts_instagram_post`
     FOREIGN KEY (`postId` , `hashtagId`)
     REFERENCES `local_sysDB`.`posts` (`postId` , `hashtagId`)

while the indexes on the posts table are:

PRIMARY KEY (`hashtagId`, `postId`),
INDEX `posts_hashtagId` (`hashtagId` ASC, `postId` ASC))

which have columns hashtagId and postID in the opposite order (the second INDEX is redundant btw. as it covers the same columns as the primary key with ASC being the default anyway)

You need to reverse column order on either the foreign key declaration or in the primary key of he posts table to make things work here.