Из статьи вы узнаете, что такое первичный и внешний ключ в SQL. Зачем они нужны и как их использовать. Я покажу на практике как их использовать в PostgreSQL.

Теория

Первичный ключ это одно или несколько полей в таблице. Он необходим для уникальной идентификации любой строки. Первичный ключ накладывает некоторые ограничения:

  • Все записи относящиеся к первичному ключу должны быть уникальны. Это означает, что если первичный ключ состоит из одного поля, то все записи в нём должны быть уникальными. А если первичный ключ состоит из нескольких полей, то комбинация этих записей должна быть уникальна, но в отдельных полях допускаются повторения.
  • Записи в полях относящихся к первичному ключу не могут быть пустыми. Это ограничение в PostgreSQL называется not null.
  • В каждой таблице может присутствовать только один первичный ключ.

К первичному ключу предъявляют следующее требование:

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

Первичный ключ может быть:

  • естественным – существует в реальном мире, например ФИО, или номер и серия паспорта;
  • суррогатным – не существует в реальном мире, например какой-то порядковый номер, который существует только в базе данных.

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

Связь между таблицами

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

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

Например, у вас есть таблица “Ученики” (pupils) и выглядит она следующим образом:

ФИО
full_name
Возраст
age
Класс
class
Иванов Иван Иванович15
Сумкин Фёдор Андреевич 15
Петров Алексей Николаевич14
Булгаков Александр Геннадьевич14
Таблица pupils

И есть таблица “Успеваемость” (evaluations):

Предмет
item
ФИО
full_name
Оценка
evaluation
Русский языкИванов Иван Иванович4
Русский языкПетров Алексей Николаевич5
МатематикаБулгаков Александр Геннадьевич3
ЛитератураСумкин Фёдор Андреевич5
Таблица evaluations

В обоих таблицах есть одинаковое поле: ФИО. При этом в таблице “Успеваемость” не может содержаться ФИО, которого нет в таблице “Ученики“. Ведь нельзя поставить ученику оценку, которого не существует.

Первичным ключом в нашем случае может выступать поле “ФИО” в таблице Ученики“. А внешним ключом будет “ФИО” в таблице “Успеваемость“. При этом, если мы удаляем запись о каком-то ученике из таблицы “Ученики“, то все его оценки тоже должны удалиться из таблицы “Успеваемость“.

Ещё стоит заметить что первичный ключ в 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, к которому относится первичный ключ не может быть пустым, это видно в колонки Nullablenot 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 можете почитать тут.

Добавить комментарий

Ваш адрес email не будет опубликован.