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?

Please in code questions give a [mcve]--cut & paste & runnable code; example input with desired & actual output (including verbatim error messages); clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) — philipxy, May 25 '19 at 06:40

score 10 · Accepted Answer · answered May 27 '19 at 14:23

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

Thanks, the "through" example is what I needed. I didn't realize I was able to add additional properties there, and query directly. Thanks for the help! — Brad, May 30 '19 at 04:45

score 3 · Answer 2 · answered May 23 '19 at 09:06

3

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.

answered May 23 '19 at 09:06

sabhari karthik

1,361
6
17

Ah, thank you! I am very sorry... I *am* using `.belongsTo` for both. I accidentally posted some experimentation in my question instead of the real code. Sorry about that, thank you for your reply in correcting me. I have corrected the code in my question. Yes, a single TaskListEntry is associated with only one Task and one TaskList. A Task can have multiple TaskListEntries associated with it, as can a TaskList. – Brad May 23 '19 at 15:26
Correct me if I am wrong. Do you need both belongsTo and hasMany relationship? Ideally you can remove the belongsTo associations on the last two lines. I feel it is worth giving a shot. – sabhari karthik May 23 '19 at 16:13

ON DELETE CASCADE for multiple foreign keys with Sequelize

2 Answers2