Блог

Привет, ребята, у меня только один быстрый вопрос:

что происходит, когда оператор insert завершается с ошибкой в столбце идентификатора?

Возможно ли, например, сказать, что если я вставлю строку со столбцом идентификаторов, этот столбец идентификаторов будет равен 1, и вставить снова, но это завершается неудачей и не вставляет данные. Затем попробуйте вставить еще раз, и этот идентификатор для этой строки теперь равен 3?

Любой совет будет высоко оценен.

Спасибо.

Это зависит от того, какова причина сбоя при вставке данных. Если, например, значения являются недопустимыми (неправильные типы), то значение идентификатора не будет увеличиваться. Однако, если первая вставка прошла успешно, но затем была удалена (из-за сбоя транзакции и отката), то значение идентификатора увеличивается.

 -- Next identity value = 1
INSERT INTO Table1 (
    field1)
VALUES ('a')

-- Next identity value = 2    
BEGIN TRAN
INSERT INTO Table1 (
  field1)
VALUES ('b')

-- Next identity value = 3    
ROLLBACK TRAN

-- Next identity value = 3, although the insertion was removed.    
INSERT INTO Table1 (
  field1)
VALUES ('c')

-- Next identity value = 4
 

Первая вставка будет иметь значение столбца идентификации = 1, вторая завершится неудачно, а третья будет иметь значение столбца идентификации = 3.

Тот факт, что столбец имеет IDENTITY спецификацию, не обязательно означает, что он уникален.

Если у вас нет уникального ограничения (или ограничения первичного ключа) для этого столбца, вы можете определенно вставить несколько идентичных значений в строки для этого столбца.

Однако, как правило, ваши IDENTITY столбцы будут первичным ключом (или, по крайней мере, будут иметь UNIQUE ограничение на них), и в этом случае попытка вставить уже существующее значение приведет к ошибке («нарушение уникального ограничения» или что-то в этом роде)

Чтобы иметь возможность вставлять определенные значения в IDENTITY столбец, вам необходимо иметь SET IDENTITY_INSERT (table name) ON — в противном случае SQL Server не позволит вам даже указывать значения для IDENTITY столбца.

Для иллюстрации — попробуйте это:

  -- create demo table, fill with values
CREATE TABLE IdentityTest (ID INT IDENTITY, SomeValue CHAR(1))

INSERT INTO IdentityTest(SomeValue) VALUES('A'), ('B'), ('C')

SELECT * FROM IdentityTest  -- Output (1)

-- insert duplicate explicit values into table
SET IDENTITY_INSERT IdentityTest ON

INSERT INTO IdentityTest(ID, SomeValue) VALUES(1, 'Z'), (2, 'Y')

SET IDENTITY_INSERT IdentityTest OFF

SELECT * FROM IdentityTest  -- Output (2)

-- add unique constraint
TRUNCATE TABLE dbo.IdentityTest

ALTER TABLE IdentityTest ADD CONSTRAINT UX_ID UNIQUE(ID)

INSERT INTO IdentityTest(SomeValue) VALUES('A'), ('B'), ('C')

SET IDENTITY_INSERT IdentityTest ON

INSERT INTO IdentityTest(ID, SomeValue) VALUES(1, 'Z')  -- error message (3)

DROP TABLE IdentityTest
 

Вывод (1):

 ID  SomeValue
1   A
2   B
3   C
 

Вывод (2):

 ID  SomeValue
1   A
2   B
3   C
1   Z
2   Y
 

Сообщение об ошибке (3):

Сообщение 2627, уровень 14, состояние 1, строка 9
Нарушение ограничения УНИКАЛЬНОГО КЛЮЧА ‘UX_ID’. Не удается вставить дубликат ключа в dbo объекта.IdentityTest’. Значение дублирующего ключа равно (1).

Один из способов — предотвратить вставку, если строка уже существует.

IF (Not Exists (Select ID From Table Where SomeCol = @SomeVal)
Insert Into Table Values (@SomeVal)