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| 

Answer 1

注意：Laravel 5.8 添加bigIncrements为默认值。带迁移的外键：不要忘记UNSIGNEDInnoDB需要外键和引用键的索引，以便外键检查可以快速进行并且不需要表扫描。我合并了你的 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();    });}