SQL Server - Identity column as a primary key

User avatar
AnyaGal
Уже с Приветом
Posts: 5771
Joined: 02 Dec 1999 10:01
Location: Saint-Petersburg --> Bellevue, WA

SQL Server - Identity column as a primary key

Post by AnyaGal »

У нас один продукт поставляется как для SQL Server так и для Access.
И DBA настоятельно рекомендует не использовать Autonumber type для PK в Access (я так понимаю, что аналог этого в SQL Server int Identity = Yes with Increment=1) в таблицах.

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

Правда ли это?

Я сейчас работают одна над относительно небольшим проектом и мне нужно создать небольшую ДБ где будут присутсвовать несколько linking tables . Так вот я думаю стоит мне использовать Identity columns для генерации IDs автоматически или генерить IDs каким другим способом?
SBolgov
Уже с Приветом
Posts: 14006
Joined: 17 Jun 2003 04:41

Re: SQL Server - Identity column as a primary key

Post by SBolgov »

AnyaGal wrote:У нас один продукт поставляется как для SQL Server так и для Access.
И DBA настоятельно рекомендует не использовать Autonumber type для PK в Access (я так понимаю, что аналог этого в SQL Server int Identity = Yes with Increment=1) в таблицах.

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

Правда ли это?

Гм. Вообще-то приводить базу в состояние, когда родительские записи (уже) удалены, а дочерние остались - это моветон. :nono#: Ведь может (теоретически) случиться так, что при "добавлении их же" что-то произойдёт "не так", и добавятся не в точности "они же", а что-то немного другое. Например, добавится на одну запись меньше, и в linking table появятся "фантомные" записи без родителя.

ID действительно изменятся, и связи действительно накроются. И это правильно. :umnik1: Потому что когда запись удаляется, а затем добавляется "другая такая же", то это другая запись (хоть и "такая же"), а не та же самая.

AnyaGal wrote:Я сейчас работают одна над относительно небольшим проектом и мне нужно создать небольшую ДБ где будут присутсвовать несколько linking tables . Так вот я думаю стоит мне использовать Identity columns для генерации IDs автоматически или генерить IDs каким другим способом?

Я думаю - стоит. Правда, я не в курсе деталей реализации Autonumber type в Access. Возможно, есть какие-то причины (типа глюков реалиации), из-за которых не стоит.
Не гоните, и не гонимы будете...
User avatar
AnyaGal
Уже с Приветом
Posts: 5771
Joined: 02 Dec 1999 10:01
Location: Saint-Petersburg --> Bellevue, WA

Post by AnyaGal »

Спасибо.
Я то как раз с SQL Server-ом работаю
Админ говорил, что по разным причинам иногда приходится переносить данные. Короче эти Autonumber i Identity он сильно не любит.

Но это уже его проблемы. :)

Просто я удивилась по моим представлениям Identity как раз и предназначалась для генерации ПК и было бы странно ее запрещать использовать.

Правда с другой стороны я слышала также, что плохо когда в качестве ПК номера 1,2,3,4,5 и т.д. Типа легче запустить вражеский запрос на удаление или сканирование данных.
SBolgov
Уже с Приветом
Posts: 14006
Joined: 17 Jun 2003 04:41

Post by SBolgov »

AnyaGal wrote:Спасибо.
Я то как раз с SQL Server-ом работаю
Админ говорил, что по разным причинам иногда приходится переносить данные. Короче эти Autonumber i Identity он сильно не любит.

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

Если, допустим, в "старой" базе "родители" имели ID = 11, 12 и 13, а в "новой" авто-нумератор выдаёт им другие ID, то "детей" надо переносить с учётом этой замены. Иначе может получиться очень весело, особенно если в "новой" уже есть (другие!) записи с ID 11, 12 и 13.

Но это не повод отказываться от использования identity. :nono#: Это повод аккуратнее писать скрипты для переноса. :umnik1:

AnyaGal wrote:Просто я удивилась по моим представлениям Identity как раз и предназначалась для генерации ПК и было бы странно ее запрещать использовать.

Точно. ;)

