在 Laravel 6 的数据库迁移中，添加外键约束时，Illuminate\Database\QueryException : SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint
1、在 Laravel 6 的数据库迁移中，添加外键约束时，Illuminate\Database\QueryException : SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint。如图1
![在 Laravel 6 的数据库迁移中，添加外键约束时，Illuminate\Database\QueryException : SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint](https://www.shuijingwanwq.com/wp-content/uploads/2022/06/1-2.png)
图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 ThemeStoreDBMigrating: 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 ThemeStoreDBMigrating: 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');}); |
近期评论