-1

How to block insert duplicate record in node MySQL for many-to-many relation ? I have two foreign keys: id_product and id_customer and I would like to create relation between them with primary key id_relation. For this moment I can save multiple times the same combination of product and customer what each time creates new id_relation. Is there a way to check first of such combination already exists before saving it into MySQL database or another way to prevent duplication of the same record?

exports.create = (req, res) => {
const relation = {
    id_product: req.body.id_product,
    id_customer: req.body.id_customer
};

Relation.create(relation)
    .then(data => {
        res.send(data);
    })
    .catch(err => {
        res.status(500).send({
            message:
                err.message || "Error"
        });
    });

}

module.exports = function(sequelize, DataTypes) {
return sequelize.define('relation', {
  id: {
    type: DataTypes.INTEGER(11),
    allowNull: false,
    primaryKey: true,
    autoIncrement: true,
    unique: true
  },
  id_product: {
    type: DataTypes.INTEGER(11),
    allowNull: false,
    references: {
      model: 'product',
      key: 'id' 
    },
    onDelete: 'CASCADE'
  },
  id_customer: {
    type: DataTypes.INTEGER(11),
    allowNull: false,
    references: {
      model: 'customer',
      key: 'id'
    },
    onDelete: 'CASCADE'
  },
}, {
  timestamps: false,
  tableName: 'relation'
});

};

anna118690
  • 7
  • 7
  • You need to add unique key too column & when inserting record refer this https://stackoverflow.com/questions/54866952/how-to-ignore-sequelizeuniqueconstrainterror-in-sequelize – Vijay Palaskar Jul 15 '20 at 09:17
  • I have just added to the both: product and customer: unique: true, and now it is not possible to create the same pair of product and customer, error is displayed. – anna118690 Jul 15 '20 at 11:42
  • Now Use upsert refer this https://sequelize.org/master/class/lib/model.js~Model.html#static-method-upsert – Vijay Palaskar Jul 15 '20 at 11:45
  • Ok I have used a method 'upsert' and now it is OK ! thanks ! – anna118690 Jul 15 '20 at 13:59

2 Answers2

1

First you need to add the UNIQUE index to your table and when inserting record refer this

How to ignore SequelizeUniqueConstraintError in Sequelize?

Sequelize upsert method documentation https://sequelize.org/master/class/lib/model.js~Model.html#static-method-upsert

ALTER TABLE table_name ADD UNIQUE INDEX(FirstName, lastName);

then using insert ignore to avoid duplicate records:

INSERT IGNORE INTO table_name (product_id, customer_id) VALUES (1, 2), (1, 2);

reference:

https://www.mysqltutorial.org/mysql-insert-ignore/

Vijay Palaskar
  • 526
  • 5
  • 15
0

You can create MySQL procedure to ensure that only the unique pair of id_customer and id_product is inserted. Code will look something like this:

CREATE PROCEDURE `procedure_name`(IN relation INT,IN customer INT,IN product INT)
BEGIN
    IF 
        (SELECT ((SELECT customer "id_custoemr", product "id_product") NOT IN (SELECT id_customer, id_product FROM table_name)))
    THEN
        INSERT INTO `table_name` (id_relation, id_customer, id_product) VALUES (relation, customer, product);
    END IF;
END

In your Node application instead of running query, you just need to call your procedure with correct parameters like this:

call procedure_name(4,4,3);
Aqib Mukhtar
  • 361
  • 1
  • 5