Создание суррогатных ключей с использованием свойства IDENTITY - Azure Synapse Analytics

Первичный ключ — составной или суррогатный?

У меня есть таблица из 3 полей, комбинация значений которых уникальна для каждой записи. Вот эти поля:

Ни одно из этих полей никогда не будет пустым, и ни один объект в один и тот же момент времени не будет иметь такое же значение Ticket_Number .

Поэтому мне кажется, что, вместо добавления нового поля, единственным назначением которого будет уникально идентифицировать строку в таблице, я могу использовать комбинацию этих трех полей. Но в руководстве PL/SQL Developer’s Guide рекомендуется не использовать составные первичные ключи.

Что ты думаешь по этому поводу?

Надо учитывать также следующее:

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

С другой стороны, если лучше добавить новое числовое поле для идентификации записей, как проще всего увеличивать значение этого поля при каждой вставке? Есть ли в Oracle подобие типа данных autonumber MS Access?

Ответ Тома Кайта

Если требуется, чтобы «эти три поля уникально идентифицировали запись в любом случае», придется задавать по ним ограничение уникальности (UNIQUE CONSTRAINT) в любом случае. Если дублирование object_id,ticket_number,start_datetime — ошибка, ограничение уникальности НЕОБХОДИМО.

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

Чтобы получить тип » auto increment » в Oracle, необходимо выполнить:

Иногда высказывают опасение, что при генерации последовательных номеров таким образом возможны пропуски (связанные с откатом транзакции, например — В.К. ).

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

Последовательности — хорошо масштабируемый способ генерации суррогатных ключей.

Я считаю, что составные ключи прекрасно работают и могут использоваться при наличии внешних ключей, но:

Последовательность меньше, чем 3 столбца. Если ключ необходимо использовать как внешний в множестве других таблиц, экономия места может оказаться огромной.
Люди часто пытаются изменять первичный ключ, чтобы исправить данные — использование неизменного суррогатного ключа решает раз и навсегда проблему каскадных изменений , поскольку первичный ключ (значение последовательности) изменять не придется никогда — только 3 столбца данных, которые больше нигде не хранятся.
Проще написать: чем да и работает первый запрос быстрее.

Эти соображения необходимо учитывать. Как я уже писал, если составной первичный ключ не используется в качестве внешнего во многих таблицах — используйте его. В противном случае серьезно задумайтесь над использованием суррогатного ключа на базе последовательности (а про «пропуски» значений не думайте вовсе — важно, что получается уникальный идентфикатор).

Составной ключ в одном столбце

Мне интересно твое мнение о том, чтро делать, если клиенты настаивают на использовании «магических кодов» — составных ключей, впихнутых в один столбец.

Простой пример — следующий идентификатор события:

Лично я пыаюсь избегать такого рода идентификаторов (как бы они не генерировались) как чумы, по следующим, как мне кажется, очевидным, причинам:

Если при вводе данных произошла ошибка, и ключ оказался неверным (например, событие было типа 3, а не 4) — все очень печално, ведь это событие уже всем известно как 03-40123
Делаются конкретные предположения о максимальных значениях:
1) В году никогда не будет более 10000 событий типа 1
2) Нельзя добавить одиннадцатый тип события
3) Две цифры года — это мы уже проходили.
Такие ключи неудобно реализовывать
Зачем вообще эта возможность узнать финансовый год и тип события без дополнительного запроса?

Я периодически сталкиваюсь с этой проблемой, обычно — при обновлении старых систем и/или систем «бумажного» документооборота, пользователи которых не хотят изменять систему нумерации. Часто мне удается уговорить клиента перейти на простые последовательности, но не всегда.

Я знаю, что ты не сторонник простых правил, но оправдывает ли природа данных или необходимость «запоминающегося» ключа создание такого типа ключей? Когда ты считаешь обоснованным использование такого составного ключевого столбца для идентфикации данных? Добавишь ли ты собственный суррогатный ключ и позволишь пользователям хранить свои магические коды где угодно, или будешь настаивать на использовании простой последовательности?

Ответ Тома Кайта

Такого рода поля могут (и должны) быть ПРОИЗВОДНЫМИ от других данных. Клиенту нет необходимости знать, как в физической схеме фактически реализован первичный ключ — это деталь реализации.

Так что, я бы сделал так:

Можно даже создать индекс по функции (function-based index) по полю their_field , если они собираются искать по его значениям.

Комментарий читателя от 23 ноября 2002 года

Как обычно, твое решение предельно ясно. Мне особенно понравилась идея про индекс по функции!

Однако это не рашает мою проблему «изменения адреса».

Как я попытался объяснить в первом пункте, как только значения fy , incident_type , и goofy_number определены и строка вставлена, значение their_number тоже неявно определено. С этого момента значение their_number может выдаваться в отчетах, сообщаться заинтересованным сторонам и т.д.

Если оказывается, что, например, значение incident_type перовначально оказалось ошибочным, и оно изменяется, значения their_number в базе данных и в отчетах, у заинтересованных сторон и т.д. перестают совпадать.

Можно строить their_number по столбцам, которые не меняются, но это не всегда соответствует требованиям клиента.

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

В конечном итоге, меня интересует следующее:

Как «эксперт», нанятый для создания солидных моделей данных, не выхожу ли я за пределы моих полномочий (и не трачу ли зря время), часами пытаясь убедить клиентов не использовать their_number , а заменить его простым значением последовательности?

Ответ Тома Кайта

Если вы представили им все факты, как в вопросе, продемонстрировав, что это может привести к ошибкам в интерпретации данных, и они все равно настаивают на своем — вы сделали все, что могли. Можете включить СВОЙ первичный ключ в отчеты, чтобы при возникновении проблемы можно было получить соотвествующее значение. Вы не выходите за пределы своих полномочий. Я неоднократно повторял, что наша работа как раз и состоит в том, чтобы обращать на подобные вещи мнимание тех, кто не является профессиональным программистом. Последний раз пободная проблема возникла, когда меня спросили на сайте, как выбрать N случайных строк из таблицы. Я написал, как это сделать, но проблема все усложнялась, пока не выяснилось, что нужна случайная выборка 4 строк из сложного запроса со множеством соединений и т.п. Причем, выборка эта должна была делаться сотни/тысячи раз в день. Для этого требовалось множество ресурсов.

