Автоинкрементные поля - вставка пропущенный значений

← →
И. Павел © (2011-10-03 19:26) [0]

Сегодня проходил собеседования.
Столкнулся с таким вопросом: "Как вставить строку со значением автоинкрементного поля, которое было уже пройдено?" Т.е, например, текущее значение последнего инкрементного поля = 100, а нужно вставить запись с id = 10. Подскажите, пожалуйста, как это можно сделать? Сейчас гуглю - пока ничего не нашел, т.к. пока не подберу ключевые слова, а вопрос интересный.
PS: собеседование уже прошел, т.е. сейчас уже просто ищу ответы на то, что не знал.

← →
_Юрий (2011-10-03 19:30) [1]

Вероятно, это от БД зависит

← →
sniknik © (2011-10-03 19:37) [2]

"insert"-ом конечно... единственная команда которая вставляет записи в sql.

> собеседование уже прошел
а я б тебя не взял... не потому что не знаешь, все знать невозможно, а потому что глупо вопросы задаешь. так что ответ на него будет скорее всего тебе бесполезен.

← →
Омлет © (2011-10-03 19:38) [3]

Просто указываешь явное значение для поля.

← →
Anatoly Podgoretsky © (2011-10-03 19:38) [4]

> И. Павел © (03.10.11 19:26)

Это применение не по назначению.

← →
И. Павел © (2011-10-03 19:39) [5]

> _Юрий (03.10.11 19:30) [1]

извиняюсь, забыл упомянуть: MS SQL SERVER

← →
И. Павел © (2011-10-03 19:44) [6]

> sniknik © (03.10.11 19:37) [2]

Перечитал вопрос - вроде бы только название СУБД забыл, а так составил вполне правильно и пример привел. Я же не у мужика из фильма "Исполнитель желаний" спрашиваю - вот с ним действительно шутки плохи - там нужна абсолютная формализация, а то подловит :)

← →
И. Павел © (2011-10-03 19:49) [7]

> Омлет © (03.10.11 19:38) [3]
> Просто указываешь явное значение для поля.

на момент написания вами ответа, я не уточнил СУБД - MS SQL SERVER. Кажется, в ней так нельзя.

На попытку явного указания id в запросе, MS SQL SERVER выдает ошибку:

Важная ошибка 0x80004005, незначительная ошибка 25004 > INSERT INTO test(id, inf) VALUES (10, 20) Невозможно изменить столбец. [ Column name = id ]

← →
sniknik © (2011-10-03 19:50) [8]

> вроде бы только название СУБД забыл
> Сейчас гуглю - пока ничего не нашел, т.к. пока не подберу ключевые слова
в поиске тоже? т.к. ответ буквально на первой странице в первый ссылках
база операция тип поля
http://www.google.ru/search?q=mssql+insert+identity&ie=utf-8&oe=utf-8&aq=t&rls=org.mozilla:ru:official&client=firefox

← →
sniknik © (2011-10-03 19:55) [9]

> выдает ошибку:
это где? ADO выдает ближе к телу/делу.

← →
И. Павел © (2011-10-03 19:55) [10]

> sniknik © (03.10.11 19:50) [8]

Спасибо. Все просто, оказывается.

Если кто-то вдруг будет искать:
SET IDENTITY INSERT table ON

← →
И. Павел © (2011-10-03 19:56) [11]

> это где?

Эту ошибку в Management Studio 10 получил.

← →
_Юрий (2011-10-03 19:58) [12]

Вопрос из серии
"как удалить гланды через попу?"
Автор, вы уверены, что хотите работать в конторе, в которой этот метод удаления гланд применяется? (судя по тому, что вопрос вынесен на собеседование, он применяется)

← →
sniknik © (2011-10-03 20:01) [13]

