[BUG] ForeignKey and non-ForeignKeys as unique generates extra constraint

[cskiwi]

Versions

• sequelize: v5
• sequelize-typescript: 1.1.0
• typescript: 3.8.3

UPDATE: Just updated to latest version (v6) issue still persists

• sequelize: ^6.5.0
• sequelize-typescript: ^2.0.0
• typescript: 3.8.3

I'm submitting a ...

• [x] bug report
• [ ] feature request

Actual behavior:

My many to many releationship is unique for a combination of 3 values (the 2 external id's and a date). However any configuration creates a unique constraint on the 2 ForeignKeys. So this doesn't allow inserts even if that 3th value is different

Error:

ERROR:  duplicate key value violates unique constraint "ClubMemberships_playerId_clubId_key"
DETAIL:  Key ("playerId", "clubId")=(1, 1) already exists.
SQL state: 23505

Expected behavior:

if you have @PrimaryKey on ForeignKeys and non-Foreignkeys, no creation of a constraint on those foreign keys. or a way to disable it.

I tried numerous combinations of @PrimaryKey, @Unique but non seem to give any correct result

Steps to reproduce:

Use the code below and .sync({force: true})

Related code:

@Table
export class ClubMembership extends Model<ClubMembership> {
  constructor(values?: Partial<ClubMembership>, options?: BuildOptions) {
    super(values, options);
  }

  @ForeignKey(() => Player)
  @Unique('unique_constraint')
  @Column
  playerId: string;

  @ForeignKey(() => Club)
  @Unique('unique_constraint')
  @Column
  clubId: string;

  @Unique('unique_constraint')
  @Column
  start: Date;

}

this generates:

If I would have the option to disable the automatic creation of the unique constraint I would be happy :)

note: I know I can remove that constraint via migrations, but this doesnt work for my tests

[cskiwi]

For other people coming across this issue, I've found a hacky way to resolve this:

By placing the following exact key on the start column : @Unique('ClubMemberships_clubId_playerId_unique'). it combined the 2 unique instances and gave the wanted result.

If you are having issues finding the unique string:

• on node_modules/sequelize/lib/dialects/abstract/query-interface.js
• Go to line: 205 (ctrl + G o on most editors)
• insert console.log(tableName, options.uniqueKey) below

  if (model) {
  options.uniqueKeys = options.uniqueKeys || model.uniqueKeys;
  }

this will spit out each unique key when doing a sync