laravel schema builder, find columnType autoincrement

Question

I'm working to update an existing database to use autoincremented primary keys. This DB currently has crazy named PK fields with custom values. I need to check each table first to see if it HAS an autoinc field first, then I want to drop it and replace with 'id' field.

I want to do this as a migration, here's what I have so far but I can't seem to identify if the first col is autoincrementing already so I can drop the existing PK and replace. I need to replace the hasColumn with something like a firstColumn then getColumnType...

    foreach ($tableNames as $name)
                if (!Schema::hasColumn($name, 'id')) {
                Schema::table($name, function ($table) {
                    $table->dropPrimary();
                    $table->increments('id')->first();
                });
            }
        }

@AbrarShariarGalib yes i'm on that track now, apparently laravel needs Doctrine/DBAL included. I guess I'd need to get a column list then feed it that first col to check if its autoinc — caro, Dec 20 '16 at 15:03
actually @AbrarShariarGalib it just says 'integer' and doesn't tell me if it is an autoincrementing int — caro, Dec 20 '16 at 15:08
Right now I can think of using `DB::raw()` where the sql query will have something like - `EXTRA like '%auto_increment%'` — Abrar, Dec 20 '16 at 15:23

Abrar · Accepted Answer · 2016-12-20T16:09:08.907

In order to solve the problem I ran the following code from a controller. Notice here that I have only two fields for the demo (id,name)

$result = DB::select("SHOW COLUMNS FROM table_name"); dd($result);

Now the output after dd() will be somewhat like this:

0 => {#162 ▼
    +"Field": "id"
    +"Type": "int(11)"
    +"Null": "NO"
    +"Key": "PRI"
    +"Default": null
    +"Extra": "auto_increment"
  }

1 => {#164 ▼
    +"Field": "name"
    +"Type": "varchar(255)"
    +"Null": "YES"
    +"Key": ""
    +"Default": null
    +"Extra": ""
  }

Now you can easily extract the "Extra" : "auto_increment" , like this:

$result = DB::select("SHOW COLUMNS FROM product");
foreach ($result as $key => $value) {
            if($value->Extra == 'auto_increment'){
                //do something
            };

I will either do this or select from the information schema, something like "select COLUMN_NAME from information_schema.`COLUMNS` where TABLE_SCHEMA = 'table_schema' and TABLE_NAME = 'table_name' and extra = 'auto_increment'". I had hoped I could do it with Laravel but this is good enough. Thanks for the input! — caro, Dec 20 '16 at 17:00

laravel schema builder, find columnType autoincrement

1 Answers1