Ключи в базе данных: практический обзор для начинающих системных аналитиков
Статья представлена для ознакомления и удобства пользователей, оригинал опубликован на Хабре, автор: @evAPPs
В этой статье разберем разные вопросы с доказательными примерами. И в этом материале акцент сделан только на следующие виды ограничений (в документации их больше, но рассмотрены только эти):
- ограничение уникальности;
- первичные ключи;
- внешние ключи.
Кому может быть полезна данная статья? Начинающим системным аналитикам и всем, у кого возникали подобные вопросы.
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, у меня возникнет ошибка:
В таблице ниже я собрала разные вопросы, с которыми столкнулась. И на основании тех практических задач, что я разбирала, указала ответы. И получилось следующее:
|
Вопрос |
Ответ |
|
1. Можно ли создать таблицу без первичного ключа? |
Да, но согласно теории баз данных каждая таблица должна иметь первичный ключ |
|
2. Можно ли использовать UNIQUE вместо PRIMARY KEY? В чем особенности? |
PostgreSQL позволяет это сделать (важно учитывать ограничение NOT NULL), но рекомендуют устанавливать первичный ключ |
|
3. Можно ли использовать первичный ключ и ограничение уникальности вместе (то есть для одного столбца установить два ограничения)? |
Можно, но это не имеет смыла. В этом случае UNIQUE будет избыточным и не будет учитываться |
|
4. Можно ли сделать связь с таблицей, в которой не установлено PRIMARY KEY и UNIQUE? |
Нет, это невозможно |
|
5. Можно ли сделать связь с полем в таблице, где установлен UNIQUE, но не установлен PRIMARY KEY? |
Да, можно |
|
6. Можно ли установить связь между таблицами без внешнего ключа? |
Нет, для установления связи необходимо использовать REFERENCES (на уровне БД) |
|
7. Можно ли в одной таблице использовать первичный ключ и ограничение уникальности? |
Да, но в разных столбцах. Если использовать для одного столбца UNIQUE и PRIMARY KEY, то UNIQUE будет считаться избыточным и не будет учитываться. |
|
8. Может ли первичный ключ состоять из нескольких столбцов? |
Да, может |
|
9. Может ли в одной таблице быть установлено несколько PRIMARY KEY? |
Нет |
|
10. Может ли ограничение уникальности включать несколько столбцов? |
Да |
|
11. Сколько ограничений уникальности может иметь таблица? |
Несколько |
|
12. Может ли быть разное число столбцов и типы данных полей у внешнего ключа и первичного (или внешнего ключа и UNIQUE)? |
Нет, число столбцов и тип данных должен быть одинаковый |
|
13. Может ли на одном поле быть уставлены и первичный и внешний ключи? |
Да |
Статья представлена для ознакомления и удобства пользователей, оригинал опубликован на Хабре, автор: @evAPPs
