Search code examples
sqlsqliteforeign-keyssequelize.jscascade

ON DELETE CASCADE for multiple foreign keys with Sequelize

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?

Solution

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