Categories: LaravelLaravel 6Laravel Database MigrationsWeb应用开发Web框架

在 Laravel 6 的数据库迁移中，添加外键约束时，Illuminate\Database\QueryException : SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint

图1

1、在 Laravel 6 的数据库迁移中，添加外键约束时，Illuminate\Database\QueryException : SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint。如图1

图1

PS E:\wwwroot\object> php artisan module:migrate ThemeStoreDB
Migrating: 2021_12_13_145303_create_theme_asset_version_table

   Illuminate\Database\QueryException  : SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table `theme_asset_version` add constraint `theme_asset_version_asset_id_foreign` foreign key (`asset_id`) references `theme_asset` (`id`))

  at E:\wwwroot\object\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|

  Exception trace:

  1   Doctrine\DBAL\Driver\PDO\Exception::("SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint")
      E:\wwwroot\object\vendor\doctrine\dbal\lib\Doctrine\DBAL\Driver\PDO\Exception.php:18

  2   Doctrine\DBAL\Driver\PDO\Exception::new(Object(PDOException))
      E:\wwwroot\object\vendor\doctrine\dbal\lib\Doctrine\DBAL\Driver\PDOStatement.php:119

  Please use the argument -v to see more details.

2、代码实现如下

        Schema::create('theme_asset_version', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->bigInteger('asset_id')->nullable(false)->comment('Theme asset ID');
            $table->foreign('asset_id')->references('id')->on('theme_asset');

        });

3、代码调整如下，仍然报错：

        Schema::create('theme_asset_version', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->bigInteger('asset_id')->nullable(false)->comment('Theme asset ID');
        });

        Schema::table('theme_asset_version', function (Blueprint $table) {
            $table->foreign('asset_id')->references('id')->on('theme_asset');
        });

PS E:\wwwroot\object> php artisan module:migrate ThemeStoreDB
Migrating: 2021_12_13_145303_create_theme_asset_version_table

   Illuminate\Database\QueryException  : SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table `theme_asset_version` add constraint `theme_asset_version_asset_id_foreign` foreign key (`asset_id`) references `theme_asset` (`id`))

  at E:\wwwroot\object\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|

  Exception trace:

  1   Doctrine\DBAL\Driver\PDO\Exception::("SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint")
      E:\wwwroot\object\vendor\doctrine\dbal\lib\Doctrine\DBAL\Driver\PDO\Exception.php:18

  2   Doctrine\DBAL\Driver\PDO\Exception::new(Object(PDOException))
      E:\wwwroot\object\vendor\doctrine\dbal\lib\Doctrine\DBAL\Driver\PDOStatement.php:119

  Please use the argument -v to see more details.

4、表 theme_asset_version 的字段 asset_id 类型调整为：unsignedBigInteger，以与 theme_asset 的字段 id 类型保持一致。运行迁移成功。

        Schema::create('theme_asset_version', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->unsignedBigInteger('asset_id')->nullable(false)->comment('Theme asset ID');
        });

        Schema::table('theme_asset_version', function (Blueprint $table) {
            $table->foreign('asset_id')->references('id')->on('theme_asset');
        });

永夜