Laravel Migrations: Add Foreign Keys Without Locking

Part 1 covered indexes and Part 2 covered columns. The next Laravel migrations most teams get wrong are the ones that tie two tables together: foreign keys and check constraints.

A foreign key looks like metadata — a rule the database enforces, not data it stores. But adding one to an existing table means scanning every row in the child table to prove the rule already holds, and that scan happens under a lock. On a busy orders table referencing a busy customers table, that's two tables at risk from one migration.

Schema::table('orders', function (Blueprint $table) {
    $table->foreignId('customer_id')->constrained();
});

This article covers what that migration actually does on MySQL and PostgreSQL, the two-step pattern that avoids blocking writes, and the cascading-delete mistake that has nothing to do with locking at all.

PostgreSQL: a lighter lock than you'd expect, still a full scan

Adding a foreign key constraint in Postgres doesn't take the heaviest lock available. Most forms of ADD CONSTRAINT take an ACCESS EXCLUSIVE lock, but ADD FOREIGN KEY specifically only requires SHARE ROW EXCLUSIVE — and it takes that same lock on both tables, the one gaining the constraint and the one it references.

SHARE ROW EXCLUSIVE still blocks every INSERT, UPDATE, and DELETE on both tables for the duration of the scan, even though it's lighter than ACCESS EXCLUSIVE. On a large orders table, that scan is the whole problem: Postgres has to walk every existing row to confirm it satisfies the new constraint before it can finish adding it.

The fix is the same shape as the NOT NULL pattern from Part 2: split the constraint from its validation.

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;

