CODEWITHSUNDEEP
phpmysqllaravel-5
user3871
user3871

Reputation: 12664

Using Auto incrementing ID as primary key

I'm making a basic blog. The user can add a post, filling in title, body, date. When submitted, the primary id field will obviously auto increment.

I'm using Laravel 5 seeding to populate the database with dummy data. The issue is, when I need to reseed data, I delete the prior rows, DB::table('articles')->delete(); then reseed.

The problem is, the autoincremented id continues from the last id. So If I'm seeding 10 rows on initial migrate, I get 1 thru 10. No problem. If I reseed, I get id 11-21.

This is problematic because I'm accessing the articles by ID, http://localhost/article/1 (Route::get('article/{id}'...) but there obviously is no longer an ID of 1. The first article ID is now 11.

To get around this, I created a second id column called article_id: $table->primary('article_id')->unsigned(); as primary key so Laravel would query that.

I'm sure this is a common issue- I'm wondering how to get around this.

Upvotes: 1

Views: 349

Answers (3)

ceejayoz
ceejayoz

Reputation: 180177

DB::table('articles')->truncate();

will delete the entire contents of articles and reset its increment ID.

For obvious reasons, make very certain this can't be called in production. :-)

Upvotes: 1

Code Magician
Code Magician

Reputation: 24032

Although these values are being created automatically, MySQL does allow you to explicitly set values for them. In other words, if you're migrating a table you don't need to worry about the keys being automatically created in the correct order with the appropriate gaps, you can simply write the values in your insert

CREATE TABLE t_autoinc1
(
   c1 INT AUTO_INCREMENT PRIMARY KEY
 );

 -- 0 or NULL means that next ID will be generated by MySQL automatically

 -- inserts 1
 INSERT INTO t_autoinc1 VALUES (0)
 -- inserts 2
 INSERT INTO t_autoinc1 VALUES (NULL)

 -- inserts 100, not 3
 INSERT INTO t_autoinc1 VALUES (100)

 -- inserts 101
 INSERT INTO t_autoinc1 VALUES (NULL)

See this answer in the MySQL forums http://forums.mysql.com/read.php?60,499281,503603#msg-503603

Upvotes: 0

imran qasim
imran qasim

Reputation: 1050

in your PHP when you want to reset auto increment id than just execute this command as you execute select or insert or any other sql command in PHP

   ALTER TABLE tablename AUTO_INCREMENT = 1

Regards

Upvotes: 1

Related Questions