Add sql table column before or after specific other column - by migrations in Laravel 4.1

Table 'users':

|id|name|address|post_code|deleted_at|created_at|

and I want add column 'phone_nr' somewhere between 'id' and 'deleted_at'

Is it possible by migrations in Laravel 4.1?

Solution 1:

Yes. Create a new migration using php artisan migrate:make update_users_table.

Then use the table command as follows (if you're using MySQL!):

Schema::table('users', function($table)
{
    $table->string('phone_nr')->after('id');
});

Once you've finished your migration, save it and run it using php artisan migrate and your table will be updated.

Documentation: https://laravel.com/docs/4.2/migrations#column-modifiers

Solution 2:

The answer by James is still correct. However, Laravel 5 has slightly changed the make migration syntax:

php artisan make:migration add_google_auth_to_users_table --table=users

(I am aware this question is tagged Laravel 4, however it ranks quite high for the question ;) )

Solution 3:

For anyone else wondering about @rahulsingh's query about adding multiple columns back to back after a single specific column:

$table->integer('col1')->after('ref_col');
$table->integer('col2')->after('what');

You can do this just by writing the new fields in reverse order, for example:

$table->integer('col4')->after('ref_col');
$table->integer('col3')->after('ref_col');
$table->integer('col2')->after('ref_col');
$table->integer('col1')->after('ref_col');

When you run this migration you'll find that your new fields col, col2, ... are in your intended order after the reference field ref_col.

Unfortunately there is no before() function for adding fields before an existing field. (If that existed we could keep the above statements in their real order.)

You cannot do chaining with fields that do not yet exist, for example:

$table->integer('col1')->after('ref_col');
$table->integer('col2')->after('col1');
$table->integer('col3')->after('col2');
$table->integer('col4')->after('col3');

This is because the migration blueprint is compiled to a single query thus executed as one, so when it comes to adding col2 your database engine will complain that col1 doesn't exist.