laravel - 为什么在区域表定义中未创建 state_id 字段?
问题描述
我尝试将旧数据库从 laravel 5.x 重构为 laravel 8 并拥有 2 个相关表: database/migrations/2019_05_21_144910_create_states_table.php :
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
class CreateStatesTable extends Migration {
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('states', function(Blueprint $table)
{
$table->smallIncrements('id')->unsigned();
$table->string('code', 3)->unique();
$table->string('name', 50)->unique();
$table->string('slug', 55)->unique();
$table->enum('active', ['A', 'I'])->default('I')->comment(' A=>Active, I=>Inactive');
$table->index(['active','code'], 'fk_states_active_code_ixd');
$table->index(['active','slug'], 'fk_states_active_slug_ixd');
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::drop('states');
}
}
和 database/migrations/2019_05_21_145001_create_regions_table.php :
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
class CreateRegionsTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('regions', function (Blueprint $table) {
$table->smallIncrements('id')->unsigned();
$table->foreign('state_id')->references('id')->on('states')->onUpdate('RESTRICT')->onDelete('RESTRICT');
$table->string('name', 50);
$table->string('slug', 55)->unique();
$table->enum('active', ['A', 'I'])->default('I')->comment(' A=>Active, I=>Inactive');
$table->unique(['state_id', 'name']);
$table->index(['state_id', 'active'], 'fk_regions_state_id_active_ixd');
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::drop('regions');
}
}
运行迁移命令时出错:
php artisan migrate:fresh --seed
...
Migrating: 2019_05_21_144910_create_states_table
Migrated: 2019_05_21_144910_create_states_table (626.91ms)
Migrating: 2019_05_21_145001_create_regions_table
Illuminate\Database\QueryException
SQLSTATE[42000]: Syntax error or access violation: 1072 Key column 'state_id' doesn't exist in table (SQL: alter table `regions` add constraint `regions_state_id_foreign` foreign key (`state_id`) references `states` (`id`) on delete RESTRICT on update RESTRICT)
at vendor/laravel/framework/src/Illuminate/Database/Connection.php:703
699▕ // If an exception occurs when attempting to run a query, we'll format the error
700▕ // message to include the bindings with SQL, which will make this exception a
701▕ // lot more helpful to the developer instead of just the database's errors.
702▕ catch (Exception $e) {
➜ 703▕ throw new QueryException(
704▕ $query, $this->prepareBindings($bindings), $e
705▕ );
706▕ }
707▕ }
+9 vendor frames
10 database/migrations/2019_05_21_145001_create_regions_table.php:31
Illuminate\Support\Facades\Facade::__callStatic()
+32 vendor frames
43 artisan:37
Illuminate\Foundation\Console\Kernel::handle()
在区域表中,我有区域表,但没有 state_id 字段。看起来像线
$table->foreign('state_id')->references('id')->on('states')->onUpdate('RESTRICT')->onDelete('RESTRICT');
被忽略了,我无法理解为什么?试了好几次...
更新 2: 我将 CreateRegionsTable 修改为:
Schema::create('regions', function (Blueprint $table) {
$table->smallIncrements('id')->unsigned();
$table->foreignId('state_id')->constrained('states')->onUpdate('RESTRICT')->onDelete('RESTRICT');
但我得到了其他错误:
SQLSTATE[HY000]: General error: 3780 Referencing column 'state_id' and referenced column 'id' in foreign key constraint 'regions_state_id_foreign' are incompatible. (SQL: alter table `regions` add constraint `regions_state_id_foreign` foreign key (`state_id`) references `states` (`id`) on delete RESTRICT on update RESTRICT)
在 database/migrations/2019_05_21_144910_create_states_table.php 我有:
Schema::create('states', function(Blueprint $table)
{
$table->smallIncrements('id')->unsigned();
我想 foreignId 与 smallIncrements 不兼容......但哪种方法有效?
提前致谢!
解决方案
$table->foreign()
方法仅将外键约束添加到现有列。您正在寻找的方法是$table->foreignId()
所以而不是
$table->foreign('state_id')->references('id')->on('states')->onUpdate('RESTRICT')->onDelete('RESTRICT');
在您的区域表迁移中,您应该这样做
$table->foreignId('state_id')->constrained('states')->onUpdate('RESTRICT')->onDelete('RESTRICT');
推荐阅读
- c - 关于在实践中正确使用 realloc 的新手问题
- angularjs - 如何使用 Angular JS 在 cshtml 中添加 if else?
- protocol-buffers - 在我自己的带有 Bazel 的 Protobufs 中使用 GoGo Protobuf .proto 源
- javascript - TypeError:无法读取未定义的属性“插入用户名”
- python - 合并张量并将字节字符串转换为浮点字符串
- javascript - 如何将geojson文件加载为平铺层
- filepond - 使用 FilePond 和 Doka,有什么方法可以将 HEIC 文件转换为 jpg 以便可以对其进行编辑?
- regex - 编写 Perl 正则表达式
- javascript - 反应:当我尝试从我的应用程序中获取组件并从中创建新文件时出现错误
- search - 在 J 中使用向量/矩阵时打印值及其相关索引