А зачем все это понадобилось? Чтобы на портале «вывесить» фотографии 4 случайно выбранных сотрудников. Я ответил: «Сообщите клиентам, что 90% ресурсов машины теперь будет уходить на выдачу этих 4 фотографий, — захотят ли они за это платить». Мнения разделились — надо ли «знать свое место» и тупо, как бараны, делать то, что требуют, или доказывать, что практически бесполезная возможность дается дорогой ценой, и не нужна.

Я бы продолжал настаивать на своем — ваши аргументы на 100% верны. Если они решат не прислушиваться к советам, попытайтесь, по возможности, защитить их от проблем (с помощью суррогатного ключа).

Не хотел бы я работать там, где за год происходит только 9999 событий. Маловато перспектив для роста. А первого января придется этот смешной счетчик снова в 0 сбрасывать.

Изменение составного первичного ключа

У нас есть две таблицы следующего вида:

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

Ответ Тома Кайта

Раз так, c1,c2 не является первичным ключом — первичный ключ не должен меняться.

Если хотите, используйте ограничения с отложенной проверкой (deferrable constraints).

Можете использовать этот пакет, но если такое действие считается «нормальным», и изменения будут происходить постоянно — выбирайте другой первичный ключ.

Изменение первичного ключа. Комментарий от 15 января 2003 года

Пакет работает прекрасно.

После изменения первичного ключа, как «отвязать» пакет от таблицы (он необходим для других таблиц, так что просто удалить его я не могу).
Этот пакет кажестя лучшим решением в нашем случае, поскольку остальной код менять не придется. Помимо снижения производительности, какие еще недостатки имеет данное решение?
«Ограничения с отложенной проверкой», о которых вы пишете, это когда добавляют ключевое слово deferrable для внешних ключей, так что можно сначала изменить первичный ключ, а потом — внешний. Вы это имеете в виду? Например:

Ответ Тома Кайта

Не знаю, как относиться к утверждения, что «Пакет работает прекрасно». Сомнительный комплимент, как по мне.

Пакет не связывается с таблицей. Он создает ряд триггеров и хранимых процедур, для указанной таблицы — он генерирует специфический код. Сам по себе он ни с одним объектом не связан.
Вы имеете ввиду помимо того факта, что изменение первичного ключа вообще недачная идея, связанная с ошибкой проектирования?
Да, проверку внешних ключей можно отложить и делать так:

Изменение первичного ключа. Комментарий от 16 января 2003 года

Сразу после установки пакета и создания таблиц t1 , t2 , t3 , для изменения первичного ключа мне пришлось сначала выполнить команду:

Но если открыть новый сеанс после этого, изменить первичный ключ мне удалось и без команды ‘ exec update_cascade.on_table(‘t2’) ‘. Но я не хочу, чтобы пользователи постоянно меняли этот первичный ключ.

SQL> connect UCDEMO/UCDEMO@e2rs Connected. SQL> update t2 2 set b=900 3 where b = 6; — первичный ключ изменен без выполнения ‘exec update_cascade.on_table(‘t2′)’ 1 row updated.

Ответ Тома Кайта

Команда exec update_cascade.on_table(‘t2’) создала (как и описано на указанной странице) триггеры и пакеты для поддержки каскадного изменения. Если его больше не нужно поддерживать, УДАЛИТЕ их.

Как насчет использования sys_guid() вместо последовательности для генерации значений первичного ключа?

Какие преимущества и недостатки связаны с использованием sys_guid() (подозреваю, что используется больше места на диске?) sys_guid() можно указать в качестве стандартного значения (и не понадобиться триггер). Понятно, что «пропуски» значений — не проблема

Ответ Тома Кайта

Да, RAW(16) — больше по размеру, чем большинство числовых полей. Его не так удобно записывать, как число. Во многих случаях данные типа raw не обрабатываются — они неявно преобразуются в 32-байтовую строку типа varchar2 . Не уверен, что использовал бы этот подход без веских причин.

Первичный ключ: sys_guid или последовательность

Мы используем sys_guid вместо последовательности (есть требование глобальной уникальности первичных ключей) — в любом случае, имеет смысл сделать тест и поделиться результатами.

Результаты показали следующее:

Думаю, причина в кэшировании значений последовательностей в области sga , правильно? Однако странно, почему sys_guid работает медленнее — как внутренне реализована функция sys_guid ?

Не мог бы ты объяснить, что это за защелки — я думаю, последние две связаны с получением и установкой следующего значения для сеанса.

С чем связано большее количество db block gets и cache buffer chains при использовании последовательностей? Судя по руководству » Oracle Reference » cache buffer chains связаны с конфликтами при доступе к блоку, т.е. последовательности многократно обращаются к одному и тому же блоку (вероятно, во внутренней таблицеЮ используемой для хранения последовательностей — таблица seq$ )?

Ответ Тома Кайта

Хотя это и сказано в документации, sys_guid обращается к ОС и, несомненно, требует больше ресурсов процессора
Защелки для получения блоков из буферного кэша. Последовательности хранятся в блоках. Эти защелки связаны с получением буфера для изменения последовательности и/или получения ее текущего значения. Защелки в библиотечном кэше. Определение последовательности находится именно там. Обращение к последовательности вызывает установку этих защелок. Очевидно Это действительно защелки, обеспечивающие поддержку последовательности.

Комментарий от 4 августа 2003 года

В документации Oracle 9.0.1.1.1 сказано, что: » Составной первичный ключ может содержать не более 32 столбцов «. Но я попробовал задать 33 столбца, и все получилось. Почему?

Вот как я это делал.

Ответ Тома Кайта

Похоже, предел — 33 столбца. Меня это не беспокоит, поскольку «1» — наиболее типичное количество, а 5 или 6 — разумный максимум. 32 будет «слегка перебор», а 33 — еще хуже.

Первичный ключ: sys_guid или последовательность — комментарий от 18 августа 2003 года

Мы используем sys_guid , поскольку хотим избежать конфликтов при переносе данных из одной базы в другую. Точная причина мне не известна, но при экспортировании данных из базы db1 и импортировании в другую базу данных, db2 , при использовании последовательностей возможны конфликты (поскольку одинаковые последовательности создавались в обеих схемах).

