Sequelize-typescript: Three foreign keys referencing same table

[devuxer]

My Transit model has three foreign keys referencing the Nation model.

When I sync to a SQLite database, however, only the first foreign key gets created.

Am I doing something wrong?

Model

import { AllowNull, BelongsTo, Column, DataType, ForeignKey, Model, PrimaryKey, Table } from "sequelize-typescript";
import Nation from "./Nation";
import Plan from "./Plan";

@Table({ tableName: "Transits" })
export default class Transit extends Model<Transit> {
    @AllowNull(false)
    @PrimaryKey
    @Column(DataType.TEXT)
    public id: string;

    @AllowNull(false)
    @ForeignKey(() => Plan)
    @Column(DataType.TEXT)
    public planId: string;

    @BelongsTo(() => Plan, { onDelete: "cascade" })
    public plan: Plan;

    @AllowNull(false)
    @ForeignKey(() => Nation)
    @Column(DataType.TEXT)
    public originNationId: string;

    @BelongsTo(() => Nation, { onDelete: "no action" })
    public originNation: Nation;

    @AllowNull(false)
    @ForeignKey(() => Nation)
    @Column(DataType.TEXT)
    public destinationNationId: string;

    @BelongsTo(() => Nation, { onDelete: "no action" })
    public destinationNation: Nation;

    @AllowNull(false)
    @Column(DataType.TEXT)
    public vesselName: string;

    @AllowNull(false)
    @ForeignKey(() => Nation)
    @Column(DataType.TEXT)
    public vesselFlagNationId: string;

    @BelongsTo(() => Nation, { onDelete: "no action" })
    public vesselFlagNation: Nation;

    @AllowNull(false)
    @Column(DataType.TEXT)
    public beginDate: string;

    @AllowNull(false)
    @Column(DataType.TEXT)
    public endDate: string;

    @AllowNull(true)
    @Column(DataType.TEXT)
    public details: string;

    @AllowNull(false)
    @Column(DataType.INTEGER)
    public isFirm: boolean;
}

Expected Result (3 references to "Nations")

CREATE TABLE `Transits` (
    `id`    TEXT NOT NULL,
    `planId`    TEXT NOT NULL,
    `originNationId`    TEXT NOT NULL,
    `destinationNationId`   TEXT NOT NULL,
    `vesselName`    TEXT NOT NULL,
    `vesselFlagNationId`    TEXT NOT NULL,
    `beginDate` TEXT NOT NULL,
    `endDate`   TEXT NOT NULL,
    `details`   TEXT,
    `isFirm`    INTEGER NOT NULL,
    FOREIGN KEY(`destinationNationId`) REFERENCES `Nations`(`id`) ON DELETE NO ACTION ON UPDATE CASCADE,
    FOREIGN KEY(`vesselFlagNationId`) REFERENCES `Nations`(`id`) ON DELETE NO ACTION ON UPDATE CASCADE,
    PRIMARY KEY(`id`),
    FOREIGN KEY(`planId`) REFERENCES `Plans`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(`originNationId`) REFERENCES `Nations`(`id`) ON DELETE NO ACTION ON UPDATE CASCADE
);

Actual Result (1 reference to "Nations")

CREATE TABLE `Transits` (
    `id`    TEXT NOT NULL,
    `planId`    TEXT NOT NULL,
    `originNationId`    TEXT NOT NULL,
    `destinationNationId`   TEXT NOT NULL,
    `vesselName`    TEXT NOT NULL,
    `vesselFlagNationId`    TEXT NOT NULL,
    `beginDate` TEXT NOT NULL,
    `endDate`   TEXT NOT NULL,
    `details`   TEXT,
    `isFirm`    INTEGER NOT NULL,
    FOREIGN KEY(`planId`) REFERENCES `Plans`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY(`id`),
    FOREIGN KEY(`originNationId`) REFERENCES `Nations`(`id`) ON DELETE NO ACTION ON UPDATE CASCADE
);

[RobinBuschmann]

Hey @devuxer, sry for the late response. You need to define which foreign key should be used for which relation. So the relations should look like:

@BelongsTo(() => Nation, { onDelete: "no action", foreignKey: "originNationId"})
originNation: Nation;

@BelongsTo(() => Nation, { onDelete: "no action", foreignKey: "destinationNationId" })
destinationNation: Nation;

@BelongsTo(() => Nation, { onDelete: "no action", foreignKey: "vesselFlagNationId" })
vesselFlagNation: Nation;

Probably an error message in case of multiple foreignKey that targets the same Model would be helpful 🤔

[devuxer]

Hey @RobinBuschmann,

Thank you for the answer and this very handy library 👍

An error message would definitely help. Perhaps adding this multiple-foreign-key use case to the documentation as well.

One thing that confuses me is why I need @BelongsTo for @ForeignKey to work. I don't actually need the navigation properties (like originNation) in this particular case. I just added them because when I didn't have them, no foreign keys would get added to the database at all.

[RobinBuschmann]

This is because @ForeignKey is more a helper for the @BelongsTo, @BelongsToMany and so on annotations then an independent decorator. In your case, since you need to define the foreign keys in the @BelongsTo annotation, you can omit the foreign key annotated properties completely.

Ones might consider extending the functionalities of the @ForeignKey decorator...

[devuxer]

Ahh OK, makes sense.

So if I want the foreign key to be nullable, I would put@AllowNull on the @BelongsTo property instead of the foreign key property?

[Arloh1]

This is because @ForeignKey is more a helper for the @BelongsTo, @BelongsToMany and so on annotations then an independent decorator. In your case, since you need to define the foreign keys in the @BelongsTo annotation, you can omit the foreign key annotated properties completely.

Ones might consider extending the functionalities of the @ForeignKey decorator...

[RobinBuschmann]

@devuxer I've completely overseen your last question - sry for that. You need to set allowNull in the options of the BelongsTo annotation like so:

@BelongsTo(() => Nation, {
    foreignKey: {
      allowNull: true
    }
  })