AnyaGal wrote:Правда с другой стороны я слышала также, что плохо когда в качестве ПК номера 1,2,3,4,5 и т.д. Типа легче запустить вражеский запрос на удаление или сканирование данных.

Первый раз слышу. :pain1: Может, кто из спецов знает подробнее? :?

По идее, запрос "delete from Table1" запускается так же легко, как и "delete from Table1 where ID = ..." Т.е. если у врагов есть возможность запустить второй, то они и первый запустят. :lol:
Не гоните, и не гонимы будете...
SkyWalker
Уже с Приветом
Posts: 317
Joined: 16 Feb 2001 10:01
Location: US

Post by SkyWalker »

При переносе таблиц в MS-SQL можно использовать SET IDENTITY_INSERT ON

Данная опция позволяет вставлять данные в identity колонку.

http://msdn.microsoft.com/library/defau ... t_7zas.asp
zVlad
Уже с Приветом
Posts: 15312
Joined: 30 Apr 2003 16:43

Post by zVlad »

На мой взгляд проблема здесь в совершенно необоснованом распространении понятия "первичный ключ" на тип данных Identity. Вспомните, первичный ключ - это "набор атрибутов" однозначно идентифицирующий сущность представленную записью в родительской таблице.
Конечно, кажется удобнее иметь одну колонку, да еще и генерируемую автоматически, но сколько потом головной боли! Уж если хочется заменить набор колонок одной - сделайте эту колонку определяемой внешним образов, как впрочем это обычно и делается всякими employee numbers, document numbers и т.д.
Интересно, а как в таком случае со связями? Что вы в MS SQL объявляете столбец с Identity как Primary key? И есть таблицы с Forieng keys ссылающиеся на родительскую? Или это все поддерживается в коде? А документация по MS SQL рекомендует использовать Identity в качестве Primary Keys?
User avatar
YellowMan
Уже с Приветом
Posts: 1099
Joined: 30 Sep 1999 09:01
Location: Bryansk,RUSSIA >> Dublin, Ireland

Post by YellowMan »

zVlad wrote:Конечно, кажется удобнее иметь одну колонку, да еще и генерируемую автоматически, но сколько потом головной боли!

Интересно, а как в таком случае со связями? Что вы в MS SQL объявляете столбец с Identity как Primary key? И есть таблицы с Forieng keys ссылающиеся на родительскую? Или это все поддерживается в коде?


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

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

Так что дело похоже в архитектуре базы а не в способах реализации ключей.
Удачи@С.Смирнов
User avatar
katit
Уже с Приветом
Posts: 23804
Joined: 05 Jul 2003 22:34
Location: Брест -> St. Louis, MO

Post by katit »

ППКС 2 поста выше.

В нашей базе есть искуственные ключи. Они для быстродействия. При этом в большинстве таблиц также существует "натуральный" ключ физически реализованный как уникальный индекс.

Identity вообще не используется. Есть 2 служебных процедуры для генерации ключей. Их можно вызывать или они автоматом вызовутся из триггера.
Программа писалась давно (во времена сервера 6) Если я правильно понимаю тогда были проблемы с извлечением ключей после вставки. Да и сейчас не надо думать о том "что-же туда вставилось". Тем более что очень много данных проходит чеpез #таблицы и всяческие манипулации перед тем как попасть в базу.

А вообще ваш вопрос избитая тема во всех newsgroups. По тем или иным причинам использование "identity" считается плохой практикой.
User avatar
Kotkov
Уже с Приветом
Posts: 342
Joined: 13 Mar 2002 10:01
Location: California

Re: SQL Server - Identity column as a primary key

Post by Kotkov »

AnyaGal wrote:И DBA настоятельно рекомендует не использовать Autonumber type для PK в Access (я так понимаю, что аналог этого в SQL Server int Identity = Yes with Increment=1) в таблицах.

100% Прав!
Аня, забей на бизнес-логику, структуру данных и т.п при определении pimary key. Если хочешь, чтоб твоя база жила долго, счастливо и с хорошей перспективой, то каждая таблица должна содержать независимый ни от чего primary key (т.е. поле типа autoincrement). А далее уже для улучшения perfomance and reliability добавляешь unique/non-unique indexes, constraints, etc. согласно бизнес-логике.
Кто ищет, тот всегда найдет.
Живи своим умом, Пчёла! ©
User avatar
Dmitry67
Уже с Приветом
Posts: 28294
Joined: 29 Aug 2000 09:01
Location: SPB --> Gloucester, MA, US --> SPB --> Paris