Сталкивались ли вы с такой ситуацией? Нет ли более элегантного решения проблемы, кроме использования sys_guid вместо последовательностей. Мы не знаем точного количества баз и не можем просто начинать последовательности с разных значений.

Ответ Тома Кайта

Пусть имеется N баз, которые потенциально придется поддерживать. Если не уверены, увеличьте количество в 100 раз.

Потом в перовй базе выполняем:

Пусть n = 10 , тогда в первой базе будут генерироваться числа:

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

Не перекрывающиеся последовательности!

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

В идеале один и тот же сценарий установки должен работать всегда, не создавая конфликтов в любой базе. Да, кроме использования централизованной таблицы с начальными значениями для последовательностей, к которой будут обращаться последовательно, — другой достойной альтернативы не видно.

Создание суррогатных ключей с использованием свойства IDENTITY и выделенного пула SQL в Azure Synapse Analytics

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

Что такое суррогатный ключ

Суррогатный ключ таблицы представляет собой столбец с уникальным идентификатором каждой строки. Ключ не генерируется из данных таблицы. Разработчики моделей данных создают суррогатные ключи для таблиц, когда проектируют модели хранилища данных. Чтобы просто и эффективно достичь этой цели, не оказывая влияния на производительность загрузки, можно использовать свойство IDENTITY.

В Azure Synapse Analytics значение IDENTITY увеличивается независимым образом в каждом распределении и не пересекается со значениями IDENTITY в других распределениях. Значение IDENTITY в Synapse не обязательно будет уникальным, если пользователь явно вставляет повторяющееся значение с помощью конструкции SET IDENTITY_INSERT ON или повторно заполняет свойство IDENTITY. Дополнительные сведения см. в разделе CREATE TABLE (Transact-SQL) IDENTITY (свойство).

Создание таблицы со столбцом IDENTITY

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

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

Затем можно использовать INSERT..SELECT для заполнения таблицы.

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

Распределение значений

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

Ниже приведен характерный пример.

В приведенном выше примере две строки попали в распределение 1. У первой строки есть суррогатное значение 1 в столбце C1 , а у второй строки есть суррогатное значение 61. Оба эти значения были созданы свойством IDENTITY. Тем не менее распределение этих значений не является связанным. В этом весь замысел.

Неравномерные данные

Диапазон значений определенного типа данных равномерно размещается в распределениях. Если распределенная таблица содержит неравномерные данные, то диапазон значений, доступных для типа данных, может быть преждевременно исчерпан. Например, если все данные попадают в отдельное распределение, фактически таблица имеет доступ к только одной шестидесятой части значений этого типа данных. По этой причине свойство IDENTITY ограничено следующими типами данных: INT и BIGINT .

SELECT..INTO

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

Инструкция SELECT содержит соединение.
несколько инструкций SELECT соединены при помощи UNION;
столбец IDENTITY более одного раза указан в списке инструкции SELECT;
столбец IDENTITY является частью выражения.

Если любое из этих условий выполняется, столбец создается как NOT NULL и не наследует свойство IDENTITY.

CREATE TABLE AS SELECT

CREATE TABLE AS SELECT (CTAS) выполняется SQL Server так же, как и SELECT..INTO. Тем не менее невозможно указать свойство IDENTITY в определении столбца в части CREATE TABLE инструкции. Невозможно также использовать функцию IDENTITY в части SELECT инструкции CTAS. Для заполнения таблицы необходимо использовать CREATE TABLE , чтобы определить таблицу, а затем указать INSERT..SELECT , чтобы ее заполнить.

Вставка значений в столбец IDENTITY явным образом

Выделенный пул SQL поддерживает синтаксис SET IDENTITY_INSERT ON|OFF . Этот синтаксис позволяет явно вставить значения в столбец IDENTITY.

Многие разработчики моделей данных используют в измерениях предопределенные отрицательные значения для определенных строк. Например, значение -1 или строка «неизвестный элемент».

Приведенный ниже сценарий показывает, как явным образом добавить эту строку с помощью инструкции SET IDENTITY_INSERT.

Загрузка данных

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

Чтобы загрузить данные в таблицу и создать суррогатный ключ с помощью свойства IDENTITY, создайте таблицу и выполните инструкцию INSERT..SELECT или INSERT..VALUES для загрузки данных.

В следующем примере представлена базовая схема.

В настоящее время невозможно использовать CREATE TABLE AS SELECT при загрузке данных в таблицу со столбцом IDENTITY.

Системные представления

Можно использовать представление каталога sys.identity_columns для поиска столбца со свойством IDENTITY.

Для лучшего понимания схемы базы данных в этом примере показано, как интегрировать sys.identity_column с остальными представлениями системных каталогов.

Ограничения

Свойство IDENTITY не может быть использовано:

если тип данных столбца не является INT или BIGINT;
если столбец также является ключом распределения;
если таблица является внешней.

В выделенном пуле SQL не поддерживаются следующие связанные функции:

Стандартные задачи

Этот раздел содержит пример кода, который можно использовать для выполнения распространенных задач при работе со столбцами IDENTITY.

Столбец C1 является столбцом IDENTITY во всех следующих задачах.

Поиск максимального распределенного значения в таблице

Используйте функцию MAX() , чтобы определить максимальное значение в распределенной таблице.

Поиск начального значения и шага приращения для свойства IDENTITY

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

9) Ключи СУБД

Ключ СУБД – это атрибут или набор атрибутов, который помогает идентифицировать строку (кортеж) в отношении (таблице). Они позволяют найти связь между двумя таблицами. Клавиши помогают однозначно идентифицировать строку в таблице по комбинации одного или нескольких столбцов в этой таблице.

Пример:

ID сотрудника	Имя	Фамилия
11	Эндрю	Джонсон
22	Том	Дерево
33	Alex	здоровый

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

В этом уроке вы узнаете:

Зачем нам нужен ключ?

Вот причины использования ключей в системе СУБД.

Ключи помогают идентифицировать любую строку данных в таблице. В реальном приложении таблица может содержать тысячи записей. Более того, записи могут быть продублированы. Ключи гарантируют, что вы сможете однозначно идентифицировать запись таблицы, несмотря на эти проблемы.
Позволяет установить связь между и определить связь между таблицами
Помочь вам обеспечить идентичность и целостность в отношениях.

