One To Many relationship using Sequelize creates more foreign keys than expected

user_15426390 0
Viewed 1037

I have been trying to model some tables and their relations in MySql for a class project using Sequelize. It works for all tables except for one, "Messages". It has two One-To-Many relationships with another table, "Users". The definition of the tables is the shown below:Table Definition

To define that relation, you need to add two not null foreign keys on the table "Messages" that reference "Users". So far so good.

The next step is to implement this using Sequelize:

db.Message.belongsTo(db.User,{as: 'receivers', foreignKey: {allowNull: false}});
db.Message.belongsTo(db.User,{as: 'transmitters', foreignKey: {allowNull: false}});

db.User.hasMany(db.Message,{as: 'receivers', foreignKey: {allowNull: false}})
db.User.hasMany(db.Message,{as: 'transmitters', foreignKey: {allowNull: false}})

The problem of all this comes when I check the output that Sequelize provides when I start the server. This is, the actual command that is given to MySql to create the table "Message". It creates three foreign keys pointing to "User": "receiversId", "transmittersId" and "userId". The output is shown below:

Executing (default): CREATE TABLE IF NOT EXISTS `messages` (`id` INTEGER NOT NULL auto_increment , `text` TEXT NOT NULL, `date` DATETIME NOT NULL, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, `receiversId` INTEGER NOT NULL, `transmittersId` INTEGER NOT NULL, `userId` INTEGER NOT NULL, PRIMARY KEY (`id`), FOREIGN KEY (`receiversId`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE, FOREIGN KEY (`transmittersId`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE, FOREIGN KEY (`userId`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;

I have tried in many forms, but that third "userId" foreign is always created, and I dont know why. Any suggests on what am I doing wrong? Or it just a bug from Sequelize? Or I can just ignore that "userId"?

Thank you.

sequelize.js npm foreign-keys mysql sql
1 Answers

You definitely need to indicate foreign key columns explicitly and the same on both ends. And also don't confuse aliases for belongsTo and hasMany. They shouldn't be the same for both ends of a certain association:

db.Message.belongsTo(db.User,{as: 'receiver', foreignKey: {allowNull: false, name: 'receiverId' }});
db.Message.belongsTo(db.User,{as: 'transmitter', foreignKey: {allowNull: false, name: 'transmitterId' }});

db.User.hasMany(db.Message,{as: 'receivedMessages', foreignKey: {allowNull: false, name: 'receiverId' }})
db.User.hasMany(db.Message,{as: 'transmittedMessages', foreignKey: {allowNull: false, name: 'transmitterId' }})

An alias is a name for a model passed as a first argument to belongsTo/hasMany to indicate how this passed model is related to a model on that belongsTo/hasMany was called.

The above aliases would lead to getting model instances with included associations like this:

// Message instance
{
  id: 1,
  // other message fields
  receiver: {
    id: 2,
    name: 'John'
  },
  transmitter: {
    id: 3,
    name: 'Luis'
  }
}
// User instance
{
  id: 3,
  name: 'Luis',
  // other user fields
  receivedMessages: [{
    id: 1,
    text: 'Hello'
  }],
  transmittedMessages: [{
    id: 2,
    name: 'Bye'
  }]
}
user_13766180
Related