sequelize-无法添加外键约束 [英] sequelize - Cannot add foreign key constraint
问题描述
我正在尝试在两个表之间设置1:1关系. RefreshToken表将有两个ForeignKey释放到Users表,如下图所示:
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:
我用sequelize-auto生成了我的sequelize模型.
I used sequelize-auto to generate my sequelize models.
用户模型:
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模型:
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:
未处理的拒绝错误:SequelizeDatabaseError: ER_CANNOT_ADD_FOREIGN:无法添加外键约束
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'
});
};
并使用RefreshToken:
and in RefreshToken:
RefreshToken.associate = (models) => {
RefreshToken.belongsTo(models.Users, {
foreignKey: 'userId'
});
RefreshToken.belongsTo(models.Users, {
foreignKey: 'userEmail'
});
};
但是我再次收到相同的错误.如果删除RefreshToken表中的引用,我看不到任何错误,但是当我检查数据库时,我看不到任何包含电子邮件和用户ID的外键关系约束
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
推荐答案
这是常见的类型错误,主要是由于
This is common type error mainly occurs because of
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.当所引用的密钥不是主密钥或唯一密钥时.
您不能有两个主键,因此其他引用的键应定义为唯一. unique:true
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.
}
这篇关于sequelize-无法添加外键约束的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!