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')

i know, even when i dropped the table and migrate it again it does not work — Harout, Mar 31 '20 at 15:51
can you try removing `$table->foreign('post_id')->references('id')->on('posts');` if this works. — Deepak, Mar 31 '20 at 15:53
I guess your Database migration files are wrong order. The first file for `posts` then `categories`. — Deepak, Mar 31 '20 at 15:55
removing it didnt work.. what to do mean its wrong can u explain more please — Harout, Mar 31 '20 at 15:57
2020_03_31_082945_create_posts_table.php ................... 2020_03_31_083307_create_categories_table.php — Harout, Mar 31 '20 at 16:38

Spholt · Answer 1 · 2020-04-01T09:50:49.583

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();

edited Apr 01 '20 at 09:50

answered Mar 31 '20 at 15:51

Spholt

3,724
1
18
29

Dropped all tables and tried again .. still SQLSTATE[HY000]: General error: 1005 Can't create table `shop`.`categories` (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter table `categories` add constraint `categories_post_id_foreign` foreign key (`post_id`) references `posts` (`id`)) – Harout Mar 31 '20 at 15:54
Ok, that is a different error at least. It is now complaining about a foreign key. This could be due to the order in which your migrations are being executed. Make sure you create your `posts` table before your `categories` table, otherwise your foreign key will fail – Spholt Mar 31 '20 at 15:57
post migration is before the category migration . – Harout Mar 31 '20 at 16:38
See my updated answer, i've run into this a few times before when they changed the default for the id columns from `integer` to `bigInteger` but hadn't updated all of the documentation to reflect it (i.e. the examples they gave had the wrong definitions) – Spholt Apr 01 '20 at 09:53

score 0 · Answer 2 · answered Sep 07 '22 at 06:11

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.

score 0 · Answer 3 · answered Sep 07 '22 at 08:58

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

score 0 · Answer 4 · edited Feb 16 '23 at 15:16

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

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
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
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
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

score 0 · Answer 5 · answered Mar 27 '23 at 20:01

0

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

answered Mar 27 '23 at 20:01

Damian Chudobiński

112
1
12

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

5 Answers5

Here are some tips for you

Linked