Как сделать уникальный ключ

Обновлено: 08.07.2024

Ограничение уникального ключа (Unique key constraint, или просто Уникальный ключ, Unique key) в реляционной модели данных, в частности в реляционных базах данных — одно из ограничений, которое можно использовать для ограничения значений указанного столбца(ов) по уникальности. При уникальном ключе все значения столбца должны быть строго уникальными, то есть не повторяющимися во всей таблице.

Содержание

Предназначение

Для чего служит ограничение уникального ключа

Ограничение уникального ключа (UNIQUE CONSTRAINT) нужно для того, чтобы убедиться в том, что в отдельные столбцы (не включенные в первичный ключ) не вводятся повторяющиеся значения, то есть столбец, помеченный как UNIQUE, является уникальным.

Первичный и уникальный ключ

UNIQUE ограничение для столбца позволяет однозначно идентифицировать каждую запись в таблице так же как и PRIMARY KEY.

Ограничения UNIQUE и PRIMARY KEY в равной степени гарантируют уникальность значений столбца таблицы. Различия лишь в том, что PRIMARY KEY может быть только один столбец в таблице, в то время как ограничением UNIQUE могут быть помечены множество столбцов одной таблицы. PRIMARY KEY по умолчанию является UNIQUE.

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

Основные постулаты уникального ограничения

Для одной таблицы можно задать много ограничений UNIQUE, и по-прежнему только одно ограничение PRIMARY KEY.
PRIMARY KEY ограничения автоматически имеет особенность UNIQUE.
В отличие от PRIMARY KEY, односоставные ограничения UNIQUE допускают значение NULL. Однако, как и всякое другое значение столбца с ограничением UNIQUE, значение NULL может встречаться только один раз.
На ограничение UNIQUE могут ссылаться ограничения FOREIGN KEY.
UNIQUE и PRIMARY KEY ограничители обеспечивает гарантию единственности столбца или набора столбцов.
При задании (написании) составных ограничений типа UNIQUE порядок перечисления полей роли не играет.
Составной (на несколько полей одновременно) UNIQUE не работает для записей (строк), содержащих NULL.

Пустота и NULL

UNIQUE KEY из нескольких полей (составной) просто не срабатывает при наличии NULL в одном из полей, входящих в составной UNIQUE, но UNIQUE по-прежнему работает, когда в поле пустота. I.e. вместо Null в поля можно вставлять просто пустоту, если тип данных поля это допускает.

Т.о. для полей, которые будут участвовать в составном UNIQUE KEY надо запрещать использование значения NULL (NOT NULL) и выставить значение по-умолчанию как пустоту, либо не выставлять значения по-умолчанию, но всегда вставлять эту пустоту для каждой новой строки так как в противном случае будет ошибка, говорящая о том, что какие-то поля не содержат значения по-умолчанию.

Использование

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

Примеры

Примеры создания и добавления уникального ключа и синтаксис

Безымянный ключ это лишь задание ключа без своего/пользовательского/кастомного названия. В этом случае сама СУБД даст ключу своё системное/служебное название.

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

Теория

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

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

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

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

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

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

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

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

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

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

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

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

Таблица pupils

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

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

Таблица evaluations

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

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

Ещё стоит заметить что первичный ключ в PostgreSQL автоматически создает индекс. Индекс ускоряет доступ к строкам таблицы и накладывает ограничение на уникальность. То есть двух Ивановых Иванов Ивановичей у нас не может существовать. Чтобы это обойти можно использовать:

составной первичный ключ – например, в качестве первичного ключа взять два поля: ФИО и Класс;
суррогатный первичный ключ – в таблице “Ученики” добавить поле “№ Ученика” и сделать это поле первичным ключом;
добавить более уникальное поле – например, можно использовать уникальный номер зачетной книжки и использовать новое поле в качестве первичного ключа;

Теперь давайте попробуем создать эти две таблички и попробуем с ними поработать.

Практика

Создадим базу данных school и подключимся к ней. Затем создадим таблицу pupils. Про создание таблиц я уже писал тут, а про типы данных тут. Затем посмотрим на табличку с помощью команды \d:

Как вы могли заметить, первичный ключ создаётся с помощью конструкции PRIMARY KEY (имя_поля) в момент создания таблицы.

