javascriptmysqlnode.jsexpresssequelize.js

sequelize - Cannot add foreign key constraint


I'm trying to set a 1:1 relation between two tables. RefreshToken table will have two foreignKey releated to Users table, as in this image: enter image description here

I used sequelize-auto to generate my sequelize models.

Users model:

module.exports = function(sequelize, DataTypes) {
  return sequelize.define('Users', {
    idUsers: {
      type: DataTypes.INTEGER(11),
      allowNull: false,
      primaryKey: true
    },
    name: {
      type: DataTypes.STRING(45),
      allowNull: true
    },
    mail: {
      type: DataTypes.STRING(45),
      allowNull: false,
      primaryKey: true
    }
  }, {
    tableName: 'Users'
  });
};

RefreshToken model:

module.exports = function(sequelize, DataTypes) {
  return sequelize.define('RefreshToken', {
    idRefreshToken: {
      type: DataTypes.INTEGER(11),
      allowNull: false,
      primaryKey: true
    },
    token: {
      type: DataTypes.TEXT,
      allowNull: true
    },
    userId: {
      type: DataTypes.INTEGER(11),
      allowNull: false,
      primaryKey: true,
      references: {
        model: 'Users',
        key: 'idUsers'
      }
    },
    userEmail: {
      type: DataTypes.STRING(45),
      allowNull: false,
      primaryKey: true,
      references: {
        model: 'Users',
        key: 'mail'
      }
    }
  }, {
    tableName: 'RefreshToken'
  });
};

When I run the application, I receive this error:

Unhandled rejection Error: SequelizeDatabaseError: ER_CANNOT_ADD_FOREIGN: Cannot add foreign key constraint

I tried to add explicit the relation, adding in Users table:

User.associate = (models) => {
    User.hasOne(models.RefreshToken, {
      foreignKey: 'userId'
    });
    User.hasOne(models.RefreshToken, {
      foreignKey: 'userEmail'
    });
  };

and in RefreshToken:

RefreshToken.associate = (models) => {
    RefreshToken.belongsTo(models.Users, {
      foreignKey: 'userId'
    });
    RefreshToken.belongsTo(models.Users, {
      foreignKey: 'userEmail'
    });
  };

But I receive again the same error. If I remove the references in the RefreshToken table I don't see any error, but when I check the database I don't see any foreign key relation constraint with email and id of the User


Solution

  • This is common type error mainly occurs because of

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

    return sequelize.define('RefreshToken', {
        userId: {
          type: DataTypes.INTEGER(11), // The data type defined here and 
          references: {
            model: 'Users',
            key: 'idUsers'
          }
        }, 
    
    
    return sequelize.define('Users', {
        idUsers: {
          type: DataTypes.INTEGER(11),  // This data type should be the same
        },
    

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

    You can not have two primary keys, so other referenced keys should be defined unique. unique:true

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