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

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

Вопросы были связаны с разными видами ключей в базе данных и с тем, как они связаны между собой (тему с реляционными БД мы разбираем на примере PostgreSQL). Я начала искать разные статьи по этой теме, очень много крутого материала на том же «Хабре», но многие вопросы так и остались не раскрытыми. И мне стало интересно разобраться с этими вопросами и «пощупать» все это на практике. В итоге начала изучать документацию PostgreSQL и теорию реляционных баз данных, но чтобы получить ответы, пришлось все проверять на практических примерах.

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

• ограничение уникальности;

• первичные ключи;

• внешние ключи.

Кому может быть полезна данная статья? Начинающим системным аналитикам и всем, у кого возникали подобные вопросы.

Итак, погнали.

1. Можно ли создать таблицу без первичного ключа?

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

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

То есть я предварительно создала таблицу User, в которой не установлен первичный ключ:

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, у меня возникнет ошибка:

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

© Habrahabr.ru