1

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.

  • This causes problems now because on article insert, I need some way to auto increment the ID.
  • Having a second id column seems unnecessary and bad way to make a schema

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

user3871
  • 12,432
  • 33
  • 128
  • 268
  • One way would be resetting the auto increment id: http://stackoverflow.com/questions/8923114/how-to-reset-auto-increment-in-mysql – Honza Haering May 05 '15 at 15:29
  • 1
    I cannot see why this is a problem. But TRUNCATE will reset the AI. – Strawberry May 05 '15 at 15:31
  • @Strawberry `http://localhost/article/1`, if there is no article with `id` of 1 in DB, then I get a `ModelNotFound` exception. – user3871 May 05 '15 at 15:32
  • @Growler As you should. Why does it matter? Users don't navigate your site by randomly picking item IDs. – ceejayoz May 05 '15 at 15:32
  • You return an array. Just select the first item in the array. – Strawberry May 05 '15 at 15:32
  • @ceejayoz what If I am testing the response of a given article. It's easier to do `article/1` everytime and expect article 1 then `article/310` as article 1. – user3871 May 05 '15 at 15:35
  • 1
    If it's just for testing, `DB::table('articles')->truncate();` will blank it out and reset the ID. Just make sure it can't be called in production. – ceejayoz May 05 '15 at 15:38
  • @ceejayoz exactly what I was looking for. Post as an answer and I'll accept :) – user3871 May 05 '15 at 15:39

3 Answers3

1

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

imran qasim
  • 1,050
  • 13
  • 18
1
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. :-)

ceejayoz
  • 176,543
  • 40
  • 303
  • 368
0

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

Code Magician
  • 23,217
  • 7
  • 60
  • 77