MySQL Laravel 5.1 迁移和播种无法截断外键约束中引用的表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31192207/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Laravel 5.1 Migration and Seeding Cannot truncate a table referenced in a foreign key constraint
提问by mtpultz
I'm trying to run the migration (see below) and seed the database, but when I run
我正在尝试运行迁移（见下文）并为数据库做种，但是当我运行时
php artisan migrate --seed
I get this error:
我收到此错误：
Migration table created successfully.
Migrated: 2015_06_17_100000_create_users_table
Migrated: 2015_06_17_200000_create_password_resets_table
Migrated: 2015_06_17_300000_create_vehicles_table
[Illuminate\Database\QueryException]
SQLSTATE[42000]: Syntax error or access violation: 1701 Cannot truncate a table
referenced in a foreign key constraint (`app`.`vehicles`, CONSTRAINT `vehic
les_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `app`.`users` (`id`
)) (SQL: truncate `users`)
[PDOException]
SQLSTATE[42000]: Syntax error or access violation: 1701 Cannot truncate a table
referenced in a foreign key constraint (`app`.`vehicles`, CONSTRAINT `vehic
les_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `app`.`users` (`id`
))
I looked up what this error is supposed to mean, and also found examplesof other people running into the same problem, even just related to using MySQL, and their solutions, but applying:
我查了一下这个错误应该是什么意思，还发现了其他人遇到同样问题的例子，甚至只是与使用MySQL和他们的解决方案有关，但应用：
DB::statement('SET FOREIGN_KEY_CHECKS=0;'); and
DB::statement('SET FOREIGN_KEY_CHECKS=1;');
Within down() doesn't seem to work and when I run describe in MySQL the tables look right.
在 down() 内似乎不起作用，当我在 MySQL 中运行 describe 时，表看起来是正确的。
The migrations are named properly to make sure the users table is migrated first, and then vehicles so the foreign key can be applied, and the tables being setup up correctly suggests the migrations were run, but then the error occurs. I dropped and recreated the DB and tried it again and it is the same result. I also don't understand why it is trying to truncate on the first migration and seed of the database, I wouldn't have thought that would occur when you tried to run php artisan migrate:refresh --seed.
迁移被正确命名以确保首先迁移用户表，然后迁移车辆以便可以应用外键，并且正确设置的表表明迁移已运行，但随后发生错误。我删除并重新创建了数据库并再次尝试，结果相同。我也不明白为什么它试图在数据库的第一次迁移和种子上截断，我没想到当您尝试运行 php artisan migrate:refresh --seed 时会发生这种情况。
// 2015_06_17_100000_create_users_table.php
class CreateUsersTable extends Migration
{
public function up()
{
Schema::create('users', function (Blueprint $table) {
$table->increments('id');
$table->string('username', 60)->unique();
$table->string('email', 200)->unique();
$table->string('password', 255);
$table->string('role')->default('user');
$table->rememberToken();
$table->timestamps();
});
}
}
public function down()
{
Schema::drop('users');
}
// 2015_06_17_300000_create_vehicles_table.php
class CreateVehiclesTable extends Migration
{
public function up()
{
Schema::create('vehicles', function (Blueprint $table) {
$table->increments('id');
$table->integer('user_id')->unsigned();
$table->string('make');
$table->string('model');
$table->string('year');
$table->string('color');
$table->string('plate');
$table->timestamps();
$table->foreign('user_id')->references('id')->on('users');
});
}
}
public function down()
{
Schema::drop('vehicles');
}
回答by haobird
DB::statement('SET FOREIGN_KEY_CHECKS=0;');
App\User::truncate();
DB::statement('SET FOREIGN_KEY_CHECKS=1;');
And it works！
它有效！
回答by user1669496
As the error says, you can not truncate tables referenced by foreign keys. Delete should work though...
正如错误所说，您不能截断外键引用的表。删除应该可以工作...
DB::table('some_table')->delete();
回答by Rob
To cleara tableusing Eloquent:
要clear一个table使用Eloquent：
Model::query()->delete();
Example using default user model
使用默认用户模型的示例
User::query()->delete();
回答by Lilian Sun
you can use
您可以使用
DB::table('your_table_name')->delete();
to empty a table, this won't delete the table structure. But the auto increment id will not start from initial number.
清空表，这不会删除表结构。但是自动递增 id 不会从初始数字开始。
回答by Nicholas English
You could just drop it with.
你可以把它扔掉。
$table->dropForeign('posts_user_id_foreign');
$table->dropForeign('posts_user_id_foreign');
回答by AhmedSeaf
before drop
下降前
Schema::disableForeignKeyConstraints();
and before close run method
在关闭运行方法之前
Schema::enableForeignKeyConstraints();
回答by Doreen Chemweno
I'm using Laravel 7.x, this worked for me. Goes without saying that it should only be used in development. To read more, check it out here.
我正在使用 Laravel 7.x，这对我有用。不言而喻，它应该只用于开发。要了解更多信息，请在此处查看。
DatabaseSeeder.php
数据库浏览器.php
<?php
use Illuminate\Database\Seeder;
class DatabaseSeeder extends Seeder
{
/**
* Seed the application's database.
*
* @return void
*/
public function run()
{
// the Eloquent part and disabling and enabling of foreign keys is only intended for development
Eloquent::unguard();
//disable foreign key check for this connection before running seeders
DB::statement('SET FOREIGN_KEY_CHECKS=0;');
$this->call(RolesTableSeeder::class);
$this->call(UsersTableSeeder::class);
// supposed to only apply to a single connection and reset it's self
// but I like to explicitly undo what I've done for clarity
DB::statement('SET FOREIGN_KEY_CHECKS=1;');
}
}
回答by Koushik Das
Here is what works for me every time.
When you're adding the foreign key, make sure to add cascade.
the syntax is like this
这是每次都对我有用的方法。添加外键时，请确保添加cascade. 语法是这样的
$table->foreign('column')->references('id')->on('table_name')->onDelete('cascade');
Make sure to replace idwith whatever field is applicable for you.
确保替换id为适用于您的任何字段。
Now before running the seeding add this instead of trucate
现在在运行播种之前添加这个而不是 trucate
DB::statement('DELETE FROM table_name');
It will delete all the data. Hope this helps.
它将删除所有数据。希望这可以帮助。
