MySQL AutoIncrement & ForeignKey

[wsv]

MySQL AutoIncrement & ForeignKey

Всем привет.
Перешарил интернет, ответа не нашел...

Есть таблица Т1. В ней лежат записи типа ID,NAME. Здесь ID - автоинкрементное поле. Запись с ID равным 0 добавлена вручную - для последующих ссылок на неё из Т2 по ForeignKey (дефолтное значение).

Делаем бэкап таблицы Т1, получаем скрипт с определением и данными в виде инсертов.
Например:
INSERT INTO Т1 (ID,NAME) VALUES
(0,'Не определено'),
(1,'Имя1'),........ и т.д.

При выполнении такого инсерта в чистую таблицу T1 - вставляются записи с ID=1,2,3..... А нулевая встает последней, УЖЕ С ID равным автоинкременту.

КАК БОРОТЬСЯ в таких случаях?

Дело в том, что после заполнения Т1 необходимо сразу же, скрипто заполнить Т2, в которой ForeignKey к Т1 с дефолтным значением 0.

Т.е. ход выполнения такой:

INSERT INTO Т1 (ID,NAME) VALUES
(0,'Не определно'),
(1,'Имя1'),........ и т.д.


INSERT INTO Т2 (ID,ID_NAME) VALUES
(0,0),
(1,1),........ и т.д.

Здесь и есть ошибка заполнения Т2, т.к. бывшая нулевая запись вставилась не с нулевым номером...

Если баян, прошу не пинать, а ткнуть пальцем, где лежит.

Спасибо.

 

[Mols]

Не надо в Т2 юзать 0 Используйте NULL Он создан именно для того ,что "не определено"

 

[wsv]

Автор оригинала: Mols
Не надо в Т2 юзать 0 Используйте NULL Он создан именно для того ,что "не определено"

Суть вопроса в целом:

1. Создать нужные таблицы Т1 и Т2 в первой БД - предположим у разработчика.
2. Заполнить их, данными (первую заполняем Т1, потом Т2, используя Т1).
3. Сделать скрипт обновления второй БД.
4. Накатить этот скрипт на вторую БД.


Вот как раз на 4 шаге и возникает необходимость иметь скрипт с сохранением ссылочной целостности и самими данными. Хорошо, мы его имеем.

Но он перестает выполняться на заполнении Т2, т.к. в Т1 НЕТ нулевой записи.

Если в скрипте заполнения Т1 делать инсерты типа (null, 'не определено'), то тогда потерятся ссылочная целостность. То же самое - для Т2.

-~{}~ 26.06.09 11:18:

Например в FireBird можно было накорякать триггер, который проверял ID при вставке в таблицу.

Типа: if new.id is null then gen_id(генератор, шаг), т.е. генерируем ID только в том случае, если пришёл null.

И этот триггер работал независимо - скриптом вставляешь или вручную. А если приходил ID равный 0, то никаких проблем не было.

-~{}~ 26.06.09 11:39:

Как временное решение проблемы нашел следующее:

В определении таблицы Т1 есть значение автоинкремента, которое накопилось пока разработчик заполнял данные в Т1.

Например:
CREATE TABLE `Т1` (
`ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`NAME` varchar(100) NOT NULL
PRIMARY KEY (`ID`),
KEY `Index_2` (`ID`,`NAME`)
) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=latin1;

Здесь это значение 41.


Далее, нужно выполнить инсерты. Например:

INSERT INTO `Т1` (`ID`,`NAME`) VALUES
(0,'не определено'),
(9,'Имя 1'),
(10,'Имя 2'),
(11,'Имя 3')......... и т.д.

После заполнения таблицы нужно сделать
UPDATE Т1 set id=0 where id=41 - это значение автоинкремента из определения таблицы.


В случае с таблицей с нулевой записью "не определено", это работает, т.к. именно с этим ID=41 вставляется нулевая запись.

 

[Beavis]

в таблице T1 вообще не должно быть записи "не определено"!
а в T2 в значении внешней ссылки должен быть NULL, который и означает "не определено"

 

[wsv]

Автор оригинала: Beavis
в таблице T1 вообще не должно быть записи "не определено"!
а в T2 в значении внешней ссылки должен быть NULL, который и означает "не определено"

Я согласен с этим... Для мускула никаких проблем нет и с точки зрения БД все правильно...

Однако, создавая далее например View по таблицам Т1 и Т2 например:

select
`t1`.`ID` AS `id_name`,
`t1`.`NAME` AS `name`,
`t2`.`ID` AS `id`,
`t2`.`N` AS `n`
from `t2` join `t1` on `t1`.`ID` = `t2`.`ID_NAME`

- записей с Null-ForeignKey ессно нет, и это понятно, т.к. тип джоина для этого случае не верный.
Меняя join на left join - записи получим, но тут подлый юзер заявляет - "ПАЧИМУ у меня поля пустые. Надо чтобы было написано "не определено".

Итого имеем две проблемы:
1. Необходимо переделать все представления на нужные джоины
2. Проблема с гадким юзером по поводу отображения "не определено" для тех записей, для которых реально не определено...

Вот.

-~{}~ 26.06.09 12:31:

Как быть-то?

 

[Mols]

1. Да, надо менять.
2. А в чем проблема при выводе данных менять NULL на "не определено"? Если уж очень хочется, чтобы именно мускл возвращал "не определено" - можно посмотреть в сторону COALESCE

 

[wsv]

Решение проблемы очень простое.

SET SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO';

По умолчанию - ставится на сессию.