Из статьи вы узнаете, что такое первичный и внешний ключ в SQL. Зачем они нужны и как их использовать. Я покажу на практике как их использовать в PostgreSQL.
Теория
Первичный ключ это одно или несколько полей в таблице. Он необходим для уникальной идентификации любой строки. Первичный ключ накладывает некоторые ограничения:
- Все записи относящиеся к первичному ключу должны быть уникальны. Это означает, что если первичный ключ состоит из одного поля, то все записи в нём должны быть уникальными. А если первичный ключ состоит из нескольких полей, то комбинация этих записей должна быть уникальна, но в отдельных полях допускаются повторения.
- Записи в полях относящихся к первичному ключу не могут быть пустыми. Это ограничение в PostgreSQL называется not null.
- В каждой таблице может присутствовать только один первичный ключ.
К первичному ключу предъявляют следующее требование:
- Первичный ключ должен быть минимально достаточным. То есть в нем не должно быть полей, удаление которых из первичного ключа не отразится на его уникальности. Это не обязательное требование но желательно его соблюдать.
Первичный ключ может быть:
- естественным — существует в реальном мире, например ФИО, или номер и серия паспорта;
- суррогатным — не существует в реальном мире, например какой-то порядковый номер, который существует только в базе данных.
Я сам не имею большого опыта работы с SQL, но в книгах пишут что лучше использовать естественный первичный ключ. Почему именно так, я пока ответить не смогу.
Связь между таблицами
Первостепенная задача первичного ключа — это уникальная идентификация каждой строки. Но первичный ключ может решить ещё одну задачу. В базе данных есть возможность связывания нескольких таблиц. Для такой связи используют первичный и внешний ключ sql. В одной из таблиц создают внешний ключ, который ссылается на поля другой таблицы. Но внешний ключ не может ссылаться на любые поля другой таблицы, а может ссылаться только на определённые:
- эти поля должны присутствовать и в ссылающейся таблице и в той таблице на которую он ссылается;
- ссылается внешний ключ из одной таблицы обычно на первичный ключ другой таблицы.
Например, у вас есть таблица «Ученики» (pupils) и выглядит она следующим образом:
| ФИО full_name | Возраст age | Класс class |
| Иванов Иван Иванович | 15 | 9А |
| Сумкин Фёдор Андреевич | 15 | 9А |
| Петров Алексей Николаевич | 14 | 8Б |
| Булгаков Александр Геннадьевич | 14 | 8Б |
И есть таблица «Успеваемость» (evaluations):
| Предмет item | ФИО full_name | Оценка evaluation |
| Русский язык | Иванов Иван Иванович | 4 |
| Русский язык | Петров Алексей Николаевич | 5 |
| Математика | Булгаков Александр Геннадьевич | 3 |
| Литература | Сумкин Фёдор Андреевич | 5 |
В обоих таблицах есть одинаковое поле: ФИО. При этом в таблице «Успеваемость» не может содержаться ФИО, которого нет в таблице «Ученики«. Ведь нельзя поставить ученику оценку, которого не существует.
Первичным ключом в нашем случае может выступать поле «ФИО» в таблице «Ученики«. А внешним ключом будет «ФИО» в таблице «Успеваемость«. При этом, если мы удаляем запись о каком-то ученике из таблицы «Ученики«, то все его оценки тоже должны удалиться из таблицы «Успеваемость«.
Ещё стоит заметить что первичный ключ в PostgreSQL автоматически создает индекс. Индекс ускоряет доступ к строкам таблицы и накладывает ограничение на уникальность. То есть двух Ивановых Иванов Ивановичей у нас не может существовать. Чтобы это обойти можно использовать:
- составной первичный ключ — например, в качестве первичного ключа взять два поля: ФИО и Класс;
- суррогатный первичный ключ — в таблице «Ученики» добавить поле «№ Ученика» и сделать это поле первичным ключом;
- добавить более уникальное поле — например, можно использовать уникальный номер зачетной книжки и использовать новое поле в качестве первичного ключа;
Теперь давайте попробуем создать эти две таблички и попробуем с ними поработать.
Практика
Создадим базу данных school и подключимся к ней. Затем создадим таблицу pupils. Про создание таблиц я уже писал тут, а про типы данных тут. Затем посмотрим на табличку с помощью команды \d:
postgres=# CREATE DATABASE school;
CREATE DATABASE
postgres=# \c school
You are now connected to database "school" as user "postgres".
school=# CREATE TABLE pupils
(full_name text,
age integer,
class varchar(3),
PRIMARY KEY (full_name)
);
CREATE TABLE
school=# \dt pupils
List of relations
Schema | Name | Type | Owner
--------+--------+-------+----------
public | pupils | table | postgres
(1 row)
school=# \d pupils
Table "public.pupils"
Column | Type | Collation | Nullable | Default
-----------+----------------------+-----------+----------+---------
full_name | text | | not null |
age | integer | | |
class | character varying(3) | | |
Indexes:
"pupils_pkey" PRIMARY KEY, btree (full_name)
Как вы могли заметить, первичный ключ создаётся с помощью конструкции PRIMARY KEY (имя_поля) в момент создания таблицы.
Вывод команды \d нам показал, что у нас в таблице есть первичный ключ. А также первичный ключ сделал два ограничения:
- поле full_name, к которому относится первичный ключ не может быть пустым, это видно в колонки Nullable — not null;
- для поля full_name был создан индекс pupils_pkey с типом btree. Про типы индексов и про сами индексы расскажу в другой статье.
Индекс в свою очередь наложил ещё одно ограничение — записи в поле full_name должны быть уникальны.
Следующим шагом создадим таблицу evaluations:
school=# CREATE TABLE evaluations
(item text,
full_name text,
evaluation integer,
FOREIGN KEY (full_name) REFERENCES pupils ON DELETE CASCADE
);
CREATE TABLE
school=# \d evaluations
Table "public.evaluations"
Column | Type | Collation | Nullable | Default
------------+---------+-----------+----------+---------
item | text | | |
full_name | text | | |
evaluation | integer | | |
Foreign-key constraints:
"evaluations_full_name_fkey" FOREIGN KEY (full_name) REFERENCES pupils(full_name) ON DELETE CASCADE
В этом случае из вывода команды \d вы увидите, что создался внешний ключ (Foreign-key), который относится к полю full_name и ссылается на таблицу pupils.
Внешний ключ создается с помощью конструкции FOREIGN KEY (имя_поля) REFERENCES таблица_на_которую_ссылаются.
Создавая внешний ключ мы дополнительно указали опцию ON DELETE CASCADE. Это означает, что при удалении строки с определённым учеником в таблице pupils, все строки связанные с этим учеником удалятся и в таблице evaluations автоматически.
Заполнение таблиц и работа с ними
Заполним таблицу «pupils«:
school=# INSERT into pupils (full_name, age, class)
VALUES ('Иванов Иван Иванович', 15, '9A'),
('Сумкин Фёдор Андреевич', 15, '9A'),
('Петров Алексей Николаевич', 14, '8B'),
('Булгаков Александр Геннадьевич', 14, '8B');
INSERT 0 4
Заполним таблицу «evaluations«:
school=# INSERT into evaluations (item, full_name, evaluation)
VALUES ('Русский язык', 'Иванов Иван Иванович', 4),
('Русский язык', 'Петров Алексей Николаевич', 5),
('Математика', 'Булгаков Александр Геннадьевич', 3),
('Литература', 'Сумкин Фёдор Андреевич', 5);
INSERT 0 4
А теперь попробуем поставить оценку не существующему ученику:
school=# INSERT into evaluations (item, full_name, evaluation)
VALUES ('Русский язык', 'Угаров Виктор Михайлович', 3);
ERROR: insert or update on table "evaluations" violates foreign key constraint "evaluations_full_name_fkey"
DETAIL: Key (full_name)=(Угаров Виктор Михайлович) is not present in table "pupils".
Как видите, мы получили ошибку. Вставлять (insert) или изменять (update) в таблице evaluations, в поле full_name можно только те значения, которые есть в этом же поле в таблице pupils.
Теперь удалим какого-нибудь ученика из таблицы pupils:
school=# delete from pupils WHERE full_name = 'Иванов Иван Иванович'; DELETE 1
И посмотрим на строки в таблице evaluations:
school=# SELECT * FROM evaluations;
item | full_name | evaluation
--------------+--------------------------------+------------
Русский язык | Петров Алексей Николаевич | 5
Математика | Булгаков Александр Геннадьевич | 3
Литература | Сумкин Фёдор Андреевич | 5
(3 rows)
Как видно, строка с full_name равная ‘Иванов Иван Иванович’ тоже удалилась. Если бы у Иванова было бы больше оценок, они всё равно бы все удалились. За это, если помните отвечает опция ON DELETE CASCADE.
Попробуем теперь создать ученика с точно таким-же ФИО, как у одного из существующих:
school=# INSERT into pupils (full_name, age, class)
VALUES ('Петров Алексей Николаевич',15, '5B');
ERROR: duplicate key value violates unique constraint "pupils_pkey"
DETAIL: Key (full_name)=(Петров Алексей Николаевич) already exists.
Ничего не вышло, так как такая запись уже существует в поле full_name, а это поле у нас имеет индекс. Значит значения в нём должны быть уникальные.
Составной первичный ключ
Есть большая вероятность, что в одной школе будут учиться два ученика с одинаковым ФИО. Но меньше вероятности что эти два ученика будут учиться в одном классе. Поэтому в качестве первичного ключа мы можем взять два поля, например full_name и class.
Давайте удалим наши таблички и создадим их заново, но теперь создадим их используя составной первичный ключ:
school=# DROP table evaluations; DROP TABLE school=# DROP table pupils; DROP TABLE school=# CREATE TABLE pupils (full_name text, age integer, class varchar(3), PRIMARY KEY (full_name, class) ); CREATE TABLE school=# CREATE TABLE evaluations (item text, full_name text, class varchar(3), evaluation integer, FOREIGN KEY (full_name, class) REFERENCES pupils ON DELETE CASCADE ); CREATE TABLE
Как вы могли заметить, разница не большая. Мы должны в PRIMARY KEY указать два поля вместо одного. И в FOREIGN KEY точно также указать два поля вместо одного. Ну и не забудьте в таблице evaluations при создании добавить поле class, так как его там в предыдущем варианте не было.
Теперь посмотрим на структуры этих таблиц:
school=# \d pupils
Table "public.pupils"
Column | Type | Collation | Nullable | Default
-----------+----------------------+-----------+----------+---------
full_name | text | | not null |
age | integer | | |
class | character varying(3) | | not null |
Indexes:
"pupils_pkey" PRIMARY KEY, btree (full_name, class)
Referenced by:
TABLE "evaluations" CONSTRAINT "evaluations_full_name_class_fkey" FOREIGN KEY (full_name, class) REFERENCES pupils(full_name, class) ON DELETE CASCADE
school=# \d evaluations
Table "public.evaluations"
Column | Type | Collation | Nullable | Default
------------+----------------------+-----------+----------+---------
item | text | | |
full_name | text | | |
class | character varying(3) | | |
evaluation | integer | | |
Foreign-key constraints:
"evaluations_full_name_class_fkey" FOREIGN KEY (full_name, class) REFERENCES pupils(full_name, class) ON DELETE CASCADE
Первичный ключ в таблице pupils уже состоит из двух полей, поэтому внешний ключ ссылается на эти два поля.
Теперь мы можем учеников с одинаковым ФИО вбить в нашу базу данных, но при условии что они будут учиться в разных классах:
school=# INSERT INTO pupils (full_name, age, class)
VALUES ('Гришина Ольга Константиновна', 12, '5A'),
('Гришина Ольга Константиновна', 14, '7B');
INSERT 0 2
school=# SELECT * FROM pupils;
full_name | age | class
------------------------------+-----+-------
Гришина Ольга Константиновна | 12 | 5A
Гришина Ольга Константиновна | 14 | 7B
(2 rows)
И также по второй таблице:
school=# INSERT INTO evaluations (item, full_name, class, evaluation)
VALUES ('Русский язык', 'Гришина Ольга Константиновна', '5A', 5),
('Русский язык', 'Гришина Ольга Константиновна', '7B', 3);
INSERT 0 2
school=# SELECT * FROM evaluations;
item | full_name | class | evaluation
--------------+------------------------------+-------+------------
Русский язык | Гришина Ольга Константиновна | 5A | 5
Русский язык | Гришина Ольга Константиновна | 7B | 3
(2 rows)
Удаление таблиц
Кстати, удалить таблицу, на которую ссылается другая таблица вы не сможете:
school=# DROP table pupils; ERROR: cannot drop table pupils because other objects depend on it DETAIL: constraint evaluations_full_name_class_fkey on table evaluations depends on table pupils HINT: Use DROP ... CASCADE to drop the dependent objects too.
Поэтому удалим наши таблицы в следующем порядке:
school=# DROP table evaluations; DROP TABLE school=# DROP table pupils; DROP TABLE
Либо мы могли удалить каскадно таблицу pupils вместе с внешним ключом у таблицы evaluations:
school=# CREATE TABLE pupils
(full_name text,
age integer,
class varchar(3),
PRIMARY KEY (full_name, class)
);
CREATE TABLE
school=# CREATE TABLE evaluations
(item text,
full_name text,
class varchar(3),
evaluation integer,
FOREIGN KEY (full_name, class) REFERENCES pupils ON DELETE CASCADE
);
school=# DROP TABLE pupils CASCADE;
NOTICE: drop cascades to constraint evaluations_full_name_class_fkey on table evaluations
DROP TABLE
school=# \d
List of relations
Schema | Name | Type | Owner
--------+-------------+-------+----------
public | evaluations | table | postgres
(1 row)
school=# \d evaluations
Table "public.evaluations"
Column | Type | Collation | Nullable | Default
------------+----------------------+-----------+----------+---------
item | text | | |
full_name | text | | |
class | character varying(3) | | |
evaluation | integer | | |
Как видно из примера, после каскадного удаления у нас вместе с таблицей pupils удался внешний ключ в таблице evaluations.
Создание связи в уже существующих таблицах
Выше я постоянно создавал первичный и внешний ключи при создании таблицы. Но их можно создавать и для существующих таблиц.
Вначале удалим оставшуюся таблицу:
school=# DROP table evaluations; DROP TABLE
И сделаем таблицы без ключей:
school=# CREATE TABLE pupils (full_name text, age integer, class varchar(3) ); CREATE TABLE school=# CREATE TABLE evaluations (item text, full_name text, class varchar(3), evaluation integer ); CREATE TABLE
Теперь создадим первичный ключ в таблице pupils:
school=# ALTER TABLE pupils ADD PRIMARY KEY (full_name, class); ALTER TABLE
И создадим внешний ключ в таблице evaluations:
school=# ALTER TABLE evaluations ADD FOREIGN KEY (full_name, class) REFERENCES pupils ON DELETE CASCADE; ALTER TABLE
Посмотрим что у нас получилось:
school=# \d pupils
Table "public.pupils"
Column | Type | Collation | Nullable | Default
-----------+----------------------+-----------+----------+---------
full_name | text | | not null |
age | integer | | |
class | character varying(3) | | not null |
Indexes:
"pupils_pkey" PRIMARY KEY, btree (full_name, class)
Referenced by:
TABLE "evaluations" CONSTRAINT "evaluations_full_name_class_fkey" FOREIGN KEY (full_name, class) REFERENCES pupils(full_name, class) ON DELETE CASCADE
school=# \d evaluations
Table "public.evaluations"
Column | Type | Collation | Nullable | Default
------------+----------------------+-----------+----------+---------
item | text | | |
full_name | text | | |
class | character varying(3) | | |
evaluation | integer | | |
Foreign-key constraints:
"evaluations_full_name_class_fkey" FOREIGN KEY (full_name, class) REFERENCES pupils(full_name, class) ON DELETE CASCADE
Итог
В этой статье я рассказал про первичный и внешний ключ sql. А также продемонстрировал, как можно создать связанные между собой таблицы и как создать связь между уже существующими таблицами. Вы узнали, какие ограничения накладывает первичный ключ и какие задачи он решает. И вдобавок, какие требования предъявляются к нему. Вместе с тем я показал вам как работать с составным первичным ключом.
Дополнительно про первичный и внешний ключ sql можете почитать тут.