How to create a unique index containing multiple fields where one is a foreign key

Question

I am trying to create an index with multiple fields, one of the field is a foriegn key to another table. However i get the following error:

Error: Index "player_id_UNIQUE" contains column that is missing in the entity (Earning): player_id

Given that player_id is a foriegn key that im joining how do i handle this

import { Column, Entity, Index, JoinColumn, ManyToOne, PrimaryColumn } from "typeorm";
import { PersonPlayer } from "./PersonPlayer";
import { Team } from "./Team";

    @Entity()
    @Index("player_id_UNIQUE", ["player_id", "period", "year"], { unique: true })
    export class Earning {

        @PrimaryColumn({length: 36})
        id: string;

        @Column({nullable: true})
        year: number;

        @Column({type: 'decimal', nullable: true})
        amount: number;

        @Column({nullable: true, length: 45})
        period: string;

        @ManyToOne(() => Team, {nullable: true})
        @JoinColumn({name: 'team_id'})
        team: Team;


        @ManyToOne(() => PersonPlayer, {nullable: true})
        @JoinColumn({name: 'player_id'})
        player: PersonPlayer;

        @Column({nullable: true, length: 45})
        dtype: string;

    }

When i generate this entity and create the sql table (without the index) i see player_id as one of the columns. But it appears that typeorm is not able to recognize right now with the index that player_id exists in the entity through the joincolumn relationship.

Answer 1

This is completely undocumented so it took some playing around until I stumbled upon the correct syntax. You can actually use sub-properties in index definitions:

@Index("player_id_UNIQUE", ["player.id", "period", "year"], { unique: true })

That way, player.id is automatically mapped to player_id in the resulting SQL:

CREATE UNIQUE INDEX "player_id_UNIQUE" ON "user_earning" ("player_id", "period", "year")