sequelize - Cannot add foreign key constraint

Question

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:

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

Try to use only `userid` as foreign key in RefreshToken table. — matrix, Sep 05 '17 at 09:24
Or you should use Composite Foreign keys: https://stackoverflow.com/questions/9780163/composite-key-as-foreign-key-sql — matrix, Sep 05 '17 at 09:36
Here is information that Composite foreign keys are not supported in sequelize.js: https://github.com/sequelize/sequelize/issues/311 — matrix, Sep 05 '17 at 09:43

bereket gebredingle · Accepted Answer · 2018-09-23T22:51:05.590

74

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

edited Sep 23 '18 at 22:51

answered Sep 23 '18 at 22:45

bereket gebredingle

12,064
3
36
47

Spot on! Thank you!! – Sisir Jan 17 '19 at 13:42
In my case, I don't know why, in my db my reference was bad, the reference was with another table that was not correct, I corrected the reference and solved my problem. – Lud Osorio Aug 05 '20 at 02:05
1

My case was nearly as same as @LudOsorio's. Instead of referencing "clients" i was referencing "client". – Alexander Santos Nov 12 '20 at 13:49

score 2 · Answer 2 · answered Dec 19 '17 at 05:14

2

I see two issues:

No table should contain two primary keys and userId shouldn't be in integer it should be a UUID.

I had a foreign key set to INT and it gave me error:

Unhandled rejection SequelizeDatabaseError: foreign key constraint "constraint_name_here" cannot be implemented

Try changing:

userId: {
  type: DataTypes.INTEGER(11),
  allowNull: false,
  primaryKey: true,
  references: {
    model: 'Users',
    key: 'idUsers'
  }
},

To

userId: {
  type: DataTypes.UUID,
  allowNull: false,
  foreignKey: true,
  references: {
    model: 'Users',
    key: 'idUsers'
  }
},

answered Dec 19 '17 at 05:14

QuickStyles

102
5

2

I have a table with two primary keys. It's a join table, and the two columns are supposed to act as a joint key. Seems to work just fine. – Tom Mar 26 '18 at 21:07
1

Hey Tom, what you're saying is exactly right. Those two primary keys are indeed primary keys but not in relation to the join table. In the join table's point of view those two keys are foreign keys pointing to the primary keys in different tables. – QuickStyles Sep 20 '18 at 18:31

sequelize - Cannot add foreign key constraint

2 Answers2

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

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

Linked