Laravel - Integrity constraint violation: 1452

Question

this is my migration :

public function up()
    {
      Schema::table('payments', function($table)
      {
        $table->dropColumn('invoice_id');
      });
    }

     public function down()
    {
      Schema::table('payments',function (Blueprint $table){
        $table->unsignedInteger('invoice_id')->index();
        $table->foreign('invoice_id')->references('id')->on('invoices')->onDelete('cascade');
      });
    }

when I run php artisan migrate:rollback it gives me this exception :

[Illuminate\Database\QueryException]                                                                                                         
  SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`invoiceninja`.`#sq  
  l-418_46`, CONSTRAINT `payments_invoice_id_foreign` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE CASCADE) (SQL: alter   
  table `payments` add constraint `payments_invoice_id_foreign` foreign key (`invoice_id`) references `invoices` (`id`) on delete cascade)     



  [PDOException]                                                                                                                               
  SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`invoiceninja`.`#sq  
  l-418_46`, CONSTRAINT `payments_invoice_id_foreign` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE CASCADE)

can any one help me ?

it gives me this exception [Illuminate\Database\QueryException] SQLSTATE[23000]: Integrity constraint violation: 1217 Cannot delete or update a parent row: a foreign key constraint fails (SQL: drop table `payments`) — Melek Yilmaz, Apr 19 '16 at 10:35

Arth · Answer 1 · 2016-04-19T10:56:29.050

I assume you are running the down method. The first line:

$table->unsignedInteger('invoice_id')->index();

Adds back the column, I don't know Laravel but I'm guessing this assigns a default value of 0 to the column.

$table->foreign('invoice_id')->references('id')->on('invoices')->onDelete('cascade');

Would then fail as there if there are no 0 values in the referenced id column.

You could probably get this to work by allowing the invoice_id column to be NULL and use this as the default value.

My first stop would be to check the values in the newly re-added column invoice_id.

score 0 · Answer 2 · answered Apr 19 '16 at 10:45

I'm not sure why do you have this logic in a down() method, because usually it's in up(), but try to do this:

public function down()
{
    Schema::table('payments', function (Blueprint $table){
        $table->unsignedInteger('invoice_id')->index();
    });

    Schema::table('payments', function (Blueprint $table){
        $table->foreign('invoice_id')->references('id')->on('invoices')->onDelete('cascade');
    });
}

Laravel - Integrity constraint violation: 1452

2 Answers2