Различные ключи в системе управления базами данных

СУБД имеет следующие семь типов ключей, каждый из которых имеет свои различные функции:

Супер Ключ
Основной ключ
Ключ-кандидат
Альтернативный ключ
Внешний ключ
Составной ключ
Композитный ключ
Суррогатный ключ

Что такое супер ключ?

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

Пример:

EmpSSN	EmpNum	EmpName
9812345098	AB05	показанный
9876512345	AB06	Рослин
199937890	AB07	Джеймс

В приведенном выше примере имена EmpSSN и EmpNum являются суперключами.

Что такое первичный ключ?

PRIMARY KEY – это столбец или группа столбцов в таблице, которые уникально идентифицируют каждую строку в этой таблице. Первичный ключ не может быть дубликатом, то есть одно и то же значение не может появляться в таблице более одного раза. Таблица не может иметь более одного первичного ключа.

Правила определения первичного ключа:

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

Пример:

В следующем примере StudID является первичным ключом.

StudID	Ролл №	Имя	Фамилия	Электронное письмо
1	11	Том	Цена	abc@gmail.com
2	12	Ник	Райт	xyz@gmail.com
3	13	Dana	Натана	mno@yahoo.com

Что такое альтернативный ключ?

ALTERNATE KEYS – это столбец или группа столбцов в таблице, которые однозначно идентифицируют каждую строку в этой таблице. Таблица может иметь несколько вариантов выбора первичного ключа, но в качестве первичного ключа может быть задан только один. Все ключи, которые не являются первичными ключами, называются альтернативными ключами.

Пример:

В этой таблице StudID, Roll No, Email могут стать первичным ключом. Но поскольку StudID является первичным ключом, Roll No, Email становится альтернативным ключом.

StudID	Ролл №	Имя	Фамилия	Электронное письмо
1	11	Том	Цена	abc@gmail.com
2	12	Ник	Райт	xyz@gmail.com
3	13	Dana	Натана	mno@yahoo.com

Что такое ключ-кандидат?

CANDIDATE KEY – это набор атрибутов, которые однозначно идентифицируют кортежи в таблице. Ключ-кандидат – это супер-ключ без повторяющихся атрибутов. Первичный ключ должен быть выбран из возможных ключей. В каждой таблице должен быть хотя бы один ключ-кандидат. Таблица может иметь несколько ключей-кандидатов, но только один первичный ключ.

Свойства ключа-кандидата:

Он должен содержать уникальные значения
Ключ-кандидат может иметь несколько атрибутов
Не должен содержать нулевые значения
Он должен содержать минимальные поля для обеспечения уникальности
Уникальная идентификация каждой записи в таблице

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

StudID	Ролл №	Имя	Фамилия	Электронное письмо
1	11	Том	Цена	abc@gmail.com
2	12	Ник	Райт	xyz@gmail.com
3	13	Dana	Натана	mno@yahoo.com

Что такое внешний ключ?

FOREIGN KEY – это столбец, который создает связь между двумя таблицами. Назначение внешних ключей заключается в поддержании целостности данных и возможности навигации между двумя различными экземплярами объекта. Он действует как перекрестная ссылка между двумя таблицами, так как ссылается на первичный ключ другой таблицы.

Пример:

DeptCode	DEPTNAME
001	Наука
002	английский
005	компьютер

ID учителя	Fname	Lname
B002	Дэвид	сигнализатор
B017	Сара	Джозеф
B009	Майк	Брантон

В этом примере у нас есть два стола, учить и отдел в школе. Тем не менее, нет способа узнать, какой поиск работает в каком отделе.

В этой таблице, добавив внешний ключ в Deptcode к имени учителя, мы можем создать связь между двумя таблицами.

ID учителя	DeptCode	Fname	Lname
B002	002	Дэвид	сигнализатор
B017	002	Сара	Джозеф
B009	001	Майк	Брантон

Эта концепция также известна как ссылочная целостность.

Что такое составной ключ?

КЛАВИША СОЕДИНЕНИЯ имеет два или более атрибута, которые позволяют однозначно распознавать конкретную запись. Возможно, что каждый столбец не может быть уникальным сам по себе в базе данных. Однако при объединении с другим столбцом или столбцами комбинация составных ключей становится уникальной. Целью составного ключа является уникальная идентификация каждой записи в таблице.

Пример:

№ заказа	PorductID	наименование товара	Количество
B005	JAP102459	мышь	5
B005	DKT321573	USB	10
B005	OMG446789	ЖК монитор	20
B004	DKT321573	USB	15
B002	OMG446789	Лазерный принтер	3

В этом примере OrderNo и ProductID не могут быть первичным ключом, поскольку они не уникально идентифицируют запись. Однако можно использовать составной ключ из идентификатора заказа и идентификатора продукта, поскольку он однозначно идентифицирует каждую запись.

Что такое композитный ключ?

КОМПОЗИЦИОННЫЙ КЛЮЧ – это комбинация двух или более столбцов, которые однозначно идентифицируют строки в таблице. Комбинация столбцов гарантирует уникальность, хотя индивидуальная уникальность не гарантируется. Следовательно, они объединены, чтобы однозначно идентифицировать записи в таблице.

Разница между составным и составным ключом заключается в том, что любая часть составного ключа может быть внешним ключом, но составной ключ может быть или не быть частью внешнего ключа.

Что такое суррогатный ключ?

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

Fname	Фамилия	Время начала	Время окончания
Энн	кузнец	9:00	18:00
Джек	Фрэнсис	8:00	17:00
Анна	McLean	11:00	20:00
показанный	Willam	14:00	23:00

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

Руководство для начинающих по естественным и суррогатным ключам базы данных

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

УНИКАЛЬНЫЙ
НЕ РАВНО НУЛЮ
НЕИЗМЕННЫЙ

При выборе первичного ключа мы должны учитывать следующие аспекты:

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

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

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

Естественные ключи

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

Естественные ключи удобны тем, что у них есть эквивалент во внешнем мире, и они не требуют дополнительной обработки базы данных. Таким образом, мы можем узнать первичный ключ еще до вставки фактической строки в базу данных, что упрощает пакетную вставку.

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

