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 Incorrecttable definition; there can be only one auto column and it must bedefined as a key (SQL: create table `one_time_password_logs` (`id` intunsi gned not null auto_increment primary key, `user_id` bigintunsigned 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)  atC:\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| 

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();

    });

}