для чего используется ключ отношения в бд

Ключ отношения

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

Содержание

Смысл

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

Использование

Первичный ключ в таблице является базовым уникальным идентификатором для записей. Значение первичного ключа используется везде, где нужно указать на конкретную запись. На использовании первичных ключей основана организация связей между таблицами реляционной БД. Чтобы организовать между двумя таблицами связь типа «один к одному» или «один ко многим(многие к одному)» в одну из связываемых таблиц добавляют поле (поля), содержащее(ие) значение первичного ключа записи в связанной таблице (такое поле называют внешним ключом). Для организации связи типа «многие ко многим» создают отдельную таблицу (так называемую «таблицу связи» или «таблицу ассоциации»), каждая запись которой содержит первичные ключи двух связанных записей в разных таблицах.

Классификация

Простые и составные ключи

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

Естественные и суррогатные ключи

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

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

Источник

Sysadminium

База знаний системного администратора

Первичный и внешний ключ SQL

Из статьи вы узнаете, что такое первичный и внешний ключ в SQL. Зачем они нужны и как их использовать. Я покажу на практике как их использовать в PostgreSQL.

Теория

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

К первичному ключу предъявляют следующее требование:

Первичный ключ может быть:

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

Связь между таблицами

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

Например, у вас есть таблица “Ученики” (pupils) и выглядит она следующим образом:

Таблица pupils

И есть таблица “Успеваемость” (evaluations):

Таблица evaluations

В обоих таблицах есть одинаковое поле: ФИО. При этом в таблице “Успеваемость” не может содержаться ФИО, которого нет в таблице “ Ученики“. Ведь нельзя поставить ученику оценку, которого не существует.

Первичным ключом в нашем случае может выступать поле “ФИО” в таблице “ Ученики“. А внешним ключом будет “ФИО” в таблице “Успеваемость“. При этом, если мы удаляем запись о каком-то ученике из таблицы “Ученики“, то все его оценки тоже должны удалиться из таблицы “Успеваемость“.

Ещё стоит заметить что первичный ключ в PostgreSQL автоматически создает индекс. Индекс ускоряет доступ к строкам таблицы и накладывает ограничение на уникальность. То есть двух Ивановых Иванов Ивановичей у нас не может существовать. Чтобы это обойти можно использовать:

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

Практика

Создадим базу данных school и подключимся к ней. Затем создадим таблицу pupils. Про создание таблиц я уже писал тут, а про типы данных тут. Затем посмотрим на табличку с помощью команды \d:

Как вы могли заметить, первичный ключ создаётся с помощью конструкции PRIMARY KEY (имя_поля) в момент создания таблицы.

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

Индекс в свою очередь наложил ещё одно ограничение – записи в поле full_name должны быть уникальны.

Следующим шагом создадим таблицу evaluations:

В этом случае из вывода команды \d вы увидите, что создался внешний ключ (Foreign-key), который относится к полю full_name и ссылается на таблицу pupils.

Внешний ключ создается с помощью конструкции FOREIGN KEY (имя_поля) REFERENCES таблица_на_которую_ссылаются.

Создавая внешний ключ мы дополнительно указали опцию ON DELETE CASCADE. Это означает, что при удалении строки с определённым учеником в таблице pupils, все строки связанные с этим учеником удалятся и в таблице evaluations автоматически.

Заполнение таблиц и работа с ними

Заполним таблицу “pupils“:

Заполним таблицу “evaluations“:

А теперь попробуем поставить оценку не существующему ученику:

Как видите, мы получили ошибку. Вставлять (insert) или изменять (update) в таблице evaluations, в поле full_name можно только те значения, которые есть в этом же поле в таблице pupils.

Теперь удалим какого-нибудь ученика из таблицы pupils:

И посмотрим на строки в таблице evaluations:

Как видно, строка с full_name равная ‘Иванов Иван Иванович’ тоже удалилась. Если бы у Иванова было бы больше оценок, они всё равно бы все удалились. За это, если помните отвечает опция ON DELETE CASCADE.

