In Laravel 6’s database migration, when adding a foreign key constraint, Illuminate\Database\QueryException : SQLState[HY000]: General error: 1215 Cannot add foreign key constraint
1. In Laravel 6’s database migration, when adding a foreign key constraint, Illuminate\Database\QueryException : SQLState[HY000]: General error: 1215 Cannot add foreign key constraint. as shown in Figure 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)
Figure 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. The code is implemented as follows
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. The code is adjusted as follows, and the error is still reported:
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. The field ASSET_ID type of the table Theme_Asset_Version is adjusted to: unsignedbigInteger, to be consistent with the field ID type of the theme_asset . The migration was successful.
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');
});