Вывод команды \d нам показал, что у нас в таблице есть первичный ключ. А также первичный ключ сделал два ограничения:

поле full_name, к которому относится первичный ключ не может быть пустым, это видно в колонки Nullable – not null;
для поля full_name был создан индекс pupils_pkey с типом btree. Про типы индексов и про сами индексы расскажу в другой статье.

Индекс в свою очередь наложил ещё одно ограничение – записи в поле full_name должны быть уникальны.

Следующим шагом создадим таблицу evaluations:

В этом случае из вывода команды \d вы увидите, что создался внешний ключ (Foreign-key), который относится к полю full_name и ссылается на таблицу pupils.

Внешний ключ создается с помощью конструкции FOREIGN KEY (имя_поля) REFERENCES таблица_на_которую_ссылаются.

Создавая внешний ключ мы дополнительно указали опцию ON DELETE CASCADE. Это означает, что при удалении строки с определённым учеником в таблице pupils, все строки связанные с этим учеником удалятся и в таблице evaluations автоматически.

Заполнение таблиц и работа с ними

Заполним таблицу “pupils“:

Заполним таблицу “evaluations“:

А теперь попробуем поставить оценку не существующему ученику:

Как видите, мы получили ошибку. Вставлять (insert) или изменять (update) в таблице evaluations, в поле full_name можно только те значения, которые есть в этом же поле в таблице pupils.

Теперь удалим какого-нибудь ученика из таблицы pupils:

И посмотрим на строки в таблице evaluations:

Как видно, строка с full_name равная ‘Иванов Иван Иванович’ тоже удалилась. Если бы у Иванова было бы больше оценок, они всё равно бы все удалились. За это, если помните отвечает опция ON DELETE CASCADE.

Попробуем теперь создать ученика с точно таким-же ФИО, как у одного из существующих:

Ничего не вышло, так как такая запись уже существует в поле full_name, а это поле у нас имеет индекс. Значит значения в нём должны быть уникальные.

Составной первичный ключ

Есть большая вероятность, что в одной школе будут учиться два ученика с одинаковым ФИО. Но меньше вероятности что эти два ученика будут учиться в одном классе. Поэтому в качестве первичного ключа мы можем взять два поля, например full_name и class.

Давайте удалим наши таблички и создадим их заново, но теперь создадим их используя составной первичный ключ:

Как вы могли заметить, разница не большая. Мы должны в PRIMARY KEY указать два поля вместо одного. И в FOREIGN KEY точно также указать два поля вместо одного. Ну и не забудьте в таблице evaluations при создании добавить поле class, так как его там в предыдущем варианте не было.

Теперь посмотрим на структуры этих таблиц:

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

Теперь мы можем учеников с одинаковым ФИО вбить в нашу базу данных, но при условии что они будут учиться в разных классах:

И также по второй таблице:

Удаление таблиц

Кстати, удалить таблицу, на которую ссылается другая таблица вы не сможете:

Поэтому удалим наши таблицы в следующем порядке:

Либо мы могли удалить каскадно таблицу pupils вместе с внешним ключом у таблицы evaluations:

Как видно из примера, после каскадного удаления у нас вместе с таблицей pupils удался внешний ключ в таблице evaluations.

Создание связи в уже существующих таблицах

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

Вначале удалим оставшуюся таблицу:

И сделаем таблицы без ключей:

Теперь создадим первичный ключ в таблице pupils:

И создадим внешний ключ в таблице evaluations:

Посмотрим что у нас получилось:

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

SQL PRIMARY KEY - это столбец в таблице, который должен содержать уникальное значение, которое можно использовать для уникальной идентификации каждой строки таблицы.

Тем не менее, SQL поддерживает первичные ключи напрямую с ограничением PRIMARY KEY.

Функционально это то же самое, что и ограничение UNIQUE, за исключением того, что для данной таблицы можно определить только один PRIMARY KEY. PRIMARY KEY не допускает значения NULL.

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

SQL PRIMARY KEY может состоять из одного или нескольких полей в таблице, и когда это происходит, они называются составным ключом.

Первичные ключи могут быть указаны во время CREATING TABLE или во время изменения структуры существующей таблицы с помощью инструкции ALTER TABLE.

