我使用迁移创建了一个表,如下所示:
public function up()
{
Schema::create('despatch_discrepancies', function($table) {
$table->increments('id')->unsigned();
$table->integer('pick_id')->unsigned();
$table->foreign('pick_id')->references('id')->on('picks');
$table->integer('pick_detail_id')->unsigned();
$table->foreign('pick_detail_id')->references('id')->on('pick_details');
$table->integer('original_qty')->unsigned();
$table->integer('shipped_qty')->unsigned();
});
}
public function down()
{
Schema::drop('despatch_discrepancies');
}
我需要更改此表并删除外键引用和列pick_detail_id
,并在pick_id
列之后添加一个名为 sku
的新 varchar 列。
因此,我创建了另一个迁移,如下所示:
public function up()
{
Schema::table('despatch_discrepancies', function($table)
{
$table->dropForeign('pick_detail_id');
$table->dropColumn('pick_detail_id');
$table->string('sku', 20)->after('pick_id');
});
}
public function down()
{
Schema::table('despatch_discrepancies', function($table)
{
$table->integer('pick_detail_id')->unsigned();
$table->foreign('pick_detail_id')->references('id')->on('pick_details');
$table->dropColumn('sku');
});
}
运行此迁移时,出现以下错误:
[Illuminate''Database''QueryException]
SQLSTATE[HY000]:常规错误:1025 重命名 时出错 "./dev_iwms_reboot/despatch_discrepancies" 到 './dev_iwms_reboot/#sql2-67c-17c464' (errno: 152) (SQL: alter tabledespatch_discrepancies
删除外键pick_detail_id)[PDOException]
SQLSTATE[HY000]:常规错误:1025 重命名 时出错 "./dev_iwms_reboot/despatch_discrepancies" 到 './dev_iwms_reboot/#sql2-67C-17C464' (errno: 152)
当我尝试通过运行php artisan migrate:rollback
命令来撤消此迁移时,我收到一条Rolled back
消息,但它实际上并没有在数据库中执行任何操作。
知道可能出了什么问题吗?如何删除具有外键引用的列?
你可以使用这个:
Schema::table('despatch_discrepancies', function (Blueprint $table) {
$table->dropForeign(['pick_detail_id']);
$table->dropColumn('pick_detail_id');
});
如果您在 dropForeign source 处达到峰值,则当您将列名作为数组传递时,它将为您构建外键索引名称。
事实证明; 当你创建一个外键时,如下所示:
$table->integer('pick_detail_id')->unsigned();
$table->foreign('pick_detail_id')->references('id')->on('pick_details');
Laravel唯一地命名外键引用,如下所示:
<table_name>_<foreign_table_name>_<column_name>_foreign
despatch_discrepancies_pick_detail_id_foreign (in my case)
因此,当你想删除带有外键引用的列时,你必须这样做:
$table->dropForeign('despatch_discrepancies_pick_detail_id_foreign');
$table->dropColumn('pick_detail_id');
更新:
Laravel 4.2+ 引入了新的命名约定:
<table_name>_<column_name>_foreign
更新:
Larave> 8.x 引入了新功能
dropConstrainedForeignId('pick_detail_id');
这将删除列以及列的外键
我的表中有多个外键,然后我必须通过在 down 方法中将列名作为数组的索引传递来逐个删除外键约束:
public function up()
{
Schema::table('offices', function (Blueprint $table) {
$table->unsignedInteger('country_id')->nullable();
$table->foreign('country_id')
->references('id')
->on('countries')
->onDelete('cascade');
$table->unsignedInteger('stateprovince_id')->nullable();
$table->foreign('stateprovince_id')
->references('id')
->on('stateprovince')
->onDelete('cascade');
$table->unsignedInteger('city_id')->nullable();
$table->foreign('city_id')
->references('id')
->on('cities')
->onDelete('cascade');
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::table('offices', function (Blueprint $table) {
$table->dropForeign(['country_id']);
$table->dropForeign(['stateprovince_id']);
$table->dropForeign(['city_id']);
$table->dropColumn(['country_id','stateprovince_id','city_id']);
});
}
使用以下语句不起作用
$table->dropForeign(['country_id','stateprovince_id','city_id']);
因为 dropForeign 不认为它们是我们要删除的单独列。所以我们必须一个接一个地放弃它们。
解决这个问题的关键(对我来说)是确保$table->dropForeign()命令传递正确的关系名称,而不一定是列名称。您不想传递列名,恕我直言,这将更直观。
对我有用的是:
$table->dropForeign('local_table_foreign_id_foreign');
$table->column('foreign_id');
所以我传递给 dropForeign() 的字符串对我有用,格式为:
[本地表]_[外键字段]_foreign
如果您可以使用Sequel Pro或Navicat之类的工具,那么能够可视化这些工具将非常有帮助。
我想到的是我不知道把Schema::table
块放在哪里。
后来我发现密钥在SQL错误上:
[Illuminate'Database'QueryException]
SQLSTATE[23000]: Integrity constraint violation: 1217 Cannot delete or update a parent row: a foreign key constraint fails (SQL: drop table if exists `lu_benefits_categories`)
因此,Schema::table
块需要进入lu_benefits_categories
迁移的down()
函数中,并且在Schema::dropIfExists
行之前:
public function down()
{
Schema::table('table', function (Blueprint $table) {
$table->dropForeign('table_category_id_foreign');
$table->dropColumn('category_id');
});
Schema::dropIfExists('lu_benefits_categories');
}
之后,php artisan migrate:refresh
或php artisan migrate:reset
将解决问题。
在 laravel 8 上使用 dropConstrainedForeignId(https://github.com/laravel/framework/pull/34806)
<?php
use Illuminate'Database'Migrations'Migration;
use Illuminate'Database'Schema'Blueprint;
use Illuminate'Support'Facades'Schema;
class AddAddressFieldsInEventTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::table('events', function (Blueprint $table) {
$table->bigInteger('address_id')->nullable();
$table->foreign('address_id')
->references('id')
->on('addresses')
->onDelete('cascade');
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::table('events', function (Blueprint $table) {
$table->dropConstrainedForeignId('address_id');
$table->dropColumn('address_id');
});
}
}
在 Laravel> 8.x 中,您可以使用以下代码添加/删除外键。在删除列之前,您需要删除外键,否则,外键可能会导致您遇到的错误。
添加外键
$table->foreignId('pick_id')->constrained('picks')->cascadeOnUpdate()
->cascadeOnDelete();
删除外键
$table->dropConstrainedForeignId('pick_id');
先禁用关系 ID
Schema::disableForeignKeyConstraints();