Попробуем теперь создать ученика с точно таким-же ФИО, как у одного из существующих:

Ничего не вышло, так как такая запись уже существует в поле full_name, а это поле у нас имеет индекс. Значит значения в нём должны быть уникальные.

Составной первичный ключ

Есть большая вероятность, что в одной школе будут учиться два ученика с одинаковым ФИО. Но меньше вероятности что эти два ученика будут учиться в одном классе. Поэтому в качестве первичного ключа мы можем взять два поля, например full_name и class.

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

Как вы могли заметить, разница не большая. Мы должны в PRIMARY KEY указать два поля вместо одного. И в FOREIGN KEY точно также указать два поля вместо одного. Ну и не забудьте в таблице evaluations при создании добавить поле class, так как его там в предыдущем варианте не было.

Теперь посмотрим на структуры этих таблиц:

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

Теперь мы можем учеников с одинаковым ФИО вбить в нашу базу данных, но при условии что они будут учиться в разных классах:

И также по второй таблице:

Удаление таблиц

Кстати, удалить таблицу, на которую ссылается другая таблица вы не сможете:

Поэтому удалим наши таблицы в следующем порядке:

Либо мы могли удалить каскадно таблицу pupils вместе с внешним ключом у таблицы evaluations:

Как видно из примера, после каскадного удаления у нас вместе с таблицей pupils удался внешний ключ в таблице evaluations.

Создание связи в уже существующих таблицах

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

Вначале удалим оставшуюся таблицу:

И сделаем таблицы без ключей:

Теперь создадим первичный ключ в таблице pupils:

И создадим внешний ключ в таблице evaluations:

Посмотрим что у нас получилось:

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

Дополнительно про первичный и внешний ключ sql можете почитать тут.

Источник

Первичные и прочие ключи в базе данных

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

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

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

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

Первичный ключ — один из ключей, выбранный в качестве основного.

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

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

В итоге, наиболее универсальным вариантом для проектировщика транзакционного приложения является выбор искусственного идентификатора числового типа. В простейшем варианте, 32-разрядный целый тип со знаком позволяет иметь 2 31 = 2 1 47 483 648 уникальных неотрицательных значений для каждой таблицы, что является достаточным для большинства случаев.

Однако, в условиях распределенной БД, имеющей два и более узла, для подобных ключей возникает проблема генерации глобально-уникальных значений, ведь, например, созданный в БД подразделения №1 клиент с внутренним номером «125» не тот же самый клиент с номером «125», созданным в подразделении №2. На практике эта проблема решается по- разному, вот возможные варианты:

Каждое из приведённых решений имеет свои преимущества и недостатки. Наиболее общим подходом является использование UUID, однако есть как минимум две причины, по которым такое решение не любят администраторы баз данных:

Пора подвести некоторые итоги по теме ключей.

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

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

Разница между так называемыми «естественными» и «суррогатными» ключами достаточно условна, зачастую «естественный» ключ является автоматически генерируемым по определённому формату и алгоритму «суррогатом» во внешней по отношению к вам системе, например в БД налоговой или пенсионной службы. Не стоит тратить время на выяснение вопросов, ответ на которые напрямую зависит от философской позиции авторов. Разумнее придерживаться практик, которые могут обеспечить максимальную гибкость и простоту при внесении изменений в систему, несмотря на то, что любая БД — наиболее консервативный её компонент.

Источник

Для чего используется ключ отношения в бд

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

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

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

Пример таблицы с несколькими потенциальными ключами

Есть очень маленькая вероятность того, что на одном заводе будут работать два человека с однинаковыми ФИО, родившиеся в одном месте одновременно. Пренебрежём этим событием.

Определение. Первичным ключом называется потенциальный ключ, выделенный особо и не содержащий значения NULL.

