DEV Community

loading...

PDOException::SQLSTATE[42000]: Incorrect table definition; there can be only one auto column and it must be defined as a key

Judith ☁ Oiku
Full stack web developer - vueJs & Laravel
・1 min read

Syntax error or access violation on database migration

This likely occurs when you are trying to migrate a pivot table in Laravel in which there is an incorrect table definition, the way to solve it by changing the data type of the foreign keys as shown with the code below:

User Table
    Schema::create('users', function (Blueprint $table) {
            $table->id()->unsigned();
            $table->string()->name();
            $table->timestamps();
        });
Enter fullscreen mode Exit fullscreen mode
Role Table
    Schema::create('roles', function (Blueprint $table) {
            $table->id()->unsigned();
            $table->string()->name();
            $table->timestamps();
        });
Enter fullscreen mode Exit fullscreen mode
Pivot Table
    Schema::create('users_roles', function (Blueprint $table) {
            $table->biginteger('user_id')->unsigned();
            $table->biginteger('role_id')->unsigned();
        });

        Schema::table('users_roles', function (Blueprint $table) {
            $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
            $table->foreign('role_id')->references('id')->on('roles')->onDelete('cascade');
        });
Enter fullscreen mode Exit fullscreen mode

Discussion (0)

Forem Open with the Forem app