Primary Key и Unique key

← →
Piter © (2005-10-04 22:19) [0]

Объясните, пожалуйста, человеческим языком - зачем нужен Primary Key?

Большинство отвечает - чтобы создать уникальное поле. Ок, а чем это тогда отличается от Unique key?
Большинство отвечает, что в Primary Key можно указывать несколько полей.

Это весь функционал этих директив?

А что физически в базе происходит при этом? Так ли важно задавать первичный ключ? Если первичный ключ - одно поле, то можно легко заменить на Unique key?

А если у меня все поля могут быть одинаковые - то Primaty Key не нужен?

А если уникальность нужна только для всех полей - допустим указывать в Primary Key все поля базы? Имеет ли это смысл?

Запутался чего-то я...

← →
Anatoly Podgoretsky © (2005-10-04 22:41) [1]

Primary Key нужен для одназначной идентификации записей, имеет специальный статус для базы.

← →
Piter © (2005-10-04 22:45) [2]

Anatoly Podgoretsky © (04.10.05 22:41) [1]
Primary Key нужен для одназначной идентификации записей

Ну я об этом писал:

Piter © (04.10.05 22:19)
Большинство отвечает - чтобы создать уникальное поле. Ок, а чем это тогда отличается от Unique key?

← →
Sergey_Masloff (2005-10-04 22:51) [3]

Да просто выбран он как праймари. Любой уникальный является кандидатом на роль первичного. Исходя из логики приложения какой-то выбирают на роль первого среди равных (уникальных) то есть первичного. А так как все имеет тенденцию к изменению то в качестве первичного обычно создается дополнительный (суррогатный) не имеющий какого-то реального воплощения в предметной области.

← →
Piter © (2005-10-04 22:58) [4]

Sergey_Masloff (04.10.05 22:51) [3]
А так как все имеет тенденцию к изменению то в качестве первичного обычно создается дополнительный (суррогатный) не имеющий какого-то реального воплощения в предметной области.

типа auto increment?

А что значит тенденцию к изменению? Разве поле первичного ключа нельзя менять? Главное, чтобы не было такого же поля...

← →
Anatoly Podgoretsky © (2005-10-04 22:59) [5]

Разница в основном в понятии, но иногда и физически - для Парадокс и MSSQL
Уникальный индекс делают для недопустимости одинаковых значений, а не для идентификации. Первичный ключ всегда уникальный и не может содержать NULL значений. И система и средства разработки знают про него.
У первичного ключа особый статус, поэтому он очень важен. Ты можешь не делать никаких индексов, но первичный ключ обязан быть, без него может быть много проблем.

Уникальный индекс не обязан состоять из одного поля, может из нескольких.

← →
Piter © (2005-10-04 23:03) [6]

В принципе, я кажется потихоньку догоняю. Я так понимаю (правда не знаю почему) - но физически в структуре базе данных должно быть уникальное поле - чисто из принципов построения СУБД наверное следует.

И поэтому нужно задать Primary Key... если же это поле не будет задано - то база сама создаст дополнительное поле и обеспечит его уникальность...

← →
Sergey_Masloff (2005-10-04 23:05) [7]

Piter © (04.10.05 22:58) [4]
>Разве поле первичного ключа нельзя менять?
Нельзя. На то он и первичный. Нет, технически это возможно. Также как два разных COM интерфейса можно с одним IID создать. Только на хрена? Так что для простоты лучше считать что нельзя.
А насчет того что изменяется - уникальные ключи имеющиеся в реальном мире имеют тенденцию к изменениям. Номера паспортов, документов, ИННы -все меняется и кто заложился на их константность раз за разом огребают проблем. Впрочем по этому поводу лучше почитать мнение Тенцера (есть в подборке статей на http://www.podgoretsky.com). Вообще по этому вопросу ИМХО все ясно было еще в конце 70-х но микрохоливоры не прекращаются.

← →
Sergey_Masloff (2005-10-04 23:09) [8]

Piter © (04.10.05 23:03) [6]
>И поэтому нужно задать Primary Key... если же это поле не будет задано - >то база сама создаст дополнительное поле и обеспечит его уникальность...
Нет это дополнительное поле представляющее адрес записи будет создано в любом случае - задавай ты праймари кей или не задавай. Но тебе от этого не жарко не холодно - нет первичного ключа огребаешь проблемы с путаной логикой и тормозами есть праймари кей - наслаждаешься всеми доп. возмжностями типа ссылочной целостности и др.

← →
Anatoly Podgoretsky © (2005-10-04 23:22) [9]

Я даже догадывают почему вопрос, в свое время огреб проблем с МФ из-за отсуствия ключа

← →
Anatoly Podgoretsky © (2005-10-04 23:24) [10]

Piter © (04.10.05 23:03) [6]
Уникальное поле не обязано быть, в отличии от первичного ключа. Еще раз у них разная семантика, хотя внешне и похоже. Как реализован ключ через индексы или по другому роли не играет - это внутреннее дело базы.

← →
sniknik © (2005-10-04 23:47) [11]

> у них разная семантика, хотя внешне и похоже.
как говорил еще капитан Врунгель © - "Каждая селедка рыба, но не каждая рыба селедка!". о как. ;) -> "каждый Primary Key Unique, но не каждый Unique key Primary" ;о))

