Laravel: SQLSTATE[23000] Integrity constraint violation

The error

Illuminate\Database\QueryException

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a
child row: a foreign key constraint fails (`shop`.`orders`,
CONSTRAINT `orders_customer_id_foreign` FOREIGN KEY (`customer_id`)
REFERENCES `customers` (`id`))

Read the number, not the class

23000 is the SQL standard's code for "you broke an integrity constraint", and on its own it does not narrow anything down. The number after it does:

Everything below is about 1452. The others are different problems that happen to share a prefix, which is why searching for SQLSTATE[23000] alone returns four unrelated conversations.

What 1452 actually means

You asked to write a row whose foreign key column holds a value that has no match in the referenced table. The constraint name in the message tells you which column and which table, and it is worth reading rather than skipping: orders_customer_id_foreign is orders.customer_id referencing customers.

The causes, roughly in order of frequency

The value is null when the column is not nullable. A relation that was not loaded, a request field that was not sent, an optional() chain quietly producing null. The column takes it as "point at nothing", which is precisely what the constraint forbids.

The parent is in an uncommitted transaction. Create a customer and an order inside one transaction and it works. Create the customer in a queued job and the order in the request that dispatched it, and the job can run before the request's transaction commits - so the worker looks for a customer that does not exist yet. Laravel has a switch for exactly this:

// config/queue.php
'after_commit' => true,

The parent was deleted. Hard deletes are the obvious version. Soft deletes are the surprising one: SoftDeletes hides a row from Eloquent but leaves it in the table, so the constraint is satisfied by a record your application believes is gone - until somebody prunes.

Seeders and migrations in the wrong order. Children seeded before parents. The fix is ordering, not disabling checks.

The types do not match. A foreignId() column is an unsigned bigint. A parent whose id is a plain integer, or a uuid column with a different collation, will refuse values that look identical in a client.

// the parent
$table->id();                    // bigint unsigned
 
// the child - matches
$table->foreignId('customer_id')->constrained();
 
// the child - does not
$table->integer('customer_id');

Diagnosing it in one query

Before changing any code, ask the database which rows would fail:

SELECT o.id, o.customer_id
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
WHERE o.customer_id IS NOT NULL AND c.id IS NULL;

Empty result and you are looking at a timing problem - a transaction or a job that ran too early. Rows come back and you have orphans already, and the constraint is the first thing that has been honest with you about it.

Handling it properly in the application

Catch it where you can say something useful, and let it through where you cannot:

try {
    $order->save();
} catch (QueryException $e) {
    if ($e->errorInfo[1] === 1452) {
        throw ValidationException::withMessages([
            'customer_id' => 'That customer no longer exists.',
        ]);
    }
 
    throw $e;
}

Validating that the parent exists before the write is better still - Rule::exists() turns a 500 into a field error - but keep the catch as well. Validation checks a moment in time; the constraint checks the moment of the write, and between those two moments somebody else can delete the row.

A customer_id that arrived through create($request->all()) raises the mass assignment exception when the column is guarded, and this one when it is not. A foreign key pointing at a table the migrations have not created yet fails as a missing table. And a child row whose parent is already gone turns up later, in a template, as a property read on null.

Where the constraint is missing altogether, adding one to a table with existing data means deciding what to do with the rows that would violate it. A soft-deleted parent is a third case: the constraint is satisfied and the application is still wrong, which is part of what soft deletes cost.