Круговая зависимость mysql во внешних ограничениях ключа

С учетом схемы:

Мне нужно иметь каждую ссылку user_identities.belongs_to a users.id.

В то же время каждый users имеет primary_identity, как показано на рисунке.

Однако, когда я пытаюсь добавить эту ссылку с помощью ON DELETE NO ACTION ON UPDATE NO ACTION, MySQL говорит

# 1452 - Невозможно добавить или обновить дочернюю строку: сбой внешнего ключа (yap. #sql-a3b_1bf, CONSTRAINT #sql-a3b_1bf_ibfk_1 ИНОСТРАННЫЙ КЛЮЧ (belongs_to) ССЫЛКИ users (id) ON DELETE NO ACTION ON UPDATE NO ACTION)

Я подозреваю, что это связано с циклической зависимостью, но как я могу ее решить ( и поддерживать ссылочную целостность)?

Ответ 1

Единственный способ решить эту проблему (по крайней мере, с ограниченными возможностями MySQL), чтобы позволить значения NULL в обоих столбцах FK. Создание нового пользователя с первичным идентификатором будет выглядеть примерно так:

insert into users (id, primary_identity)
values (1, null);

insert into identities (id, name, belongs_to)
values (1, 'foobar', 1);

update users 
  set primary_identity = 1
where id = 1;

commit;

Единственным недостатком этого решения является то, что вы не можете заставить пользователя иметь первичный идентификатор (потому что столбец должен быть нулевым).

^{Другим вариантом было бы перейти на СУБД, поддерживающую отложенные ограничения, тогда вы можете просто вставить две строки, и ограничение будет проверяться только в момент фиксации. Или используйте СУБД, где вы можете иметь частичный индекс, тогда вы можете использовать решение с столбцом is_primary}

Ответ 2

Я бы не реализовал его таким образом.

Удалите поле primary_identity из таблицы users и добавьте дополнительное поле в таблицу user_profiles, называемое is_primary, и используйте это скорее как индикатор первичного профиля

Ответ 3

Это предотвратит наличие NULL для FKs, но при этом не будет применяться для основного профиля, который должен управляться приложением.

Обратите внимание на альтернативный ключ (уникальный индекс) {UserID, ProfileID} на Profile таблицу и соответствующий FK на PrimaryProfile.

Ответ 4

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

SET foreign_key_checks = 0;
INSERT <user>
INSERT <user identity>
SET foreign_key_checks = 1;

Сделайте транзакцию и передайте ее все сразу. Я не пробовал, но он работает для удаления, поэтому я не знаю, почему он не будет работать для вставок.

Ответ 5

Я не использовал его, но вы можете попробовать INSERT IGNORE. Я бы сделал два из них: по одному для каждой таблицы, так что, как только они будут сделаны, ссылочная целостность - это maintaing. Если вы делаете их в транзакции, вы можете откат, если есть проблема с вставкой второго.

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

Спасибо @Mihai за то, что он указал на проблему с вышесказанным. Другим подходом было бы отключить ограничения, в то время как вы вставляете их, и впоследствии их повторно активировать. Однако на большой таблице, которая может привести к большему количеству накладных расходов, чем приемлемо - попробуйте?

Ответ 6

Проблема заключается в том, что вы пытаетесь сохранить основную идентификационную информацию в таблице user_identities.

Вместо этого я предлагаю вам поместить основную информацию пользователя (имя/адрес электронной почты) в таблицу users. Не используйте внешний ключ для таблицы user_identities.

Только внешний ключ из таблицы user_identities

Все ограничения теперь будут работать нормально, поскольку они являются только одним способом.

user_identities не может быть введено, если основной пользователь (в пользователях таблицы) не присутствует. Точно так же основной пользователь не должен быть удалён, если существуют существующие дочерние тождества (в user_identities).

Возможно, вы захотите изменить имя таблиц на "primary_users" и "secondary_users", чтобы было очевидно, что происходит.

Звучит ли это нормально?