← →
Piter © (2005-10-05 00:55) [12]

Sergey_Masloff (04.10.05 23:05) [7]
Нельзя. На то он и первичный

мне кажется, ты меня не понял. Я имел в виду, что значение поля, которое является уникальным ключом, можно менять. Это ведь так?

Ну например, есть поле Name, оно является уникальным ключом. Могу же я у конкретной записи сменить значение Name?
Или другими словами, поле Name может входить в выражение UPDATE?

А то, что менять сам ключ, например с поля Name на поле Family, не рекомендуется - это понятно...

Anatoly Podgoretsky © (04.10.05 23:22) [9]
в свое время огреб проблем с МФ из-за отсуствия ключа

На самом деле, вопрос не из-за МФ возник, тут просто захотелось разобраться в вопросе.
Да и в принципе, как таковых проблем я в МФ не замечал, хоть там и действительно не задан PK.
Единственное, что может он может быть мог работать быстрее, если было бы PK...

Вообще, сейчас интересен вопрос - а чем грозит отсутствие PK? Я так понимаю, только тормозами?

И вот еще что хотелось бы узнать. Например, логика базы такова, что уникальными могут быть сочетания только всех полей. Имеет смысл делать PK по ВСЕМ полям таблицы?

И еще один случай, например база данных сотрудников, включает:

Name | Family | Year ...

В предельном случае понятно, что все поля могут совпадать у двух сотрудников. Имеет смысл заводить какое-то отдельное auto increment поле, которое и назначать уникальным ключом?
И если это имеет смысл - то какой?

← →
Megabyte © (2005-10-05 01:29) [13]

Смысл, наверное, в том, чтобы не беспокоиться о том, что могут быть совпадения в этих 3-х полях.
Да и послений номер автоинкрементного говорит о том, сколько записей вносилось в эту базу(некоторая информация о масштабности базы).

← →
Sergey_Masloff (2005-10-05 06:24) [14]

Piter © (05.10.05 00:55) [12]
>>Нельзя. На то он и первичный

>мне кажется, ты меня не понял. Я имел в виду, что значение поля, которое >является уникальным ключом, можно менять. Это ведь так?

Да все я понял. Лучше считай что нельзя - ну честное слово. Вот смотри - есть у меня интерфейс. У него есть уникальный идентификатор - GUID. Я создаю другой интерфейс с таким же идентификатором - физически это возможно, языковые средства позволяют. Но сделать так - путь к огромным проблемам так как вся идеология COM базируется на предположении что IID каждого интерфейса - разный.
Продолжим. Можно в Delphi в наследнике понизить видимость члена класса? Был паблик стал привейт? Да пожалуйста. Ну подумаешь хинт компайлера. Только делать это нельзя ибо противоречит всей идеологии ООП (да и, к счастью, ,бесполезно потому что реально все останется как было ).
Также и с ПК. В некоторых (большинстве) СУБД менять можно его значение. Просто необходимость в изменении значения ПК записи - в 99.9999 % случаев признак плохого проектирования.

>Имеет смысл заводить какое-то отдельное auto increment поле, которое и >назначать уникальным ключом?
>И если это имеет смысл - то какой?
Экономия времени и памяти на индексах внешних ключей.

← →
Sergey_Masloff (2005-10-05 06:26) [15]

Megabyte © (05.10.05 01:29) [13]
>Да и послений номер автоинкрементного говорит о том, сколько записей >вносилось в эту базу
Не, вот так лучше не делать (обычно). Не говоря о том что шаг инкремента не обязан быть 1, есть и масса других "против"

