Laravel Error "Illuminate\Database\QueryException" when run the 'php artisan migrate'

Question

I can't run the "php artisan migrate" in laravel project. If I run this command then below error will display.

Migrating: 2021_08_02_173519_create_access_user

Illuminate\Database\QueryException

SQLSTATE[42000]: Syntax error or access violation: 1072 Key column 'access_id' doesn't exist in table (SQL: alter table access_user add constraint access_user_access_id_foreign foreign key (access_id) references access (id))

at C:\Users\HETTIARACHCHIGEDAMIT\LanexGloble\lanex-internal-backend\api\vendor\laravel\framework\src\Illuminate\Database\Connection.php:692 688▕ // If an exception occurs when attempting to run a query, we'll format the error 689▕ // message to include the bindings with SQL, which will make this exception a 690▕ // lot more helpful to the developer instead of just the database's errors. 691▕ catch (Exception $e) { 693▕ $query, $this->prepareBindings($bindings), $e 694▕ ); 695▕ } 696▕ }

1 C:\Users\HETTIARACHCHIGEDAMIT\LanexGloble\lanex-internal-backend\api\vendor\laravel\framework\src\Illuminate\Database\Connection.php:485 \Database\Conne PDOException::("SQLSTATE[42000]: Syntax error or access violation: 1072 Key column 'access_id' doesn't exist in table") in table")

2 C:\Users\HETTIARACHCHIGEDAMIT\LanexGloble\lanex-internal-backend\api\vendor\laravel\framework\src\Illuminate\Database\Conne\Database\Connection.php:485 PDOStatement::execute()

this is an error occurred file.

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateAccessUser extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('access_user', function (Blueprint $table) {
            $table->increments('id');
            $table->foreign('access_id')->references('id')->on('access');
            $table->foreign('user_id')->references('id')->on('user');
            $table->boolean('status');
            $table->timestamp('updated_at');
            $table->timestamp('created_at');
            $table->string('updated_by');
            $table->string('created_by');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('access_user');
    }
}

You need to create the column before configuring it as a foreign key, add $table->unsignedBigInteger('access_id'); before you define the foreign keys. It looks like you will have to do the same for user_id as well. — RJK
– RJK, Commented Oct 26, 2021 at 8:34

hms5232 · Accepted Answer · 2021-10-26 08:41:02Z

You should add a column access_id before add foreign key on it. The foreign() is adding foreign key to exists column.

So, you should change migration to below:

public function up()
    {
        Schema::create('access_user', function (Blueprint $table) {
            $table->increments('id');
            // add this line
            $table->unsignedBigInteger('access_id');  // depend on your foreign column type

            $table->foreign('access_id')->references('id')->on('access');
            $table->foreign('user_id')->references('id')->on('user');
            $table->boolean('status');
            $table->timestamp('updated_at');
            $table->timestamp('created_at');
            $table->string('updated_by');
            $table->string('created_by');
        });
    }

or use foreignId():

public function up()
    {
        Schema::create('access_user', function (Blueprint $table) {
            $table->increments('id');
            $table->foreignId('access_id')->constrained('access');  // change here
            $table->foreign('user_id')->references('id')->on('user');
            $table->boolean('status');
            $table->timestamp('updated_at');
            $table->timestamp('created_at');
            $table->string('updated_by');
            $table->string('created_by');
        });
    }

See docs.

Collectives™ on Stack Overflow

Laravel Error "Illuminate\Database\QueryException" when run the 'php artisan migrate'

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related