How to change column length of existing table in SQL?

Question

I have one SQL table which will contain (TEXT/VARCHAR) columns of some length.

How can I change the existing column length without dropping or hardcode existing database table.

2020_02_13_065846_create_users_documents_table.php

public function up()
    {
        Schema::create('user_documents', function (Blueprint $table) {

            $table->increments('id');
            $table->string('upload_url', 200)->nullable();
            $table->string('user_name', 50)->nullable();
        });

Now I want to change the length of column user_name to 200 by creating a new table.

You etheir correct it in that file and refresh the migration (deletes everything) or you make a new migration and add the alteration statement in it (production solution). — N69S, Sep 30 '21 at 15:16
You can change columns with [`change()`](https://laravel.com/docs/8.x/migrations#modifying-columns) method. — Mohamed Gamal Eldin, Sep 30 '21 at 15:22
i never used that solution @MohamedGamalEldin because it needs the package `doctrine/dbal` when you can do it with a statement. — N69S, Sep 30 '21 at 15:22
I know that if small changes can use a statement but for big projects, I think `doctrine/dbal` is better. @N69S — Mohamed Gamal Eldin, Sep 30 '21 at 15:26

score 1 · Answer 1 · answered Sep 30 '21 at 15:21

Development solution

you correct it in that file and refresh your migration with php artisan migrate:fresh but you lose all the data in your database

Production solution

Make a new migration with the alter statement.

public function up()
{
    \DB::statement('ALTER TABLE user_documents ALTER COLUMN upload_url VARCHAR (200)');
}

score 1 · Accepted Answer · answered Sep 30 '21 at 15:23

you just have to modify you columns:

The change method allows you to modify the type and attributes of existing columns. For example, you may wish to increase the size of a string column

make a new Migration, set this statement:

Schema::table('users', function ($table) {
    $table->string('upload_url', 500)->change();
 $table->string('user_name', 500)->change();
});

How to change column length of existing table in SQL?

2 Answers2