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

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

Часть 11.4: Внешние ключи в базах данных SQLite: FOREIGN KEY в SQLite3

Привет, посетитель сайта ZametkiNaPolyah.ru! Продолжаем изучать базы данных и наше знакомство с библиотекой SQLite3. В реляционных базах данных, вернее, в реляционных СУБД есть множество приемов, которые позволяют обеспечить целостность данных, мы уже разобрались с ограничением уровня столбца и первичным ключом, который является одновременно и индексом таблицы базы данных, позволяющим значительно ускорить выборку данных из базы данных, и правилом, которое четко выполняет SQLite. В этой записи мы поговорим про еще одно ограничение уровня таблиц: внешний ключ или FOREIGN KEY.

Внешние ключи в базах данных SQLite: FOREIGN KEY в SQLite3

Из этой записи вы узнаете как работает и для чего нужны внешние ключи в базах данных SQLite3. Познакомитесь с синтаксисом FROREIGN KEY в SQLite. А так же узнаете, как реализовать связи один ко многим и многие ко многим между таблицами базы данных под управлением SQLite при помощи внешних ключей. Но, стоит заметить, что вы сможете реализовать данные связи и без использования FOREIGN KEY, но в этом случае вы никак не обеспечите целостность данных в ваших таблицах.

Работа внешних ключей в базах данных SQLite: FOREIGEN KEY и REFERENCE в SQLite3

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

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

Синтаксис ограничения внешнего ключа в базах данных SQLite3

Правила использования внешнего ключа не очень сложные, но давайте разберемся с тем, как реализован внешний ключ в SQLite3 и его конструкции: FOREIGEN KEY и REFEERENCE. Обратите внимание: когда вы связываете таблицы при помощи внешнего ключа одна таблица является родительской, а вторая таблица является дочерней. Внешний ключ всегда ссылается на родительскую таблиц, другими словами конструкция FOREIGN KEY и REFERENCE указывается в дочерней таблице.

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

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

Реализация связи один ко многим в базах данных SQLite. Пример связи один ко многим и FOREIGEN KEY в SQLite

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

Чтобы реализовать связь один ко многим, нам нужно воспользоваться конструкцией FOREIGEN KEY и REFERENCE при создании таблицы при помощи команды CREATE.

Источник

Первичный ключ и внешний ключ таблиц реляционных баз данных

Вступление

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

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

Типы данных в базах

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

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

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

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

Выше мы вспоминали: каждая строка (запись) БД должна быть уникальна. Именно первичный ключ в виде наборов определенных значений, максимально идентифицируют каждую запись. Можно определить по-другому. Первичный ключ: набор определенных признаков, уникальных для каждой записи. Обозначается первичный ключ, как primary key.

Primary key (PK) очень важен для каждой таблицы. Поясню почему.

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

Ключ внешний

Foreign key, кратко FK. Обеспечивает однозначную логическую связь, между таблицами одной БД.

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

Более сложный пример.

Две таблицы данных: Люди и Номера телефонов.

Таблица: Люди

Таблица: Номера телефонов

В таблице Номера телефонов PK уникален. FK этой таблицы является PK таблицы Люди. Связь между номерами телефонов и людьми обеспечивает FK таблицы телефонов. То есть:

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

Источник

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

Эта статья написана Бриттни Паркер, писателем из 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таблице, появится сообщение об ошибке:

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

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

Источник

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

Источник

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

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

При выделении связи выделяют главную или родительскую таблицу (primary key table / master table) и зависимую, дочернюю таблицу (foreign key table / child table). Дочерняя таблица зависит от родительской.

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

Связи между таблицами бывают следующих типов:

Один к одному (One to one)

Один к многим (One to many)

Многие ко многим (Many to many)

Связь один к одному

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

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

Например, таблица Users представляет пользователей и имеет следующие столбцы:

UserId (идентификатор, первичный ключ)

Name (имя пользователя)

И таблица Blogs представляет блоги пользователей и имеет следующие столбцы:

BlogId (идентификатор, первичный и внешний ключ)

Name (название блога)

В этом случае столбец BlogId будет хранить значение из столбца UserId из таблицы пользователей. То есть столбец BlogId будет выступать одновременно первичным и внешним ключом.

Связь один ко многим

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

ArticleId (идентификатор, первичный ключ)

BlogId (внешний ключ)

Title (название статьи)

В этом случае столбец BlogId из таблицы статей будет хранить значение из столбца BlogId из таблицы блогов.

Связь многие ко многим

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

Например, в случае со статьями и тегами пусть будет таблица Tags, которая имеет два столбца:

TagId (идентификатор, первичный ключ)

Также пусть будет промежуточная таблица ArticleTags со следующими полями:

TagId (идентификатор, первичный и внешний ключ)

ArticleIdId (идентификатор, первичный и внешний ключ)

Технически мы получим две связи один-ко-многим. Столбец TagId из таблицы ArticleTags будет ссылаться на столбец TagId из таблицы Tags. А столбец ArticleId из таблицы ArticleTags будет ссылаться на столбец ArticleId из таблицы Articles. То есть столбцы TagId и ArticleId в таблице ArticleTags представляют составной первичный ключ и одновременно являются внешними ключами для связи с таблицами Articles и Tags.

Ссылочная целостность данных

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

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

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

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

Аномалия удаления

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

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

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

Аномалия вставки

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

Аномалии обновления

Для решения проблемы аномалии обновления применяется нормализация, которая будет рассмотрена далее.

Источник