Нечисловые ключи менее эффективны, чем числовые (целочисленные, bigint), как для индексирования, так и для объединения. Естественный ключ CHAR(17) (например, идентификационный номер транспортного средства) занимает 17 байт, а не 4 байта (32 – разрядное целое число) или 8 байт (64-разрядный бигинт).

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

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

Суррогатные ключи

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

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

Если мы хотим контролировать генерацию суррогатных ключей, мы можем использовать 128-разрядный GUID или UUID . Это упрощает пакетирование и может повысить производительность вставки, поскольку больше не требуется дополнительная обработка генерации ключей базы данных. Однако, будучи больше, чем автоматически увеличивающееся число, выбор идентификатора UUID не лишен недостатков. Кроме того, для кластеризованных индексов, которые используются по умолчанию в MySQL и SQL Server, случайно сгенерированный идентификатор потребует дополнительной балансировки индекса кластера , может работать против предварительного выделения записей индекса и может привести к раздуванию индекса.

Когда ответственность за создание идентификатора базы данных ложится на систему баз данных, существует несколько стратегий автоматического увеличения суррогатных ключей:

Оракул	ПОСЛЕДОВАТЕЛЬНОСТЬ, ИДЕНТИЧНОСТЬ (Oracle 12c)
SQL Server	ИДЕНТИФИКАТОР, ПОСЛЕДОВАТЕЛЬНОСТЬ (SQL Server 2012)
PostgreSQL	ПОСЛЕДОВАТЕЛЬНОСТЬ, ПОСЛЕДОВАТЕЛЬНЫЙ ТИП
MySQL	АВТО_ИНКРЕМЕНТ
DB2	ИДЕНТИЧНОСТЬ, ПОСЛЕДОВАТЕЛЬНОСТЬ
HSQLDB	ИДЕНТИЧНОСТЬ, ПОСЛЕДОВАТЕЛЬНОСТЬ

Аспекты проектирования

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

Когда генерируется порядковый номер, последовательность увеличивается, независимо от фиксации или отката транзакции	Оракул
Порядковые номера генерируются за пределами области текущей транзакции. Они используются независимо от того, зафиксирована или откатана транзакция, использующая порядковый номер	SQL Server
Поскольку последовательности не являются транзакционными, изменения, внесенные setval, не отменяются, если транзакция откатывается	PostgreSQL

И тип ИДЕНТИФИКАТОРА, и генератор ПОСЛЕДОВАТЕЛЬНОСТЕЙ определяются стандартом SQL:2003 , поэтому они стали стандартными стратегиями генератора первичных ключей.

Некоторые механизмы баз данных позволяют вам выбирать между ИДЕНТИФИКАТОРОМ и ПОСЛЕДОВАТЕЛЬНОСТЬЮ, поэтому вам нужно решить, какой из них лучше соответствует вашим текущим требованиям к схеме.

Режим гибернации отключает пакетирование вставок JDBC при использовании стратегии генератора идентификаторов.

Суррогатный ключ — Базы данных: основные понятия

Суррога́тный ключ — понятие теории реляционных баз данных.
Это дополнительное служебное поле, добавленное к уже имеющимся информационным полям таблицы, единственное предназначение которого — служить первичным ключом. Значение этого поля не образуется на основе каких-либо других данных из БД, а генерируется искусственно.

Реализация
Как правило, суррогатный ключ — это просто числовое поле, в которое заносятся значения из возрастающей числовой последовательности. Это может делаться при помощи триггеров (типичный способ порождения ключей в Oracle). В ряде СУБД (например, PostgreSQL, Sybase, MySQL[1] или SQL Server [2]) существует специальный тип данных для таких полей — числовое поле, в которое при добавлении записи в таблицу автоматически записывается уникальное для этой таблицы числовое значение — т.н. «автоинкремент» (англ. autoincrement) или serial в терминологии PostgreSQL. В последнее время появилась тенденция использования в качестве суррогатных первичных ключей значений UUID в той или иной форме.

Использование
Главное достоинство суррогатного ключа состоит в том, что он никогда не изменяется, поскольку не является информативным полем таблицы (не несёт никакой информации об описываемом записью объекте).
Работа с таблицей, содержащей суррогатный ключ, организуется так, чтобы при добавлении новой записи в поле суррогатного ключа было записано заведомо уникальное значение. Для этого используются либо специальные типы данных, либо триггеры и последовательности. После создания новой записи значение этого поля используется в качестве первичного ключа. Именно на него указывают все ссылки в связанных таблицах.
Использовать суррогатный первичный ключ имеет смысл, если естественный первичный ключ (составленный из информативных полей таблицы) — составной, и на него придётся ссылаться во внешних ключах многих таблиц. В этом случае проще написать запрос: SELECT * FROM p, c WHERE p.primary_key = c.foreign_key;
чем SELECT * FROM p, c WHERE p.id1 = c.fk1 AND p.id2 = c.fk2 AND p.id3 = c.fk3;
Кроме того первый вариант работает быстрее.
Также использовать суррогатный ключ имеет смысл в случае, когда возможны изменения полей, составляющих (естественный) первичный ключ (в особенности если этот ключ — составной). В этом случае возникает проблема т. н. «каскадных изменений» — при изменении полей, входящих в ключ, для сохранения ссылочной целостности необходимо в одной транзакции внести адекватные изменения во все записи, ссылающиеся на изменяемую. При использовании же суррогатного ключа в качестве первичного изменять его не придётся, не придётся делать и каскадные изменения.

Учёт опозданий и прогулов (Учёт рабочего времени) база данных Учёт опозданий и прогулов
Сельскохозяйственные работы Скачать базу данных Сельскохозяйственные работы MS Access
Спорт (Командные виды спорта) Готовая база данных (БД) Спорт (Командные виды спорта)
Телефонная станция Пример базы данных Телефонная станция Access
Поликлиника Курсовая по базам данных (БД) Поликлиника
Деканат Скачать готовую БД Деканат MS Access
Авторемонтные мастерские Скачать базу данных (БД) Авторемонтные мастерские
Абитуриент Готовая база данных Абитуриент
Библиотека института Пример базы данных (БД) Библиотека института
Записная книжка Готовая бд Записная книжка Access
Строительная фирма Скачать пример базы данных Строительная фирма
Фотоателье Скачать базу данных (БД) Фотоателье
Склад (с лимитом товара) Готовая база данных (БД) Склад (с лимитом товара)
Салон красоты Пример базы данных Салон красоты
Парикмахерская Курсовая по базам данных (БД) Парикмахерская
Туристическая фирма Скачать базу данных Туристическая фирма
Контроль исполнения поручений Скачать базу данных Контроль исполнения поручений Access

