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

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

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

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

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

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

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

Определение. Первичным ключом называется потенциальный ключ, выделенный особо и не содержащий значения 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 игнорируются. При обнаружении искажения данных администратор базы данных читает все записи, в том числе и исправленные и удалённые. Чтобы установить виновного в ошибке, к таблице добавляются ещё два столбца: Кто и Когда, в которых фиксируется пользователь, внёсший запись, и дата внесения. В приведённом примере сделка с акциями эмитента Алмаз была создана, затем дважды отредактирована. Сделка с акциями змитента Сапфир удалена.

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

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

Источник

BestProg

Реляционные базы данных. Понятие ключа. Виды ключей. Отношения. Главная и подчиненная таблицы

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

Содержание

Поиск на других ресурсах:

Входные данные

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

Таблицы имеют следующую структуру.

Таблица «Работник». Содержит данные о работнике

Таблица «Зарплата». Содержит сведения о заработной плате работников.

Вопрос/ответ

1. Что такое первичный ключ в таблице базы данных? Для чего используются первичные ключи?

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

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

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

Пример. Для таблицы «Работник» можно ввести дополнительное поле, которое будет первичным ключом. Однако, поле (атрибут) «Табельный номер» также обеспечивает уникальность. Так как, теоретически, не может быть двух одинаковых табельных номеров. На практике могут быть случаи, что один и тот же табельный номер будет введен по ошибке и совпадут значения всех полей таблицы. В результате возникнут два одинаковых записи в таблице. Во избежание такой ошибки, лучше создать в таблице дополнительное поле-счетчик, которое обеспечит уникальность.

Также для таблицы «Зарплата» можно ввести дополнительное поле, которое будет первичным ключом.

2. Что такое отношение (связь) между таблицами (relationship)? Пример

Таблицы в реляционной модели данных могут иметь связи между собой. Такие связи называются отношениями. Для таблиц «Работник» и «Зарплата» можно установить связь по полю «Табельный номер».

Пример. Проанализируем таблицы «Работник» и «Зарплата». В этих таблицах можно установить отношение между таблицами на основе поля «Табельный номер». То есть, связь между таблицами происходит на основе поля (атрибуту) «Табельный номер».

Это означает следующее. Если нужно найти начисленную заработную плату в таблице «Зарплата» для работника Иванов И.И., то нужно выполнить следующие действия:

Рис. 1. Иллюстрация связи между таблицами. Табельный номер 2145 таблицы «Работник» отображается в таблице «Зарплата»

Рис. 2. Связь (отношение) между полями таблиц

3. Что такое внешний ключ (foreign key)? Пример

Понятие «внешний ключ» есть важным при рассмотрении связанных таблиц.

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

Пример. Пусть между таблицами «Работник» и «Зарплата» существует взаимосвязь по полю «Табельный номер». В этом случае, поле «Табельный номер» таблицы «Работник» может быть первичным ключом, а поле «Табельный номер» таблицы «Зарплата» внешним ключом. Это означает, что значения поля «Табельный номер» таблицы «Зарплата» заменяются значениями поля «Табельный номер» таблицы «Работник».

4. Что такое рекурсивный внешний ключ?

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

5. Могут ли первичный и внешний ключи быть простыми или составными (сложными)?

Первичный, вторичный и внешний ключи могут быть как простыми так и составными (сложными). Простые ключи – это ключи, которые содержат только одно поле (один атрибут). Составные (сложные) ключи – это ключи, которые содержат несколько полей (атрибутов).

6. Какое отличие между искусственным и естественным ключом? Пример

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

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

Пример. В таблице «Работник» естественном ключом есть поле (атрибут) «Табельный номер». Поле «Табельный номер» есть само по себе уникальным, так как не может быть двух работников с одинаковым табельным номером.

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

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

7. Какие существуют способы выбора первичного ключа?

Существует 3 способа выбора первичного ключа:

8. Что означают термины «главная таблица» (master) и «подчиненная таблица» (detail)?

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

Пример. Если рассмотреть таблицы «Работник» и «Зарплата», то таблица «Работник» есть главной, а таблица «Зарплата» есть подчиненной.

9. Какие существуют типы отношений (связей) между таблицами?

Существует 4 основных типа отношений между таблицами:

Пример. Если рассмотреть отношение между таблицами «Работник» и «Зарплата», то это отношения есть типа «один ко многим». Таблица «Работник» есть главной. Таблица «Зарплата» есть подчиненной.

Источник

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 можете почитать тут.

Источник