Здравствуйте.
Я считаю, что разработчику всегда стоит иметь представление о том, как работают низлежащие абстракции. Хотя бы потому, что часто абстракции бывают протекающими.
А веду я это к тому, что когда фреймворк вываливает стектрейс с несколькими вложенными (и, часто, для непосвященных непонятными) исключениями, то бывает непонятно, что с этим делать, особенно, если не знаешь, как реализована абстракция.
Иногда бывает нужно хранить двунаправленные связи между таблицами. Например, есть игрок и есть его статистика и нужно, чтобы каждая из сущностей знала про другую.
Вопрос - каким образом вставлять в базу данных такие сущности?
При вставке в таблицу в Player нам нужно уже иметь запись в таблице Statistics, а для вставки в таблицу Statistics нужно знать идентификатор игрока, к которому относится эта запись.
Выглядит замкнутым кругом, но решение есть.
Во-первых, вставки в обе таблицы должны происходить в рамках одной транзакции (как я уже писал, транзакция обеспечивает перевод базы данных из одного непротиворечащего состояния в другое непротиворечащее состояние).
К сожалению, этого недостаточно:
BEGIN TRANSACTION;
INSERT INTO "Player" VALUES (10, 20);
INSERT INTO "Statistics" VALUES (20, 10);
COMMIT;
выдаёт следующую ошибку:
ERROR: insert or update on table "Player" violates foreign key constraint "statisticsId"
SQL state: 23503
Detail: Key (statisticsId)=(20) is not present in table "Statistics".
И снова кажется, что замкнутый круг не разорвать
Но решение всё же есть.
Многие базы данных позволяют нарушать консистентность базы данных внутри транзакции. В данном случае можно “попросить” базу данных отложить проверку ссылочной целостности. На примере PostgreSQL рассмотрим, как это работает.
begin transaction;
set constraints all deferred; -- 1
INSERT INTO "Player" VALUES (10, 20);
INSERT INTO "Statistics" VALUES (20, 10);
set constraints all immediate; -- 2
commit; -- 3
1 - Сделать все ограничения отложенными. Ограничения будут проверены в момент проведения фиксации транзакции (в строке 3)
2 - Явно делаем все ограничения немедленными. В данном примере это необязательно, так как в 3) они всё равно будут проверены.
После выполнения данного кода получаем:
Query returned successfully with no result in 24 ms.
Это уже полностью рабочий пример, чтобы это заработало нужно явно сделать ограничения отложенными - они должны быть объявлены как DEFERRED. Официальная документация к PostgreSQL гласит:
Upon creation, a constraint is given one of three characteristics: DEFERRABLE INITIALLY DEFERRED, DEFERRABLE INITIALLY IMMEDIATE, or NOT DEFERRABLE. The third class is always IMMEDIATE and is not affected by the SET CONSTRAINTS command. The first two classes start every transaction in the indicated mode, but their behavior can be changed within a transaction by SET CONSTRAINTS.
Так что необходимо также модифицировать объявления ограничений:
ALTER TABLE "Player"
ADD CONSTRAINT "statisticsId" FOREIGN KEY ("statisticsId")
REFERENCES "Statistics" ("statisticsId") MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE;
Данный пример был приведён для PostgreSQL.
Для других баз данных решения следующие:
В Sybase есть переменная WAIT_FOR_COMMIT, которая управляет поведением проверки ограничений. По умолчанию она отключена. Кроме того, это поведение можно переопределить при объявлении можно указывать CHECK ON COMMIT. Подробнее можно посмотреть в официальной документации.
Также, как и в PostgreSQL:
You can define constraints as either deferrable or not deferrable, and either initially deferred or initially immediate. These attributes can be different for each constraint. You specify them with keywords in the CONSTRAINT clause: DEFERRABLE or NOT DEFERRABLE INITIALLY DEFERRED or INITIALLY IMMEDIATE
Constraints can be added, dropped, enabled, disabled, or validated. You can also modify a constraint’s attributes.
Подробнее в официльной документации.
К сожалению, mySQL не имеет возможности отложенной проверки ограничении, о чём в документации и сказано:
Deviation from SQL standards: Like MySQL in general, in an SQL statement that inserts, deletes, or updates many rows, InnoDB checks UNIQUE and FOREIGN KEY constraints row-by-row. According to the SQL standard, the default behavior should be deferred checking. That is, constraints are only checked after the entire SQL statement has been processed. Until InnoDB implements deferred constraint checking, some things will be impossible, such as deleting a record that refers to itself via a foreign key.
Но это не конец света, так как можно отключить проверку внешних ключей:
SET foreign_key_checks = 0;
-- code goes here
SET foreign_key_checks = 1;
Чем эта информация может быть полезна для разработчика? Например, в случае, если ошибки и недоработки Hibernate покажут себя в виде стектрейса в логе(например, HHH-2248), то разработчик будет понимать причину ошибки и то, как реализовать work-around.