Ключевые слова: база данных access; бд access; субд access; базы данных access; access пример; программирование access; готовая база данных; создание база данных; база данных СУБД; access курсовая; база данных пример; программа access; access описание; access реферат; access запросы; access 2007 примеры; скачать бд access; объекты access; бд в access; скачать субд access 2003; база данных ms access; субд access реферат; субд ms access; преимущества access; базу данных; скачать базу данных на access 2010; базы данных; реляционная база данных; системы управления базами данных; реляционная; с база данных; создание СУБД; нормализация данных; примеры СУБД; база данных примеры; курсовые работы по СУБД; нормализация; базе данных; структура БД; пример БД; база запросов; учебная база данных; проектирование БД; данных; описание БД; субд реферат; создать БД; база данных по; использование БД; курсовая работа база данных; готовая; использование СУБД; таблица БД; база данных 2008 скачать

Поиск Яндекса по сайту

Для поиска базы данных access введите слово, например, «сотрудники» и нажмите кнопку

Естественные ключи против искусcтвенных ключей

Каждая запись в таблице, входящей в РСУБД, должна иметь первичный ключ (ПК) – набор атрибутов, уникально идентифицирующий её в таблице. Случай, когда таблица не имеет первичного ключа, имеет право на существование, однако в данной статье не рассматривается.

Естественный Ключ (ЕК) – набор атрибутов описываемой записью сущности, уникально её идентифицирующий (например, номер паспорта для человека) или
Суррогатный Ключ (СК) – автоматически сгенерированное поле, никак не связанное с информационным содержанием записи. Обычно в роли СК выступает автоинкрементное поле типа INTEGER.

СК должны использоваться, только если ЕК не существует. Если же ЕК существует, то идентификация записи внутри БД осуществляется по имеющемуся ЕК;
СК должны добавляться в любую таблицу, на которую существуют ссылки (REFERENCES) из других таблиц, и связи между ними должны организовываться только при помощи СК. Разумеется, поиск записи и представление её пользователю по прежнему производятся на основании ЕК.

Когда появляются СК?

Для понимания места и значения СК рассмотрим этап проектирования, на котором они вводятся в структуру БД, и методику их введения.

Для ясности рассмотрим БД из 2-х отношений – Города (City) и Люди (People) Предполагаем, что город характеризуется Hазванием (Name), все города имеют разные названия, человек характеризуется Фамилией (Family), номером паспорта (Passport) и городом проживания (City). Также полагаем, что каждый человек имеет уникальный номер паспорта. Hа этапе составления инфологической модели БД её структура одинакова и для ЕК и для СК.

CREATE TABLE City(
Name VARCHAR(30) NOT NULL PRIMARY KEY
);

CREATE TABLE People(
Passport CHAR(9) NOT NULL PRIMARY KEY,
Family VARCHAR(20) NOT NULL,
City VARCHAR(30) NOT NULL REFERENCES City(Name)
);

CREATE TABLE City(
/*
В разных диалектах языка SQL автоинкрементное поле будет выражено по-разному –
например, через IDENTITY, SEQUENCE или GENERATOR.
Здесь мы используем условное обозначение AUTOINCREMENT.
*/
Id INT NOT NULL AUTOINCREMENT PRIMARY KEY
Name VARCHAR(30) NOT NULL UNIQUE
);

CREATE TABLE People(
Id INT NOT NULL AUTOINCREMENT PRIMARY KEY,
Passport CHAR(9) NOT NULL UNIQUE,
Family VARCHAR(20) NOT NULL,
CityId INT NOT NULL REFERENCES City(Id)
);

Все условия, диктуемые предметной областью (уникальность имени города и номера паспорта) продолжают присутствовать в БД, только обеспечиваются не условием PRIMARY KEY, а условием UNIQUE;
Ключевого слова AUTOINCREMENT ни в одном из известных мне серверов нет. Это просто обозначение, что поле генерируется автоматически.

В таблицу добавляется поле INTEGER AUTOINCREMENT;
Оно объявляется PRIMARY KEY;
Старый PRIMARY KEY (ЕК) заменяется на UNIQUE CONSTRAINT ;
Если в таблице есть REFERENCES на другие таблицы, то поля, входящие в REFERENCES, заменяются на одно поле типа INTEGER, составляющее первичный ключ (как People.City заменена на People.CityId).

Зачем всё это надо?

Возникает резонный вопрос – а зачем? Действительно, вводить в таблицы какие-то поля, что-то заменять, зачем? Итак, что мы получаем, проделав эту «механическую» операцию.

Упрощение сопровождения

Это область, где СК демонстрируют наибольшие преимущества. Поскольку операции связи между таблицами отделены от логики «внутри таблиц» – и то и другое можно менять независимо и не затрагивая остального.

Hапример, выяснилось, что города имеют дублирующиеся названия. Решено ввести в City еще одно поле – Регион (Region) и сделать ПК (City, Region). В случае ЕК – изменяется таблица City, изменяется таблица People – добавляется поле Region (да, да, для всех записей, про размеры молчу), переписываются все запросы, в том числе на клиентах, в которых участвует City, в них добавляются строка AND XXX.Region = City.Region.

Да, чуть не забыл, большинство серверов сильно не любят ALTER TABLE на поля, входящие в PRIMARY KEY и FOREIGN KEY.

В случае СК – добавляется поле в City, изменяется UNIQUE CONSTRAINT. Всё.

Еще пример – в случае СК изменение списка полей в SELECT никогда не заставляет переписывать JOIN. В случае ЕК – добавилось поле, не входящее в ПК связанной таблицы – переписывайте.

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

В условиях меняющегося законодательства это достоинство СК само по себе достаточно для их использования.

Уменьшение размера БД

