Ошибка когда параметр IDENTITY_INSERT имеет значение OFF WPF C# MS SQL
Добрый день, сообщество, выше дан код. При записи в базу, т.е. при нажатии кнопки срабатывает исключение:
Невозможно вставить явное значение для столбца идентификаторов в таблице «Student», когда параметр IDENTITY_INSERT имеет значение OFF.
Что делать? Идентификатор в БД для этой таблицы включён и начинается с 1.
Отслеживать
задан 7 мая в 4:44
e.shikhanov e.shikhanov
53 7 7 бронзовых знаков
Мало информации, скорее всего что-то можно найти в AppConnect, можно конечно ещё выставить SET IDENTITY_INSERT ON) Но скорее всего это наоборот всё поломает
Когда параметр identity insert имеет значение off
Здравствуйте!
Платформа 8.3.15.1830 (x64)
Хочу с помощью менеджера записи изменить значение одного поля, но выдается следующая ошибка:
: Ошибка при вызове метода контекста (Записать)
ЗаписьТаблицыВнешнейБД.Записать();
по причине:
Ошибка внешней базы данных:
ошибка при выполнении запроса
по причине:
Ошибка ODBC. SQLSTATE: 23000
Номер ошибки: 544
Описание: [Microsoft][SQL Server Native Client 11.0][SQL Server]Невозможно вставить явное значение для столбца идентификаторов в таблице «build_main», когда параметр IDENTITY_INSERT имеет значение OFF.
Собственно, вот кусок кода, где возникает ошибка:
ЗаписьТаблицыВнешнейБД = ВнешниеИсточникиДанных.УчетВесовогоМатериала.Таблицы.ОсновнаяТаблицаУчета.СоздатьМенеджерЗаписи();
ЗаписьТаблицыВнешнейБД.НомерСтроки = СтрокаТЗ.НомерСтроки;
ЗаписьТаблицыВнешнейБД.Прочитать();
ЗаписьТаблицыВнешнейБД.ЗагруженоВ1С = 1;
ЗаписьТаблицыВнешнейБД.Записать();
Основные свойства таблицы:
Тип данных таблицы внешнего источника данных — НеобъектныеДанные
Поле ключа — НомерСтроки
Только чтение — Ложь
У всех столбцов, кроме «ЗагруженоВ1С», свойство Только чтение установлено в значение Истина
Из-за чего может возникать ошибка?
Я так понимаю, что сама ошибка говорит о том, что я якобы пытаюсь вставить новую строку с установленным значением ключевого поля. Почитал описание процедуры Записать(), там действительно написано, что изменение записи происходит путем удаления текущей записи и вставки новой измененной. Так а почему оно не работает тогда? Поле ключа ведь обозначено как только для чтения, зачем 1С его заполняет, по логике, должна добавиться новая запись, с новым ID, но этого не происходит. Но этого и не хотелось бы, хочется, чтобы обычный UPDATE по ключу сработал. Никто не знает как с этим разобраться?
SQL-Ex blog
В предыдущей статье я рассмотрел основную информацию о столбцах identity в SQL Server. В этой статье обсуждаются более продвинутые вещи. В частности, как вручную вставить значения identity, как избежать дублирующих значений identity, как изменить значение точки отсчета identity (seed), как идентифицировать функции и переменные и многое другое.
Вставка значений identity вручную
По умолчанию невозможно вставить вручную значение непосредственно в столбец identity, но значения identity могут быть введены вручную, если включить сессионную опцию. Чтобы выяснить, что произойдет при попытке вставить значение identity без включения свойства Identity Insert, выполните код в листинге 1.
Листинг 1: Попытка вставить значение identity
CREATE TABLE Widget(WidgetID INT NOT NULL IDENTITY,
WidgetName NVARCHAR(50), WidgetDesc NVARCHAR(200));
INSERT INTO Widget
VALUES (110,'MyNewWidget','New widget to test insert');
Вставка значения 110 в столбец identity, наряду со значениями остальных столбцов, в таблицу Widget вернет ошибку, показанную ниже:
В сообщении об ошибке ясно говорится, что вы не можете явно вставлять значение identity, если не указываете список столбцов в операторе INSERT, и свойство IDENTITY_INSERT для таблицы Widget не установлено в ON.
Свойство IDENTITY_INSERT является сессионным, и оно управляет тем, может ли вставляться значение identity или нет. По умолчанию значение этого свойство равно OFF, но оно может быть включено для таблицы Widget с помощью кода в листинге 2.
Листинг 2: Включение свойства IDENTITY_INSERT
SET IDENTITY_INSERT Widget ON;
После включения свойства IDENTITY_INSERT для таблицы Widget можно выполнить код в листинге 3 и не получить ошибки.
Листинг 3: Код со списком столбцов, требуемым для вставки значения identity
INSERT INTO Widget(WidgetID,WidgetName,WidgetDesc)
VALUES (110,'MyNewWidget','New widget to test insert');
Только одна таблица в сессии может иметь включенным свойство INDENTITY_INSERT в одно и то же время. Если вам потребуется вставить значения identity более чем в одну таблицу, вам сначала нужно выключить свойство INDENTITY_INSERT для первой таблицы, используя код в листинге 4, до включения свойства INDENTITY_INSERT для другой таблицы.
Листинг 4: Выключение сессионного свойства INDENTITY_INSERT
SET IDENTITY_INSERT Widget OFF;
Следует соблюдать осторожность при вставке значений identity вручную. SQL Server не требует от значений identity уникальности. По этой причине вам нужно позаботиться при вставке значений identity вручную, чтобы не вставить значение identity, которое уже существует.
Избежать дублирующих значений identity
Дубликаты значений identity могут возникнуть в таблице при вставке значений identity или повторной установке значения identity. Наличие дубликатов значений identity не обязательно плохо, если нет требования к уникальности этих значений. Если все значения identity должны быть различными, то это требование должно поддерживаться созданием PRIMARY KEY, ограничения UNIQUE или индекса UNIQUE.
Использование функции IDENTITY
SQL Server предоставляет функцию IDENTITY для определения столбца identity при создании новой таблицы с помощью оператора SELECT с предложением INTO. Функция IDENTITY подобна, но не идентична свойству IDENTITY, которое используется в операторах CREATE или ALTER TABLE. Функция IDENTITY может использоваться только в операторе SELECT, содержащем предложение INTO, который создает и заполняет новую таблицу.
Ниже приведен синтаксис функции IDENTITY:
IDENTITY (data_type [ , seed , increment ] ) AS column_name
data-type — допустимый числовой тип данных, который поддерживает целые значения, отличный от bit или decimal.
seed — определяет первое значение identity, которое будет вставлено в таблицу.
increment — целое значение, которое будет прибавляться к значению seed для каждой добавленной строки.
column_name — имя столбца identity, который будет создан в новой таблице.
Для демонстрации работы функции IDENTITY выполните код в листинге 5.
Листинг 5: Использование функции IDENTITY в команде SELECT INTO
USE AdventureWorks2019;
GO
SELECT IDENTITY(int, 90000, 1) AS Special_ProductId,
Name AS Special_Name,
ProductNumber,
ListPrice
INTO Production.SpecialProduct
FROM Production.Product
WHERE Name like '%LL Road Frame%Black%';
-- Вывод новой таблицы
SELECT * FROM Production.SpecialProduct;
Вывод кода в листинге 5 показан ниже.
Результаты показывают, что столбец с именем Special_ProductID является столбцом identity, который был создан при помощи функции IDENTITY. Первая строка получила значение seed. Каждое значение identity для последующих строк вычислялось прибавлением значения increment к значению identity текущей вставленной строки.
Иногда вам может потребоваться программным образом выяснить значения seed и increment или последнее вставленное значение в столбец identity. Для получения подобной информации SQL Server предоставляет несколько функций.
Для получения значения seed, вы можете использовать функцию IDENT_SEED. Эта функция использует следующий синтаксис:
IDENT_SEED ( 'table_or_view' )
Если вы меняли значение identity с помощью команды DBCC CHECKIDENT, то эта функция вернет исходное значение seed, назначенное столбцу identity при его начальном создании.
Парная функция с именем IDENT_INCR, которая позволяет получить значение приращения (increment), имеет следующий синтаксис:
IDENT_INCR ( 'table_or_view' )
Чтобы увидеть обе эти функции в действии, выполните код в листинге 6.
Листинг 6: Получение исходных значений seed и increment
SELECT IDENT_SEED('Production.SpecialProduct') AS OriginalSeed,
IDENT_INCR('Production.SpecialProduct') AS IncrementValue;
Вот результат выполнения этого кода:
Здесь видно, что OriginalSeed и IncrementValue являются теми же, что и аргументы, которые использовались при создании таблицы SpecialProduct в коде из листинга 5.
Вам также может понадобиться знать последнее значение identity, вставленное в таблицу. Обычно это требуется, когда у вас есть две таблицы со связью «родитель-потомок», и дочернюю запись нужно привязать к родительской записи, используя значение identity родительской записи. Есть три различных способа вернуть значение identity последней вставленной записи, которые мы тут рассмотрим: @@IDENTITY, IDENT_CURRENT и SCOPE_IDENTITY.
@@IDENTITY
Системная функция @@IDENTITY возвращает последнее вставленное значение identity. Если последний оператор вставки вставил несколько значений identity, то только последнее значение из них возвращается этой функцией. Если никаких новых значений identity не было вставлено в данной сессии, то функция вернет значений NULL. Если срабатывает триггер на вставку, и этот триггер в свою очередь вставляет строку в таблицу, которая содержит столбец identity, то будет возвращено значение identity, вставленное триггером.
SCOPE_IDENTITY
Функция SCOPE_IDENTITY так же возвращает последнее вставленное значение identity, как и @@IDENTITY, но с одним отличием. Разница состоит в том, что функция SCOPE_IDENTITY возвращает значение identity для последнего оператора INSERT, выполненного в той же сессии и области действия (scope). Напротив, функция @@IDENTITY возвращает последнее вставленное значение независимо от области действия.
Для лучшего понимания того, как влияет область действия на значение identity, возвращаемое этими двумя функциями, выполните код в листинге 7.
Листинг 7: Код, показывающий разницу между SCOPE_IDENTITY и @@IDENTITY
DROP TABLE IF EXISTS TestTable1, TestTable2;
CREATE TABLE TestTable1(
ID INT IDENTITY(1,1),
InsertText1 VARCHAR(100)
);
CREATE TABLE TestTable2(
ID INT IDENTITY(100,100),
InsertText2 VARCHAR(100)
);
GO
CREATE TRIGGER MyTrigger ON TestTable1 AFTER INSERT AS
BEGIN
INSERT INTO TestTable2(InsertText2) VALUES ('Trigger Insert 1');
INSERT INTO TestTable2(InsertText2) VALUES ('Trigger Insert 2');
END
GO
INSERT INTO TestTable1(InsertText1) VALUES ('Original Insert');
GO
-- Возвращаем значения Identity
SELECT @@IDENTITY AS [@@IDENTITY], SCOPE_IDENTITY() AS [SCOPE_IDENTITY];
Код в листинге 7 сначала вставляет одну запись в таблицу TestTable1 в текущей области действия, затем еще 2 записи вставляются в таблицу TestTable2 в другой области действия, когда срабатывает триггер. После вставки и срабатывания триггера на вставку выполняется оператор SELECT, чтобы показать значения, возвращаемые функциями @@IDENTITY и SCOPE_IDENTITY(). Вывод показан ниже.
Следовательно, если вы хотите узнать последнее значение identity независимо от области действия, вы можете использовать @@IDENTITY. Если вам нужно знать последнее значение identity, вставленное в текущей области действия, вам нужно использовать функцию SCOPE_IDENTITY(). На представленных результатах видно, что функция @@IDENTITY вернула значение 200. Это произошло потому, что @@IDENTITY возвращает последнее вставленное значение вне зависимости от области действия. Значение identity для второй записи было вставлено в таблицу TestTable2 триггером «после вставки». Функция SCOPE_IDENTITY() вернула значение 1, это значение identity было присвоено, когда запись вставлялась в TextTable1 в той же области действия.
Имейте в виду, что обе функции @@IDENTITY и SCOPE_IDENTITY() возвращают последнее вставленное значение identity, оставляя без внимания таблицу, куда это значение было вставлено. Если вам нужно знать последнее значение identity, вставленное в конкретную таблицу, вам следует использовать функцию IDENT_CURRENT().
IDENT_CURRENT
Функция IDENT_CURRENT() возвращает последнее значение identity, вставленное в конкретную таблицу, вне зависимости от сессии или области действия, когда это было сделано. С помощью функции IDENT_CURRENT() вы можете легко определить последнее значение identity, созданное для конкретной таблицы, как показано в коде листинга 8.
Листинг 8: Определение последних значений identity, вставленных в таблицы TestTable1 и TestTable2
SELECT IDENT_CURRENT('TestTable1') AS IdentityForTestTable1,
IDENT_CURRENT('TestTable2') AS IdentityForTestTable2;
Выполнение кода в листинге 2 дает следующие результаты:
Функции @@IDENTITY и SCOPE_IDENTITY() не требуют передачи имени таблицы в качестве параметра, поэтому нелегко идентифицировать, из какой таблицы пришло значение identity. Напротив, IDENT_CURRENT() требует передать имя таблицы. Следовательно, если вы хотите знать последнее значение identity, вставленное в конкретную таблицу вне зависимости от сессии и области действия, вам следует обратить внимание на функцию IDENT_CURRENT().
При вставке множества строк в таблицу со столбцом identity не гарантировано, что каждая строка получит последовательные значения в столбце identity. Это может произойти, когда в то же время другие пользователи вставляют строки. Если вам действительно нужны последовательные значения identity, убедитесь, что ваш код использует эксклюзивную блокировку для таблицы или уровень изоляции SERIALIZE.
Вы также можете обнаружить, что значения identity не всегда присваиваются последовательно. Одной из причин этого является откат транзакции. При откате транзакций любые значения identity, которые были отменены, не будут повторно использоваться. Еще одной причиной появления зазоров является способ, которым SQL Server кэширует значения identity в целях повышения производительности.
Кэширование identity для повышения производительности
Чтобы найти следующее значение identity, SQL Server требуются некоторые ресурсы машины, чтобы заглянуть внутрь и найти это значение. Поэтому для оптимизации производительности и экономии ресурсов машины SQL Server кэширует имеющиеся значения identity. Кэшируя имеющиеся значения identity, SQL Server не нужно вычислять следующее доступное значение идентификатора при вставке новой строки.
Кэширование identity было введено в SQL Server 2012. Проблема с кэшированием identity состоит в том, что когда SQL Server неожиданно падает, он теряет значения, сохраняемые во внутреннем кэше. При потере кэшированных значений эти значения identity больше никогда не будут использоваться. Это может привести к созданию зазора в значениях identity.
Новая опция конфигурации базы данных с именем IDENTITY_CACHE была введена в SQL Server 2017, чтобы помочь решить проблему с зазорами, которые могут вызываться кэшированием. Опция IDENTITY_CACHE включена по умолчанию, но может быть выключена (OFF). При выключении опции SQL Server не кэширует значения identity; тем самым они не будут потеряны при крушении или неожиданной остановке SQL Server. Конечно, отключение кэширования identity приведет к ухудшению производительности.
Чтобы проверить установку IDENTITY_CACHE для базы данных, выполните код из листинга 9.
Листинг 9: Вывод установки IDENTITY_CACHE для текущей базы данных
SELECT * FROM sys.database_scoped_configurations
WHERE NAME = 'IDENTITY_CACHE';
Вывод выполнения кода из листинга 9 на SQL Server 2017 показан ниже.
Видно, что значение IDENTITY_CACHE установлено в 1, что означает, что кэширование identity включено. Чтобы запретить кэширование identity для текущей базы данных, выполните код из листинга 10.
Листинг 10: Выключение кэширования identity
ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE=OFF;
Если вы обнаружите множество зазоров в ваших значениях identity, и это является проблемой, вы можете отключить кэширование identity.
Недостатки столбцов identity
- Только один столбец identity можно определить на таблицу.
- Столбец identity нельзя изменить или удалить после его создания.
- Столбцы identity не являются уникальными по умолчанию. Чтобы обеспечить их уникальность, необходимо определить первичный ключ, ограничение уникальности или уникальный индекс.
Обратные ссылки
Нет обратных ссылок
Комментарии
Показывать комментарии Как список | Древовидной структурой
Автор не разрешил комментировать эту запись
Вставка строк в таблицу, содержащую автоинкрементируемое поле стр. 1
Многие коммерческие продукты допускают использование автоинкрементируемых столбцов в таблицах, то есть столбцов, значение которых формируется автоматически при добавлении новых записей. Такие столбцы широко используются в качестве первичных ключей таблицы, так как они автоматически обеспечивают уникальность за счет того, что генерируемые значения не повторяются. Типичным примером столбца такого типа является последовательный счетчик, который при вставке строки генерирует значение на единицу большее предыдущего значения (значения, полученного при вставке предыдущей строки).
Ниже приводится пример создания таблицы Printer_Inc с автоинкрементируемым столбцом (code) в MS Язык структурированных запросов) — универсальный компьютерный язык, применяемый для создания, модификации и управления данными в реляционных базах данных. SQL Server.
Автоинкрементируемое поле определяется посредством конструкции IDENTITY (1, 1). При этом первый параметр свойства IDENTITY (1) определяет, с какого значения начнется отсчет, а второй, — какой шаг будет использоваться для приращения значения. Таким образом, в нашем примере первая вставленная запись будет иметь в столбце code значение 1, вторая — 2 и т. д.
Поскольку в поле code значение формируется автоматически, оператор
приведет к ошибке, даже если в таблице нет строки со значением в поле code, равным 15. Поэтому для вставки строки в таблицу просто не будем указывать это поле точно так же, как и в случае использования значения по умолчанию, то есть
В результате выполнения этого оператора в таблицу Printer_Inc будет вставлена информация о модели 3111 цветного лазерного принтера, стоимость которого равна $599. В поле code окажется значение, которое только случайно может оказаться равным 15. В большинстве случаев этого оказывается достаточно, так как значение автоинкрементируемого поля, как правило, не несет никакой информации; главное, чтобы оно было уникальным.
Однако бывают случаи, когда требуется подставить вполне конкретное значение в автоинкрементируемое поле. Например, нужно перенести уже имеющиеся данные во вновь создаваемую структуру; при этом эти данные участвуют в связи «один-ко-многим» со стороны «один». Таким образом, мы не можем допустить тут произвола. С другой стороны, не хочется отказываться от автоинкрементируемого поля, так как оно упростит обработку данных при последующей эксплуатации базы данных.
Поскольку стандарт языка SQL не предполагает наличия автоинкрементируемых полей, то не существует и единого подхода. Здесь мы покажем, как это реализуется в MS SQL Server. Оператор
отключает (значение ON) или включает (OFF) использование автоинкремента. Поэтому чтобы вставить строку со значением 15 в поле code, нужно написать:
Обратите внимание, что список столбцов в этом случае является обязательным, то есть мы не можем написать так:
ни, тем более, так:
В последнем случае в пропущенный столбец code значение не может быть подставлено автоматически, так как автоинкрементирование отключено.
Важно отметить, что если значение 15 окажется максимальным в столбце code, то далее нумерация продолжится со значения 16. Естественно, если включить автоинкрементирование:
Наконец, рассмотрим пример вставки данных из таблицы Product в таблицу Product_Inc, сохранив значения в поле code:
По поводу автоинкрементируемых столбцов следует добавить следующее. Пусть последнее значение в поле code было равно 16, после чего строка с этим значением была удалена. Какое значение будет в этом столбце после вставки новой строки? Правильно, 17, так как последнее значение счетчика сохраняется, несмотря на удаление строки, его содержащей. Поэтому нумерация значений в результате удаления и добавления строк не будет последовательной. Это является еще одной причиной для вставки строки с заданным (пропущенным) значением в автоинкрементируемом столбце.
| Страницы: | 1 | 2 | 3 | 4 |