laravel SQLSTATE[23000]：完整性约束违规：1217

SQLSTATE[23000]: Integrity constraint violation: 1217

提问by omarsafwany

After creatin multiple migrations in my project, I wanted to rollback to update a few things but suddenly I got this error when I tried to drop projectstable. I double checked the foreign key constrains but I can't find the error.

在我的项目中创建多个迁移后，我想回滚以更新一些内容，但是当我尝试删除projects表时突然出现此错误。我仔细检查了外键约束，但找不到错误。

[Illuminate\Database\QueryException]                                         
  SQLSTATE[23000]: Integrity constraint violation: 1217 Cannot delete or upda  
  te a parent row: a foreign key constraint fails (SQL: drop table `projects`  
  )                                                                                                                                                           
  [PDOException]                                                               
  SQLSTATE[23000]: Integrity constraint violation: 1217 Cannot delete or upda  
  te a parent row: a foreign key constraint fails  

Here are my migrations: 1.create table users:

这是我的迁移： 1.create table users：

public function up()
    {
        Schema::create('users', function(Blueprint $table)
        {
            $table->increments('id');
            $table->string('first_name');
            $table->string('last_name');
            $table->string('email', 50)->unique();
            $table->string('password', 60);
            $table->string('password_temp', 60);
            $table->string('code', 60);
            $table->boolean('active');
            $table->string('remember_token', 100);
            $table->timestamps();
        });
    }
    public function down()
    {
        Schema::drop('users');
    }

2.create clients table:

2.创建客户表：

public function up()
    {
        Schema::create('clients', function(Blueprint $table)
        {
            $table->increments('id');
            $table->integer('user_id')->unsigned()->index()->nullable();
            $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
            $table->timestamps();
        });
    }
    public function down()
    {
        Schema::drop('clients');
    }

3.create projects table:

3.创建项目表：

public function up()
    {
        Schema::create('projects', function(Blueprint $table)
        {
            $table->increments('id');
            $table->string('name');
            $table->integer('status');
            $table->integer('client_id')->unsigned()->index()->nullable();
            $table->foreign('client_id')->references('id')->on('users')->onDelete('cascade');    
            $table->timestamps();
        });
    }
    public function down()
    {
        Schema::drop('projects');
    }

In the last migration, what could possibly be the mistake I am doing?! I face no trouble when migrating but it appears only when I rollback to add any changes.

在上次迁移中，我做的错误可能是什么？！迁移时我没有遇到任何问题，但只有在我回滚以添加任何更改时才会出现。

Any idea why this happens?

知道为什么会这样吗？

回答by Janak Bhatta

Use this in down function.

在向下功能中使用它。

public function down()
{
    DB::statement('SET FOREIGN_KEY_CHECKS = 0');
    Schema::dropIfExists('tableName');
    DB::statement('SET FOREIGN_KEY_CHECKS = 1');
}

回答by Matthew Brown

When you use foreign keys in your tables you need to remove those using the dropForeign method before you drop your table, else you will run into the integrity constraint issues you are currently getting.

当您在表中使用外键时，您需要在删除表之前使用 dropForeign 方法删除那些外键，否则您将遇到当前遇到的完整性约束问题。

public function down()
{
    Schema::table('projects', function(Blueprint $table) {
        $table->dropForeign('projects_client_id_foreign');
    });

    Schema::drop('projects');
}

回答by robintan93

If there are only 3 tables above mentioned, there is no issue in your migration as I have tried it myself with

如果上面只提到了 3 个表，那么您的迁移没有问题，因为我自己尝试过

php artisan migrate

to create the tables and

创建表和

php artisan migrate:rollback

to rollback.

回滚。

One thing that I know is Laravel is going to assume the sequence of the migration based on the migration file timestamp.

我知道的一件事是 Laravel 将根据迁移文件的时间戳来假设迁移的顺序。

Therefore, I am quite sure there is another table that has a foreign key reference to the projectstables that has not been dropped as the error messsage is (SQL: drop table projects)

因此，我很确定还有另一个表projects具有对尚未删除的表的外键引用，因为错误消息是 (SQL: drop table projects)

Try to use php artisan tinkerand then Schema::drop('some_table_name');where some_table_nameis the table that has reference to projectstable, then drop the projectstable again.

尝试使用php artisan tinker然后再使用引用表的表Schema::drop('some_table_name');在哪里，然后再次删除该表。some_table_nameprojectsprojects

回答by Harry Bosh

This problem is commonly created by editing/adding to existing migrations. Either create new migration files when dealing with foreign keys or be prepared to potentially dump/drop your entire db and refresh.

此问题通常是通过编辑/添加到现有迁移而产生的。要么在处理外键时创建新的迁移文件，要么准备好潜在地转储/删除整个数据库并刷新。