Unhandledpromiserejectionwarning: Sequelizeforeignkeyconstrainterror

Question

insert or update on table "appointments" violates foreign key constraint "appointments_user_id_fkey"

I have this error in Nodejs when trying to create a new table that has foreign key in Migration, I cannot find the error.

Follow the Migration code:

module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('appointments', {
      id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
      },
      date: {
        allowNull: false,
        type: Sequelize.DATE,
      },
      user_id: {
        type: Sequelize.INTEGER,
        references: { model: 'users', key: 'id' },
        onUpdate: 'CASCADE',
        onDelete: 'SET NULL',
        allowNull: true,
      },
      provider_id: {
        type: Sequelize.INTEGER,
        references: { model: 'users', key: 'id' },
        onUpdate: 'CASCADE',
        onDelete: 'SET NULL',
        allowNull: true,
      },
      canceled_at: {
        type: Sequelize.DATE,
      },
      created_at: {
        type: Sequelize.DATE,
        allowNull: false,
      },
      updated_at: {
        type: Sequelize.DATE,
        allowNull: false,
      },
    });
  },

  down: queryInterface => {
    return queryInterface.dropTable('appointments');
  },
};

Appointmentcontroller.js

import * as Yup from 'yup';
import User from '../models/User';
import Appointment from '../models/Appointment';

class AppointmentController {
  async store(req, res) {
    const schema = Yup.object().shape({
      provider_id: Yup.number().required(),
      date: Yup.date().required(),
    });

    if (!(await schema.isValid(req.body))) {
      return res.status(400).json({ error: 'Validation fails' });
    }

    const { provider_id, date } = req.body;

    const checkIsProvider = await User.findOne({
      where: { id: provider_id, provider: true },
    });

    if (!checkIsProvider) {
      return res
        .status(401)
        .json({ error: 'You can only create appointments with providers' });
    }

    const appointment = await Appointment.create({
      user_id: req.userId,
      provider_id,
      date,
    });

    return res.json(appointment);
  }
}

export default new AppointmentController();

Answer 1

I had this problem and solved as follows, when you are passing the provider_id or user_id look in the users table if these ids exist because the tables are related and not how to add in the table appointments an id that does not exist in the users table.

Answer 2

Speak my guri! This error occurs exactly as the colleague above spoke: why are you trying to insert in the bank a id referenced that doesn’t exist. This can occur for two reasons, or you are passing in Insominia a provider_id that does not exist, OR, you at some point manually edited the user_id in your database, and I continue using a token that was created with the old user_id. If you edited the ids in the database, create a new session and update your Token used in Insomina.

A hug and success.