Post by Dmitry67 »

zVlad wrote: Интересно, а как в таком случае со связями? Что вы в MS SQL объявляете столбец с Identity как Primary key? И есть таблицы с Forieng keys ссылающиеся на родительскую? Или это все поддерживается в коде? А документация по MS SQL рекомендует использовать Identity в качестве Primary Keys?


Это обычная практива во многих фирмах
Документация рекомендует использовать наиболее короткий primary key
Любой натуральный ключ, а котором куча полей (чтобы уж точно уникальность была) оказываются очень длинными
Велики и риски. Вы сделаете аппликацию где PK=SSN
Уж вроде точно уникальный
А потом фирма наладит оффшор, и попытается ввести в базу человека без SSN :)

При все том неудобства при работе identity доставляют изрядно
Зарегистрированный нацпредатель, удостоверение N 19719876044787 от 22.09.2014
User avatar
AnyaGal
Уже с Приветом
Posts: 5771
Joined: 02 Dec 1999 10:01
Location: Saint-Petersburg --> Bellevue, WA

Post by AnyaGal »

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

Так что тогда лучше написать служебную процедуру, которая бы вычисляла зачение следующего свободного первичного ключа и вызывать ее при Insert-ах, а Identity убрать?
User avatar
dim635csi
Удалён за грубость
Posts: 3347
Joined: 23 Nov 1999 10:01
Location: NC -> NYC -> KC -> Chicago

Post by dim635csi »

а удаление связанных records в linked tables гарантируется

Enforce relationship for INSERTs and UPDATEs - On
Cascade delete related records - On

on Manage Indexes/Keys panel for SQL Server.

В Access тоже такое вроде было.

О необходимости исползовать surrogate PK чтобы отвязать lookup/finder от business logic выше уже все сказали...
User avatar
Dmitry67
Уже с Приветом
Posts: 28294
Joined: 29 Aug 2000 09:01
Location: SPB --> Gloucester, MA, US --> SPB --> Paris

Post by Dmitry67 »

AnyaGal wrote:Так что тогда лучше написать служебную процедуру, которая бы вычисляла зачение следующего свободного первичного ключа и вызывать ее при Insert-ах, а Identity убрать?


Ни в коем случае
Дело в том что identity использует механизм autonomous transactions, который в явном виде пользователю SQL server недоступен.

Благодаря этому в таблицу с identity могу вставлять сколько угодно коннекций сразу. Если вы сделаете свой счетчик, то все сразу пересекуться на блокировке по этому счетчику, и вы сразу превратите систему в однопользовательскую
Зарегистрированный нацпредатель, удостоверение N 19719876044787 от 22.09.2014
User avatar
katit
Уже с Приветом
Posts: 23804
Joined: 05 Jul 2003 22:34
Location: Брест -> St. Louis, MO

Post by katit »

Dmitry67 wrote:Ни в коем случае

...

, и вы сразу превратите систему в однопользовательскую


У нас есть отдельная таблица с "TableName" и "NextKey" полями.
Процедура просто берет и increment (или insert)

Никаких блокировок у тысяч пользователей нет. Поддерживает до 200 клиентов совершенно без проблем.

А вот "вычислять следующее значение" не советую. Это может тормозить.
User avatar
Dmitry67
Уже с Приветом
Posts: 28294
Joined: 29 Aug 2000 09:01
Location: SPB --> Gloucester, MA, US --> SPB --> Paris

Post by Dmitry67 »

если Вы это делаете в отдельной транзакции. Если в транзакции вы заранее не знаете сколько и какие объекты будете вставлять то либо ваши изменения счетчика попадут в транзакцию, либо их надо делать в отдельной коннекции.
Зарегистрированный нацпредатель, удостоверение N 19719876044787 от 22.09.2014
User avatar
katit
Уже с Приветом
Posts: 23804
Joined: 05 Jul 2003 22:34
Location: Брест -> St. Louis, MO

