Одним из наиболее важных понятий в базах данных является создание связей между таблицами базы данных. Эти отношения обеспечивают механизм для связывания данных, хранящихся в нескольких таблицах, и их эффективного извлечения. Чтобы создать связь между двумя таблицами, необходимо указать внешний ключ в одной таблице, который ссылается на столбец в другой таблице.
Таблицы базы данных и отношения
Возможно, вы уже знаете, что базы данных — это просто серия таблиц, аналогичных тем, которые вы уже можете использовать в программах для работы с электронными таблицами, таких как Microsoft Excel. На самом деле, вы даже можете конвертировать электронную таблицу Excel в базу данных. Однако базы данных расходятся с электронными таблицами, когда речь идет о построении мощных взаимосвязей между таблицами.
Рассмотрим, например, базу данных, используемую компанией для отслеживания информации о людских ресурсах. В этой базе данных может быть таблица «Сотрудники», которая содержит следующую информацию для каждого сотрудника компании:
- ID сотрудника
- Имя
- Фамилия
- Офисный телефон
- Домашний телефон
- PositionID
В этом примере идентификатор сотрудника — это уникальное сгенерированное целое число, которое присваивается каждому сотруднику при добавлении в базу данных. Идентификатор должности — это код вакансии, используемый для ссылки на должность сотрудника в компании. В этой схеме сотрудник может иметь только одну должность, но несколько (или нет) сотрудников могут занимать каждую должность. Например, у вас могут быть сотни сотрудников с позицией «Кассир».
База данных также может содержать таблицу с названием Позиции со следующей дополнительной информацией о каждой позиции:
- PositionID
- заглавие
- Уровень работы
- SkillCategory
- Место нахождения
Поле идентификатора позиции в этой таблице аналогично полю идентификатора сотрудника в таблице сотрудников — это уникальное сгенерированное целое число, которое создается при добавлении позиции в базу данных.
Когда мы собираемся вывести список сотрудников из базы данных, было бы естественно запросить имя каждого человека и его должность. Однако эта информация хранится в нескольких таблицах базы данных, поэтому ее можно получить только с помощью запроса JOIN, который требует наличия существующей связи между таблицами.
Когда вы смотрите на структуру таблиц, поле, определяющее взаимосвязь, вероятно, очевидно — поле Position ID. У каждого сотрудника может быть только одна должность, и эта позиция идентифицируется путем добавления идентификатора должности из соответствующей записи таблицы позиций. Помимо того, что он является первичным ключом для таблицы «Позиции», в этом примере поле «Идентификатор позиции» также является внешним ключом из таблицы «Сотрудники» в таблицу «Позиции». Затем база данных может использовать это поле для сопоставления информации из нескольких таблиц и обеспечения того, чтобы любые изменения или дополнения в базе данных продолжали обеспечивать ссылочную целостность.
После того, как вы определили внешний ключ, вы можете продолжить и извлечь нужную информацию из базы данных, используя следующий запрос:
ВЫБЕРИТЕ Имя, Фамилия, Название
ОТ сотрудников INNER JOIN
ON Employees.PositionID = Positions.PositionID
Создание внешних ключей в SQL Server
Технически, вам не нужно явно определять отношения, чтобы иметь возможность выполнять запросы, подобные приведенному выше. Однако, если вы определите явное отношение с помощью ограничения внешнего ключа, база данных сможет выполнить некоторые вспомогательные работы за вас:
- Когда вы добавляете новую запись в таблицу «Сотрудники», база данных гарантирует, что введенный вами идентификатор позиции является действительным первичным ключом в таблице «Позиции».
- Если вы измените ID позиции в таблице Positions, база данных может выполнить необходимые обновления таблицы Employees, чтобы сохранить согласованность.
- База данных может защитить от воздействия удаления позиции из таблицы позиций, либо отказавшись удалить позицию с соответствующими записями сотрудников, либо выполнив каскадное удаление всех связанных сотрудников.
Вот как вы можете создать внешний ключ в SQL Server:
ALTER TABLE Сотрудники
ДОБАВИТЬ ИНОСТРАННЫЙ КЛЮЧ (PositionID)
ССЫЛКИ Позиции (PositionID)
Вы также можете создать внешний ключ при создании таблицы, добавив предложение:
ИНОСТРАННЫЕ КЛЮЧЕВЫЕ ССЫЛКИ Позиции (PositionID)
до конца определения столбца для столбца внешнего ключа.