← →
MOA © (2005-10-05 09:41) [16]

>чем грозит отсутствие PK?
Тем, что данное отношение не будет ни в какой нормальной форме. Все кортежи в отношении обязаны быть уникальными - по определению отношения (мнодества). Та минимальная совокупность полей, которая позволяет отличить один кортеж от другого - есть ключ-кандидат. Таких совокупностей в отношении может быть не одна. Из ключей-кандитатов выбираем какой-то один и называем его первичным ключом.
Можно самим ввести суррогатный ключ (автоинкремент, GUID и т.д.). Холиваров по этому поводу куча.
Если мы не выделяем первичный ключ - значит, мы игнорируем ту теорию, на которой, собственно, и построены РСУБД - а значит, рано или поздно наступим на грабли, и чем позже наступим - тем больше разобъём лоб ;).

← →
Piter © (2005-10-05 09:55) [17]

Sergey_Masloff (05.10.05 6:24) [14]
Да все я понял. Лучше считай что нельзя - ну честное слово

погодь... а если PK по нескольим полям... Что, у у записи ни одно это поле менять не надо?!

А как же быть тогда в случае, который я привел - что все поля могут совпадать, например Имя, Фамилия, год рождения человека?

← →
msguns © (2005-10-05 09:55) [18]

Основное семантичесое (смысловое) назначение PK - поддержка межтабличных связей. Как наиболле распространенный пример - счет-фактура. Для того, чтобы строки фактуры не "потерялись" в самом счете (заголовке) и служит PK таблицы заголовка, на которую ссылаются записи таблицы фактуры. Именно поэтому не имеет смысла менять это поле в главной таблице.
Кроме того, на PK посторен механизм FK, обеспечивающий т.н. справочніе ссылки.
Для лучшего понимания механизма PK советую почитать немного теории. В частности о нормализации, избыточности и ссылочной целостности данных в БД.
Еще одна характеристика PK - это существенное ускорение доступа к данным из-за встроенного в любой сервер механизма индексации и кэширования. Доступ к таблицам без PK, даже линейным (не имеющим структурированные данные и хранящие инфу об объектах в виде одиночных, несвязанных записей), в сотни, а то и тысячи раз медленнее по сравнению с той же таблицей, но имеющей PK.

УИ - это совсем другое, хотя, как справедливо заметил АП, механизм его реализации построен похоже. Однако, в отличие от PK, неграмотно и невдумчиво построенные индексы могут не только не ускорить работу с таблицами, но и существенно ее замедлить (нарушено правило 20%).

← →
Sergey_Masloff (2005-10-05 10:01) [19]

Piter © (05.10.05 09:55) [17]

>погодь... а если PK по нескольим полям... Что, у у записи ни одно это поле >менять не надо?!
Именно так. Или не использовать эти поля как первичный ключ. Как уникальный - пожалуйста.

← →
msguns © (2005-10-05 10:08) [20]

>Piter © (05.10.05 09:55) [17]
>погодь... а если PK по нескольим полям... Что, у у записи ни одно это поле менять не надо?!

Существует два типа PK:
- нативный (естественный). Например, данные при рождении человека ФИО. Этот "ключ" содержит 3 поля. И, в общем-то может меняться. Однако эта триада уникальна лишь в отдельном, небольшом пространстве объектов. Например, в семье. У Петра Иванова вряд ли будет два сына Сидора, однако в классе, где учится Сидор Петрович Иванов, запросто может быть его полный тезка. В этом случае ключ уже не будет уникальным и к нему следует добавить еще одно поле: например дату рождения. Хотя нет никакой гарантии, что когда Сидор поступит в институт, там не будет учиться такой же Сидор, но родившийся с ним в один день.
Кроме того, по жизни Сидор Петрович может поменять свое имя или даже фамилию.
Именно поэтому нативный PK имеет ограниченное использование, хотя порою весьма оправданное. Как пример, можно привести лицевой счет по з/пл, где в качестве нативного ключа используется связка [год]-[месяц]-[ид-р работника]-[код начиления/удержания]. В рамках БД этот "салат" гарантированно не должен повторяться.

- суррогатный, т.е. реально не сущестующий, но необходимый для однозначной идентификации объекта хранения атрибут-поле. Как пример - ИНН (индивидуальный налоговый номер). Один раз присвоенный, такой ключ не меняется до самой смерти человека. Но даже когда он помрет, инфа о нем останентся в БД. Именно по ИНН чел может быть однозначно идентифицирован. При этом он может менять свой адрес, ФИО, даже паспорт.

