php - erore SQLSTATE[42000]：语法错误或访问冲突：1075 表定义不正确；当我运行迁移时

Question

我在 laravel 中使用 otp 包（Laravel OTP 登录包）

我的迁移：

public function up()
{
    Schema::create('one_time_password_logs', function (Blueprint $table) {
        $table->increments('id');
        $table->bigIncrements("user_id")->index();
        $table->string('otp_code')->index();
        $table->string('refer_number')->index();
        $table->string('status')->index();
        $table->timestamps();
    });

    Schema::table('one_time_password_logs', function (Blueprint $table) {
        $table->foreign('user_id')
            ->references('id')
            ->on('users')
            ->onDelete('cascade');
    });
}

运行迁移时显示此错误：

Illuminate\Database\QueryException 

SQLSTATE[42000]: Syntax error or access violation: 1075 Incorrect
table definition; there can be only one auto column and it must be
defined as a key (SQL: create table `one_time_password_logs` (`id` int
unsi gned not null auto_increment primary key, `user_id` bigint
unsigned not null auto_increment primary key, `otp_code` varchar(255)
not null, `refer_number` varchar(255) not null, `status` varchar(255)
not null, ` created_at` timestamp null, `updated_at` timestamp null)
default character set utf8mb4 collate 'utf8mb4_unicode_ci' engine =
innodb)

  at
C:\Users\aliaz\Desktop\smart-lighting\vendor\laravel\framework\src\Illuminate\Database\Connection.php:671
    667|         // If an exception occurs when attempting to run a query, we'll format the error
    668|         // message to include the bindings with SQL, which will make this exception a
    669|         // lot more helpful to the developer instead of just the database's errors.
    670|         catch (Exception $e) {
  671|             throw new QueryException(
    672|                 $query, $this->prepareBindings($bindings), $e
    673|             );
    674|         }
    675| 

  1  
C:\Users\aliaz\Desktop\smart-lighting\vendor\laravel\framework\src\Illuminate\Database\Connection.php:464
      PDOException::("SQLSTATE[42000]: Syntax error or access violation: 1075 Incorrect table definition; there can be only one auto
column and it must be defined as a key")

  2  
C:\Users\aliaz\Desktop\smart-lighting\vendor\laravel\framework\src\Illuminate\Database\Connection.php:464
      PDOStatement::execute()

标签： phpmysqllaravel

Answer 1

• 注意：Laravel 5.8 添加bigIncrements为默认值。
• 带有迁移的外键：不要忘记UNSIGNED
• InnoDB需要外键和引用键上的索引，以便外键检查可以快速且不需要表扫描。

我合并了你的 2 Schema。尝试这个 ：

public function up()
{
    Schema::create('one_time_password_logs', function (Blueprint $table) {
        $table->engine = 'InnoDB';
        $table->bigIncrements('id');
        $table->bigInteger('user_id')->unsigned();
        $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
        $table->string('otp_code')->index();
        $table->string('refer_number')->index();
        $table->string('status')->index();
        $table->timestamps();
    });
}