#tsql
#tsql
Вопрос:
У меня есть дочерняя таблица, связанная с родительской родительским первичным ключом. В настоящее время дочерняя таблица имеет стандартный идентификатор int IDENTITY(1,1) . Для моих целей это нормально, и это не изменится, но для пользователя они будут видеть списки разрозненных идентификаторов при просмотре дочерних строк, связанных с родительским.
Я хотел бы иметь идентификатор отображения, который начинается с 1 и увеличивается (как столбец идентификатора) для каждой группы записей.
Моя дочерняя таблица выглядела бы примерно так;
ChildID-PK ParentID-FK DisplayID
1 1 1
2 1 2
3 2 1
4 3 1
5 1 3
Возможные решения;
- При вставке записи,
SELECT MAX(DisplayID) 1 FROM tbl WHERE ParentID = @ParentIDчтобы использовать в качестве новых записей DisplayID. Я мог видеть, что это проблематично, если много людей редактируют группу записей одновременно. - Используется
ROW_NUMBER()для получения DisplayID, но в этом случае, если запись должна быть удалена, DisplayID для существующих записей может измениться. Этого не может произойти, потому что пользователи могут ссылаться на определенные DisplayID при вводе данных.
Комментарии:
1. Если это просто для отображения, разве вы не можете использовать row_number() вместо предложения order by childID при запросе записей для определенного родительского идентификатора?
2. Проблема в том, что я не хочу, чтобы отображаемый идентификатор менялся, если строка в середине удалена. Пользователь мог бы ссылаться на «дочерний элемент 4», но завтра, когда «дочерний элемент 3» будет удален, «дочерний элемент 4» станет «дочерним элементом 3».
3. Даже если вы не хотите использовать
row_number(), совет mellamokb в основном правильный. Вы должны не изменять правильную, разумную реализацию суррогатного ключа только из-за требования к представлению (в конце концов, суррогатные ключи в любом случае не должны предоставляться). Если вам действительно нужен более естественный ключ, создайте новое поле в своей таблице и используйте какой-нибудь SQL или серверный код для его заполнения.4. Я не хочу изменять поведение ключевого поля. Я хочу сохранить отображаемый идентификатор только для только пользователя для каждой дочерней записи. Я не буду ссылаться на этот идентификатор в коде.
5. Гораздо лучше сообщить пользователям, что будут пробелы, и что это нормально и ожидаемо. Это НЕ та задача, которую вы хотите выполнять, если вы можете избежать этого и никогда не выполнять без требования пользователя, на котором вы отказались, а он все еще настаивает. Это непростая задача, и в ней легко ошибиться. Разработка и обслуживание могут быть дорогостоящими, и пользователь ничего не получает в 99% случаев.
Ответ №1:
Самый безопасный способ, который я могу придумать, — это создать что-то похожее на объект последовательности Oracle. Хороший пример этого приведен в этом сообщении в блоге группы консультантов по разработке Microsoft SQL Server для клиентов (я предпочитаю вариант 2).
Вы должны создать последовательность для каждой вставленной родительской строки:
EXEC usp_CreateNewSeq N'ParentSeq' @parentId
и получите следующую последовательность для каждой строки в вашей таблице ассоциаций:
INSERT INTO [table] VALUES (@childId, @parentId, EXEC 'GetNewSeqVal_' @parentId)
Я не уверен в своем синтаксисе и не могу его проверить, так что не стесняйтесь поправлять меня.
Более простой метод: добавьте столбец в родительскую таблицу с именем MaxChildId со значением по умолчанию = 0. Каждый раз, когда вы добавляете дочернюю строку, вы должны обновлять этот столбец и использовать его новое значение в качестве DisplayID .
declare @vid int
UPDATE [ParentTable]
SET @vid = MaxChildId 1, MaxChildId = MaxChildId 1
WHERE Id = ParentID
select @vid
Этот метод может вызвать совпадения при обновлении родительской таблицы.
После всего сказанного, я думаю, вам лучше рассмотреть редизайн для решения этой проблемы.
Комментарии:
1. Не приведет ли таблица sequence к тем же проблемам параллелизма, что и сохранение MaxChildId в родительской таблице или даже выбор MAX childID при вставке?
2. @Orange Kid: вставка строки в упрощенную таблицу и извлечение идентификатора области — это гораздо более легкая операция, чем обновление строки в таблице, поскольку для этого требуется найти строку. Даже авторы сообщения сказали, что «Вариант 2 значительно улучшает параллелизм». Обратите внимание, что этот метод выполняет очистку обслуживания на ходу.
3. Спасибо. Я думаю, что попробую использовать первый метод, когда у меня будет немного времени.