SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row (Laravel 6)

2
votes

What am I doing wrong?

Environment: Laravel 6, Homestead (Local), Windows 10

Create External Table (Migration): 

Schema::create('external', function (Blueprint $table) {
                $table->increments('id')->unsigned();
                $table->foreign('id')->references('order_id')->on('order');
            });

Create Order Table (Migration):

Schema::create('order', function (Blueprint $table) {
    $table->increments('id');
    $table->unsignedInteger('order_id')->index();

External.php (model):

class External extends Model
    public function orders()
    {
        return $this->hasMany(Order::class);
    }
}

Order.php (Model):

public function external()
{
    return $this->belongsTo(External::class);
}

Error Message:

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (homestead.external, CONSTRAINT external_id_foreign FOREIGN KEY (id) REFERENCES order (order_id)) (SQL: insert into external (site_order_id, order_status,...

2
laraveleloquent

2 Answers

1
votes

The error is a common one, and easy to explain. Your Laravel code is generating one (or more) insert which is referring to a record in the order table which does not exist. Try running the following query:

SELECT o.*
FROM [order] o
WHERE order_id = <external.id value here>

If you get back an empty result set, then it means your insert is referring to data in order which does not exist.

0
votes

Edited (External Table Migration):

$table->unsignedBigInteger('id')->nullable();
$table->foreign('id')->references('order_id')->on('order');

Edited (Order Table Migration):

$table->increments('id');
$table->unsignedBigInteger('order_id')->index()->unsigned();