Предположим в нашем примере, что средняя длина названия города – 10 байт. Тогда на каждого человека в среднем будет приходиться 10 байт для хранения ссылки на город (реально несколько больше за счёт служебной информации на VARCHAR и гораздо больше за счёт индекса по People.City, который придётся построить, чтобы REFERENCES работала эффективно). В случае СК – 4 байта. Экономия – минимум 6 байт на человека, приблизительно 10 Мб для г. Hовосибирска. Очевидно, что в большинстве случаев уменьшение размера БД – не самоцель, но это, очевидно, приведет и к росту быстродействия.

Звучали аргументы, что БД может сама оптимизировать хранение ЕК, подставив вместо него в People некую хэш-функцию (фактически создав СК сама). Hо ни один из реально существующих коммерческих серверов БД так не делает, и есть основания полагать, что и не будет делать. Простейшим обоснованием такого мнения является то, что при подобной подстановке банальные операторы ADD CONSTRAINT … FOREIGN KEY или DROP CONSTRAINT … FOREIGN KEY будут приводить к нешуточной перетряске таблиц, с ощутимым изменением всей БД (надо будет физически добавить или удалить (с заменой на хэш-функцию)) все поля, входящие в CONSTRAINT.

Увеличение скорости выборки данных

База данных нормализована;
Записей в таблицах много (десятки тысяч и более);
Запросы преимущественно возвращают ограниченные наборы данных (максимум единицы процентов от размера таблицы).

Требуется только информация, входящая в первичные ключи связанных таблиц;
Нет условий WHERE по полям связанных таблиц.

Казалось бы, ЕК дает более простой запрос с меньшим количеством таблиц, который выполнится быстрее. Hо и тут не всё так просто: размеры таблиц для ЕК – больше (см. выше) и дисковая активность легко съест преимущество, полученное за счёт отсутствия JOIN`а. Ещё сильнее это скажется, если при выборке данных используется их фильтрование (а при сколько-либо существенном объеме таблиц оно используется обязательно). Дело в том, что поиск, как правило, осуществляется по информативным полям типа CHAR, DATETIME и т.п. Поэтому часто бывает быстрее найти в справочной таблице набор значений, ограничивающий возвращаемый запросом результат, а затем путем JOIN`а по быстрому INTEGER-индексу отобрать подходящие записи из большой таблицы. Например,

(CК) SELECT P.Family, C.Name
FROM People P INNER JOIN City C ON P.CityId = C.Id
WHERE C.Name = ‘Иваново’;

В случае ЕК – будет INDEX SCAN большой таблицы People по CHARACTER-индексу. В случае СК – INDEX SCAN меньшей CITY и JOIN по эффективному INTEGER индексу.

А вот если заменить = ‘Иваново’ на LIKE ‘%ваново’, то речь пойдет о торможении ЕК относительно СК на порядок и более.

Аналогично, как только в случае с ЕК понадобится включить в запрос поле из City, не входящее в её первичный ключ – JOIN будет осуществлятся по медленному индексу и быстродействие упадет ощутимо ниже уровня СК. Выводы каждый может делать сам, но пусть он вспомнит, какой процент от общего числа его запросов составляют SELECT * FROM ЕдинственнаяТаблица. У меня – ничтожно малый.

Да, сторонники ЕК любят проводить в качестве достоинства «информативность таблиц», которая в случае ЕК растет. Ещё раз повторю, что максимальной информативностью обладает таблица, содержащая всю БД в виде flat-file. Любое «повышение информативности таблиц» есть увеличение степени дублирования в них информации, что не есть хорошо.

Увеличение скорости обновления данных

INSERT

Hа первый взгляд ЕК быстрее – не надо при INSERT генерировать лишнего поля и проверять его уникальность. В общем-то так оно и есть, хотя это замедление проявляется только при очень высокой интенсивности транзакций. Впрочем и это неочевидно, т. к. некоторые серверы оптимизируют вставку записей, если по ключевому полю построен монотонно возрастающий CLUSTERED индекс. В случае СК это элементарно, в случае ЕК – увы, обычно недостижимо. Кроме этого, INSERT в таблицу на стороне MANY (который происходит чаще) пойдет быстрее, т. к. REFERENCES будут проверяться по более быстрому индексу.

UPDATE

При обновлении поля, входящего в ЕК, придётся каскадно обновить и все связанные таблицы. Так, переименование Ленинграда в Санкт-Петербург потребует с нашем примере транзакции на несколько миллионов записей. Обновление любого атрибута в системе с СК приведет к обновлению только одной записи. Очевидно, что в случае распределенной системы, наличия архивов и т.п. ситуация только усугубится. Если обновляются поля не входящие в ЕК – быстродействие будет почти одинаковым.

Еще о CASCADE UPDATE

Далеко не все серверы БД поддерживают их на декларативном уровне. Аргументы «это у вас сервер кривой» в этом случае вряд ли корректны. Это вынуждает писать отдельную логику для обновления, что не всегда просто (приводился хороший пример – при отсутствии CASCADE UPDATE обновить поле, на которое есть ссылки, вообще невозможно – надо отключать REFERENCES или создавать копию записи, что не всегда допустимо (другие поля могут быть UNIQUE)).

DELETE

В случае СК будет выполняться быстрее, по той простой причине, что проверка REFERENCES пойдет по быстрому индексу.

А есть ли хорошие ЕК?

Hичто не вечно под Луной. Самый, казалось бы, надежный атрибут вдруг отменяется и перестаёт быть уникальным (далеко ходить не буду – рубль обычный и рубль деноминированный, примерам несть числа). Американцы ругаются на неуникальность номера социального страхования, Microsoft – на китайские серые сетевые платы с дублирующимися MAC-адресами, которые могут привести к дублированию GUID, врачи делают операции по смене пола, а биологи клонируют животных. В этих условиях (и учитывая закон неубывания энтропии) закладывать в систему тезис о неизменности ЕК – закладывать под себя мину. Их надо выделять в отдельный логический слой и по возможности изолировать от остальной информации. Так их изменение переживается куда легче. Да и вообще, однозначно ассоциировать сущность с каким-то из атрибутов этой сущности – ну, странно, что-ли. Hомер паспорта ещё не есть человек. СК же – это некая субстанция, именно и означающая сущность. Именно сущность, а не какой-то из её атрибутов.

Типичные аргументы сторонников ЕК

В системе с СК не осуществляется контроль правильности ввода информации

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

