Первичный и внешний ключи… Это гениально!

Перед мной стала задача объяснить людям, которые никогда не слышали о базах данных, что это такое.  Нашел вот такой великолепный пример. С радостью делюсь с вами.

Gold key

Ну давай-те такой “простенький пример” рассмотрим. Допустим ведет препод бумажку, в которой отмечает, какой юзер сдал сколько лабораторок. Выглядит это где-то так:

Имя, Фамилия Лаб сдано
Алексей Иванов 3
Дмитрий Смирнов 7
Татьяна Смирнова 9
Татьяна Яшина 0

Видим: Танечка Смирнова и ее брат Дмитрий молодцы-отличники, а другая Танечка, Яшина — лентяйка и прогульщица, но это ее не парит. Все счастливы, все довольны, семестр идет к концу, и тут вдруг — оп-па! — выясняется, что Дима С. без ума от Тани Я., что он предложил ей руку и сердце и что она даже согласилась. И что теперь она уже не Т. Яшина, а самая что ни на есть Т. Смирнова. Все бы ничего, но Т. Смирновых тепереча в группе две, причем у одной уже допуск к экзаменам в кармане, а у другой — отчисление на носу:

Имя, Фамилия Лаб сдано
Алексей Иванов 3
Дмитрий Смирнов 7
Татьяна Смирнова 9
Татьяна Смирнова 0

Как же быть в этом случае бедному профессору, как отличить наших Тань, как узнать — какая из них с третьей строчки таблицы, а какая с четвертой? Написать рядом с именем еще и отчество? А вдруг они обе, скажем, Татьяны Ивановны? Думал он думал, скрипел мозгами скрипел, да и решил: “а буду-ка я писать в списочек номера зачетных книжек! Они-то поди у разных студентов не повторяются, то бишь уникальны. А назову я это дело внешним ключом, потому как книжки не у меня хранятся, а где-то вовне — у студентов под подушкой и еще в деканате их номера отдельным списочком перечислены — вот пусть теперь с Танечкой деканат и разбирается, мое дело маленькое — номера зачеток и оценки им сказать”. Сказано-сделано, и получилось уже вместо одной таблички две:

Табличка препода

N зачетки Лаб сдано
0611101 3
0611102 7
0611103 9
0611104 0

Табличка деканата

N зачетки Имя, Фамилия Группа
0611101 Алексей Иванов 2005-П1
0611102 Дмитрий Смирнов 2005-П1
0611103 Татьяна Смирнова 2005-П1
0611104 Татьяна Смирнова 2005-П1

Другие преподы тоже были не дураки и новшество оценили, через день у каждого было по такому “пронумерованому” списку. Зашел как-то на кафедру декан, покурить с коллегами. Глядит, а они все с листочками, а в листочках циферки какие-то. Заинтересовался он, расспросил что да как, а потом и говорит: “ну раз пошла такая пьянка, то и мы в деканате у себя такие списочки сделаем. Так что несите-ка все эти листочки к нам в секретарскую, да на уголке фамилию свою напишите и инициалы”. На что преподы ему и отвечают: “мы-то напишем, да вот только легче тебе, Сан Саныч, от этого не станет. Потому что например Ивановых И.И у нас аж целых две штуки — попробуй-ка их различи”. “Ага” — отвечает декан — “ну раз так, тогда, чтоб легче в списочках разбираться было возьму-ка я одного Иванова — и сокращу! И впредь приказ издам — чтоб на работу людей с одинаковыми фамилиями и именами не принимали, а сперва проверяли бы. Такой вот у нас будет первичный ключ”. Сказано-сделано, написал приказ:

Приказываю уволить с такого-то числа Иванова И. И. /дата, подпись/

Написал, а преподы смеются: “чудак ты, Саныч, ей-богу! Написал “Иванова”, а какого из них — не написал! Теперь хоть обоих увольняй по твоему приказу”. Рассерчал декан, кричит: “Зачем обоих?!! Одного выберите!!!”. А оказывается нету ну никакой возможности выбрать одного: двоих-пожалуйста, а одного — ну никак! Так и остались Ивановы на кафедре, декан — без листочков, студенты — с лабами, а Танюша Яшина — с академом из-за декрета.

Выводы:

  1. Первичный ключ нужен для однозначной идентификации некоего экземпляра множества (одного препода среди множества преподов, одной строки из таблицы и т.п.). Подходить к его выбору надо вдумчиво, чтоб его уникальность можно было легко соблюсти, иначе может получиться как у того декана с его профессорами — “пойди туда не знаю куда, уволь того не знаю кого”. Большинство СУБД предоставляют возможность создать так называемый “суррогатный” ключ, не имеющий реального “понятийного” смысла (в отличие от “естественного”, который обычно состоит из реальных атрибутов сущности) и служащий исключительно для идентификации строк в таблице. Механизмы могут быть самые различные — от автоинкрементных полей и GUID’ов до задания специальных последовательностей чисел (sequence) или генераторов (generator) следующего значения ключа.
  2. Внешний (ака secondary) ключ нужен для ссылки на некие “внешние” сущности (номера зачетки в списке зачетов, табельного номера в ведомости на зарплату и т.п.). То есть он показывает связь именно конкретно с этим студентом №1611103, независимо от того Татьяна она Яшина, Смирнова, или вообще некто Алекс Ван Хелленгс неизвестного пола и возраста. Таким образом достигается то, что неключевые атрибуты таблиц могут изменяться независимо от связанных сущностей, не порождая при этом противоречий и коллизий
  3. Что бы ни говорили — понятия “первичный/внешний ключ” относятся только к теории реляционных баз данных и ни с какими конкретными механизмами конкретной СУБД оно не связано. В том числе оно не подразумевают под собой ни наличия индексов, построенных по “ключам” ни даже автоматической проверки и контроля их верности и/или уникальности.
    • Однако и то, и другое обычно все же СУБД поддерживается, а как правило — и задается по умолчанию для “ключевых” полей таблиц. Сделано это из чисто практических соображений о том, что а) если пользователь пометил некие поля как первичный ключ, следовательно он хочет чтоб этот ключ был уникальным и б) скорее всего именно по этим реквизитам будут заданы связи (внешние ключи в других таблицах) и, следовательно, именно его следует оптимизировать для поиска. Кроме того здесь играет роль и такая вещь, как селективность построенного индекса (которая для индекса по уникальному ключу будет наилучшей), но это уже немного другая тема.
    • Для внешних ключей также обычно имеется возможность задать проверку т.н. “ссылочной целостности” — отсутствия внешних ключей, указывающих “в никуда”, то есть ссылающихся на строку, отсутствующую во “внешней” таблице. В этом случае при попытке “неправильного” задания внешнего или первичного ключа в одной из сущностей, СУБД в зависимости от установок либо предпримет попытку сама исправить ошибку (например удалив при удалении ПК все связанные записи из других таблиц), либо сообщит пользователю о невозможности совершения такой операции. Обычно для ускорения такой проверки по этим полям также строится индекс, который опять же, не имеет прямогоотношения к понятию “внешний ключ” и служит исключительно для ускорения работы с базой.

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

Advertisements

One thought on “Первичный и внешний ключи… Это гениально!

  1. Yurii says:

    Круто!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: