ON DELETE and ON UPDATE constraint

[amirAlamian]

Hello. I`m using sequelize typescript for a short time and I encountered a bug in foreign key constraint. when I want to set onDelete and onUpdate to RESTRICT mode, it stays on CASCADE. my code is:

  @BelongsTo(() => Action, {
    onDelete: 'RESTRICT',
    onUpdate: 'RESTRICT',
  })

what is the problem.

[roznik-navisys]

I have encountered the same problem, RESTRICT does not seem to work. Had to implement a custom hook as a workaround. Though a cool thing is it will also work to prevent soft deleting. It might need some refactoring to work in different projects.

type ModelWithNameOrId = Model & { name?: string; id?: any };

export const PreventSoftDeleteIfAssociationExists = (
    relationFactory: () => ModelCtor<ModelWithNameOrId>,
    pluralName?: string,
) => async (instance: Model): Promise<void> => {
    const relation = relationFactory();
    const relationHasName = !!relation.rawAttributes.name;
    const relations = await instance.$get(
        <any>(pluralName ?? relation.name + 's'),
        {
            attributes: [relationHasName ? 'name' : 'id'],
            raw: true,
        },
    );
    if (relations.length) {
        throw new BadRequestException(
            `Cannot delete a ${instance.constructor.name} with ${
                pluralName ?? relation.name + 's'
            } linked to it (${
                relationHasName
                    ? relations.map((c: ModelWithNameOrId) => c.name).join(', ')
                    : relations.map((c: ModelWithNameOrId) => c.id).join(', ')
            }).`,
        );
    }
};

@BeforeDestroy({})
static preventSoftDeleteIfAssociationExists = PreventSoftDeleteIfAssociationExists(
    () => Model,
);

[dever23b]

TL;DR

I think a much simpler workaround is to move the options from your associated column decorator into the @Column decorator on the "real" column of your table. If you define the reference options on the foreign key attribute, it works; if you define them using the association decorator, they don't make it all the way to Sequelize when it evaluates the association. See examples below.

---

Running into this issue too, after converting a previously-working JS project to TS and adopting sequelize-typescript.

I think it's a bug with this project. When I first started, I just dumped all of my column options into the @Column decorators. This worked to an extent, but I ran into an unrelated bug/issue with indexes: adding unique: true to @Column seems to reliably result in Sequelize adding a new (additional/redundant) unique constraint to the table on every sync until the DB fails the table for too many indexes. It wasn't until I ran into that, that I started experimenting with the various other decorators that this project exposes (namely, at the time, @Index).

I didn't run into this issue until today, when I attempted to move my onDelete and onUpdate options from the source @Column into the @HasOne/@BelongsTo/etc decorators on the target columns. As soon as I did this, the options started to get (intermittently, somehow) lost. On one of my models, I have two references to the same target model (different aliases); other than the foreign key, they are defined identically. Oddly, one of the columns gets the correct foreign key options; the other one doesn't. I don't quite understand that but, regardless, the obvious workaround/solution for me was to move my association options back into the source @Column.

I did some debugging and I believe it has to do with the options being set on a column that doesn't actually exist in the model's table (ie: in my example, bar is effectively a virtual property of the class that doesn't have a corresponding column in its own table). While I was stepping through Sequelize's sync process, it seemed like the options being defined on the associated property meant that they weren't available when Sequelize looks at the "real" column (that exists in that model's table) and sets up the foreign constraints.

Perhaps the developers here can tweak the association decorators to tweak the "real" column's properties, if that's possible with decorators. Else, I think that the Type Declarations & documentation are misleading because both suggest that developers do exactly what we have done, and it doesn't seem to work.

---

This way works:

  @ForeignKey(() => Bar)
  @Column({
    type: DataType.SMALLINT.UNSIGNED,
    allowNull: false,
    onDelete: 'RESTRICT',
    onUpdate: 'CASCADE'
  })
  declare barId: number;

  @BelongsTo(() => Bar)
  declare bar: Bar;

This way fails:

  @ForeignKey(() => Bar)
  @AllowNull(false)
  @Column({ type: DataType.SMALLINT.UNSIGNED })
  declare barId: number;

  @BelongsTo(() => Bar, {
    onDelete: "RESTRICT",
    onUpdate: "CASCADE",
  })
  declare bar: Bar;

[kevinlbatchelor]

Thanks, and for those of us still avoiding TypeScript like the plague, something like this:

export const locationSchema = {
    name: { type: db.STRING, allowNull: false }
};

export const partSchema = {
    defaultLocationId:{
        type: db.INTEGER,
        references: {
            model: Location,
            key: 'id',
            onDelete: 'RESTRICT',
            onUpdate: 'CASCADE'
        }
    }
};