Recently i’ve ran into a problem which the id field of collection type didn’t reset after all entry got deleted. for example i have 2 articles have 2 id 1 ,2 respectively, but after i delete it and create a new article the id still remain the same ( it starts from 3 instead of resetting back to 1 ). How can I solve this such a problem, is that a problem of database?
ID field is auto increment, this is how sql databases work. Just ignore it.
ALTER TABLE yourdb.yourtable AUTO_INCREMENT=1;
Sorry for asking this dumb question! Can you give me a hint on how to execute raw db query inside strapi?
strapi.connections.default.raw('ALTER TABLE yourdb.yourtable AUTO_INCREMENT=1;')
Thanks for your help! It helped me a lot
Dear sunny! I followed your instruction but I got following error?
this code is written inside bootstrap.js
(That SQL statement was for MySQL/MariaDB and might work on PG)
UPDATE SQLITE_SEQUENCE SET SEQ=0 WHERE NAME='table_name'; for SQLite, gathered from: sql - SQLite Reset Primary Key Field - Stack Overflow
Oh, you didn’t mention that you use SQLite. The first thing I thought about is that you use MySQL. This is why we should mention Strapi version, Database and other details in the first post to avoid confusions.
I am actually looking for a template plugin for Discourse right now exactly for that reason
I really appreciate all the effort that you guy put on my post ! But i want to ask one more question, I want to that query to be invoked whenever one entry get deleted, I first started at boostrapt.js file but it only invoke once. Can I ask what file should handle delete action?
You can invoke it in
afterDelete lifecycle. Make a findOne request sorted by ID descendent and reset AUTO_INCREMENT to that
id + 1.
In case if there are no results returned then reset it to 1.
My bad for not listing all the details, thanks for your and DMehaffy help!
No problem @Timo_Raj
Thanks for your guys helping, without those I could not solve that problem !
This post helped me for PostgreSQL: postgresql - How to reset sequence in postgres and fill id column with new data? - Stack Overflow
Reset the sequence:
ALTER SEQUENCE sequence_name RESTART;
Then update the table’s ID column:
UPDATE table_name SET id = DEFAULT;