laravel - 在 laravel 7 中，外键约束的格式不正确

Question

我正在使用 laravel 构建一个酒店管理应用程序。我正在尝试在 laravel 中创建表 'reservations'，但是当我运行 'migrate:fresh' 命令时，我得到以下信息：错误“外键约束的格式不正确”。谁能告诉你这个错误是什么意思？

查看错误

public function up()
        {
            Schema::create('room_types', function (Blueprint $table) {
                $table->id();
                $table->string('title')->unique();
                $table->string('slug')->unique();
                $table->string('short_code')->unique();
                $table->longText('description')->nullable();
                $table->integer('base_capacity')->default(0);
                $table->integer('higher_capacity')->default(0);
                $table->boolean('extra_bed')->default(0);
                $table->integer('kids_capacity')->default(0);
                $table->float('base_price',8,2)->default(0);
                $table->float('additional_person_price',8,2)->default(0);
                $table->float('extra_bed_price',8,2)->default(0);
                $table->boolean('status')->default(1);
                $table->softDeletes();
                $table->timestamps();
            });
        }

public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->string('email')->unique();
            $table->timestamp('email_verified_at')->nullable();
            $table->string('password');

            $table->string('usertype')->default('user');
            $table->string('last_name')->nullable();
            $table->string('phone')->nullable();
            $table->date('dob')->nullable();
            $table->longText('address')->nullable();
            $table->enum('sex',['M','F','O'])->default('M');
            $table->string('picture')->nullable();
            $table->string('id_type')->nullable();
            $table->string('id_number')->nullable();
            $table->string('id_card_image_front')->nullable();
            $table->string('id_card_image_back')->nullable();
            $table->string('company_name')->nullable();
            $table->string('gst_no')->nullable();
            $table->text('remarks')->nullable();
            $table->boolean('vip')->default(0);
            $table->boolean('status')->default(1);

            $table->rememberToken();
            $table->timestamps();
        });
    }




public function up()
    {
        Schema::create('reservations', function (Blueprint $table) {
            $table->id();
            $table->integer('uid')->unique();
            $table->timestamp('date');
            $table->unsignedInteger('user_id');
            $table->unsignedInteger('room_type_id');
            $table->integer('adults')->default(1);
            $table->integer('kids')->default(0);
            $table->date('check_in');
            $table->date('check_out');
            $table->integer('number_of_room')->default(1);
            $table->enum('status',['PENDING','CANCEL','SUCCESS'])->default('PENDING');
            $table->timestamps();
            $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
            $table->foreign('room_type_id')->references('id')->on('room_types')->onDelete('cascade');
        });
    }

错误信息

  SQLSTATE[HY000]: General error: 1005 Can't create table `hotelplex`.`reservations` (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter table `reservations` add constraint `reservations_reservations_user_id_foreign` foreign key (`reservations_user_id`) references `users` (`id`) on delete cascade)

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

          1   C:\xampp\htdocs\hotelplex\vendor\laravel\framework\src\Illuminate\Database\Connection.php:463
              PDOException::("SQLSTATE[HY000]: General error: 1005 Can't create table `hotelplex`.`reservations` (errno: 150 "Foreign key constraint is incorrectly formed")")

          2   C:\xampp\htdocs\hotelplex\vendor\laravel\framework\src\Illuminate\Database\Connection.php:463
              PDOStatement::execute()

标签： laravel

Answer 1

Laravel6

Laravel6没有任何方法可以在表id()中创建。确实.. 我尝试使用using创建并得到以下错误。idLaravel7id$table->id()Laravel6

您似乎发布了错误的错误，或者您已经id在表中手动创建了。

您可以使用bigIncrements, bigInteger,increments等integer。

你可以在这里找到所有可用的方法

Laravel7

As perLaravel7 $table->id()是$table->bigIncrements('id')ie的别名的别名unsigned big integer。

要创建Foreign key的数据类型child column必须完全匹配parent column。

既然users.idandroom_types.id是一个bigIncrementsthenreservations.user_id并且reservations.room_type_id也需要是一个unsignedbigInteger，而不是一个unsignedInteger。

所以让它工作

改变

$table->unsignedInteger('user_id'); 
$table->unsignedInteger('room_type_id');

到

$table->unsignedBigInteger('user_id');
$table->unsignedBigInteger('room_type_id');

像这样：

public function up()
    {
        Schema::create('reservations', function (Blueprint $table) {
            $table->id();
            $table->integer('uid')->unique();
            $table->timestamp('date');
            $table->unsignedBigInteger('user_id');
            $table->unsignedBigInteger('room_type_id');
            $table->integer('adults')->default(1);
            $table->integer('kids')->default(0);
            $table->date('check_in');
            $table->date('check_out');
            $table->integer('number_of_room')->default(1);
            $table->enum('status',['PENDING','CANCEL','SUCCESS'])->default('PENDING');
            $table->timestamps();
            $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
            $table->foreign('room_type_id')->references('id')->on('room_types')->onDelete('cascade');
        });
    }

https://laravel.com/docs/7.x/migrations#creating-columns