в каком случае внешний ключ обладает свойством уникальности

Что такое внешний ключ базы данных? Учебник для новичков

Эта статья написана Бриттни Паркер, писателем из Girls Write Tech, которая специализируется на написании технических материалов. Они стремятся побудить больше женщин-разработчиков делиться своими знаниями.

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

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

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

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

Что такое внешний ключ в базе данных?

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

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

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

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

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

Скажем, у нас есть две таблицы с именами customerи order. Мы можем использовать внешний ключ для создания связи между ними. В ordersтаблице мы создаем ключ, который ссылается на клиента (т.е. CUSTOMER_ID) в другой таблице.

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

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

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

Ограничение FK

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

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

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

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

Внешний ключ против первичного ключа

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

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

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

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

Внешний ключ против составного ключа

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

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

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

Ссылочные действия внешнего ключа

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

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

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

Совет: передовой опыт указывает на использование NOT NULLограничения при создании внешних ключей для поддержания структурной целостности базы данных.

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

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

Внешние ключи в SQL и MySQL

Давайте посмотрим на синтаксис с использованием SQL и MySQL. В следующем примере создается FOREIGN KEY в столбце «PersonID».

Узнайте больше о различных типах баз данных здесь

Следующий синтаксис позволяет нам назвать ограничение FOREIGN KEY:

Реальный пример SQL

А теперь давайте уточним. Ниже Actorsтаблица является таблицей, на которую ссылаются, и называется родительской таблицей. Здесь справочная таблица DigitalAssetsявляется дочерней таблицей.

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

В нашем примере мы изменяем наш DigitalAssetsи устанавливаем ActorIDстолбец как внешний ключ следующим образом:

Теперь, если мы добавим в DigitalAssetsтаблицу строку с идентификатором актера, которого нет в Actorsтаблице, появится сообщение об ошибке:

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

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

Источник

Внешние ключи

Целостность сущностей

Т.к. потенциальные ключи фактически служат идентификаторами объектов предметной области (т.е. предназначены для различенияобъектов), то значения этих идентификаторов не могут содержать неизвестные значения. Действительно, если бы идентификаторы могли содержать null-значения, то мы не могли бы дать ответ «да» или «нет» на вопрос, совпадают или нет два идентификатора.

Это определяет следующее правило целостности сущностей:

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

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

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

Таблица 5 Отношение «Поставщики и поставляемые детали»

Потенциальным ключом этого отношения может выступать пара атрибутов <«Номер поставщика», «Номер детали»>– в таблице они выделены курсивом.

Приведенный способ хранения данных обладает рядом недостатков.

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

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

Подобные проблемы возникают потому, что мы смешали в одном отношении различные объекты предметной области – и данные о поставщиках, и данные о деталях, и данные о поставках деталей. Говорят, что это отношение плохо нормализовано(просто нормализованным оно является хотя бы потому, что оно есть отношение и, следовательно, автоматически находится в 1НФ).

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

Эти фразы отражают различные типы взаимосвязей. Чтобы более точно отразить предметную область, можно иначе переформулировать фразы: «Один Поставщик может выполнять несколько Поставок», «Одна Деталь может поставляться несколькими Поставками». Это пример взаимосвязи типа «один-ко-многим».

Взаимосвязь между «Поставщиками» и «Деталями» можно переформулировать так: «Несколько Деталей может поставляться несколькими Поставщиками». Это пример взаимосвязи типа «много-ко-многим».

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

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

Таким образом, наш пример с поставщиками и поставляемыми деталями должен выглядеть следующим образом:

Таблица 6 Отношение «Поставщики»

Таблица 7 Отношение «Детали»

Таблица 8 Отношение «Поставки»

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

Дадим точное определение.

Определение 2. Пусть дано отношение R. Подмножество атрибутов FK отношения R будем называть внешним ключом, если:

Отношение S называется родительским отношением, отношение R называется дочерним отношением.

Замечание. Внешний ключ, также как и потенциальный, может быть простым и составным.

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

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

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

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

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

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

Источник

В каком случае внешний ключ обладает свойством уникальности

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

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

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

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

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

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

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

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

Источник