Это ограничение является комбинацией ограничения NOT NULL и ограничения UNIQUE. Это ограничение гарантирует, что конкретный столбец или комбинация из двух или более столбцов для таблицы имеют уникальную идентификацию, которая помогает легче и быстрее найти конкретную запись в таблице.

Синтаксис:

Параметры:

название	Описание
table_name	Имя таблицы, в которой хранятся данные.
column1, column2	Наименование столбцов таблицы.
тип данных	Это char, varchar, целое число, десятичное число, дата и многое другое.
размер	Максимальная длина столбца таблицы.

Хорошая практика для первичных ключей в таблицах

Пример:

Предположим, мы собираемся создать таблицу с именем 'agent1'. Он содержит столбцы и типы данных, которые показаны ниже. Для каждой строки таблицы 'agent1' требуется идентифицировать каждого агента с помощью уникального кода, поскольку имена двух или более агентов в городе страны могут совпадать.

Так что не стоит создавать PRIMARY KEY для 'agent_name'. 'Agent_code' может быть единственным и исключительным выбором для PRIMARY KEY для этой таблицы.

Имя поля	Тип данных	Размер	Десятичные знаки	НОЛЬ
agent_code	голец	6	нет	ОСНОВНОЙ КЛЮЧ
имя агента	голец	40	нет
рабочая область	голец	35	да
комиссия	десятичный	10	2	да
номер телефона	голец	17	да

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

Ограничение первичного ключа на уровне столбца:

Код SQL:

Ограничение первичного ключа на уровне таблицы:

Код SQL:

SQL CREATE TABLE с основным ограничением

SQL PRIMARY KEY CONSTRAINT является комбинацией ограничения NOT NULL и ограничения UNIQUE. Это ограничение гарантирует, что конкретный столбец или комбинация из двух или более столбцов для таблицы имеют уникальную идентификацию, которая помогает легче и быстрее найти конкретную запись в таблице.

Пример :

В следующем примере создается таблица. Вот имя поля и типы данных:

Имя поля	Тип данных	Размер	Десятичные знаки	НОЛЬ
agent_code	голец	6	нет	ОСНОВНОЙ КЛЮЧ
имя агента	голец	40	нет
рабочая область	голец	35	да
комиссия	десятичный	10	2	да
номер телефона	голец	17	да

можно использовать следующий оператор SQL:

Код SQL:

Чтобы увидеть структуру созданной таблицы:

Код SQL:

SQL CREATE TABLE с первичным ключом и уникальным ограничением

В следующем примере создается таблица. Вот имя поля и типы данных:

Имя поля	Тип данных	Размер	Десятичные знаки	НОЛЬ
cust_code	голец	6	нет	ОСНОВНОЙ КЛЮЧ
CUST_NAME	голец	25	нет	УНИКАЛЬНАЯ
cust_city	голец	25	нет
класс	целое число	да
agent_code	голец	6	нет

можно использовать следующий оператор SQL:

Код SQL:

Чтобы увидеть структуру созданной таблицы:

Код SQL:

SQL CREATE TABLE с PRIMARY KEY CONSTRAINT для большего количества столбцов

В следующем разделе мы обсудим использование SQL PRIMARY KEY CONSTRAINT вместе с оператором CREATE TABLE для двух или более столбцов.

Пример:

В следующем примере создается таблица. Таблица должна содержать ПЕРВИЧНЫЙ КЛЮЧ с комбинацией двух столбцов 'cust_code' и 'cust_city'. Вот имя поля и типы данных:

Имя поля	Тип данных	Размер	Десятичные знаки	НОЛЬ
cust_code	голец	6	нет	ОСНОВНОЙ КЛЮЧ
CUST_NAME	голец	25	нет
cust_city	голец	25	нет	ОСНОВНОЙ КЛЮЧ
класс	целое число	да
agent_code	голец	6	нет

можно использовать следующий оператор SQL:

Код SQL:

Чтобы увидеть структуру созданной таблицы:

Код SQL:

Упражнения по SQL

