CODEWITHSUNDEEP
sqlsqliteforeign-keyssequelize.jscascade
Brad
Brad

Reputation: 163438

ON DELETE CASCADE for multiple foreign keys with Sequelize

Suppose I have three models:

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?

Upvotes: 7

Views: 13140

Answers (2)

Sven 31415
Sven 31415

Reputation: 1835

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

Upvotes: 11

sabhari karthik
sabhari karthik

Reputation: 1371

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.

Upvotes: 3

Related Questions