Sequelize relations foreign key constraint

Question

I have a post route that is using the sequelize create method to add an event to the database. I have defined the tables for user and event as such:

Events

    const Events = sequelize.define('Events', {
        Event_id: {
            type: DataTypes.INTEGER,
            autoIncrement: true,
            primaryKey: true
          },
          name: {
            type: DataTypes.STRING,
            allowNull: false
          },
          month: {
            type: DataTypes.STRING,
            allowNull: false
          },
          day:{
            type:DataTypes.INTEGER,
            allowNull: false
          },
          year: {
            type: DataTypes.INTEGER,
            allowNull: false
          },
          important:{
              type: DataTypes.BOOLEAN,
              allowNull: false,
              defaultValue: false
          },
          description:{
              type: DataTypes.STRING,
              allowNull: true
          }
    });
    return Events;
}

User

    const User = sequelize.define('Users', {
        user_id: {
            type: DataTypes.INTEGER,
            autoIncrement: true,
            primaryKey: true
          },
          name: {
            type: DataTypes.STRING,
            allowNull: false
          },
          email: {
            type: DataTypes.STRING,
            allowNull: false
          },
          password: {
            type: DataTypes.STRING,
            allowNull: false
          }
    }, {});
    User.associate = function(models){
      User.hasMany(models.Events, {as: 'user'})
    }
    return User;
}

The post route seems to run correctly with the exception on sequelize not being able to attach the user Id to the event that is created do to

"Cannot add or update a child row: a foreign key constraint fails (planitdb.events, CONSTRAINT events_ibfk_1 FOREIGN KEY (UserUserId) REFERENCES users (user_id) ON DELETE SET NULL ON UPDATE CASCADE)"

I am not sure I understand exactly why this is happening. I am thinking I need to define the relation in the Events table as well? If that is the case, I am not sure I understand what type of relationship the Events table has with the User table. Or is the relationship a single event to a single user?

Any help would be greatly appreciated.

NorthernDev NorthernDev · Accepted Answer · 2019-11-21T21:15:48

Your events object does not contain a mapping for user_id. This object needs to include a user_id field and this needs to be populated prior to attempting to insert a record to the events table.

EDIT - including sequelize relations solution:

Instead of this:

const Events = sequelize.define('Events', {...});
User.associate = function(models){
  User.hasMany(models.Events, {as: 'user'})
}

Try:

const Events = sequelize.define('Events', {...});
const User = sequelize.define('User', {...});
User.hasMany(Events, {as: 'user'});

or:

const Events = sequelize.define('Events', {});
const User = sequelize.define('User', {...});
Events.belongsTo(User,{
    foreignKey: 'user_id',
    constraints: false,
    as: 'user'
});

Sequelize relations foreign key constraint

1 Answers