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 ?

Answer 1

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. 
        }
    }
)