SQL Server - Identity column as a primary key
-
- Уже с Приветом
- Posts: 5771
- Joined: 02 Dec 1999 10:01
- Location: Saint-Petersburg --> Bellevue, WA
SQL Server - Identity column as a primary key
У нас один продукт поставляется как для 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 каким другим способом?
И 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 каким другим способом?
-
- Уже с Приветом
- Posts: 14006
- Joined: 17 Jun 2003 04:41
Re: SQL Server - Identity column as a primary key
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 соответственно изменятся и все связи в линкинг табле накроются.
Правда ли это?
Гм. Вообще-то приводить базу в состояние, когда родительские записи (уже) удалены, а дочерние остались - это моветон. Ведь может (теоретически) случиться так, что при "добавлении их же" что-то произойдёт "не так", и добавятся не в точности "они же", а что-то немного другое. Например, добавится на одну запись меньше, и в linking table появятся "фантомные" записи без родителя.
ID действительно изменятся, и связи действительно накроются. И это правильно. Потому что когда запись удаляется, а затем добавляется "другая такая же", то это другая запись (хоть и "такая же"), а не та же самая.
AnyaGal wrote:Я сейчас работают одна над относительно небольшим проектом и мне нужно создать небольшую ДБ где будут присутсвовать несколько linking tables . Так вот я думаю стоит мне использовать Identity columns для генерации IDs автоматически или генерить IDs каким другим способом?
Я думаю - стоит. Правда, я не в курсе деталей реализации Autonumber type в Access. Возможно, есть какие-то причины (типа глюков реалиации), из-за которых не стоит.
Не гоните, и не гонимы будете...
-
- Уже с Приветом
- Posts: 5771
- Joined: 02 Dec 1999 10:01
- Location: Saint-Petersburg --> Bellevue, WA
Спасибо.
Я то как раз с SQL Server-ом работаю
Админ говорил, что по разным причинам иногда приходится переносить данные. Короче эти Autonumber i Identity он сильно не любит.
Но это уже его проблемы.
Просто я удивилась по моим представлениям Identity как раз и предназначалась для генерации ПК и было бы странно ее запрещать использовать.
Правда с другой стороны я слышала также, что плохо когда в качестве ПК номера 1,2,3,4,5 и т.д. Типа легче запустить вражеский запрос на удаление или сканирование данных.
Я то как раз с SQL Server-ом работаю
Админ говорил, что по разным причинам иногда приходится переносить данные. Короче эти Autonumber i Identity он сильно не любит.
Но это уже его проблемы.
Просто я удивилась по моим представлениям Identity как раз и предназначалась для генерации ПК и было бы странно ее запрещать использовать.
Правда с другой стороны я слышала также, что плохо когда в качестве ПК номера 1,2,3,4,5 и т.д. Типа легче запустить вражеский запрос на удаление или сканирование данных.
-
- Уже с Приветом
- Posts: 14006
- Joined: 17 Jun 2003 04:41
AnyaGal wrote:Спасибо.
Я то как раз с SQL Server-ом работаю
Админ говорил, что по разным причинам иногда приходится переносить данные. Короче эти Autonumber i Identity он сильно не любит.
С переносом действительно бывают трудности в случаях, когда нужно перенести из одной базы в другую несколько "родительских" записей с прицепленным к ним "детьми".
Если, допустим, в "старой" базе "родители" имели ID = 11, 12 и 13, а в "новой" авто-нумератор выдаёт им другие ID, то "детей" надо переносить с учётом этой замены. Иначе может получиться очень весело, особенно если в "новой" уже есть (другие!) записи с ID 11, 12 и 13.
Но это не повод отказываться от использования identity. Это повод аккуратнее писать скрипты для переноса.
AnyaGal wrote:Просто я удивилась по моим представлениям Identity как раз и предназначалась для генерации ПК и было бы странно ее запрещать использовать.
Точно.
AnyaGal wrote:Правда с другой стороны я слышала также, что плохо когда в качестве ПК номера 1,2,3,4,5 и т.д. Типа легче запустить вражеский запрос на удаление или сканирование данных.
Первый раз слышу. Может, кто из спецов знает подробнее?
По идее, запрос "delete from Table1" запускается так же легко, как и "delete from Table1 where ID = ..." Т.е. если у врагов есть возможность запустить второй, то они и первый запустят.
Не гоните, и не гонимы будете...
-
- Уже с Приветом
- Posts: 317
- Joined: 16 Feb 2001 10:01
- Location: US
При переносе таблиц в MS-SQL можно использовать SET IDENTITY_INSERT ON
Данная опция позволяет вставлять данные в identity колонку.
http://msdn.microsoft.com/library/defau ... t_7zas.asp
Данная опция позволяет вставлять данные в identity колонку.
http://msdn.microsoft.com/library/defau ... t_7zas.asp
-
- Уже с Приветом
- Posts: 15312
- Joined: 30 Apr 2003 16:43
На мой взгляд проблема здесь в совершенно необоснованом распространении понятия "первичный ключ" на тип данных Identity. Вспомните, первичный ключ - это "набор атрибутов" однозначно идентифицирующий сущность представленную записью в родительской таблице.
Конечно, кажется удобнее иметь одну колонку, да еще и генерируемую автоматически, но сколько потом головной боли! Уж если хочется заменить набор колонок одной - сделайте эту колонку определяемой внешним образов, как впрочем это обычно и делается всякими employee numbers, document numbers и т.д.
Интересно, а как в таком случае со связями? Что вы в MS SQL объявляете столбец с Identity как Primary key? И есть таблицы с Forieng keys ссылающиеся на родительскую? Или это все поддерживается в коде? А документация по MS SQL рекомендует использовать Identity в качестве Primary Keys?
Конечно, кажется удобнее иметь одну колонку, да еще и генерируемую автоматически, но сколько потом головной боли! Уж если хочется заменить набор колонок одной - сделайте эту колонку определяемой внешним образов, как впрочем это обычно и делается всякими employee numbers, document numbers и т.д.
Интересно, а как в таком случае со связями? Что вы в MS SQL объявляете столбец с Identity как Primary key? И есть таблицы с Forieng keys ссылающиеся на родительскую? Или это все поддерживается в коде? А документация по MS SQL рекомендует использовать Identity в качестве Primary Keys?
-
- Уже с Приветом
- Posts: 1099
- Joined: 30 Sep 1999 09:01
- Location: Bryansk,RUSSIA >> Dublin, Ireland
zVlad wrote:Конечно, кажется удобнее иметь одну колонку, да еще и генерируемую автоматически, но сколько потом головной боли!
Интересно, а как в таком случае со связями? Что вы в MS SQL объявляете столбец с Identity как Primary key? И есть таблицы с Forieng keys ссылающиеся на родительскую? Или это все поддерживается в коде?
На мой взгляд проблема зесь в недостатке понимания что такое первичный ключ и нормализация и зачем они нужны - ну скажите мне на радость чем головная боль от исскуственного ключа отличается от боли вызванной естественным ключом, не считая того что естественный ключ редко бывает не составным, а соответственно более сложным в программировании и поддержке? Я конечно не говорю про обеспечение уникальности - это совсем другая история.
Со связями - все точно так-же. Если Вы осознаете что делаете, искуственный ключ ничем не отличается от естественного и точно так-же может поддерживаться связями или кодом, кому что ближе по духу.
Так что дело похоже в архитектуре базы а не в способах реализации ключей.
Удачи@С.Смирнов
-
- Уже с Приветом
- Posts: 23804
- Joined: 05 Jul 2003 22:34
- Location: Брест -> St. Louis, MO
ППКС 2 поста выше.
В нашей базе есть искуственные ключи. Они для быстродействия. При этом в большинстве таблиц также существует "натуральный" ключ физически реализованный как уникальный индекс.
Identity вообще не используется. Есть 2 служебных процедуры для генерации ключей. Их можно вызывать или они автоматом вызовутся из триггера.
Программа писалась давно (во времена сервера 6) Если я правильно понимаю тогда были проблемы с извлечением ключей после вставки. Да и сейчас не надо думать о том "что-же туда вставилось". Тем более что очень много данных проходит чеpез #таблицы и всяческие манипулации перед тем как попасть в базу.
А вообще ваш вопрос избитая тема во всех newsgroups. По тем или иным причинам использование "identity" считается плохой практикой.
В нашей базе есть искуственные ключи. Они для быстродействия. При этом в большинстве таблиц также существует "натуральный" ключ физически реализованный как уникальный индекс.
Identity вообще не используется. Есть 2 служебных процедуры для генерации ключей. Их можно вызывать или они автоматом вызовутся из триггера.
Программа писалась давно (во времена сервера 6) Если я правильно понимаю тогда были проблемы с извлечением ключей после вставки. Да и сейчас не надо думать о том "что-же туда вставилось". Тем более что очень много данных проходит чеpез #таблицы и всяческие манипулации перед тем как попасть в базу.
А вообще ваш вопрос избитая тема во всех newsgroups. По тем или иным причинам использование "identity" считается плохой практикой.
-
- Уже с Приветом
- Posts: 342
- Joined: 13 Mar 2002 10:01
- Location: California
Re: SQL Server - Identity column as a primary key
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. согласно бизнес-логике.
Кто ищет, тот всегда найдет.
Живи своим умом, Пчёла! ©
Живи своим умом, Пчёла! ©
-
- Уже с Приветом
- Posts: 28294
- Joined: 29 Aug 2000 09:01
- Location: SPB --> Gloucester, MA, US --> SPB --> Paris
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
-
- Уже с Приветом
- Posts: 5771
- Joined: 02 Dec 1999 10:01
- Location: Saint-Petersburg --> Bellevue, WA
Я понимаю про натуральные и искусственные первичные ключи, но в данном случае у меня большие сомнения, что постановщик задачи полностью знает чего хочет и может четко сказать, будет ли поле - кандидат на естественный первичный ключ - уникальным. Поэтому гораздо удобнее создать искуственный.
Так что тогда лучше написать служебную процедуру, которая бы вычисляла зачение следующего свободного первичного ключа и вызывать ее при Insert-ах, а Identity убрать?
Так что тогда лучше написать служебную процедуру, которая бы вычисляла зачение следующего свободного первичного ключа и вызывать ее при Insert-ах, а Identity убрать?
-
- Удалён за грубость
- Posts: 3347
- Joined: 23 Nov 1999 10:01
- Location: NC -> NYC -> KC -> Chicago
а удаление связанных 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 выше уже все сказали...
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 выше уже все сказали...
-
- Уже с Приветом
- Posts: 28294
- Joined: 29 Aug 2000 09:01
- Location: SPB --> Gloucester, MA, US --> SPB --> Paris
AnyaGal wrote:Так что тогда лучше написать служебную процедуру, которая бы вычисляла зачение следующего свободного первичного ключа и вызывать ее при Insert-ах, а Identity убрать?
Ни в коем случае
Дело в том что identity использует механизм autonomous transactions, который в явном виде пользователю SQL server недоступен.
Благодаря этому в таблицу с identity могу вставлять сколько угодно коннекций сразу. Если вы сделаете свой счетчик, то все сразу пересекуться на блокировке по этому счетчику, и вы сразу превратите систему в однопользовательскую
Зарегистрированный нацпредатель, удостоверение N 19719876044787 от 22.09.2014
-
- Уже с Приветом
- Posts: 23804
- Joined: 05 Jul 2003 22:34
- Location: Брест -> St. Louis, MO
Dmitry67 wrote:Ни в коем случае
...
, и вы сразу превратите систему в однопользовательскую
У нас есть отдельная таблица с "TableName" и "NextKey" полями.
Процедура просто берет и increment (или insert)
Никаких блокировок у тысяч пользователей нет. Поддерживает до 200 клиентов совершенно без проблем.
А вот "вычислять следующее значение" не советую. Это может тормозить.
-
- Уже с Приветом
- Posts: 28294
- Joined: 29 Aug 2000 09:01
- Location: SPB --> Gloucester, MA, US --> SPB --> Paris
-
- Уже с Приветом
- Posts: 23804
- Joined: 05 Jul 2003 22:34
- Location: Брест -> St. Louis, MO
-
- Уже с Приветом
- Posts: 5771
- Joined: 02 Dec 1999 10:01
- Location: Saint-Petersburg --> Bellevue, WA
Чего-то я вообще запуталась.
Мне собственно надо создать небольшую content managment систему для сайта нашей компании для пользователей.
То есть чтобы кто-то из нашего CS мог через web страницы выбрать категорию нашего продукта, сам продукт, его версию, номер патча/билда/сервис пака и т.д и добавить информацию для пользователя, путь к файлу, который можно прочитать или сделать upload/download.
Также выбрать дату когда вся эта информация expired. Все это сохраняется в БД.
А в run time для пользователя все ASP страницы генерятся автоматически и информация берется из БД.
То есть в БД будут такие таблицы как ProductCategory, Product, ReleaseGroup, ReleaseElement, ProductVersion, уникальных названий для всех этих категорий можно сказать нет, или есть но длинные, и которые могут менятся со временем. Я конечно могу для них придумать сокращения, но их надо будет всем помнить.
Но я боюсь, что разные люди из разных отделов уже используют свои сокращенные названия.
Какие мне все-таки выбрать ключи - естественные или искусственные s Identity?
Kонтроль за всякими constraints i cascade delete/update могу установить - тут моя вотчина, начинаю с нуля, чего хочу того и ворочу , поэтому хочется сделать как лучше.
Мне собственно надо создать небольшую content managment систему для сайта нашей компании для пользователей.
То есть чтобы кто-то из нашего CS мог через web страницы выбрать категорию нашего продукта, сам продукт, его версию, номер патча/билда/сервис пака и т.д и добавить информацию для пользователя, путь к файлу, который можно прочитать или сделать upload/download.
Также выбрать дату когда вся эта информация expired. Все это сохраняется в БД.
А в run time для пользователя все ASP страницы генерятся автоматически и информация берется из БД.
То есть в БД будут такие таблицы как ProductCategory, Product, ReleaseGroup, ReleaseElement, ProductVersion, уникальных названий для всех этих категорий можно сказать нет, или есть но длинные, и которые могут менятся со временем. Я конечно могу для них придумать сокращения, но их надо будет всем помнить.
Но я боюсь, что разные люди из разных отделов уже используют свои сокращенные названия.
Какие мне все-таки выбрать ключи - естественные или искусственные s Identity?
Kонтроль за всякими constraints i cascade delete/update могу установить - тут моя вотчина, начинаю с нуля, чего хочу того и ворочу , поэтому хочется сделать как лучше.
-
- Уже с Приветом
- Posts: 28294
- Joined: 29 Aug 2000 09:01
- Location: SPB --> Gloucester, MA, US --> SPB --> Paris
katit wrote:Dmitry67 wrote:если Вы это делаете в отдельной транзакции. Если в транзакции вы заранее не знаете сколько и какие объекты будете вставлять то либо ваши изменения счетчика попадут в транзакцию, либо их надо делать в отдельной коннекции.
Да, там по старинке. Каждому клиенту - по соединению.
Да нет
Вам каждому клиенту нужно ДВА соединения
Либо система достаточна примитивна и например за раз вставляет только один объект. Тогда Вы можете заранее открутить счетчик в отдельной транзакции
Наконец, можно даже предположить что Вы читаете счетчик в readuncommitted, но не думаю что так у вас делают. Впрочем, и в жтом случае есть засады
Зарегистрированный нацпредатель, удостоверение N 19719876044787 от 22.09.2014
-
- Уже с Приветом
- Posts: 28294
- Joined: 29 Aug 2000 09:01
- Location: SPB --> Gloucester, MA, US --> SPB --> Paris
AnyaGal wrote:Какие мне все-таки выбрать ключи - естественные или искусственные s Identity?
Увы
Этот спор так же стар как C vs pascal
На эту тему сломано бесчисленное множество копий
Спор отчасти из разряда религиозных
А что лучше... как говаривал старина Малдер, the truth is out there
Зарегистрированный нацпредатель, удостоверение N 19719876044787 от 22.09.2014
-
- Уже с Приветом
- Posts: 23804
- Joined: 05 Jul 2003 22:34
- Location: Брест -> St. Louis, MO
-
- Уже с Приветом
- Posts: 5771
- Joined: 02 Dec 1999 10:01
- Location: Saint-Petersburg --> Bellevue, WA
-
- Уже с Приветом
- Posts: 28294
- Joined: 29 Aug 2000 09:01
- Location: SPB --> Gloucester, MA, US --> SPB --> Paris
katit wrote:Dmitry67 wrote:Либо система достаточна примитивна и например за раз вставляет только один объект. Тогда Вы можете заранее открутить счетчик в отдельной транзакции
Наверное примитивна
Как правило счетчик обрабатывается вне транзакции. Если пролет - то пролет. Пробелы не запoлняются.
Вот в этом и проблема
А если в одной транзакции вставляется заранее неизвестное количество объектов то ...
Впрочем у меня самого так было... система была тянута с Btrieve, а там identity не было
Зарегистрированный нацпредатель, удостоверение N 19719876044787 от 22.09.2014
-
- Уже с Приветом
- Posts: 23804
- Joined: 05 Jul 2003 22:34
- Location: Брест -> St. Louis, MO
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
-
- Уже с Приветом
- Posts: 28294
- Joined: 29 Aug 2000 09:01
- Location: SPB --> Gloucester, MA, US --> SPB --> Paris
AnyaGal wrote:Хорошо, если выбрать Identity, то какие основные неудобства в работе меня ожидают и как их избижать или преодолеть малой кровью?
Неудобства заключаются в следуюшем
Если данные глобальны, то есть передаются между серверами, то придется вести xref tables. Либо использовать guid (но у низ свои неудобства)
По таблице с identity не создать partitioned table. Но вряд ои Вам жто нужно
Главное, если вставляется в стиле insert ... select много записей. то понятно @@identity недостаточно. Копирование документов в identity-based snowflake schema - это мука. Люди пишут циклы (без циклов копирование написать довольно сложно но можно)
Поэтому в случае зранения документов я рекомендую star, ну или смесь snowflake и star, так чтобы каждая запись имела непосредственный выход на корень
Зарегистрированный нацпредатель, удостоверение N 19719876044787 от 22.09.2014
-
- Уже с Приветом
- Posts: 28294
- Joined: 29 Aug 2000 09:01
- Location: SPB --> Gloucester, MA, US --> SPB --> Paris
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)
Вы даеете так
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