Ключи в базе данных: практический обзор для начинающих системных аналитиков

Всем привет! Меня зовут Оксана, я системный аналитик из компании EvApps. Что побудило меня написать эту статью? Я обучаю стажеров – системных аналитиков, и недавно столкнулась с такими вопросами, о которых раньше даже не задумывалась.

Вопросы были связаны с разными видами ключей в базе данных и с тем, как они связаны между собой (тему с реляционными CREATE TABLE public.user ( id int, "name" varchar, age int );

Далее хочу создать таблицу Order и связать ее с таблицей User. И при выполнении запроса получаю ошибку.

2. Можно ли использовать ограничение уникальности вместо первичного? В чём особенности?

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

Вариант с использованием UNIQUE:

CREATE TABLE public.user1 (  id int UNIQUE,  "name" varchar,  age int );

Вариант с использованием PRIMARY KEY:

CREATE TABLE public.user2 (  id int PRIMARY KEY,  "name" varchar,  age int );

Теперь попробуем создать запись в таблице user1, но для id установим значение null:

INSERT INTO public.user1 (id, "name", age) VALUES(null, 'Иван', 10); select * from public.user1

Как видим, запись в таблицу добавилась успешно.

Теперь попробуем то же самое сделать в таблице user2:

INSERT INTO public.user2 (id, "name", age) VALUES(null, 'Иван', 10); 

И видим, что при создании записи возникает ошибка.

Какие выводы можно сделать:

• PRIMARY KEY – первичный ключ, позволяет сделать запись уникальной и включает в себя ограничение NOT NULL.

• UNIQUE – ограничение уникальности, обеспечивает уникальность значений в столбце/столбцах, при этом не имеет проверку на NULL. Чтобы этого избежать, нужно использовать UNIQUE NOT NULL.

По факту, вместо PRIMARY KEY можно использовать UNIQUE NOT NULL и Postgres позволяет это сделать, но рекомендуют следовать теории реляционных баз данных и устанавливать первичные ключи.

3. Можно ли использовать первичный ключ и ограничение уникальности вместе (т. е. для одного столбца установить два ограничения)?

Да, можно, но это бессмысленно. В данном случае учитывается ограничение первичного ключа, а UNIQUE считается избыточным и отбрасывается.

Например, я создаю таблицу:

CREATE TABLE public.user3 (  id int PRIMARY KEY UNIQUE,  "name" varchar,  age int );

Но в DDL можем увидеть, что в ограничениях остался только первичный ключ.

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

4. Можно ли сделать связь с ограничением уникальности?

Да, можно.

Пример:

Создаем таблицу, где установлено ограничение уникальности:

CREATE TABLE public.user1 (  id int UNIQUE,  "name" varchar,  age int );

Далее создаем таблицу с ссылкой на таблицу user1:

CREATE TABLE public.order (  id int,  order_number varchar,  user_id int references public.user1(id),  sum float );

Таблица создана успешно, связь настроена.

И если мы попытаемся в таблицу Order добавить запись с пользователем, которого нет в таблице user1, то получим ошибку.

Можно ли установить связь между таблицами без внешнего ключа?

Нет, это невозможно. Для установления связи между таблицами обязательно использование REFERENCES.

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

5. Можно ли в одной таблице (разных полях) использовать первичный ключ и ограничение уникальности? Может ли ограничение уникальности включать несколько столбцов?

Ответ на оба вопроса – да.

Давайте для примера рассмотрим два случая:

CREATE TABLE public.user4 (  id int PRIMARY KEY,  "name" varchar,  series_passport varchar UNIQUE NOT NULL,  number_passport varchar UNIQUE NOT NULL,  age int );

и

CREATE TABLE public.user5 (  id int PRIMARY KEY,  "name" varchar,  series_passport varchar,  number_passport varchar,  age int,  UNIQUE (series_passport, number_passport)  );

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

Например, если у меня есть такая запись в таблице:

То я не смогу добавить строку со значением series_passport=7777 или number_passport=123454. Будет выдаваться ошибка. То есть проверка уникальности значений происходит по КАЖДОМУ столбцу, где установлен UNIQUE.

Теперь рассмотрим второй случай.

Я могу создавать строки с одинаковыми значениями series_passport или number_passport. Но пара этих значений должна быть уникальной. То есть во втором случае происходит проверка СОЧЕТАНИЯ значений всех столбцов.

6. Сколько первичных ключей может иметь таблица?

Один и только один. Иначе получаем такую ошибку:

7. Сколько ограничений уникальности может иметь таблица?

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

8. Может ли быть разное число столбцов и типы данных полей у внешнего ключа и первичного (или внешнего ключа и UNIQUE)?

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

Рассмотрим связь, которую установили в примерах выше.

У полей user_id и id, одинковый тип данных – int. Если бы тип отличался, или мы бы пытались связать поле user_id с двуми полями, а не одним (id), то установить связь не получилось бы.

9. Могут ли на одном столбце быть установлены и первичный и внешний ключи?

Да, могут. Рассмотрим такую задачу. У нас есть три таблицы: продукты, пользователи и отзывы. И есть условие: пользователь на один товар может оставить только один отзыв.

Я выбрала вот такую реализацию.

Создаем таблицы пользователи и продукты:

CREATE TABLE public.user_new (  id int PRIMARY KEY,  "name" varchar,  age int ); CREATE TABLE public.product (  id int PRIMARY KEY,  "name" varchar,  price float );

Далее при создании таблицы Отзывы я установила первичный ключ на два столбца user_id,product_id (то есть сочетание их значений мне нужно проверять):

CREATE TABLE public.review (  user_id int REFERENCES public.user_new(id),   product_id int REFERENCES public.product(id) ,  "text" varchar,  grade int,  PRIMARY KEY (user_id,product_id) );

И с этих же полей я установила связь с внешними таблицами user_new и product.

И если я захочу добавить запись, где повторяется сочетание user_id + product_id, у меня возникнет ошибка:

---

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