OiO.lk Community platform!

Oio.lk is an excellent forum for developers, providing a wide range of resources, discussions, and support for those in the developer community. Join oio.lk today to connect with like-minded professionals, share insights, and stay updated on the latest trends and technologies in the development field.
  You need to log in or register to access the solved answers to this problem.
  • You have reached the maximum number of guest views allowed
  • Please register below to remove this limitation

sequelize - Cannot add foreign key constraint

  • Thread starter Thread starter gio
  • Start date Start date
G

gio

Guest
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:

Code:
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:

Code:
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:

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

and in RefreshToken:

Code:
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

<p>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:
<a href="https://i.sstatic.net/B2fcU.png" rel="noreferrer"><img src="https://i.sstatic.net/B2fcU.png" alt="enter image description here"></a></p>

<p>I used sequelize-auto to generate my sequelize models.</p>

<p><strong>Users model:</strong></p>

<pre><code>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'
});
};
</code></pre>

<p><strong>RefreshToken model:</strong></p>

<pre><code>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'
});
};
</code></pre>

<p>When I run the application, I receive this error:</p>

<blockquote>
<p>Unhandled rejection Error: SequelizeDatabaseError:
ER_CANNOT_ADD_FOREIGN: Cannot add foreign key constraint</p>
</blockquote>

<p>I tried to add explicit the relation, adding in Users table:</p>

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

<p>and in RefreshToken:</p>

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

<p>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</p>
 

Latest posts

D
Replies
0
Views
1
Dima Tisnek
D
S
Replies
0
Views
1
sandeep kumar
S
F
Replies
0
Views
1
Felipe Reis
F
Top