SQLSTATE[23000]: Integrity constraint violation: 1217

Question

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 projects table. I double checked the foreign key constrains but I can't find the error.

[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:

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:

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:

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?

Answer 1

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

Answer 2

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

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.

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

Try to use php artisan tinker and then Schema::drop('some_table_name'); where some_table_name is the table that has reference to projects table, then drop the projects table again.

Answer 3

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.

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

    Schema::drop('projects');
}

Answer 4

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.