6

In Sequelize, I am using this function model.destory({ truncate: true }), it delete all data in table. But the issue is that it does not reset the primary key sequence in table which should be set to Zero. I am using Mysql. Some said that Mysql automatically reset the primary key sequence, but it is not happening in my case.

Here is my code: 

db.Booking.destroy({ truncate: { cascade: false } })
    .then(() => {
      res.json({ status: true });
    }, (err) => {
      console.log('truncate: ', err);
      res.json(err);
    });
coder
  • 906
  • 1
  • 12
  • 19

2 Answers2

17

You're not using the correct syntax:

db.Booking.destroy({ truncate: { cascade: false } })

That should be:

db.Booking.destroy({ truncate : true, cascade: false })

See the documentation.

robertklep
  • 198,204
  • 35
  • 394
  • 381
  • 2
    It does not work for me. I'm not using the set to null approach which is the default – Marc Aug 28 '17 at 14:55
  • @Marc might be worthwhile to create a new question for your specific issue. – robertklep Aug 28 '17 at 14:58
  • Note that this does not work for sqlite3 "5.0.2" on sequelize 6.5.1. `truncate: true` does not change the SQLite querry since it has no `TRUNCATE` only an automatic optimization, so it just runs `DELETE FROM`, which does not reset the index. No one has managed so far without raw SQL: https://stackoverflow.com/questions/55966627/how-to-reset-autoincrement-primary-key-with-sequelize an issue was created at: https://github.com/sequelize/sequelize/issues/13286 – Ciro Santilli OurBigBook.com Jun 17 '21 at 10:39
0

if you are, in any case, using a custom foreign key, use this instead

db.Booking.truncate({cascade: true, restartIdentity:true})

as destroy would not work at will with custom foreign key

this one is nested pretty deep in the documentation

See here

diazlp
  • 552
  • 5
  • 9