Как отключить проверку внешних ключей в MySQL и Laravel

Введение

С ошибкой внешнего ключа MySQL обычно сталкиваются в одних и тех же ситуациях: нужно удалить пользователя, за которым числятся заказы, очистить через TRUNCATE таблицу-справочник, залить дамп или прогнать сидер Laravel, который перед заполнением чистит таблицы. Быстрее всего временно выключить проверку, но за эту скорость потом можно заплатить целостностью данных.

В статье разобраны коды ошибок, случаи, когда отключение вовсе не нужно, способы отключить проверку в MySQL и в Laravel, а также причины, по которым тот же код иначе работает в SQLite и PostgreSQL.

Коды ошибок внешних ключей в MySQL

Самое полезное в сообщении — часть в скобках: там указаны имя ограничения и таблицы, между которыми оно действует, так что сразу понятно, какая связь не пускает запрос. Скобок может и не быть: если у пользователя нет прав на родительские таблицы, MySQL прячет подробности. Документация связывает такой случай с короткими ошибками 1216/1217, но MySQL 8.4 на практике возвращает те же 1451 и 1452, только с урезанным текстом. Код зависит и от версии сервера: DROP TABLE родительской таблицы в MySQL 5.7 завершается ошибкой 1217, в MySQL 8.0 и новее — 3730, а в MariaDB — 1451.

Нужно ли вообще отключать проверку

Внешний ключ для того и существует, чтобы в базе не появлялись ссылки в никуда. Поэтому ошибка чаще всего сигнализирует, что операции выполняются не в том порядке, а вовсе не о том, что ограничение лишнее. До отключения проверки полезно перебрать другие решения:

• удалять в обратном порядке: сначала дочерние записи (заказы), затем родительскую (пользователя);
• заложить поведение в саму схему — ON DELETE CASCADE или ON DELETE SET NULL, в Laravel это ->cascadeOnDelete() и ->nullOnDelete();
• чистить таблицу командой DELETE, а не TRUNCATE: первая выполняет правила ON DELETE, вторая для таблицы, на которую есть ссылки, просто запрещена;
• если нужно разово поправить данные, хватит обычного UPDATE (пример — в статье как заменить слово в таблице через REPLACE()).

Schema::create('orders', function (Blueprint $table) {
    $table->id();
    // вместе с пользователем удалятся и его заказы
    $table->foreignId('user_id')->constrained()->cascadeOnDelete();
    // при удалении купона ссылка в заказе станет NULL
    $table->foreignId('coupon_id')->nullable()->constrained()->nullOnDelete();
    $table->timestamps();
});

Когда связи описаны в схеме, удаление пользователя проходит без ошибок и без ручного порядка. Отключать проверку имеет смысл там, где целостность восстанавливается сразу же: в сидерах, тестах, при импорте дампов и в разовых сервисных операциях.

Отключение проверки в MySQL

За проверку отвечает системная переменная foreign_key_checks; по умолчанию она включена.

SET FOREIGN_KEY_CHECKS = 0;

TRUNCATE TABLE orders;
TRUNCATE TABLE users;

SET FOREIGN_KEY_CHECKS = 1;

Три момента, о которых стоит помнить:

• Без модификатора SET FOREIGN_KEY_CHECKS меняет сессионную переменную, то есть действует только в текущем соединении. Для остальных клиентов и параллельных запросов приложения проверка остаётся включённой.
• SET GLOBAL FOREIGN_KEY_CHECKS = 0 выключает проверку для всех новых соединений к серверу. На рабочей базе этого делать нельзя.
• Когда проверку включают обратно, MySQL не проходит заново по строкам, записанным без неё. Если в orders за это время попал user_id несуществующего пользователя, запись так и останется в таблице — ни ошибки, ни предупреждения не будет.

У дампов от mysqldump порядок таблиц при импорте не важен: отключение проверки уже прописано в начале файла:

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

В конце дампа прежнее значение возвращается из @OLD_FOREIGN_KEY_CHECKS. Если файл сделан другим инструментом или собран вручную, строку SET FOREIGN_KEY_CHECKS = 0; в его начало нужно дописать самому.

Поиск «висячих» записей

Поскольку при включении MySQL данные не перепроверяет, после любых операций без проверки целостность ссылок приходится подтверждать вручную. Дочерние строки, у которых пропал родитель, ищут через LEFT JOIN:

-- заказы, ссылающиеся на несуществующего пользователя
SELECT o.id, o.user_id
FROM orders AS o
LEFT JOIN users AS u ON u.id = o.user_id
WHERE o.user_id IS NOT NULL
  AND u.id IS NULL;

С найденными строками два пути: удалить их или перепривязать к существующему родителю. Такой запрос выполняют по каждой связи, которую могла затронуть работа без проверки.

Laravel: сидеры и truncate()

Писать SQL под конкретную СУБД в Laravel не приходится: фасад Schema предоставляет методы disableForeignKeyConstraints() и enableForeignKeyConstraints(). Но удобнее и безопаснее вариант с замыканием, withoutForeignKeyConstraints(). Он возвращает то, что вернуло замыкание, а проверку включает в блоке finally — значит, она восстановится, даже если внутри вылетит исключение.

<?php

namespace Database\Seeders;

use App\Models\Order;
use App\Models\User;
use Illuminate\Database\Seeder;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

class ShopSeeder extends Seeder
{
    public function run(): void
    {
        Schema::withoutForeignKeyConstraints(function (): void {
            // с включённой проверкой здесь упали бы с ошибкой 1701
            DB::table('order_items')->truncate();
            Order::truncate();
            User::truncate();
        });

        User::factory(10)->has(Order::factory()->count(3))->create();
    }
}

