Sequelize throwing error "must be unique"

Question

I am using Sequelize with my mysql database and have a many to many relationship with a CostumerDriverTransaction model as a through table

when I try to create a row form this table I get this error "message": "driverId must be unique"

CostumerDriverTransaction Model

const CostumerDriverTransaction = sequelize.define('CostumerDriverTransaction', {
costumerId: {
  type:DataTypes.INTEGER,
},
driverId: {
  type:DataTypes.INTEGER,
},
restaurantId: {
  type:DataTypes.INTEGER,
},
orderId: DataTypes.INTEGER,
transactionId: {
  type:DataTypes.INTEGER,
  primaryKey:true,
},
},{});

and this the association:

index.js

db.user.belongsToMany(db.driver,{
  through:db.costumerDriverTransaction,
  foreignKey:{
    name:'costumerId'
  }
});
db.driver.belongsToMany(db.user,{
  through:db.costumerDriverTransaction,
  foreignKey:{
    name:'driverId'
  }
});
db.user.hasMany(db.costumerDriverTransaction,{
  foreignKey:{
    name:'costumerId'
  }
});
db.driver.hasMany(db.costumerDriverTransaction,{
  foreignKey:{
    name:'driverId'
  }
});
db.costumerDriverTransaction.belongsTo(db.user,{
  foreignKey:{
    name:'costumerId'
  }
});
db.costumerDriverTransaction.belongsTo(db.driver,{
  foreignKey:{
    name:'driverId'
  }
});

what's the problem please ?

Answer 1

Most of the columns do not need to be specified in your define and will be create automatically, such at the primary keys and relationship fields. If you would like to override them, you can specify them in the column definition (this is only really notable because transactionId in your example would become id when autogenerated.

Here we create models for each of your objects and then define all the different relationships between them. Because the relationship table is a "super" many-to-many by having it's own primary key instead of a composite you can query from it or any of the other models "through" it.

If you don't want the createdAt and updatedAt columns on a table pass timestamps: false, into the options for sequelize.define().

// your other models
const User = sequelize.define('User', {}, {});
const Driver = sequelize.define('Driver', {}, {});
const Restaurant = sequelize.define('Restaurant', {}, {});
const Order = sequelize.define('Order', {}, {});

// the relational table, note that leaving off the primary key will use `id` instead of transactionId
const CostumerDriverTransaction = sequelize.define('CostumerDriverTransaction', {}, {});

// relate the transaction to the other 
CostumerDriverTransaction.belongsTo(User, { foreignKey: 'customerId' });
CostumerDriverTransaction.belongsTo(Driver, { foreignKey: 'driverId' });
CostumerDriverTransaction.belongsTo(Restaurant, { foreignKey: 'restaurantId' });
CostumerDriverTransaction.belongsTo(Order, { foreignKey: 'orderId' });

// relate the models to the transactions
User.hasMany(CostumerDriverTransaction, { as: 'transactions', foreignKey: 'customerId' });
// relate models to other models through transactions
User.hasMany(Driver, { as: 'drivers', through: 'CostumerDriverTransaction', foreignKey: 'customerId', otherKey: 'driverId' });
User.hasMany(Restaurant, { as: 'restaurants', through: 'CostumerDriverTransaction', foreignKey: 'customerId', otherKey: 'restaurantId' });
User.hasMany(Order, { as: 'orders', through: 'CostumerDriverTransaction', foreignKey: 'customerId', otherKey: 'orderId' });

// Drivers
Driver.hasMany(CostumerDriverTransaction, { foreignKey: 'driverId' });
Driver.hasMany(User, { as: 'customers', through: 'CostumerDriverTransaction', foreignKey: 'driverId', otherKey: 'customerId' });
Driver.hasMany(Restaurant, { as: 'restaurants', through: 'CostumerDriverTransaction', foreignKey: 'driverId', otherKey: 'restaurantId' });
Driver.hasMany(Order, { as: 'orders', through: 'CostumerDriverTransaction', foreignKey: 'driverId', otherKey: 'orderId' });

// Restaurants
Restaurant.hasMany(CostumerDriverTransaction, { foreignKey: 'restaurantId' });
Restaurant.hasMany(Driver, { as: 'drivers', through: 'CostumerDriverTransaction', foreignKey: 'restaurantId', otherKey: 'driverId' });
Restaurant.hasMany(User, { as: 'customers', through: 'CostumerDriverTransaction', foreignKey: 'restaurantId', otherKey: 'customerId' });
Restaurant.hasMany(Order, { as: 'orders', through: 'CostumerDriverTransaction', foreignKey: 'restaurantId', otherKey: 'orderId' });

// Orders
Order.hasMany(CostumerDriverTransaction, { foreignKey: 'orderId' });
Order.hasMany(Driver, { as: 'drivers', through: 'CostumerDriverTransaction', foreignKey: 'orderId', otherKey: 'driverId' });
Order.hasMany(User, { as: 'customers', through: 'CostumerDriverTransaction', foreignKey: 'orderId', otherKey: 'customerId' });
Order.hasMany(Restaurant, { as: 'restaurants', through: 'CostumerDriverTransaction', foreignKey: 'orderId', otherKey: 'restaurantId' });

This will create models with primary key and relationship columns of:

// User
{
  id: primary key,
  createdAt: create date,
  updatedAt: update date,
}

// Driver
{
  id: primary key,
  createdAt: create date,
  updatedAt: update date,
}

// Restaurant
{
  id: primary key,
  createdAt: create date,
  updatedAt: update date,
}

// Order
{
  id: primary key,
  createdAt: create date,
  updatedAt: update date,
}

// Transaction
{
  id: primary key,
  userId: relationship to User,
  driverId: relationship to Driver,
  restaurantId: relationship to Restaurant,
  orderId: relationship to Order,
  createdAt: create date,
  updatedAt: update date,
}

Answer 2

As error indicates, you need to set driverId to be unique, that is:

const CostumerDriverTransaction = sequelize.define('CostumerDriverTransaction', {
costumerId: {
  type:DataTypes.INTEGER,
},
driverId: {
  type:DataTypes.INTEGER,
  unique: true
},
restaurantId: {
  type:DataTypes.INTEGER,
},
orderId: DataTypes.INTEGER,
transactionId: {
  type:DataTypes.INTEGER,
  primaryKey:true,
},
},{});