javascript sequelize - 无法添加外键约束

sequelize - Cannot add foreign key constraint

提问by tinytanks

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:

我正在尝试在两个表之间设置 1:1 的关系。RefreshToken 表将有两个与用户表相关的外键，如下图所示：

I used sequelize-auto to generate my sequelize models.

我使用 sequelize-auto 来生成我的 sequelize 模型。

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

未处理的拒绝错误：SequelizeDatabaseError：ER_CANNOT_ADD_FOREIGN：无法添加外键约束

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 中：

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

但我再次收到同样的错误。如果我删除 RefreshToken 表中的引用，我看不到任何错误，但是当我检查数据库时，我看不到任何带有电子邮件和用户 ID 的外键关系约束

回答by bereket gebredingle

This is common type error mainly occurs because of

这是常见的类型错误，主要是因为

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

1.当主键数据类型和外键数据类型不匹配时

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.

2. 当引用的键不是主键或唯一键时。

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

您不能有两个主键，因此其他引用的键应该定义为唯一的。 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. 
    }

回答by QuickStyles

I see two issues:

我看到两个问题：

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

任何表都不应包含两个主键，并且 userId 不应为整数，而应为 UUID。

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

我有一个外键设置为 INT ，它给了我错误：

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

未处理的拒绝 SequelizeDatabaseError：无法实现外键约束“constraint_name_here”

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'
  }
},