Как создать первичные и внешние ключи MySQL
В этой инструкции рассказываем про первичный и внешний ключи SQL, зачем они нужны и как их создать различными способами.
Эта инструкция — часть курса «MySQL для новичков».
Смотреть весь курс
Введение
Работа современных приложений и сложных сайтов, которыми мы привыкли пользоваться, невозможна без баз данных. Поэтому правильно настроенные БД важны для приложения не меньше, чем их дизайн и код, который с этой базой взаимодействует. Работа современных реляционных баз данных сложно представить без корректно настроенных первичных и внешних ключей. В этой инструкции мы подробно разберем создание этих ключей MySQL, расскажем для чего они нужны и как их использовать.
Облачные базы данных
Что такое первичный и внешний ключи и зачем они нужны
Начнем рассмотрение данного вопроса с двух самых главных элементов: первичного и внешнего ключей.
Первичный ключ или primary key
Первичный ключ — особенное поле в SQL-таблице, которое позволяет однозначно идентифицировать каждую запись в ней. Как правило, эти поля используются для хранения уникальных идентификаторов объектов, которые перечислены в таблице, например, это может быть ID клиента или товара.
Первичный ключ имеет несколько свойств:
- каждая запись в таком поле должна быть уникальной;
- запись в поле не должна быть пустой;
- в одной таблице может быть только один ключ (существуют также составные ключи, которые могут включать в себя несколько полей, однако в этой статье мы не будем их рассматривать).
Внешний ключ или foreign key
Внешний ключ нужен для того, чтобы связать две разные SQL-таблицы между собой. Внешний ключ таблицы должен соответствует значению первичного ключа таблицы, с которой он связан. Это помогает сохранять согласованность базы данных путем обеспечения так называемой «ссылочной целостности» (referential integrity).
Давайте рассмотрим все это на примере простой базы данных. Для начала нам понадобится создать базу данных. Заходим в MySQL и создаем базу данных, в которой будут храниться записи о книгах в библиотеке:
create database slcbookshelf; 
Так как дальше мы будем работать с этой базой, вводим команду:
use slcbookshelf; И создаем таблицу, в которой будут храниться записи о книгах в библиотеке:
CREATE TABLE books ( book_id INT NOT NULL, book_name VARCHAR(255) NOT NULL, book_category VARCHAR(255)); Создание первичного ключа при создании таблицы и с помощью ALTER TABLE
В созданной выше таблице ни одно из полей не является первичным ключом. Увидеть это мы можем, выполнив команду:
DESC books; Вывод команды будет выглядеть следующим образом:
mysql> DESC books; +---------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+-----+---------+-------+ | book_id | int | NO | | NULL | | | book_name | varchar(255) | NO | | NULL | | | book_category | varchar(255) | YES | | NULL | | +---------------+--------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) Первичный ключ при создании таблицы
Вы можете также создать его при создании таблицы, добавив в команду для создания таблицы следующую запись:
PRIMARY KEY (book_id) В таком случае поле book_id после создания таблицы будет являться первичным ключом для таблицы books.
Создание первичного ключа при помощи ALTER TABLE
Если таблица уже создана, а первичный ключ в ней не указан, вы можете создать ключевое поле, с помощью команды ALTER TABLE. Команда ALTER TABLE помогает изменять уже существующие столбцы, удалять их или добавлять новые. Чтобы определить первичный ключ в поле book_id, выполните команду:
ALTER TABLE books ADD PRIMARY KEY (book_id); Проверяем: mysql> DESC books; +---------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+-----+---------+-------+ | book_id | int | NO | PRI | NULL | | | book_name | varchar(255) | NO | | NULL | | | book_category | varchar(255) | YES | | NULL | | +---------------+--------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) Установка внешнего ключа MySQL при создании таблицы и с помощью ALTER TABLE
Предположим, у нас есть еще одна таблица под названием authors, которую нам необходимо связать с текущей таблицей books с помощью внешнего ключа author_id.
Внешний ключ при создании таблицы
Для того, чтобы привязать к таблице внешний ключ сразу при создании таблицы, вам необходимо дополнить запрос, которым вы создаете таблицу следующей записью:
FOREIGN KEY (author_id) REFERENCES authors(author_id) В итоге запрос, которым создается такая таблица будет выглядеть следующим образом:
CREATE TABLE books ( book_id INT NOT NULL, book_name VARCHAR(255) NOT NULL, book_category VARCHAR(255), FOREIGN KEY (author_id) REFERENCES authors(author_id)); Создание внешнего ключа при помощи ALTER TABLE
Если вам нужно определить поле уже созданной таблицы в качестве внешнего ключа, вы можете воспользоваться командой ALTER TABLE и создать внешний ключ в таблице командой:
ALTER TABLE books ADD FOREIGN KEY (author_id) REFERENCES authors(author_id); Сценарии использования внешнего ключа
Внешний ключ является очень важным и мощным инструментом в работе современных баз данных. С помощью внешнего ключа, например, вы можете настроить параметры, которые зададут действия при удалении или обновлении строки в таблице, настроить каскадное удаление или запретить изменять строку в таблице. Давайте рассмотрим на примерах.
Каскадное удаление или CASCADE
Каскадное удаление позволит вам одновременно удалить строки из главной таблицы, а вместе с ними удалить все связанные строки в других таблицах. Задается каскадное удаление таким запросом:
CREATE TABLE Orders( Id INT PRIMARY KEY AUTO_INCREMENT, CustomerId INT, CreatedAt Date, FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE CASCADE); Аналогично работает метод ON UPDATE CASCADE. При попытке изменить значение, записанное в поле первичного ключа, изменение будет применено к внешнему ключу, связанному с данным полем. Этот метод используется крайне редко, так как первичные ключи практически не являются изменяемыми полями.
RESTRICT
Опция RESTRICT позволяет отклонять все запросы на изменение или удаление строк в главной таблице при условии, что в связанной таблице также имеются строки. Задается данное ограничение следующим запросом:
CREATE TABLE Orders( Id INT PRIMARY KEY AUTO_INCREMENT, Customer_Id INT, CreatedAt Date, FOREIGN KEY (Customer_Id) REFERENCES Customers (Id) ON DELETE RESTRICT); Заключение
В данной статье мы рассмотрели что такое первичный и внешний ключи SQL, зачем они нужны и как их создать различными способами: при создании таблицы или при помощи ALTER TABLE. Также мы рассмотрели несколько сценариев использования внешнего ключа для управления таблицами.
Как установить и использовать MySQL Workbench
Стоит ли делать составной первичный ключ?
Я правильно понимаю, что legacy это практически синоним слову «устаревший»?
Если так, то стоит ли, при разработке нового приложения и новой базы данных, делать составные ключи? Или просто добавить ещё один столбец, который по сути ничего не будет делать, ведь всю работу с бд обычно выполняют фреймворки?
Отслеживать
задан 5 мар 2015 в 21:10
2,041 4 4 золотых знака 28 28 серебряных знаков 49 49 бронзовых знаков
Я обычно пользуюсь следующим правилом: если на таблицу другие таблицы не ссылаются, значит в этой таблице можно использовать составной ключ. Это верно не всегда (например, полученный ключ может быть не уникальным, либо в ключе придется использовать объемные поля и т.п.), но срабатывает часто. Тогда мы экономим память на лишнем индексе (вернее на его отсутствии), а значит увеличиваем эффективность индексов.
6 мар 2015 в 6:08
3 ответа 3
Сортировка: Сброс на вариант по умолчанию
Существует несколько устаревший подход в проектировании БД, иногда ошибочно называемый «академическим», когда первичный ключ отношения выбирается как один из естественных ключей отношения. Обычно таким ключом становится имя записи.
В приведенном вами вопросе именно такой подход и используется. Но этот подход устарел по многим причинам. Первая: первичный ключ записи — это то, что используется для ссылок на запись как в самой БД, так и за ее пределами. Желательно делать его как можно меньше — все же большинство естественных ключей строковые.
Причина вторая — многие вещи, выглядящие на первый взгляд как естественные ключи, на самом деле не являются такими. К примеру, среди людей бывают полные тезки (да еще и родившиеся в один день). В магазине может быть два товара с одним и тем же названием в разных отделах.
Причина третья — естественный ключ еще и может изменяться, что так же ведет к проблемам. Так, для человека естественным ключом мог бы быть номер паспорта или свидетельства о рождении — но эти документы могут быть заменены при достижении определенного возраста или в случае утери.
Поэтому хорошей практикой является введение суррогатного ключа — это новое поле, не имеющее никакого отношения к предметной области. Обычно его тип — автоинкрементное 32х-разрядное целое, реже — 64х-разрядное. Еще это может быть UUID.
Как правило, при наличии такого поля нет никакого смысла в составных ключах.
Тем не менее, иногда составные ключи бывают полезны. Вот эти случаи:
- Таблицы-связки для отношений «много ко многим». Обычно такие таблицы создаются и управляются библиотеками ORM самостоятельно — но иногда приходится создавать их отдельно. В таких таблицах естественным ключом является вся запись целиком — и нет никаких причин создавать отдельный суррогатный ключ.
- Некоторые дочерние подобъекты, не имеющие смысла в отрыве от родительского. Если мы делаем систему для тестирования учащихся — то иногда имеет смысл обращаться к ответу на вопрос как к 5му ответу на 147й вопрос — а не к 3423му ответу вообще. А иногда наоборот.
- Использование отношений в БД для дополнительной проверки целостности. Существует так называемая доменно-ключевая нормальная форма, в которой любые ограничения на данные реализуются в формате внешних ключей. В таком случае иногда имеет смысл включать дополнительные поля в первичный ключ.
Первичные ключи
Рассмотрим следующую ситуацию: пусть у нас есть 2 таблицы. Первая содержит список сотрудников, вторая — размер бонусов к зарплате для какой-то части этих сотрудников.
В жизни есть определенная вероятность того, что двух разных людей могут звать одинаково. Так вышло и у нас — 2 абсолютно разных сотрудника имеют одинаковое имя — «Алексей Иванов».
Предположим, что мы хотим одному из них начислить бонус в размере 200$. Глядя на список сотрудников с бонусами, можем ли мы сказать, какому именно Алексею Иванову мы должны начислить бонус в размере 200$? Однозначный ответ — нет.
Каждый отдельно взятый сотрудник — отдельная сущность, и всегда нужно иметь возможность различать их между собой. Именно для этого и используются первичные ключи.
Первичный ключ — это такой атрибут, который позволяет однозначно идентифицировать отдельно взятую строку в таблице.
Исходя из этого можно выделить еще некоторые свойства первичного ключа:
- Он не может быть пустым
- Он уникален в пределах отдельно взятой таблицы
- В таблице может быть только один первичный ключ
Добавление первичного ключа в таблицу
Добавить первичный ключ в таблицу можно несколькими способами. Первый — добавление при создании таблицы:
create table employees( id number primary key, -- Колонка id будет являться первичным ключом emp_name varchar2(100 char) not null, birth_date date not null );create table employees( id number primary key, -- Колонка id будет являться первичным ключом emp_name varchar2(100 char) not null, birth_date date not null );Данный способ — самый простой. Мы просто добавляем к нужной колонке primary key , и Oracle наделит ее всеми необходимыми свойствами.
Теперь давайте убедимся, что это действительно первичный ключ — попробуем добавить 2 строки в таблицу с одинаковым значением колонки id :
insert into employees(id, emp_name, birth_date) values(1, 'Андрей Иванов', to_date('1984.12.04', 'yyyy.mm.dd')); insert into employees(id, emp_name, birth_date) values(1, 'Петр Иванов', to_date('1990.01.30', 'yyyy.mm.dd'));insert into employees(id, emp_name, birth_date) values(1, 'Андрей Иванов', to_date('1984.12.04', 'yyyy.mm.dd')); insert into employees(id, emp_name, birth_date) values(1, 'Петр Иванов', to_date('1990.01.30', 'yyyy.mm.dd'));Первая строка вставится без ошибок, но при попытке добавить еще одну с уже существующим id получим ошибку ORA-00001: unique constraint (SQL_PXTWBEIMXHBUXOWCVTDQXEQKK.SYS_C0029851757) violated ORA-06512 . Эта ошибка говорит о том, что произошла попытка нарушить свойство уникальности нашего ключа. Длинная строка в скобках — это название нашего ключа. При создании его таким способом Oracle автоматически назначает каждому первичному ключу уникальное имя. В таких небольших примерах нам легко понять, где именно произошла ошибка, но в сложных системах с сотнями таблиц, с большим количеством запросов на вставку в БД понять, на каком ключе происходит сбой очень трудно.
К счастью, мы можем сами назначать имя для первичного ключа при создании таблицы:
create table employees( id number, emp_name varchar2(100 char) not null, birth_date date not null, constraint employees_PK primary key(id) -- создаем первичный ключ и назначаем ему имя )create table employees( id number, emp_name varchar2(100 char) not null, birth_date date not null, constraint employees_PK primary key(id) -- создаем первичный ключ и назначаем ему имя )Теперь попробуем вставить дублирующие значения в колонку id :
insert into employees(id, emp_name, birth_date) values(1, 'Андрей Иванов', to_date('1984.12.04', 'yyyy.mm.dd')); insert into employees(id, emp_name, birth_date) values(1, 'Петр Иванов', to_date('1990.01.30', 'yyyy.mm.dd'));insert into employees(id, emp_name, birth_date) values(1, 'Андрей Иванов', to_date('1984.12.04', 'yyyy.mm.dd')); insert into employees(id, emp_name, birth_date) values(1, 'Петр Иванов', to_date('1990.01.30', 'yyyy.mm.dd'));На этот раз сообщение об ошибке будет немного другим: ORA-00001: unique constraint (SQL_EAIYWBGLYOEYCEZDANCUIWUWH.EMPLOYEES_PK) violated . Теперь мы явно видим, что ошибка в ключе EMPLOYEES_PK .
Составные первичные ключи
Первичный ключ может состоять из нескольких колонок. Подобный ключ обладает теми же особенностями, что и ключ из одной колонки.
Рассмотрим примеры создания таблицы с составным первичным ключом.
Предположим, что мы хотим начислять дополнительные бонусы сотрудникам каждый месяц. Одному сотруднику в месяц может быть начислено не более одного бонуса. Данные в этой таблице могли бы выглядеть вот так:
|id сотрудника |месяц |Размер бонуса |1 |2020.01.01|300 |1 |2020.02.01|150 |2 |2020.02.01|240 |3 |2020.02.01|100 Сделать колонку c id сотрудника первичным ключом нельзя, т.к. в таком случае в таблице можно будет иметь лишь по одной строке на каждого сотрудника. Но ключ из колонок с id сотрудника и месяца бонуса отлично подойдет — на один месяц можно будет давать бонус только одному сотруднику, в противном случае уникальность ключа будет нарушена.
create table month_bonuses( emp_id number not null, month_bonus date not null, bonus_value number not null, constraint month_bonuses_pk primary key(emp_id, month_bonus) )create table month_bonuses( emp_id number not null, month_bonus date not null, bonus_value number not null, constraint month_bonuses_pk primary key(emp_id, month_bonus) )Указать primary key напротив нескольких колонок нельзя, т.к. Oracle будет пробовать каждую из этих колонок сдалеть первичным ключом, а он может быть только один. В итоге мы получим ошибку ORA-02260: table can have only one primary key :
-- Получим ошибку при создании таблицы! create table month_bonuses( emp_id number not null primary key, month_bonus date not null primary key, bonus_value number not null )-- Получим ошибку при создании таблицы! create table month_bonuses( emp_id number not null primary key, month_bonus date not null primary key, bonus_value number not null )7) Понятие ключей (первичный, внешний, составной), пояснить каждый вид ключа, примеры.
Целостность базы данных — свойство базы данных, означающее, что БД содержит полную и непротиворечивую информацию, необходимую для корректного функционирования приложений. Для обеспечения целостности накладывают ограничения целостности.
Достоверность:
Достоверность (или истиность) есть соответствие фактов, хранящихся в базе данных, реальному миру.
9) Язык SQL. Применение операторов сравнения в предложении Where. Пояснить на конкретной предметной области.
Язык SQL (Structured Query Language — «язык структурированных запросов») — универсальный компьютерный язык, применяемый для создания, модификации и управления данными в базах данных.
WHERE — оператор в SQL, указывающий, что оператор языка управления данными должен действовать только на записи, удовлетворяющие определенным критериям.
SELECT * FROM TABLE WHERE S1 = 1 – выборка всех записей, где в столбце C1 будет имеется значение 1.
10) Эксплуатация БД — актуализация БД, ведение БД. Категории пользователей БД, с которыми связаны эти понятия.
Актуализация базы данных — это подтверждение имеющейся информации и получение дополнительных необходимых данных.
Ведение базы данных – это процесс заполнения базы данных в соответствии с достоверностью.
С этими понятиями связана категория конечных пользователей, которая отвечает за актуализацию и ведение базы данных.
11) Язык SQL. Выборка заданных строк, когда требуется найти строки со значением поля по символьному шаблону. Пояснить на конкретной предметной области.
Язык SQL (Structured Query Language — «язык структурированных запросов») — универсальный компьютерный язык, применяемый для создания, модификации и управления данными в базах данных.
Выборка заданных строк, когда требуется найти строки со значением поля по символьному шаблону осуществляется посредством LIKE:
SELECT * FROM table WHERE s1=1 LIKE ‘King’;
12) Понятия: сущность, атрибут, кортеж. Показать на примере. Привязать к модели данных.
Сущность — любой различимый объект (объект, который мы можем отличить от другого), информацию о котором необходимо хранить в базе данных. Сущностями могут быть люди, места, самолеты, рейсы, вкус, цвет и т.д.
Атрибут это записи об определенных параметрах каждой из сущностей называются атрибутами. Например, для сущности «заказчик», видимо, будет храниться информация об его наименовании, представителях, адресе и т.п.
Кортежем называется группа взаимосвязанных элементов данных. В реляционных базах данных кортеж это строка таблицы.
13) Язык SQL. Выборка значений входящий в заданный интервал. Пояснить на конкретной предметной области.
Язык SQL (Structured Query Language — «язык структурированных запросов») — универсальный компьютерный язык, применяемый для создания, модификации и управления данными в базах данных.
SELECT * FROM table WHERE s1>0 AND s1
SELECT – выборка значений
FROM table – название таблицы
14) Правила перехода от ER-модели к реляционной модели данных. Рассказать на конкретном примере.
Для начало необходимо создать ER-модель. После этого необходимо реализовать каждую сущность и кортеж в реляционной модели данных. Например у нас есть следующая ER-модель: