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

由 永夜 · 2022/06/06

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

图1

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

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、代码实现如下

1 2 3 4 5 6

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、代码调整如下，仍然报错：

1 2 3 4 5 6 7 8

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'); });

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

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 类型保持一致。运行迁移成功。

1 2 3 4 5 6 7 8

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'); });

 

Share

标签： 1215 CannotDatabaseforeign key constraintGeneral errorLaravelQueryExceptionSQLSTATE[HY000]外键约束数据库迁移类型

您可能还喜欢...

• 0

  排查分析 http 请求响应 500 的过程，jQuery 属性操作 – attr() 方法，设置 src 属性导致

  2021/03/23

• 0

  在 Yii 2.0 中，基于映射过滤条件关键字以构建方法。新增特定条件构建器(fis)的实现，以支持 MySQL FIND_IN_SET() 函数

  2019/08/27

• 0

  在 Yii 2.0 中的命令行中，从A数据库迁移数据至B数据库的实现

  2021/04/30