Остальные потенциальные ключи называются альтернативными.

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

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

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

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

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

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

Пример. Сущности Факультет и Кафедра имеют связь типа один ко многим. Им соответствуют таблицы Fak и Kaf. Для отображения связи один ко многим служат первичный ключ ID в таблице Fak и внешний ключ IdFak в в таблице Kaf.

Все строки таблицы Kaf, в которых IdFak равен ID из таблицы IdFak, относятся к соответствующему факультету. Например, кафедры Органической химии и Общей химии описаны в строках с IdFak =2. В таблице Fak в строке с ID =2 описан факультет химии, к которому нужно отнести кафедры Органической химии и Общей химии.

Формальное определение внешнего ключа выглядит очень абстрактно.

Множество атрибутов FK, принимающее значения только из значений множества PK, называется внешним ключом.

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

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

Использование составных ключей для связи таблиц увеличивает вероятность нарушения целостности базы данных по сравнению с использованием простых ключей. В приведённом примере при изменении номера цеха придётся вносить изменения в две таблицы: Участок и План. Количество участков в цехе невелико, обычно меньше десяти, а в таблице План придётся изменить сотни строк. При использовании простых ключей вносить изменения в таблицу План не потребовалось бы.

ЦЕЛОСТНОСТЬ БАЗЫ ДАННЫХ

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

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

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

Нарушение целостности атрибута

К этой группе нарушений целостности относится пример 1, в котором дата 43.25.2016 имеет недопустимые значения дня и месяца. Эта проблема легко устраняется правильным выбором типа данных. Но проблема контроля даты сложнее, чем может показаться на первый взгляд. Например дата 03-25-12 в формате, принятом в США, читается так: 25-е марта 2012 года. Существует большое количество форматов дат. В разных СУБД форматы дат различны. Для текстовой информации очень важен выбор кодировки. При неправильном выборе кодировки на экране монитора вместо русских букв (кириллицы) появятся странные значки (крякозябры).

Эффективным средством предотвращения ошибок при вводе данных является предложение пользователю выбрать, если это возможно, значение из списка, т.е. домена атрибута. Примеры практически возможных доменов: все научные степени и звания преподавателя, все регионы России, все факультеты одного вуза (см. Лабораторная работа № 1. ).

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

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

Самостоятельно постройте реляционную модель, соответствующую инфологической модели расписания, приведённой в лекции Модель сущность-связь

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

Практические приёмы поддержания целостности данных

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

Рассмотрим три приема поддержания целостности данных.

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

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

Таблица Численность населения

Создадим таблицу Страна состоящую из двух столбцов: КодСтраны и Название, а в таблице Численность населения заменим название страны на её код из таблицы Страна. Теперь, если изменится название страны, его придётся менять только один раз в таблице Страна.

Таблица Численность населения

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

2. Неудаляемые записи. Для устранения возникших по злому умыслу или случайно нарушений целостности даннных можно использовать такую организацию данных, при которой записи не удаляются, а лишь помечаются как удалённые и становятся невидимыми пользователю. Для этого в таблицу добавляются столбцы Ngr и priznak. Когда в таблице появляется новая запись, ей присваивается уникальный номер группы и признак d (действующая). При редактировании в этой записи меняется лишь признак на s (старая), а результаты редактирования помещаются в новую запись с тем же номером группы и признаком d (действующая). Запись которую пытается удалить пользователь, помечается признаком u (удалённая). При выборке все записи с признаками s и u игнорируются. При обнаружении искажения данных администратор базы данных читает все записи, в том числе и исправленные и удалённые. Чтобы установить виновного в ошибке, к таблице добавляются ещё два столбца: Кто и Когда, в которых фиксируется пользователь, внёсший запись, и дата внесения. В приведённом примере сделка с акциями эмитента Алмаз была создана, затем дважды отредактирована. Сделка с акциями змитента Сапфир удалена.

Пример таблицы с неудаляемыми записями

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

Источник