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.
- 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.
- 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 manual, a 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.