DEV Community

Judith ☁ Oiku
Judith ☁ Oiku

Posted on

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

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

Top comments (0)