Уникальный столбец автоинкремента SQL Server в контексте другого столбца

Предположим, что таблица с двумя столбцами:

ParentEntityId int foreign key
Number int

ParentEntityId является внешним ключом к другой таблице.

Number является локальным тождеством, т.е. является единственным в пределах одного ParentEntityId.

Уникальность легко достигается с помощью уникального ключа над этими двумя столбцами.

Как сделать Number автоматически увеличиваться в контексте ParentEntityId на вставке?

Добавление 1

Чтобы прояснить проблему, вот тезис.

ParentEntity имеет несколько ChildEntity, и каждый ChiildEntity должен иметь уникальный инкрементный Number в контексте его ParentEntity.

Добавление 2

Относитесь к ParentEntity как Клиент.

Относитесь к ChildEntity как Заказ.

Итак, заказы для каждого клиента должны быть пронумерованы 1, 2, 3 и т.д.

Ответ 1

Ну, нет родной поддержки для этого типа столбца, но вы можете реализовать его с помощью триггера:

CREATE TRIGGER tr_MyTable_Number
ON MyTable
INSTEAD OF INSERT
AS

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN;

WITH MaxNumbers_CTE AS
(
    SELECT ParentEntityID, MAX(Number) AS Number
    FROM MyTable
    WHERE ParentEntityID IN (SELECT ParentEntityID FROM inserted)
)
INSERT MyTable (ParentEntityID, Number)
    SELECT
        i.ParentEntityID,
        ROW_NUMBER() OVER
        (
            PARTITION BY i.ParentEntityID
            ORDER BY (SELECT 1)
        ) + ISNULL(m.Number, 0) AS Number
    FROM inserted i
    LEFT JOIN MaxNumbers_CTE m
        ON m.ParentEntityID = i.ParentEntityID

COMMIT

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

Просто чтобы объяснить, что происходит здесь:

SERIALIZABLE - самый строгий режим изоляции; он гарантирует, что только одна транзакция базы данных одновременно может выполнять эти инструкции, которые нам нужны, чтобы гарантировать целостность этой "последовательности". Обратите внимание, что это необратимо продвигает всю транзакцию, поэтому вы не захотите использовать ее внутри долговременной транзакции.
CTE берет наибольшее число, уже использованное для каждого родительского идентификатора;
ROW_NUMBER генерирует уникальную последовательность для каждого родительского идентификатора (PARTITION BY), начиная с номера 1; мы добавляем это к предыдущему максимуму, если есть одна, чтобы получить новую последовательность.

Я, вероятно, также должен упомянуть, что если вам нужно только вставить один новый дочерний объект за раз, вам лучше просто выполнить эти операции с помощью хранимой процедуры вместо использования триггера - вы определенно получите лучшую производительность из него. Вот как это делается в настоящее время с столбцами hierarchyid в SQL'08.

Ответ 2

Нужно добавить предложение OUTPUT, чтобы вызвать совместимость Linq to SQL.

Например:

INSERT MyTable (ParentEntityID, Number)
OUTPUT inserted.* 
SELECT
  i.ParentEntityID,
  ROW_NUMBER() OVER
  (
   PARTITION BY i.ParentEntityID
   ORDER BY (SELECT 1)
  ) + ISNULL(m.Number, 0) AS Number
FROM inserted i
LEFT JOIN MaxNumbers_CTE m
ON m.ParentEntityID = i.ParentEntityID

Ответ 3

Это решает вопрос, как я его понимаю:: -)

DECLARE @foreignKey int
SET @foreignKey = 1  -- or however you get this

INSERT Tbl (ParentEntityId, Number) 
VALUES (@foreignKey, ISNULL((SELECT MAX(Number) FROM Tbl WHERE ParentEntityId = @foreignKey), 0) + 1)