> Эту ошибку в Management Studio 10 получил.
выкинь его, он неадекватен... зато конечно русифицирован как вижу... ;(
вот "оригинал"
EOleException : Cannot insert explicit value for identity column in table "test" when IDENTITY_INSERT is set to OFF

← →
Ega23 © (2011-10-03 20:15) [14]

> судя по тому, что вопрос вынесен на собеседование, он применяется

Не обязательно, это просто один из общих вопросов по знанию MSSQL.
Я таких вопросов могу напридумывать массу.
И, кстати, один раз я такое на практике таки делал.
Вообще я не понимаю, почему такой вопрос вызывает смущение? Что, в други СУБД значение генератора или сиквенса нельзя "вернуть"? Почему MSSQL в этом плане должен отличаться?
Хотите полной уникальности - используйте GUID.

← →
Ega23 © (2011-10-03 20:16) [15]

> Просто указываешь явное значение для поля.

В MSSQL нельзя, если ты identity полю указал, то его в insert вообще не перечисляешь.

← →
Игорь Шевченко © (2011-10-03 20:52) [16]

> В MSSQL нельзя, если ты identity полю указал, то его в insert
> вообще не перечисляешь.

Я извиняюсь, а получить-то его после вставки как ? Ну там в дочерние таблицы ссылку на родительскую установить.

В Oracle или в IB все как-то прозрачно - можно получить отдельно значение "автоинкремента", потом его явно указать в INSERT, ну и вуаля

← →
картман © (2011-10-03 20:53) [17]

>
> В Oracle или в IB все как-то прозрачно - можно получить
> отдельно значение "автоинкремента", потом его явно указать
> в INSERT, ну и вуаля

лишние телодвижения

← →
Игорь Шевченко © (2011-10-03 20:57) [18]

картман © (03.10.11 20:53) [17]

Че хотел сказать-та ?

← →
Ega23 © (2011-10-03 21:08) [19]

> Я извиняюсь, а получить-то его после вставки как ? Ну там
> в дочерние таблицы ссылку на родительскую установить.

Было 3 варианта, сейчас не в курсе (давно с ним не работал):
1. select @@identity
2. select scope_identity()
3. Уже не помню. Склероз.

Отличия между ними есть, в msdn подробно расписано. В большинстве случаев используется второй вариант.

Спорить о целесообразности такого дела можно до посинения, собственно, это довольно известный холивар Identity vs. Generator или Identity vs. Sequence.
Преимущества: наверное, больше чем в половине случаев "счётчик" - это всего лишь счётчик. Некий суррогатный ключ. Далеко не всегда его значение нужно для дальнейшей работы. Соответственно, не надо делать лишние телодвижения по предварительному извлечению его значения из генератора или последовательности, либо поддерживать стопицот триггеров, где это будет происходить автоматически. С другой стороны он "дисциплинирует", собственно, см. сабж, Павел вроде как совсем не новичок, однако сходу не нашёл, как это сделать. А что в FB значение генератора можно легко поменять, что в Postgres значение последовательности - раз плюнуть.
За Oracle не скажу, но подозреваю, что идеология Postgres в данном случае оттуда свистнута.

← →
Игорь Шевченко © (2011-10-03 21:14) [20]

Ega23 © (03.10.11 21:08) [19]

> Было 3 варианта

Лишние телодвижения (с) :)

> Далеко не всегда его значение нужно для дальнейшей работы.

Если что-то не нужно для дальнейшей работы, его надо выпилить.

← →
Ega23 © (2011-10-03 21:22) [21]

> Лишние телодвижения (с) :)

Они постфактум совершаются. А не до.

> Если что-то не нужно для дальнейшей работы, его надо выпилить.

Таблица "Категории товаров", таблица "Товары", таблица "Товар-Категория" (многие ко многим).
Скажи, ты таблице-отношении будешь составной первичный ключ делать, или сделаешь суррогатный ключ-счётчик + уникальный составной индекс по ТоварID + КатегорияID?
Лично я суррогатный сделаю, тем более что с очень большой долей вероятности он нигде в рамках базы использоваться не будет. В выборках и последующем построении дерева на клиенте - будет. А вот в базе - нафиг не нужен.

