SQL Server - Identity column as a primary key

[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]

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

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

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

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

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

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

Я думаю - стоит. Правда, я не в курсе деталей реализации Autonumber type в Access. Возможно, есть какие-то причины (типа глюков реалиации), из-за которых не стоит.

[AnyaGal]

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

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

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

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

[SBolgov]

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

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

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

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

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

Точно.

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

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

По идее, запрос "delete from Table1" запускается так же легко, как и "delete from Table1 where ID = ..." Т.е. если у врагов есть возможность запустить второй, то они и первый запустят.

[SkyWalker]

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

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

http://msdn.microsoft.com/library/defau ... t_7zas.asp

[zVlad]

На мой взгляд проблема здесь в совершенно необоснованом распространении понятия "первичный ключ" на тип данных Identity. Вспомните, первичный ключ - это "набор атрибутов" однозначно идентифицирующий сущность представленную записью в родительской таблице.
Конечно, кажется удобнее иметь одну колонку, да еще и генерируемую автоматически, но сколько потом головной боли! Уж если хочется заменить набор колонок одной - сделайте эту колонку определяемой внешним образов, как впрочем это обычно и делается всякими employee numbers, document numbers и т.д.
Интересно, а как в таком случае со связями? Что вы в MS SQL объявляете столбец с Identity как Primary key? И есть таблицы с Forieng keys ссылающиеся на родительскую? Или это все поддерживается в коде? А документация по MS SQL рекомендует использовать Identity в качестве Primary Keys?

[YellowMan]

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

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

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

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

Так что дело похоже в архитектуре базы а не в способах реализации ключей.

[katit]

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

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

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

А вообще ваш вопрос избитая тема во всех newsgroups. По тем или иным причинам использование "identity" считается плохой практикой.

[Kotkov]

И 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. согласно бизнес-логике.

[Dmitry67]

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

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

При все том неудобства при работе identity доставляют изрядно

[AnyaGal]

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

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

[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 выше уже все сказали...

[Dmitry67]

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

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

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

[katit]

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

...

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

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

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

А вот "вычислять следующее значение" не советую. Это может тормозить.

[Dmitry67]

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