Laravel Error : SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'categories' already exists

Question

I cant figure out what the problem is, the 2 tables are not connecting for some reason, I read many articles and tried many things still not working.

I want to link post and category tables together, so when I can display the category chosen in the post made.

  public function up()
    {
        Schema::create('posts', function (Blueprint $table) {
            $table->increments('id');
            $table->timestamps();
            $table->string('name');
            $table->text('description');
            $table->integer('category_id');
            $table->integer('price');
            $table->integer('currency_id');
        });
    }

Category

 public function up()
    {
        Schema::create('categories', function (Blueprint $table) {
            $table->id();
            $table->timestamps();
            $table->string('name');
            $table->bigInteger('post_id')->unsigned();
            $table->foreign('post_id')->references('id')->on('posts');
        });
    }

This is the error I get:

SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'categories' already exists (SQL: create table categories (id bigint unsigned not null auto_increment primary key, created_at timestamp null, updated_at timestamp null, name varchar(255) not null, post_id bigint unsigned not null) default character set utf8mb4 collate 'utf8mb4_unicode_ci')

Answer 1

Try refreshing you database entirely using the migrate:refresh artisan command.

php artisan migrate:refresh --seed

It may be that a database migration ran and failed before it could register in the migrations table of your database.

---

Issues: (so far)

1) As per above, a migrate:refresh sorts out the original error

2) $table->bigInteger('post_id')->unsigned(); will not work as posts.id is an integer and not a bigInteger.

Solution:

Change your post_id definition to

$table->integer('post_id')->unsigned();

Answer 2

It says the categories table already exists. So what you have to do is, If you are in dev env, you can delete the table and try it or you can do like the below command.

Run artisan like this, it will drop all tables and migrate fresher.

php artisan migrate:fresh

This worked for me.

Answer 3

The answer is very simple, you have to change $table->increments('id') in posts table to $table->id(), because the foreign key must refer to a primary key as the message says.

Here are some tips for you

• You have to use bigIncrements in the posts table instead integer because the length of integer is 4-byte but the bigIncrements is 8-byte and this may cause a problem in the future

• You may love to use this line

$table->foreignId('post_id')->constrained();

instead

$table->bigInteger('post_id')->unsigned();
$table->foreign('post_id')->references('id')->on('posts');

for simplicity

Answer 4

Though, this is a past issue, but I found a very good and simple solution to this issue;

1. Check your category and post migration file Check your migration files and check the public function up and public function down if the tables_names are correct and then check on your http://localhost/phpmyadmin/index.php and then go to your database

2. Checking migrations table Inside your database, check for the migrations table, It looks like this Picture of some rows inside a migrations table, you will find out that those two tables(post and category) are not inside

3. Copy Names of Migrations Files Go into your laravel-app/database/migrations folder or IDE to that folder named migrations, then copy the name of both migrations files

4. Create new Migration rows and paste the copied names Back to your localhost database migrations table, create new rows for the two copied names and paste the names accordingly numerically AND THEN RUN THE MIGRATION AGAIN. IT IS DONE

php artisan migrate

Answer 5

For me issue was artisan/laravel cached somewhere migration so i run php artisan optimize:clear and it worked without an error any more