← →
Sergey_Masloff (2005-10-05 10:10) [21]

msguns © (05.10.05 10:08) [20]
>Именно по ИНН чел может быть однозначно идентифицирован
Побожись ;-)
У меня лично ИНН менялся 3 раза. Официально.

← →
Val © (2005-10-05 10:14) [22]

>Sergey_Masloff (05.10.05 10:10)
ну и что? где противоречие? думаете старые присвоили другим людям? :) записи с pk тоже удаляют из таблиц, но pk не перестает однозначно определять запись.

← →
msguns © (2005-10-05 10:15) [23]

>Sergey_Masloff (05.10.05 10:10) [21]
>У меня лично ИНН менялся 3 раза. Официально.

Сережа, помилосердствуй.. Для наших "правителей", меняющих законы и системы контроля и учета, как перчатки, в угоду то одной, то другой правящей верхушке, вообще правила не писаны. Какие уж тут БД. Однако разработчик модели данных любой прикладухи должен исходить из определенных предпосылок-догм. Иначе вообще ничего не стоит проектировать.

← →
MOA © (2005-10-05 10:26) [24]

Кстати, с коды наподобие ИНН и пример приведённый msguns © (05.10.05 10:08) [20] как раз хорошая иллюстрация.
Делаем суррогатный ключ, назначаем его первичным.
А затем объявляем, что поле ИНН (пенсионный код, код мед. страхования) д.б. уникальное (не может быть одинаковый код у двух людей). Понятно, что они уникальные, понятно, первичный-один. И крайне желательно, чтобы не менялся - иначе геморрой с каскадными обновленмиями - но это уже как раз тема холивара ;).
Или объявляем, что msguns © комбинация полей "[год]-[месяц]-[ид-р работника]-[код начисления/удержания]" - должна быть уникальной. Тем самым, вводим "бизнес-правила" - и гарантируем, что база будет избавлена хотя бы от этих ошибок.

← →
Sergey_Masloff (2005-10-05 10:39) [25]

MOA © (05.10.05 10:26) [24]
ап чем и речь ;-))

← →
evvcom © (2005-10-05 10:41) [26]

Кроме того, чем ближе разрядность PK к разрядности регистра процессора, тем быстрее будет осуществляться связка (join). Поэтому в качестве первичных ключей крайне нежелательно использовать символьные поля длиной более 4 байт для 32-разрядных ОС, имхо.
Наиболее часто употребимо, насколько мне известно, первое поле с именем, начинающимся на "ID" целого типа (4 байта) (суррогатный ключ). На все остальное, если надо, создаются индексы уникальные (ключи) или неуникальные.

← →
Sergey_Masloff (2005-10-05 10:43) [27]

Val © (05.10.05 10:14) [22]
>ну и что? где противоречие? думаете старые присвоили другим людям? :)
Не исключаю. Допустим с бланками полисов ОСАГО такое было - а это БСО со степенью защиты как у ден. знаков. Это сведения 100% точности и не единичный случай а массовое явление было.

>записи с pk тоже удаляют из таблиц, но pk не перестает однозначно >определять запись.
У меня не удаляют. В историю можно отправить а удалить - фигушки. Много раз спасало от таких проблем что страшно представить. Вообще, насколько я сталкивался, в больших системах это довольно распространенная практика.

← →
ANB © (2005-10-05 10:47) [28]

Чисто логически ПК и УК не отличаются. Более того, по ПК автоматически создается УК.
Идея придумана для облегчения жизни нам - разработчикам. Когда залезаешь в таблицу, то УК там может быть много, а ПК - только один и я сразу вижу, что является ключом таблицы. И другим программистам видно. В своем проекте через год уже придется разбираться заново, а в чужом, так вообще без указания ПК повесишься копаться.

Как правило, ПК делают суррогатным. ИНН, кстати, попытка использовать естественный ключ, так же как и номер в ПФР. Я раз попался на эту удочку - сделал его ключом (в Clipper прямое понятие ПК нет, генераторов тоже и я поленился делать суррогатную генерацию ключей). Сколько граблей я после этого огреб - мама дорогая. Выяснилось, что далеко не у всех работников есть ИНН и номер ПФР. А различать их надо. Пришлось генерить "суррогатные" номера ПФР, отличать их от обычных (в документах левый номер не должен был появляться). Затем начались проблемы с каскадным обновлением при присвоении номеров.
Когда перешел на оракл - завязал я с естественными ключами баловаться.
Более того, на одном месте работы нас приучили даже в линковочные таблицы вставлять суррогатный ИД (там то он вообще не нужен), в противном случае не принимали таблицу на генерацию на реальный сервер.

