Laravel 5.1迁移和播种无法截断外键约束中引用的表 [英] Laravel 5.1 Migration and Seeding Cannot truncate a table referenced in a foreign key constraint

问题描述

我正在尝试运行迁移(请参见下文)并为数据库设置种子，但是当我运行时

I'm trying to run the migration (see below) and seed the database, but when I run

php artisan migrate --seed

我收到此错误:

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`
))

我查看了该错误的含义，并找到了示例遇到相同问题的其他人，甚至与使用 MySQL 有关，及其解决方案，但适用于:

I looked up what this error is supposed to mean, and also found examples of other people running into the same problem, even just related to using MySQL, and their solutions, but applying:

DB::statement('SET FOREIGN_KEY_CHECKS=0;'); and 
DB::statement('SET FOREIGN_KEY_CHECKS=1;'); 

在down()内似乎不起作用，当我在MySQL中运行describe时，表看起来正确.

Within down() doesn't seem to work and when I run describe in MySQL the tables look right.

对迁移进行正确命名，以确保先迁移users表，然后迁移车辆，以便可以应用外键，并且正确设置的表表明已在运行迁移，但随后发生错误.我删除并重新创建了数据库，然后再次尝试，结果相同.我也不明白为什么它要在数据库的第一次迁移和种子上截断，我不会想到当您尝试运行php artisan migration:refresh --seed时会发生这种情况.

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.

// 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');
}

推荐答案

如错误所示，您不能截断外键引用的表.删除应该可以...

As the error says, you can not truncate tables referenced by foreign keys. Delete should work though...

DB::table('some_table')->delete();

这篇关于Laravel 5.1迁移和播种无法截断外键约束中引用的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！