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
    }
  })

[EgoBrainProgrammer]

Hello @RobinBuschmann Thank you a lot for answers about several FK referencing same table. But can you explain how to implement model association in this case? (getters and setters methods)

@BelongsTo(() => User, { foreignKey: { 
    name: "createUserId", 
    allowNull: true
} })
createUser: User;

@BelongsTo(() => User, { foreignKey: {
    name: "updateUserId",
    allowNull: true
 } })
updateUser: User;

public getCreateUser!: BelongsToGetAssociationMixin<User>;
public getUpdateUser!: BelongsToGetAssociationMixin<User>; //--What field will be used?--

[sa698]

how to defin hasmany at National table?