В системе с ЕК меньше JOIN`ов, следовательно, запросы проще и разработка удобнее

CREATE VIEW PeopleEK AS
SELECT P.Family, P.Passport, C.Name
FROM People P INNER JOIN City C ON P.CityId = C.Id

И можно иметь все те же прелести. С более, правда, высоким быстродействием. При этом неплохо упомянуть, что в случае ЕК многим придется программировать каскадные операции, и, не дай Бог в распределённой среде, бороться с проблемами быстродействия. Hа фоне этого «короткие» запросы уже не кажутся столь привлекательными.

Введение ЕК нарушает третью нормальную форму

Вспомним определение: Таблица находится в третьей нормальной форме (3НФ), если она удовлетворяет определению 2НФ, и ни одно из её неключевых полей не зависит функционально от любого другого неключевого поля.

То есть, речи о ключевых полях там не идёт вообще. Поэтому добавление ещё одного ключа в таблицу ни в коей мере не может нарушить 3НФ. Вообще, для таблицы с несколькими возможными ключами имеет смысл говорить не о 3 НФ, а о Нормальной Форме Бойса-Кодда, которая специально введена для таких таблиц.

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

Таким образом, таблица, имеющая СК, легко может быть нормализована хоть до 5НФ. Точнее будет сказать, что СК к нормализации не имеют никакого отношения. Более того, введение СК уменьшает избыточность данных в БД, что вообще хорошо согласуется с идеологией нормализации. В сущности, нормализация и есть уменьшение информативности отдельных таблиц по определенным правилам. Только СК устраняют аномалии не внутри таблицы, а на межтабличном уровне (типа устранения каскадных обновлений). Так сказать, система с СК – святее Папы Римского :-). В самом деле ситуация, когда при изменении одного из полей таблицы приходится изменять содержимое этого же поля в других записях ЭТОЙ ЖЕ таблицы, рассматривается как аномалия обновления. Но в системе с ЕК придется проделать то же самое В СВЯЗАННОЙ таблице при изменении ключевого атрибута на стороне 1 отношения 1:N. Очевидно, что эта ситуация с точки зрения физической реализации БД ничем не лучше. В системе с СК таких ситуаций не возникает.

Таблицы в системе с ЕК информативнее

Максимальной информативностью обладает таблица, содержащая всю БД в виде flat-file. Любое «повышение информативности таблиц» есть увеличение степени дублирования в них информации, что не обязательно есть хорошо. Да и вообще термин «Информативность таблицы» сомнителен. Видимо, более важна информативность БД, которая в обоих случаях одинакова.

Заключение

В общем-то, выводы очевидны – введение СК позволяет получить лучше управляемую, более компактную и быстродействующую БД. Разумеется, это не панацея. В некоторых случаях (например, таблица на которую нет REFERENCES и в которую осуществляется интенсивная вставка данных и т. п.) более верно использовать ЕК или не использовать ПК вообще (последнее категорически противопоказано для многих РСУБД и средств разработки клиентских приложений). Но речь шла именно о типовой методике, которую надо рекомендовать к применению в общем случае. Уникальные ситуации могут потребовать уникальных же решений (иногда и нормализацией приходится поступаться).

Система управления базами данных SQLite. Изучаем язык запросов SQL и реляционные базы данных на примере библиотекой SQLite3. Курс для начинающих.

Часть 3.3: Ключи и ключевые атрибуты в базах данных

26.05.2016
SQLite библиотека, Базы данных
Комментариев нет

Здравствуйте, уважаемые посетители сайта ZametkiNaPolyah.ru. Продолжаем изучать базы данных и наше знакомство с библиотекой SQLite3. В данной публикации мы разберем, что такое ключи и ключевые атрибуты в реляционных базах данных, для чего нужны ключевые атрибуты и какими могут быть ключи в базах данных.

Ключи и ключевые атрибуты в базах данных

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

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

Ключи или ключевой атрибут — атрибут (читай столбец) или набор атрибутов, который однозначно идентифицирует сущность/объект/таблицу в базе данных.
Первичный ключ — ключ, который используется для идентификации объекта.
Ключ-кандидат (альтернативный ключ) — ключ, по каким-либо причинам неиспользуемый как первичный.
Составной ключ — ключ, который использует несколько атрибутов.
Суррогатный ключ — ключ, значение которого генерируется СУБД.

Ключевые атрибуты или ключи по своему виду делятся на: простые и составные, естественные и суррогатные, первичные ключи и ключи кандидаты.

Рассмотрим различие между естественными и суррогатными ключами, естественно, на примере. Для этого обратимся к таблице с городами из базы данных World.

Таблица с суррогатным ключом

В этой таблице ключом является столбец ID, данный столбец автоматически генерируется СУБД при добавлении новой записи в таблицу, следовательно, атрибут ID–суррогатный ключ. В данной таблице мы видим столбец с именем CountryCode, который может выступать в роли ключа для таблицы Country, такой ключ будет естественным.

Как нам определить, что столбец может быть ключом? Есть два очень простых признака того, что столбец является ключом или ключевым атрибутом: ключ уникален и ключ вечен. Но хочу отметить, что ключ – абстрактное понятие. Например, представим, что у нас есть таблица, в которой хранится информация о учениках класса, в принципе, ничего страшного не будет, если в такой таблице столбец ФИО будет выступать в роли ключа. Но, когда наша база данных работает в масштабах города, области, региона или страны, то столбец ФИО никак не может выступать в роли ключа, даже номер паспорта – это не ключ, так как со временем мы меняем паспорт, а у несовершеннолетних его нет.

Поясню принцип составного ключа. Представим, что гражданин Петров был задержан сотрудниками полиции в нетрезвом виде за нарушение правопорядка. По факту задержания составляется рапорт (гражданин Петров не имеет при себе паспорта). Сотрудник полиции в рапорте укажет ФИО задержанного, но ФИО в масштабах города никак не идентифицируют Петрова Петра Петровича, поэтому сотрудник записывает дату рождения, если город большой, то Петр Петрович Петров, родившийся 14 февраля 1987 года в нем не один, поэтому записывается адрес фактического проживание и адрес прописки, для достоверности указывается время задержания. Сотрудник полиции составил набор характеристик, которые однозначно идентифицируют гражданина Петрова. Другими словами, все эти характеристики – составной ключ.

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