ON DELETE CASCADE for multiple foreign keys with Sequelize

Question

Suppose I have three models:

• Task: A thing that needs done, like "take out the recycling". Can be done many times.
• TaskList: An object that represents a list of tasks, and has its own metadata.
• TaskListEntry: An association between Task and TaskList, that may have data such as the priority or who is assigned to it.

I have my associations set up like this:

Task.hasMany(TaskListEntry, {onDelete: 'cascade', hooks: true}));

TaskList.hasMany(TaskListEntry, {onDelete: 'cascade', hooks: true});

TaskListEntry.belongsTo(TaskList);
TaskListEntry.belongsTo(Task);

This works fine, except for deleting. When I delete a Task, any associated TaskListEntries are deleted as expected. However, when I delete a TaskList, its associated TaskListEntries simply have their foreign key for the TaskList set to null.

It seems that Sequelize is generating the following table:

CREATE TABLE `TaskListEntries`(
  `id` UUID PRIMARY KEY, 
  /* some other fields here */
  `createdAt` DATETIME NOT NULL, 
  `updatedAt` DATETIME NOT NULL, 
  `TaskId` UUID REFERENCES `Tasks`(`id`) ON DELETE CASCADE ON UPDATE CASCADE, 
  `TaskListId` UUID REFERENCES `TaskLists`(`id`) ON DELETE SET NULL ON UPDATE CASCADE);

Despite the associations being configured the same, the foreign keys for Tasks and TaskLists have different DELETE behavior. If I remove one of the associations, the other works just fine.

Therefore, I think the issue is multiple foreign keys with ON DELETE CASCADE, at least as far as Sequelize seeis it.

Any thoughts on how to correct this?

Answer 1

I had to set the allowNull:false for the foreignKey for getting 'CASCADE' on deletions to work. So it should be something like this in your case:

TaskListEntry.belongsTo(TaskList, {
  onDelete: 'cascade', 
  foreignKey: { allowNull: false }    //   <-------------
  hooks: true
});

---

Given the case, that your models are in general similar to this structure from http://docs.sequelizejs.com/manual/associations.html#belongs-to-many-associations:

class User extends Model {}
User.init({}, { sequelize, modelName: 'user' })

class Project extends Model {}
Project.init({}, { sequelize, modelName: 'project' })

class UserProjects extends Model {}
UserProjects.init({
  status: DataTypes.STRING
}, { sequelize, modelName: 'userProjects' })

User.belongsToMany(Project, { through: UserProjects })
Project.belongsToMany(User, { through: UserProjects })

Answer 2

can you try

TaskListEntry.belongsTo(TaskList);
TaskListEntry.belongsTo(Task);

instead of

TaskListEntry.belongsToMany(TaskList);
TaskListEntry.belongsToMany(Task); 

Because, from my understanding of this problem, a single TaskListEntry record can only belong to a single Task and a single TaskList.

Or Are you trying to establish a Many-to-Many relationship here? In that case, I don't think this is the ideal way of implementation.