MySql - Sequalize - Cannot add foreign key constraint

Question

I am trying to using Nodejs sequelize to create database. The commands being invoked are

CREATE TABLE IF NOT EXISTS `wheel` (`id` INTEGER NOT NULL auto_increment , `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, `shopId` VARCHAR(255), PRIMARY KEY (`id`), 
FOREIGN KEY (`shopId`) REFERENCES `shop` (`id`) ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `segments` (`segmentID` VARCHAR(255) NOT NULL , `heading` VARCHAR(255) NOT NULL, `subHeading` VARCHAR(255) NOT NULL, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, `wheelId` INTEGER, PRIMARY KEY (`segmentID`),
 FOREIGN KEY (`wheelId`) REFERENCES `wheel` (`id`) ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `shop` (`id` VARCHAR(255) NOT NULL , `accessToken` VARCHAR(255) NOT NULL, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;

But I get this error

Unhandled rejection SequelizeDatabaseError: ER_CANNOT_ADD_FOREIGN: Cannot add foreign key constraint

When I try to see the last foreign key error , it says

------------------------
LATEST FOREIGN KEY ERROR
------------------------
2016-07-28 19:23:21 0x700000d95000 Error in foreign key constraint of table exitpopup/segments:
FOREIGN KEY (`wheelId`) REFERENCES `wheel` (`id`) ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=InnoDB:
Cannot resolve table name close to:
 (`id`) ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=InnoDB

Strangely, When I put the sql statements in sql console , it works and there isn't any error. What am I doing wrong ?

Solution 1

The order needs to change. You are creatig the wheel table before you have created the shop table. However wheel refers to the shop table which does not exists in your original set of queries. When you change the order the shop table already exists so the error does not occur.

CREATE TABLE IF NOT EXISTS `shop` 
 (`id` VARCHAR(255) NOT NULL , `accessToken` VARCHAR(255) NOT NULL, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, 
 PRIMARY KEY (`id`)) ENGINE=InnoDB;


CREATE TABLE IF NOT EXISTS `wheel` 
(`id` INTEGER NOT NULL auto_increment , `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, `shopId` VARCHAR(255), 
 PRIMARY KEY (`id`), 
 FOREIGN KEY (`shopId`) REFERENCES `shop` (`id`) ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `segments` 
(`segmentID` VARCHAR(255) NOT NULL , `heading` VARCHAR(255) NOT NULL, `subHeading` VARCHAR(255) NOT NULL, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, `wheelId` INTEGER, 
 PRIMARY KEY (`segmentID`),
 FOREIGN KEY (`wheelId`) REFERENCES `wheel` (`id`) ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=InnoDB;

Solution 2

If you need to turn off this check, because you're importing a bunch of tables from a dump from another DB, you want to run:

set FOREIGN_KEY_CHECKS=0

As if it was a SQL statement. So for me in Sequelize I ran:

let promise = sequelize.query("set FOREIGN_KEY_CHECKS=0");

Solution 3

This is because of mainly following 2 reasons

1. When the primary key data type and the foreign key data type did not match.

return sequelize.define('Manager', {
    id: {
      type: DataTypes.INTEGER(11), // The data type defined here and 
      references: {
        model: 'User',
        key: 'id'
      }
    }
  }
)

return sequelize.define('User', {
    id: {
      type: DataTypes.INTEGER(11),  // This data type should be the same
    }
  }
)

2. When the referenced key is not a primary or unique key.

return sequelize.define('User', {
        id: {
          primaryKey: true  
        },
        mail: {
            type: DataTypes.STRING(45),
            allowNull: false,
            primaryKey: true   // You should change this to 'unique:true'. you cant have two primary keys in one table. 
        }
    }
)

Solution 4

It's possible your char encoding is different between the foreign key and the original key. Check your schema.

View more solutions

Comments

[Mendon Ashwini]

where did u add this?

[Ryan Shillington]

@MendonAshwini Just before creating the tables.

[Ilyas karim]

This should be fixed by Sequlize because I am using database sync.