Migration:refresh is throwing an error when trying to add a foreign key-CodePudding

I am trying to add a foreign key to one table, but when I add the foreign key, and then try to run php artisan migrate:refresh, I keep getting thrown the below error.

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

properties migration schema:

Schema::create('properties', function (Blueprint $table) {
            $table->id();
            $table->timestamps();
            $table->string('property_title');
            $table->string('property_description');
            $table->string('property_image');
            $table->bigInteger('user_id')->unsigned();
            $table->foreignId('id')->constrained('users');
        });

users migration schema:

Schema::create('users', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->string('email')->unique();
            $table->timestamp('email_verified_at')->nullable();
            $table->string('password');
            $table->rememberToken();
            $table->timestamps();
        });

CodePudding user response：

The $table->foreignId('x') creates a column, so having the bigInteger and foreign id lines is redundant and will cause errors.

Remove:

$table->bigInteger('user_id')->unsigned();

Then the foreign ID should be the name of the foreign column. So 'id' should be changed to 'user_id'. So you can change the last line to this:

Change

$table->foreignId('user_id')->constrained('users');

You could also get rid of the 'constrained('users') at the end and laravel will figure out which table it belongs to based on the columns name:

$table->foreignId('user_id')

Here is the docs link describing this

Down function

Lastly, migrate:refesh won't work if you don't have a down() function in the migration file. So make sure you include a down function like so:

public function down()
{
    Schema::dropIfExists('properties');
}

This should be in the same file as your 'up()' function for the properties table