Упражнения по SQL, практика, решение
SQL Получить данные из таблиц [33 Упражнения]
Булевы и реляционные операторы SQL [12 упражнений]
Подстановочные знаки SQL и специальные операторы [22 упражнения]
Агрегатные функции SQL [25 упражнений]
Вывод запроса форматирования SQL [10 упражнений]
SQL-запросы к нескольким таблицам [7 упражнений]
ФИЛЬТРАЦИЯ И СОРТИРОВКА в базе данных персонала [38 упражнений]
SQL СОЕДИНЯЕТ
- SQL СОЕДИНЯЕТСЯ [29 упражнений]
- SQL присоединяется к базе данных HR [27 упражнений]
- ПОДПИСИ SQL [39 упражнений]
- SQL ПОДПИСИ по базе данных HR [55 упражнений]
- ОСНОВНЫЕ запросы к базе данных фильмов [10 упражнений]
- ПОДПИСКИ на фильм База данных [16 упражнений]
- ПРИСОЕДИНЯЕТСЯ к базе данных фильма [24 упражнения]
- Вступление
- ОСНОВНЫЕ запросы по футболу базы данных [29 упражнений]
- ПОДПИСКИ по футбольной базе данных [33 упражнения]
- ПРИСОЕДИНЯЕТСЯ к запросам по футбольной базе данных [61 упражнений]
- Вступление
- ОСНОВНЫЕ, ПОДПИСИ И СОЕДИНЕНИЯ [39 упражнений]
- ОСНОВНЫЕ запросы к базе данных сотрудников [115 упражнений]
- БРОНИРОВАНИЕ на сотрудника База данных [77 Упражнения]
Хотите улучшить вышеуказанную статью? Вносите свои заметки / комментарии / примеры через Disqus.

Предыдущая: Создать таблицу
Далее: Внешний ключ

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

План на сегодня такой:

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

Если говорить простыми словами, то ключи в sql созданы для того, чтобы указать дополнительную функциональность столбца. Будь то уникальность или то, что столбец ссылается на другую таблицу (внешний ключ).

Первичный ключ

Столбец, который в базе данных должен быть уникальным помечают первичным ключом. Первичный ключ или primary key означает, что в таблице значение колонки primary key не может повторяться. Таким образом данный ключ позволяет однозначно идентифицировать запись в таблице не боясь при этом, что значение столбца повториться. Сразу пример: допустим у Вас есть таблица пользователей. В данной таблице есть поля: ФИО, год рождения, телефон. Как идентифицировать пользователя? Таким параметрам как ФИО и телефон доверять нельзя. Ведь у нас может быть несколько пользователей не только с одинаковой фамилией, но и с именем. Телефон может меняться со временем и пользователь с номером телефона может оказаться не тем кто у нас в базе данных.

Вот для этого и придумали первичный ключ. Один раз присвоили уникальный идентификатор и все. В mySql на примере которой мы выполняем все примеры из цикла статей по SQL поле AUTO_INCREMENT нельзя задать если не указать, что это первичный ключ.

К стати, в предыдущей теме мы уже познакомились с первичным ключом: что это такое и как его создавать. Синтаксис по созданию этого ключа Вы найдете в статье Создание, изменение, удаление таблиц SQL.

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

Внешний ключ (foreign key)

Есть еще внешний ключ (foreign key). Его еще называют ссылочным. Он нужен для связывания таблиц между собой.

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

Колонка supplier_id будет уникальна для каждой записи. Ее значение и будет выступать на мести колонки поставщик в таблице обувь. Предлагаю сразу и рассмотреть на примере как создается внешний ключ.

Создание внешнего ключа

Как видно на примере выше, синтаксис по созданию внешнего ключа довольно прост. Нужно в таблицу добавить поле, а после объявить это поле как внешний ключ и указать, куда он будет ссылаться. В данном случае поле supplier будет ссылаться на поле supplier_id в таблице supplier.

Составной ключ (composite key)

Что касается составного ключа — это несколько первичных ключей в таблице. Таким образом, создав composite key, уникальность записи будет проверяться по полям, которые объединенные в этот ключ.

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

В примере выше два поля объединенные в составной ключ и в таблице не будет записей с этими одинаковыми полями.

Это все, что касается ключей в SQL. Это небольшое пособие — подготовка к статье связи sql где мы подробно рассмотрим как объединять таблицы, чтобы они составляли единую базу данных.

Читайте также: