How can I have my composite primar key in a junction table using sequelize?

Question

I have a composite primary key in my table Foo, and a single primary key in Bar. When I make a junction using the belongsToMany syntax, only one primary key component shows up in the junction table (the first one that is beeing defined). I was expecting to see FooId, FooDate , BarId and state.

I'm using MariaDb 10.4.21 and sequelize v6.12.1 if that matters.

  const Foo = sequelize.define("Foo", {
    id: {
      type: DataTypes.INTEGER, primaryKey: true, unique: true, allowNull: false
    },
    date: {
      type: DataTypes.DATEONLY, primaryKey: true, unique: true, allowNull: false
    },
  });

  const FooBar = sequelize.define("FooBar", {
    state: {  
      type: DataTypes.INTEGER
    }
  });

  const Bar = sequelize.define("Bar", {
    id: {
      type: Sequelize.INTEGER, primaryKey: true, unique: true, allowNull: false
    }
  });


  Foo.belongsToMany(Bar, { through: FooBar});
  Bar.belongsToMany(Foo, { through: FooBar});

I know I could do a workaround by just using a single primary key, but I want to understand my mistake. Thank you in advance.

Answer 1

Sequelize does not support composite primary and foreign keys so you need to make id in Foo as the only unique key (ideally should be generated by DB) and so you will have a one foreign key column in FooBar per each table.