如何在多对多关系中阻止在节点mysql中插入重复记录？

问题描述

如何为多对多关系阻止在节点MySQL中插入重复记录？我有两个外键：id_product和id_customer，我想用主键id_relation创建它们之间的关系。目前，我可以多次保存相同的产品和客户组合，每次创建新的id_relation。有没有办法在将其保存到MySQL数据库之前先检查这种组合是否已经存在，或者另一种方法来防止相同记录的重复？

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'
});

};

推荐答案

首先，您需要向表中添加UNIQUE索引，并在插入记录时参考此

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

Sequelize upsert方法文档

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

ALTER TABLE table_name添加唯一索引（FirstName，lastName）；

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

然后使用插入忽略避免重复记录：

then using insert ignore to avoid duplicate records:

插入IGNORE INTO表名（product_id，customer_id）值（1、2），（1、2）；

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

引用：

这篇关于如何在多对多关系中阻止在节点mysql中插入重复记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！