← →
Игорь Шевченко © (2011-10-03 21:25) [22]

> Таблица "Категории товаров", таблица "Товары", таблица "Товар-
> Категория" (многие ко многим).

Мне в такой постановке это ничего не говорит. Как используется, зачем используется, ну и т.п.

> Они постфактум совершаются. А не до.

Какая разница ?

← →
Ega23 © (2011-10-03 21:51) [23]

> Мне в такой постановке это ничего не говорит. Как используется,
> зачем используется, ну и т.п.

Ну стандартно: есть дерево категорий, есть таблица товаров. Товар может находиться в разных категориях.

> Какая разница ?

Игорь, давай этот вопрос за кружкой ируканского поднимем? Уж больно много писать нужно, не форумный формат, а целая статья получится. Разница действительно есть. Небольшая и возможно несущественная, но есть.

← →
Anatoly Podgoretsky © (2011-10-03 22:23) [24]

> я не уточнил СУБД - MS SQL SERVER. Кажется, в ней так нельзя.

Как раз наоборот, это штатная возможность, смотри в БОЛ

← →
DiamondShark © (2011-10-03 23:35) [25]

> Ega23 © (03.10.11 21:08) [19]
> 3. Уже не помню. Склероз.

Для 2005+

insert into ... values(...) output INSERTED.*

Для 2000 и ниже третьего способа нет.

> Игорь Шевченко © (03.10.11 21:25) [22]

Сиквенсы и генераторы -- лишние сущности. Мастдай.

← →
antonn © (2011-10-04 00:11) [26]

> (судя по тому, что вопрос вынесен на собеседование, он применяется)

Когда чайник закипает в нем очень горячая вода. Это "знание" необходимо отнюдь не для того чтобы сувать в чайник руку, а наоборот.
Примерно в таком направлении :)

← →
Ega23 © (2011-10-04 00:14) [27]

> Для 2005+
>
> insert into ... values(...) output INSERTED.*

Не. Вот, нашёл:
IDENT_CURRENT
http://msdn.microsoft.com/ru-ru/library/ms175098%28SQL.90%29.aspx

Для 2000 не помню, была она или нет. @@IDENTITY была ещё в 7.х, в 2000 появилось scope_identity().
IDENT_CURRENT - не знаю, BOL для 2000 под рукой нет, но завтра попробую уточнить.

← →
Ega23 © (2011-10-04 00:24) [28]

> insert into ... values(...) output INSERTED.*
>

Кстати, а что оно вернёт? Они ведь недаром разделили @@identity и scope_identity
Например, есть некая таблица с ID identity. На таблице висит insert-триггер с логгированием в таблицу, где тоже свой id identity
Если сразу после вставки в такую таблицу сделать select @id=@@identity, то в переменную @id вернётся значение id таблицы лога. Если сделать select @id=scope_identity, то вернётся значение нового id этой таблицы.

А что будет тут insert into ... values(...) output INSERTED.* ?

← →
b z (2011-10-04 00:38) [29]

> таблице-отношении будешь составной первичный ключ делать,
> или сделаешь суррогатный ключ-счётчик + уникальный составной
> индекс по ТоварID + КатегорияID?
Конечно составной. А зачем тут сурогат, чем он помогает?

> Ega23 © (04.10.11 00:14) [27]
> Не. Вот, нашёл:IDENT_CURRENT

Ну, оно никогда не считалось средством получения вставленного значения. Даже в БОЛ предупреждают:

> Be cautious about using IDENT_CURRENT to predict the next
> generated identity value. The actual generated value may
> be different from IDENT_CURRENT plus IDENTITY_SEED because
> of insertions performed by other sessions.

Ни @@IDENTITY, ни SCOPE_IDENTITY с другими сессиями не интерферируют.