Post by katit »

Dmitry67 wrote:если Вы это делаете в отдельной транзакции. Если в транзакции вы заранее не знаете сколько и какие объекты будете вставлять то либо ваши изменения счетчика попадут в транзакцию, либо их надо делать в отдельной коннекции.


Да, там по старинке. Каждому клиенту - по соединению.
User avatar
AnyaGal
Уже с Приветом
Posts: 5771
Joined: 02 Dec 1999 10:01
Location: Saint-Petersburg --> Bellevue, WA

Post by AnyaGal »

Чего-то я вообще запуталась. :)

Мне собственно надо создать небольшую content managment систему для сайта нашей компании для пользователей.

То есть чтобы кто-то из нашего CS мог через web страницы выбрать категорию нашего продукта, сам продукт, его версию, номер патча/билда/сервис пака и т.д и добавить информацию для пользователя, путь к файлу, который можно прочитать или сделать upload/download.
Также выбрать дату когда вся эта информация expired. Все это сохраняется в БД.

А в run time для пользователя все ASP страницы генерятся автоматически и информация берется из БД.

То есть в БД будут такие таблицы как ProductCategory, Product, ReleaseGroup, ReleaseElement, ProductVersion, уникальных названий для всех этих категорий можно сказать нет, или есть но длинные, и которые могут менятся со временем. Я конечно могу для них придумать сокращения, но их надо будет всем помнить.

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

Какие мне все-таки выбрать ключи - естественные или искусственные s Identity?

Kонтроль за всякими constraints i cascade delete/update могу установить - тут моя вотчина, начинаю с нуля, чего хочу того и ворочу :) , поэтому хочется сделать как лучше.
User avatar
Dmitry67
Уже с Приветом
Posts: 28294
Joined: 29 Aug 2000 09:01
Location: SPB --> Gloucester, MA, US --> SPB --> Paris

Post by Dmitry67 »

katit wrote:
Dmitry67 wrote:если Вы это делаете в отдельной транзакции. Если в транзакции вы заранее не знаете сколько и какие объекты будете вставлять то либо ваши изменения счетчика попадут в транзакцию, либо их надо делать в отдельной коннекции.


Да, там по старинке. Каждому клиенту - по соединению.


Да нет
Вам каждому клиенту нужно ДВА соединения
Либо система достаточна примитивна и например за раз вставляет только один объект. Тогда Вы можете заранее открутить счетчик в отдельной транзакции
Наконец, можно даже предположить что Вы читаете счетчик в readuncommitted, но не думаю что так у вас делают. Впрочем, и в жтом случае есть засады
Зарегистрированный нацпредатель, удостоверение N 19719876044787 от 22.09.2014
User avatar
Dmitry67
Уже с Приветом
Posts: 28294
Joined: 29 Aug 2000 09:01
Location: SPB --> Gloucester, MA, US --> SPB --> Paris

Post by Dmitry67 »

AnyaGal wrote:Какие мне все-таки выбрать ключи - естественные или искусственные s Identity?


Увы
Этот спор так же стар как C vs pascal
На эту тему сломано бесчисленное множество копий
Спор отчасти из разряда религиозных
А что лучше... как говаривал старина Малдер, the truth is out there
Зарегистрированный нацпредатель, удостоверение N 19719876044787 от 22.09.2014
User avatar
katit
Уже с Приветом
Posts: 23804
Joined: 05 Jul 2003 22:34
Location: Брест -> St. Louis, MO

Post by katit »

Dmitry67 wrote:Либо система достаточна примитивна и например за раз вставляет только один объект. Тогда Вы можете заранее открутить счетчик в отдельной транзакции


Наверное примитивна :pain1:
Как правило счетчик обрабатывается вне транзакции. Если пролет - то пролет. Пробелы не запoлняются.
User avatar
AnyaGal
Уже с Приветом
Posts: 5771
Joined: 02 Dec 1999 10:01
Location: Saint-Petersburg --> Bellevue, WA

Post by AnyaGal »

