Супернова / Сверхновая - Форумы

Решение проблемы с констраинтами на примере таблицы users и поля ally_tag

Нам нужно добавить вот такой constraint: Он связывает поле ally_tag таблицы users (подчиненная) с полем ally_tag таблицы alliance (главная). Когда поле ally_tag изменяется в таблице alliance, то связанное поле автоматически изменяется в таблице users. Если запись удаляется из таблицы users - поле ally_tag d соответствующей записи удаляется из таблицы users.

Что бы можно было сделать такую связь, нужна логическая целостность обоих таблиц. Это означает, что

1. Типы и кодировка связываемых полей должны совпадать.
2. В подчиненной таблице в свойствах поля должно стоять DEFAULT NULL
3. Для всех связываемых полей должны существовать индексы во всех используемых таблицах
4. На момент связывания в подчиненной таблице НЕ ДОЛЖНО быть таких значений в связываемом поле, которых нет в главной таблице. В нашем случае (самый сложный) нужно: во-первых обновить все значения ally_tag до текущих

   Код: Выделить всё

   doquery("UPDATE {{users}} AS u LEFT JOIN {{alliance}} AS a ON a.id = u.ally_id SET u.ally_tag = a.ally_tag;");

   во-вторых - для тех записей, для которых Альянсы не существуют, установить соответствующее поле в NULL

   Код: Выделить всё

   doquery("UPDATE {{users}} AS u LEFT JOIN {{alliance}} AS a ON a.id = u.ally_id SET u.ally_id = NULL, u.ally_tag = NULL WHERE a.id is NULL;");

5. Если бы в constraint стояло бы ON DELETE CASCADE, а не ON DELETE SET NULL, то нужно было бы просто удалить из подчиненной таблицы все записи, для которых нет соответствия в главной таблице. На примере constraint для таблицы shortcut:

   Код: Выделить всё

   ADD CONSTRAINT `FK_shortcut_planet_id` FOREIGN KEY (`shortcut_planet_id`) REFERENCES `{$config->db_prefix}planets` (`id`) ON DELETE CASCADE ON UPDATE CASCADE

   соответствующий запрос:

   Код: Выделить всё

   doquery('DELETE FROM {{shortcut}} WHERE shortcut_planet_id NOT IN (SELECT id FROM {{planets}});');

6. ??????
7. PROFIT!

Собственно, апдейтер сам делает все нужные изменения. НО ТОЛЬКО если ранее все апдейты проходили нормально! Если, например, ранее сбился constraint на ally_id, то апдейтер НЕ СМОЖЕТ нормально отработать constraint на ally_tag.

Мануал для курения:
http://dev.mysql.com/doc/refman/5.5/en/ ... aints.html

И совсем продвинутый мануал:
http://dev.mysql.com/doc/refman/5.5/en/constraints.html