Sequelize 6, delete cascade with M:N association-CodePudding

I'm trying to define some specific M:N association using sequelize v6.20.1 and i'm facing and issue...

I have 2 models, an Account model and a Group model. My rules about these models are the following:

An account can exists without any group
An account can have multiple groups
A group can exists with a least one account associated, so that mean a group cannot exists without an account associated
A group can be associated with multiple accounts

Here is the code definition of all models and association :

const Sequelize, { Model, DataTypes } = require('sequelize');

const sequelize = new Sequelize({
  dialect: 'sqlite',
  storage: `./database.sqlite`,
});

/* ----- Account model ----- */

class Account extends Model {
  // Some account's methods definitions...
}
Account.init({
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true,
  },
  name: DataTypes.STRING,
  username: DataTypes.STRING,
  email: DataTypes.STRING,
  password: DataTypes.STRING,
}, { sequelize });

/* ----- Group model ----- */

class Group extends Model {
  // Some group's methods definitions...
}
Group.init({
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true,
  },
  name: DataTypes.STRING,
}, { sequelize });

/* ----- AccountGroup model ----- */

class AccountGroup extends Model {
}
AccountGroup.init({
  AccountId: {
    type: DataTypes.INTEGER,
    references: {
      model: Account,
      key: 'id',
    },
    onDelete: 'RESTRICT',
  },
  GroupId: {
    type: DataTypes.INTEGER,
    references: {
      model: Group,
      key: 'id',
    },
    allowNull: false,
    onDelete: 'CASCADE',
  },
}, {
  sequelize,
  timestamps: false,
});

/* ----- Association definition ----- */

Account.belongsToMany(Group, { through: AccountGroup });
Group.belongsToMany(Account, { through: AccountGroup });

sequelize.authenticate();
sequelize.sync({ force: true };

(async () => {
  const group = new Group({ name: 'Group' });
  await group.save();

  const account = new Account({ name: 'Doe', username: 'John', email: '[email protected]', password: 'secret' });
  account.addGroup(group);
  await account.save();

  // some processing code...

  await account.destroy();
})();

After account.destroy() finished, the AccountGroup row is successfully deleted but not the Group. And i want to delete unreferenced groups.

What am i missing ?

CodePudding user response：

This is the way that the cascading deletes works. In your example, when the Account is deleted, rows in the AccountGroup table may now have an invalid value in their AccountId foreign key column. You are telling the database that when this situation occurs, delete the AccountGroup entirely. Similarly, if you delete a Group, this will cascade down and delete any AccountGroup with that Group as its GroupId.

No such issue arises for the Account or Group tables when an AccountGroup is deleted. They do not contain any foreign keys that have been made invalid.

To find the functionality that you are searching for, deleting any groups that no longer belong to an AccountGroup, you will likely want to put a separate query in your code, or you may be able to use a Hook to be executed after an Account is deleted.