Первичный ключ нужен для обеспечения ссылочной целостности в БД.
Уникальный ключ нужен для обеспечения неповторяемости значений в таблице.
То, что первичный одновременно является уникальным, - следствие, иногда вызывающее непонимание, как у автора. Назначение у них разное.

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

В некоторых условиях на таблицу я накладываю ограничение уникальности, не ставя первичного ключа. Например, если это таблица с внешним ключом (он же будет и уникальным). Эта таблица может использоваться чрезвычайно редко, да и строк в ней меньше, чем в той, на которую она ссылается. Поэтому я не объединяю эти 2 таблицы в одну. Денормализация, конечно, но иногда оправданная.

Кстати, в некоторых СУБД есть еще дополнительные возможности при наличии в таблице первичного ключа. Например, в Оракл таблицу можно организовать по индексу, но только по PK. Т.е. без PK так сделать нельзя. Организовать по индексу - это таблица-индекс в одном флаконе.

>ANB © (05.10.05 10:47) [28]

Похожая траблища была и на радиозаводе, где я когда-то работал. Там была прога еще на ЕС-ках по з/пл, где ключом был табельный номер. Все бы хорошо, но вся закавыка была в том, что этот табельный у чела вполне мог поменяться, например, при переходе из одного подразделения в другое (таб номер был ПППЧЧЧЧ (№ подразделения+№чела в подразделении). И получалась милая штука: в первые несколько месяцев после смены места работы челу нельзя было посчитать ни отпускные, но больничные, ни квартальную премию. Все делалось "ручками" непосредственно в расчетной группе бухгалтерии. А в ИВЦ два прогера трудились исключительно для выявления "перебежчиков", сбора по ним данных из разных ТН, распечатке этих данных и предоставлении отчетов в расчетную группу. Учитывая, что на заводе работало 12-14 тыщ народу, работы у них было выше крыши.
И уж совсем ж.. начиналась, когда надо было дать инфу для исчисления пенсии. Помницца, даже написал прожку на асме, которая выгребала из архивов инфу по челу, ища его по ФИО и доп.указанным реквизитам (номер подразделения, дата рождения, стаж, должность и т.д.).
Кстати, насколько асмовские проги, работающие с лентами (архивы были на лентах) на физуровне были шустрее аналогичных кобол- и PL-ных ;))

> Там была прога еще на ЕС-ках
- на больших ? Круто. Вообще то я на них только учился, работать нормально уже не довелось. Но когда учился - то делали учетную систему для курсантов. На адабасе. Кстати, куда он делся ? Клевая СУБД была.

msguns © (05.10.05 10:08)
- суррогатный, т.е. реально не сущестующий, но необходимый для однозначной идентификации объекта хранения атрибут-поле. Как пример - ИНН (индивидуальный налоговый номер).

брат, когда еще работал в налоговой, рассказывал, что у них в базе есть куча людей с одинаковыми ИНН :)))
конечно, такого быть не должно и как-то они их разруливают, но, всетаки, на PK он не тянет.

> Danilka © (05.10.05 11:28) [32]
- точно точно. Я сам задрался ругаться. В фоксе не всегда работает контроль на дубли, к тому же при приеме сведений от других источников этот контроль не ведется даже в новых системах. Куча дублей получается и при объединении районных налоговых.

msguns © (05.10.05 9:55) [18]
Основное семантичесое (смысловое) назначение PK - поддержка межтабличных связей

а если в логике приложения таблица только одна. PK не нужен?

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

[34]
можно обойтись уником, если он не очень длинный и прироста таблиц не ожидается
но я бы все равно сделал суррогатный PK
по нему потом легче позиционироваться при UPDATE|DELETE|SELECT - быстрее на сервере и в коде понятнее

Anatoly Podgoretsky © (05.10.05 18:44) [35]

не проникся духом написанного? Это ответ программиста или священика?

У меня вполне конкретный вопрос - если PK нужен чисто для целостности ссылок - то при наличии всего одной таблицы - он не нужен?

> если PK нужен чисто для целостности ссылок
но он нужен не только "чисто для целостности".

перечитай написанное, видать не проникся.

Primary Key и Unique key Найти похожие ветки