Home » Laravel » How to Rename Table in Laravel

How to Rename Table in Laravel

Laravel has a migration mechanism for creating and changing database tables. If you want to create a new table, just create a migration and call the Schema::create() method. When you want to add or remove columns or indexes just call the Schema::table() method.

But sometimes you may want to rename table. For example, you have to rename Eloquent model and want to rename the table too. Here things become more complicated. In this article I will explain how to rename table in Laravel.

How to Rename a Table in Laravel

So, what is the problem to rename table? There is the Schema::rename() method that can to do it. But any table in the database has a few entities that uses the table name. It are indexes, foreign keys, sequences etc. When you rename table they wouldn’t become broken. They will continue working, but they will have incorrect names. You have two options here:

  • Drop foreign keys, indexes and other stuff before table renaming and create another ones after it.
  • Rename them after the table is renamed.

The commands for the second ways may be different for each database engine. Let’s have a look how to rename table in Laravel in MySQL and PostgreSQL.

Imagine, that you have the “articles” table, that has a primary key and column author_id that references to id column in the “authors” table. You want to rename articles to posts. First of all, find out what additional entities do you have. There is two ways to do it. You can view all previous migrations and find all required information in them. For example. Let’s look at the migration that creates the “articles” table:

Schema::create("articles", function (Blueprint $table) { $table->id(); $table->string("title"); $table->string("content"); $table ->foreignId("author_id") ->references("id") ->on("authors"); $table->string("slug")->unique(); $table->timestamps(); });

As you can see here we have a foreign key and unique index. But if the table was changed a lot of times it may be pretty hard.

1. MySQL

Also you can open DataGrip, DBeaver or another program for database management and look at indexes section:

Here, you can also see a foreign key and a unique index. Now, you are ready for table renaming. Create a migration file:

php artisan make:migration RenameCommentsTable

This command will produce an empty migration file. After this, remove indexes, rename table, and create new indexes. If indexes have a default names, you can just pass a column name into removing methods. For example::

Schema::table("articles", function (Blueprint $table) { $table->dropForeign(["author_id"]); $table->dropUnique(["slug"]); }); Schema::rename("articles", "posts"); Schema::table("posts", function (Blueprint $table) { $table ->foreign("author_id") ->references("id") ->on("authors"); $table->unique(["slug"]); });

Note, that you shouldn’t use the dropConstrainedForeignId() method, because it deletes the index with the column and you will loose data in this case. Also you can’t create index using the foreignId() method because it creates index with a column. In our case the column already exists.

When indexes have non-standard names, you should delete them by name. Find their names using DBeaver or DataGrip and pass them into remove methods instead of columns as a string.

Schema::table("articles", function (Blueprint $table) { $table->dropForeign("articles_author_id_foreign"); $table->dropUnique("articles_slug_unique"); }); Schema::rename("articles", "posts"); Schema::table("posts", function (Blueprint $table) { $table ->foreign("author_id") ->references("id") ->on("authors"); $table->unique(["slug"]); });

It will work in the same way. It would also be nice to implement the ability to return the table to its original state in the down() method. If you don’t do this, any developers will be unable to revert their migrations that were executed before yours. And as additional benefit, you can run migrate/migrate:rollback multiple times to ensure that everything works fine. Just rename the posts table into articles:

public function down() { Schema::table("posts", function (Blueprint $table) { $table->dropForeign(["author_id"]); $table->dropUnique(["slug"]); }); Schema::rename("posts", "articles"); Schema::table("articles", function (Blueprint $table) { $table ->foreign("author_id") ->references("id") ->on("authors"); $table->unique(["slug"]); }); }

Run the following commands and ensure that nothing crashes, and indexes have correct names:

php artisan migrate php artisan migrate:rollback --step=1 php artisan migrate

2. PostgreSQL

The code above is actual for both database engines, MySQL and PostgreSQL. But if you are using PostgreSQL you should do few more things. PostgreSQL has several differences. It stores the table name in the primary key name, so you should rename it too. If you use auto incrementing value, the table will have a sequence used to get the next value. The sequence is an additional entity that you should rename. Let’s have a look at the PostgreSQL version of this table using DBeaver. Open Tables -> articles -> Constraints and you will see a primary key and unique constraint. In the Foreign Keys section you can find a foreign key to authors table:

Then open the Sequences section in the database root and you will see the article_id_seq sequence:

You can rename primary index as well as other indexes: just drop it, and create a new one for the renamed table. But you can’t drop sequences. If the table it is not empty it will result in data loss and impossibility of creating new records. So you should rename them only. At the moment of writing there is no way to rename the sequence using the Schema facade, so you should use a raw SQL. For example:

DB::statement("ALTER SEQUENCE article_id_seq RENAME TO posts_id_seq");

After this, update a default value for the id field in the renamed table. For example:

DB::statement( "ALTER TABLE {$table->getTable()} ALTER COLUMN id SET DEFAULT nextval('posts_id_seq')" );

Now, let’s put all of this together:

Schema::table("articles", function (Blueprint $table) { $table->dropForeign(["author_id"]); $table->dropUnique(["slug"]); $table->dropPrimary(["id"]); }); Schema::rename("articles", "posts"); Schema::table("posts", function (Blueprint $table) { $table ->foreign("author_id") ->references("id") ->on("authors"); $table->unique(["slug"]); $table->primary("id"); DB::statement("ALTER SEQUENCE articles_id_seq RENAME TO posts_id_seq"); DB::statement( "ALTER TABLE {$table->getTable()} ALTER COLUMN id SET DEFAULT nextval('posts_id_seq')" ); });

After this, you can run this migration and check indexes names in DBeaver:

If everything ok add the revert code into the down() method:

Schema::table("posts", function (Blueprint $table) { $table->dropForeign(["author_id"]); $table->dropUnique(["slug"]); $table->dropPrimary(["id"]); }); Schema::rename("posts", "articles"); Schema::table("articles", function (Blueprint $table) { $table ->foreign("author_id") ->references("id") ->on("authors"); $table->unique(["slug"]); $table->primary("id"); DB::statement("ALTER SEQUENCE posts_id_seq RENAME TO articles_id_seq"); DB::statement( "ALTER TABLE {$table->getTable()} ALTER COLUMN id SET DEFAULT nextval('articles_id_seq')" ); });

Wrapping Up

In this article I have explained how to rename table in Laravel when you use PostgreSQL or MySQL database engine. As you can see you can face some difficulties. I showed you how to rename indexes and sequences, but there may still be triggers, events, materialized views and other things. Laravel does not have any interface for renaming them. So you should use a raw SQL queries to do this.

Your Reaction

Leave a Comment