> Ega23 © (04.10.11 00:24) [28]
> Кстати, а что оно вернёт?

Columns returned from OUTPUT reflect the data as it is after the INSERT, UPDATE, or DELETE statement has completed but before triggers are executed.

залочить всю таблицу
отключить автоинкремент
вставить
включить автоинкремент

> Сиквенсы и генераторы -- лишние сущности. Мастдай.

Почему?
Например, если в DB2 при создании таблицы какое-то поле создаешь GENERATED ALWAYS AS IDENTITY, то все равно в системе создается сиквенция.

на дурацкие вопросы надо давать дурацкие ответы.

вопрос был "как вставить...."
ответ : "вставлю инсертом"

напомнило этот текст: http://lleo.me/arhive/esse/ej/mikrowave.shtml

> то все равно в системе создается сиквенция.

Дык и в MSSQL тоже что-то там создаётся. В системных таблицах. Другое дело, сколько тебе, как разработчику, телодвижений надо сделать, дабы очередное значение извлечь.

← →
Вариант (2011-10-04 10:29) [36]

> DiamondShark © (03.10.11 23:35) [25]
> Сиквенсы и генераторы -- лишние сущности. Мастдай.

Кстати, я читал в MS SQL 2011 генераторы таки появились

> pavel_guzhanov © (04.10.11 09:14) [32]
> > Сиквенсы и генераторы -- лишние сущности. Мастдай.
> Почему?

Ну вот на пальцах попробую объяснить.
Представь себе, что ты пишешь такой оператор:

a := -b;

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

А теперь представь, что ты вынужден это делать вот таким образом:

a := Integer(NOT Cardinal(b) + 1);

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

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

Col1 int not null identity(1,1)

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

Когда из высокоуровневой абстракции торчат кишки, то это всегда мастдай.

← →
stone (2011-10-04 16:35) [38]

> DiamondShark © (03.10.11 23:35) [25]
>
> > Ega23 © (03.10.11 21:08) [19]
> > 3. Уже не помню. Склероз.
>
> Для 2005+
>
> insert into ... values(...) output INSERTED.*
>
> Для 2000 и ниже третьего способа нет.

MSSQL 2008 R2

DECLARE @T TABLE (F int identity(1,1), S varchar(10))
INSERT @T VALUES("sdfsd") output INSERTED.*

-----

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near "output".

Есть другая конструкция.

DECLARE @IDs TABLE
(
TargetID int,
SourceID int
)

MERGE TableName AS Target
USING (SELECT ... FROM ...)
AS Source (... Fields ...)
ON ( Statement )
WHEN MATCHED THEN
UPDATE SET
Field = Source.Field
WHEN NOT MATCHED THEN
INSERT (...Fields...)
VALUES (...Source.Values...)
OUTPUT Inserted.ID, Source.ID INTO @IDs;

> Это вываленные наружу кишки деталей реализации. На кой ляд
> мне нужно заботиться о том, как там унутре реализована какая-
> то фигня, которая внешне выглядит как атрибут колонки.

В случае какого-то сбоя в автоинкременте гораздо проще найти генератор или секвенцию по разумному наименованию, а не по сгенеренному СУБД. Кроме того, опять же применительно к DB2, сгенеренная секвенция не видна в схеме, в которой находится таблица. Также можно одну секвенцию применить к нескольким таблицам, что тоже иногда полезно....

> В случае какого-то сбоя в автоинкременте

8 лет за MSSQL. От 7.2 до 2005, вместе с портативными версиями. Ни одного сбой не видел.

> Также можно одну секвенцию применить к нескольким таблицам,
> что тоже иногда полезно....

Вот это действительно аргумент, с identity такого явным образом было сделать нельзя. Хотя сейчас уже наверняка можно, просто не слежу за развитием.

Автоинкрементные поля - вставка пропущенный значений Найти похожие ветки