Все эти методы работают с соединением по умолчанию. Для таблиц в другой базе соединение задают явно — Schema::connection('warehouse')->withoutForeignKeyConstraints(…), — и запросы внутри замыкания тоже отправляют через DB::connection('warehouse'). Отключение касается только того соединения, в котором выполнено, и на соседнее не распространяется.

В интернете часто советуют «сырой» запрос:

// только для MySQL/MariaDB; если случится исключение, проверка останется выключенной
DB::statement('SET FOREIGN_KEY_CHECKS=0');
User::truncate();
DB::statement('SET FOREIGN_KEY_CHECKS=1');

У него два недостатка. Код намертво привязан к MySQL, а ещё он наследует слабое место ручных disable/enable: стоит truncate() упасть, и соединение до конца процесса проработает без проверки.

Копирование записей вместе со связями, кстати, не требует отключения вовсе — подробности в статье «Дублирование и клонирование записей с Laravel».

Миграции

В миграции отключение бывает нужно, когда пересоздают таблицу, на которую ссылаются другие, или переносят данные из одной таблицы в другую:

<?php

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

return new class extends Migration
{
    public function up(): void
    {
        Schema::withoutForeignKeyConstraints(function (): void {
            // на categories ссылается products, поэтому с проверкой DROP не выполнится
            Schema::drop('categories');

            Schema::create('categories', function (Blueprint $table) {
                $table->id();
                $table->string('title');
                $table->string('slug')->unique();
            });
        });
    }
};

Новая таблица обязана подходить под ссылающиеся на неё ключи: те же имена и типы столбцов, индекс на ключевом столбце. Иначе MySQL не создаст её даже с выключенной проверкой — вернёт ошибку 1005 с errno: 150 в тексте.

Такой приём — обходной. Как правило, чище изменить порядок операций, на время удалить внешний ключ через $table->dropForeign([...]) и затем создать его снова или поменять таблицу через Schema::table(), не пересоздавая её.

Как это работает в SQLite и PostgreSQL

Для каждой СУБД Schema::withoutForeignKeyConstraints() генерирует свой SQL, и эффект у него сильно отличается.

SQLite

Laravel по умолчанию включает внешние ключи SQLite: у соединения sqlite в config/database.php указано 'foreign_key_constraints' => env('DB_FOREIGN_KEYS', true). Выключаются они прагмой PRAGMA foreign_keys, а документация SQLite прямо говорит, что внутри транзакции эта прагма ни на что не влияет.

Именно на этом часто спотыкаются в тестах. RefreshDatabase заворачивает каждый тест в транзакцию, так что Schema::withoutForeignKeyConstraints() или $this->seed(ShopSeeder::class) внутри теста проверку не снимают, и truncate() (для SQLite это DELETE) заканчивается ошибкой FOREIGN KEY constraint failed. Когда внешние ключи в тестах не нужны, их отключают в phpunit.xml:

<env name="DB_FOREIGN_KEYS" value="false"/>

Минус такого решения — тесты перестанут замечать ошибки связей, которые потом всплывут на MySQL. Обычно лучше переделать сидер, чтобы он удалял данные в правильной последовательности.

PostgreSQL

SET CONSTRAINTS ALL DEFERRED переносит проверку на конец транзакции, причём касается это только ограничений, объявленных DEFERRABLE. Внешние ключи по умолчанию создаются как NOT DEFERRABLE — Laravel делает так же, — и на них команда не влияет. А вне транзакции она не работает совсем: PostgreSQL ограничится предупреждением.

Есть и обратная проблема: truncate() в PostgreSQL, как правило, выполняется и без всякого отключения — и это отдельный риск. Laravel превращает его в TRUNCATE … RESTART IDENTITY CASCADE, а CASCADE очищает все таблицы, которые ссылаются на очищаемую. Вызов User::truncate() сотрёт заодно заказы, комментарии и прочие данные, связанные с пользователями. Каскад отключают глобально, например в AppServiceProvider::boot():

use Illuminate\Database\Query\Grammars\PostgresGrammar;

// теперь truncate() таблицы, на которую ссылаются, выбросит ошибку, а не очистит зависимые
PostgresGrammar::cascadeOnTruncate(false);

Для SQL Server в Laravel есть собственный механизм отключения через sp_msforeachtable со своими нюансами, но в этой статье он не разбирается.

Итог

• За ошибками 1451, 1452, 1217 и 1701 чаще стоит неправильный порядок операций. Первым делом стоит проверить последовательность удаления, cascadeOnDelete() / nullOnDelete() и замену TRUNCATE на DELETE.
• SET FOREIGN_KEY_CHECKS = 0 затрагивает только текущее соединение; SET GLOBAL и выключенная проверка на рабочей базе недопустимы.
• Включив проверку обратно, MySQL старые данные не перепроверяет — «висячие» записи находят запросом с LEFT JOIN.
• В Laravel используют Schema::withoutForeignKeyConstraints(): проверка вернётся, даже если замыкание выбросит исключение.
• В SQLite отключение бессильно внутри транзакции, в PostgreSQL оно не действует на обычные внешние ключи, а truncate() там каскадом чистит зависимые таблицы.

Похожие

• Как заменить слово в таблице через REPLACE()
• Подключение Laravel к MySQL в Yandex Cloud
• Дублирование и клонирование записей с Laravel
• Все шпаргалки по MySQL