Хорошо, если выбрать Identity, то какие основные неудобства в работе меня ожидают и как их избижать или преодолеть малой кровью?
User avatar
Dmitry67
Уже с Приветом
Posts: 28294
Joined: 29 Aug 2000 09:01
Location: SPB --> Gloucester, MA, US --> SPB --> Paris

Post by Dmitry67 »

katit wrote:
Dmitry67 wrote:Либо система достаточна примитивна и например за раз вставляет только один объект. Тогда Вы можете заранее открутить счетчик в отдельной транзакции


Наверное примитивна :pain1:
Как правило счетчик обрабатывается вне транзакции. Если пролет - то пролет. Пробелы не запoлняются.


Вот в этом и проблема
А если в одной транзакции вставляется заранее неизвестное количество объектов то ...

Впрочем у меня самого так было... система была тянута с Btrieve, а там identity не было
Зарегистрированный нацпредатель, удостоверение N 19719876044787 от 22.09.2014
User avatar
katit
Уже с Приветом
Posts: 23804
Joined: 05 Jul 2003 22:34
Location: Брест -> St. Louis, MO

Post by katit »

Dmitry67 wrote:Вот в этом и проблема
А если в одной транзакции вставляется заранее неизвестное количество объектов то ...


Не понимаю в чем проблема.

Вот это код процедуры:

Code: Select all

begin transaction
   UPDATE tciSurrogateKey
      SET NextKey = CASE NextKey
                    WHEN 2147483647 THEN 1
                    ELSE NextKey + 1                   
                    END
    WHERE TableName = @iTableName

   IF @@rowcount = 0
   BEGIN
      INSERT tciSurrogateKey
            (TableName
            ,NextKey)
      VALUES(@iTableName
            ,2)
   END
   
   SELECT @oNewKey = CASE NextKey
                     WHEN 1 THEN 2147483647
                     ELSE NextKey - 1
                     END
     FROM tciSurrogateKey
    WHERE TableName = @iTableName

   commit transaction
User avatar
Dmitry67
Уже с Приветом
Posts: 28294
Joined: 29 Aug 2000 09:01
Location: SPB --> Gloucester, MA, US --> SPB --> Paris

Post by Dmitry67 »

AnyaGal wrote:Хорошо, если выбрать Identity, то какие основные неудобства в работе меня ожидают и как их избижать или преодолеть малой кровью?


Неудобства заключаются в следуюшем
Если данные глобальны, то есть передаются между серверами, то придется вести xref tables. Либо использовать guid (но у низ свои неудобства)

По таблице с identity не создать partitioned table. Но вряд ои Вам жто нужно

Главное, если вставляется в стиле insert ... select много записей. то понятно @@identity недостаточно. Копирование документов в identity-based snowflake schema - это мука. Люди пишут циклы (без циклов копирование написать довольно сложно но можно)

Поэтому в случае зранения документов я рекомендую star, ну или смесь snowflake и star, так чтобы каждая запись имела непосредственный выход на корень
Зарегистрированный нацпредатель, удостоверение N 19719876044787 от 22.09.2014
User avatar
Dmitry67
Уже с Приветом
Posts: 28294
Joined: 29 Aug 2000 09:01
Location: SPB --> Gloucester, MA, US --> SPB --> Paris

Post by Dmitry67 »

katit, дело не в процедуре

Вы даеете так
exec GetCounter 'mytab', @cnt output
begin transaction
exec InsMyTab @cnt,'my data'
commit transaction

Пока все хорошо, было две транзакции
теперь в одной транзакции мы вставляем много документов

exec GetCounter 'mytab', @cnt output
begin transaction
exec InsMyTab @cnt,'my data 1'
exec GetCounter 'mytab', @cnt output
exec InsMyTab @cnt,'my data 2'
commit transaction

Проблема в том что если создать два документа вы должны в одной транзакции, то getCounter в любом случае окажется внутри, и будет держать блокировку на счетчик (думаю вы знаете что после commit внутри теле процедуры счетчика блокировку не сбросит и транзакцию не завершит, а просто уменьшит счетчик с 2 до 1)
Зарегистрированный нацпредатель, удостоверение N 19719876044787 от 22.09.2014

Return to “Вопросы и новости IT”