Как удалить все строки из родительской таблицы на которые не действуют ограничения внешнего ключа?

Question

CREATE TABLE "method" (
    "id"    INTEGER,
    "name"  TEXT NOT NULL UNIQUE,
    PRIMARY KEY("id" AUTOINCREMENT)
);

CREATE TABLE "signal" (
    "id"    INTEGER,
    "name"  TEXT NOT NULL UNIQUE,
    "method_id" INTEGER,
    PRIMARY KEY("id" AUTOINCREMENT),
    FOREIGN KEY("method_id") REFERENCES "method"("id") ON UPDATE RESTRICT ON DELETE RESTRICT
);

INSERT INTO "main"."method"("id","name") VALUES (NULL,'method1');
INSERT INTO "main"."method"("id","name") VALUES (NULL,'method2');
INSERT INTO "main"."method"("id","name") VALUES (NULL,'method3');

INSERT INTO "main"."signal"("id","name","method_id") VALUES (NULL,'signal1',1);

Нужно из таблицы method удалить все методы, которые можно удалить без ошибки ограничения внешнего ключа (method2 и method3). Основная трудность здесь заключается в том, что нельзя явно ссылаться на таблицы, которые объявляют внешний ключ. То есть, при написании запроса удаления метода абсолютно неизвестно какие таблицы ссылаются на метод и в каких количествах. Следующий запрос явно не решает проблему, так как напрямую ссылается на таблицу сигналов:

DELETE FROM method WHERE NOT EXISTS (
    SELECT name FROM signal WHERE method_id=method.id
)

Предполагаемые методы решения:

Возможно, есть какая-нибудь возможность продолжать удаление методов игнорируя те методы, при попытке удалить которые произошла ошибка.
Возможно, в sqlite3 есть какая-нибудь скрытая системная таблица или функция, которую можно использовать в условии для определения того, ссылается ли на метод какой-либо внешний ключ.

Answer 1

После продолжительных поисков пришёл к выводу, что подобной функциональности не существует в sqlite. Нет никакого оператора по типу DELETE OR IGNORE. А системную таблица связей, к которой можно достучаться через PRAGMA, нельзя нужным образом динамически стыковать с подзапросами.

Моя цель, в первую очередь, состояла в том, чтобы удаление всех 'свободных' методов происходило в рамках транзакции. Предпочтительно было при этом сохранить производительность и лёгкость реализации. Усидеть сразу на всех стульях не удалось.

Пришлось расставлять приоритеты. В результате я достиг нужного мне функционала с помощью транзакций и SAVEPOINT оператора. Таким образом, я последовательно и по одному удаляю нужные мне методы игнорируя ошибки. Однако, здесь явно присутствует проблема N + 1 запросов. В моём случае, это было приемлемо.

Достичь же в точности такого результата, как в вопросе, можно только через промежуточные таблицы и WHERE EXISTS операторы. Печалька.

БЛОГ НА HUSL

Как удалить все строки из родительской таблицы на которые не действуют ограничения внешнего ключа?

Ответы (1 шт):