How do you set a specific starting auto-increment value?

I received this question from a reader recently. He had a table where he wanted to start the auto-incrementing values at a specific number, and wanted to do it with normal Laravel migrations, not raw SQL statements.

The migrations provide a from() method that you can chain onto a column definition that will set the starting value for the auto-incrementing column.

Seemed easy enough, and it worked for me, but when I replied to the original developer, he said he got an error when he tried it.

What was going on? His table already had a different column set as primary key, but Laravel assumes that any auto-increment field must be the primary key:

// from MySqlGrammar
protected function modifyIncrement(Blueprint $blueprint, Fluent $column)
{
    if (in_array($column->type, $this->serials) && $column->autoIncrement) {
        return ' auto_increment primary key';
    }
}

So the error was due to trying to define more than one primary key on a table, which the database rejects.

The MySQL docs confirm that any auto-increment column must have some sort of index on it preventing duplicate values, although it doesn't have to be a primary key.

I can sympathize with why Laravel just makes it a primary key, as it's the most common use case, and it prevents InnoDB errors if you try to add it to a column that would allow duplicates.

Unfortunately, though, this means that we have to fall back to a raw DDL statement to define the column, and can't use the nice fluent interface in the Blueprint class.

Here to help,

Joel

P.S. Keep those questions coming. Hit reply and let me know if something has you stumped.