Основные проблемы после неправильных апдейтов

Сообщение **Gorlum** » 25 ноя 2011, 12:47

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

Нам нужно добавить вот такой constraint:

ADD CONSTRAINT `FK_users_ally_tag` FOREIGN KEY (`ally_tag`) REFERENCES `{$config->db_prefix}alliance` (`ally_tag`) ON DELETE SET NULL ON UPDATE CASCADE

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

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

Типы и кодировка связываемых полей должны совпадать.
В подчиненной таблице в свойствах поля должно стоять DEFAULT NULL
Для всех связываемых полей должны существовать индексы во всех используемых таблицах
На момент связывания в подчиненной таблице НЕ ДОЛЖНО быть таких значений в связываемом поле, которых нет в главной таблице. В нашем случае (самый сложный) нужно: во-первых обновить все значения 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;");
```
Если бы в 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}});');
```
??????
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