It's All Writing.

Writing makes you happy.

How to delete child records ( cascade deleting ) at the time of deleting parent record.

I wrote The application in which have the structure in which User ( parent record ) has many Comments ( children records ).

And I tried to implement cascade deleting ( at the time of deleting User (parent record ), I also want to delete Comments ( child records ) ), but It does not work well.

Environment

  • Node.js: v10.5.0
  • Express.js: 4.16.0
  • Sequelize: 4.0.0
  • MySQL: 5.6.40

The way which succeeded (define cascade in addConstraint method in migration.)

Now, I’ll Set foreign key which defined cascade in addConstraint method in migration.

  1. create migration like below.
$ sequelize migration:create --name AddConstraintToComment
'use strict';

// migrations/20180728081647-AddConstraintToComment.js

module.exports = {
  up: (queryInterface, Sequelize) => {
    /*
      Add altering commands here.
      Return a promise to correctly handle asynchronicity.

      Example:
      return queryInterface.createTable('users', { id: Sequelize.INTEGER });
    */
    return Promise.all([
      queryInterface.addConstraint('Comments', ['user_id'], {
        type: 'foreign key',
        name: 'fk_comments_users',
        references: {
          table: 'Users',
          field: 'id'
        },
        onDelete: 'cascade',
        onUpdate: 'cascade'
      })
    ]);
  },

  down: (queryInterface, Sequelize) => {
    /*
      Add reverting commands here.
      Return a promise to correctly handle asynchronicity.

      Example:
      return queryInterface.dropTable('users');
    */
    return queryInterface.removeConstraint('Comments', 'fk_comments_users');
  }
};

the option ( onDelete : ‘cascade’ ) in addConstraint is important.

  1. execute migration.
$ sequelize db:migrate

When execution succeeds, you can confirm the following table definition ( in MySQL ).

mysql> show create table Comments;
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                   |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Comments | CREATE TABLE `Comments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `content` text,
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL,
  `user_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_comments_users` (`user_id`),
  CONSTRAINT `fk_comments_users` FOREIGN KEY (`user_id`) REFERENCES `Users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8 |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

When User is deleted by CASCADE constraint, Comments also become to be deleted.

The way which failed

When giving a onDelete option and a hooks option to the hasMany method as follows in model according to a manuala tutorial and Stack Overflow, cascade deleting doesn’t work.

Users.hasMany(Comments, { onDelete: 'cascade', hooks: true });

The more concrete description is as follows.

'use strict';

// models/user.js

module.exports = (sequelize, DataTypes) => {
  var User = sequelize.define('User', {
    name: DataTypes.STRING,
    age: DataTypes.INTEGER
  }, {});
  User.associate = function(models) {
    // associations can be defined here
    User.hasMany(models.Comment, {
      foreignKey: 'user_id',
      onDelete: 'cascade',
      hooks: true
    });
  };
  return User;
}

If you have some knowledge, please tell me.
Thank you.

Reference