return new class extends Migration
{
    public function up(): void
    {
        // Step 1: add the constraint without checking existing rows.
        // Still takes SHARE ROW EXCLUSIVE, but only briefly — no scan.
        DB::statement('
            ALTER TABLE orders
            ADD CONSTRAINT orders_customer_id_foreign
            FOREIGN KEY (customer_id) REFERENCES customers (id)
            NOT VALID
        ');

        // Step 2: validate separately. This does the actual scan,
        // but only takes SHARE UPDATE EXCLUSIVE — it does not block writes.
        DB::statement('ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_id_foreign');
    }

    public function down(): void
    {
        DB::statement('ALTER TABLE orders DROP CONSTRAINT orders_customer_id_foreign');
    }
};

Run step 1 as part of your normal deploy — it's fast. Run step 2 separately, during a quiet period if the table is large enough that the scan takes real time, since it's safe to run any time after step 1 without blocking the app.

Laravel's schema builder doesn't currently expose a fluent NOT VALID modifier for foreign keys the way it does online() for indexes, so this one stays as raw DB::statement() calls regardless of Laravel version.

MySQL: online by default, with one setting you'll likely need

MySQL's story here is better news. Adding a foreign key on InnoDB can use the same ALGORITHM=INPLACE, LOCK=NONE online DDL from Part 1:

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up(): void
    {
        if (Schema::hasColumn('orders', 'customer_id') && $this->hasForeignKey()) {
            return;
        }

        DB::statement('SET SESSION lock_wait_timeout = 5');

        // Adding a foreign key needs foreign_key_checks off for this
        // session, or ALGORITHM=INPLACE is rejected outright.
        DB::statement('SET SESSION foreign_key_checks = 0');

        try {
            DB::statement('
                ALTER TABLE orders
                ADD CONSTRAINT orders_customer_id_foreign
                FOREIGN KEY (customer_id) REFERENCES customers (id),
                ALGORITHM=INPLACE, LOCK=NONE
            ');
        } finally {
            DB::statement('SET SESSION foreign_key_checks = 1');
            DB::statement('SET SESSION lock_wait_timeout = DEFAULT');
        }
    }

    public function down(): void
    {
        DB::statement('
            ALTER TABLE orders
            DROP FOREIGN KEY orders_customer_id_foreign,
            ALGORITHM=INPLACE, LOCK=NONE
        ');
    }

    private function hasForeignKey(): bool
    {
        return DB::table('information_schema.table_constraints')
            ->where('table_schema', DB::getDatabaseName())
            ->where('table_name', 'orders')
            ->where('constraint_name', 'orders_customer_id_foreign')
            ->exists();
    }
};

The foreign_key_checks = 0 line is the part most examples skip. Without it, MySQL rejects ALGORITHM=INPLACE on a foreign key addition outright with an error, because validating referential integrity against existing rows is exactly the check that algorithm normally skips doing inline. Turning checks off for the session lets the in-place algorithm proceed; MySQL still builds the constraint correctly, it just doesn't re-verify every existing row against it in the same blocking way a COPY-algorithm rebuild would.

Setting foreign_key_checks = 0 is a session-level setting in the code above, not a global one — it only affects the connection running this migration, not concurrent queries from your application.

The metadata lock trap from Part 1 applies here too, and arguably matters more: a foreign key's ALTER has to coordinate with two tables, so a long-running transaction on either orders or customers can stall it.

Check constraints: the same two-step pattern

Check constraints follow Postgres's general rule rather than the foreign-key exception: adding one with ADD CONSTRAINT ... CHECK (...) takes a full ACCESS EXCLUSIVE lock if you validate it immediately, because by default Postgres scans the table to confirm every row passes. NOT VALID works here too, and the same two-step split applies:

-- Step 1: instant, no scan.
ALTER TABLE orders
ADD CONSTRAINT orders_total_positive
CHECK (total >= 0) NOT VALID;

-- Step 2: does the scan, takes SHARE UPDATE EXCLUSIVE only.
ALTER TABLE orders VALIDATE CONSTRAINT orders_total_positive;

This is the same mechanism Part 2 used to add NOT NULL to an existing column without a blocking scan — a CHECK (col IS NOT NULL) constraint, validated separately, then promoted. Foreign keys, check constraints, and NOT NULL all converge on the same underlying trick in Postgres: separate "define the rule" from "prove existing data follows it."

The gotcha that isn't about locking: cascading deletes on hot tables

Once the foreign key exists, onDelete('cascade') changes what a single DELETE does at runtime, every time, for the life of the constraint:

Schema::table('orders', function (Blueprint $table) {
    $table->foreignId('customer_id')
        ->constrained()
        ->onDelete('cascade');
});

Deleting one customer with 50,000 historical orders now deletes 50,001 rows in a single transaction, each one individually checked and logged. On MySQL that's one long-held lock on the orders table for the whole cascade; on Postgres it's one long transaction that can block autovacuum and hold back replication while it runs. Neither database warns you about this at migration time — the constraint itself is cheap to add, and the cost only shows up the first time someone deletes a parent row with a lot of children.

For high-volume child tables, onDelete('restrict') (the default) paired with an explicit, chunked application-level delete is usually safer than cascade, precisely because it puts the pacing under your control instead of the database's.

Quick reference

Operation	Lock taken	Safe pattern
Add FK (PostgreSQL)	SHARE ROW EXCLUSIVE on both tables, held for the scan	NOT VALID, then VALIDATE CONSTRAINT separately
Add CHECK constraint (PostgreSQL)	ACCESS EXCLUSIVE if validated immediately	NOT VALID, then VALIDATE CONSTRAINT separately
Add FK (MySQL/InnoDB)	None, with ALGORITHM=INPLACE, LOCK=NONE	Requires foreign_key_checks=0 for the session
onDelete('cascade') on a hot table	Long-held lock/transaction at delete time, not add time	Prefer restrict() + chunked application deletes

Before you run it in production

Run php artisan migrate --pretend and confirm NOT VALID or ALGORITHM=INPLACE, LOCK=NONE appears in the SQL. On Postgres, run the VALIDATE CONSTRAINT step as its own migration so you control when the table scan happens. On MySQL, remember foreign_key_checks = 0 is required for the in-place algorithm to be accepted at all. Before adding onDelete('cascade') to a high-volume relationship, ask what a single parent delete will actually do to the child table at 2am, not just whether the migration runs cleanly.

Related reading: this builds on Part 1: Adding Indexes to Large Tables in Laravel Without Locking Them and Part 2: Add & Change Columns Without Downtime.

---

Originally published on DEV Talk.