Бизнес ключ в базе данных это

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

Оглавление скрыть

Теория

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

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

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

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

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

Типы ключей в базе данных

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

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

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

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

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

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

Таблица pupils

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

Таблица 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, к которому относится первичный ключ не может быть пустым, это видно в колонки Nullable – not 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 можете почитать тут.

Источник: sysadminium.ru

Ключи SQL — Изучите 7 различных типов ключей в SQL (пример)

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

В RDBMS ключи играют важную роль. Это участвует в нескольких действиях в реляционной базе данных. Использование ключа может сделать поиск данных намного быстрее и эффективнее. Это может установить отношения в двух или более таблицах. Использование ключей позволяет нам хранить действительные и согласованные данные в базе данных.

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

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

Синтаксис:

CREATE TABLE `customer` (
`cust_id` int(11) NOT NULL,
`cust_name` varchar(100) NOT NULL,
`cust_address` text NOT NULL,
`cust_aadhaar_number` varchar(50) DEFAULT NULL,
`cust_pan_number` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE `customer` ADD PRIMARY KEY (`cust_id`);

В приведенном выше SQL-запросе мы видим, как столбец cust_id установлен в качестве первичного ключа.

Тип SQL-ключей

SQL Server поддерживает несколько типов ключей.

Ниже приведен список ключей SQL:

• Первичный ключ
• Уникальный ключ
• Ключ-кандидат
• Альтернативный ключ
• Композитный ключ
• Супер Ключ
• Иностранный ключ

Например

Ниже приведена таблица «Заказ», содержащая связанные данные, соответствующие «cust_id» из Таблицы клиентов.

Теперь мы пройдемся по одному на каждом из ключей:

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

Первичный ключ — это поле, которое можно использовать для уникальной идентификации всех кортежей в базе данных. Только один из столбцов может быть объявлен как первичный ключ. Первичный ключ не может иметь значение NULL.

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

2. Уникальный ключ

Уникальный ключ может быть полем или набором полей, которые могут быть использованы для уникальной идентификации кортежа из базы данных. Одно или несколько полей могут быть объявлены как уникальные ключи. Уникальный столбец Key также может содержать значение NULL. Использование уникального ключа повышает производительность поиска данных. Это делает поиск записей из базы данных намного быстрее и эффективнее.

Пример: в приведенной выше реляционной таблице «cust_aadhaar_number», «cust_pan_number» являются уникальным ключом, так как он может разрешить одно значение как NULL в столбце

3. Ключ-кандидат

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

Пример: в приведенной выше реляционной таблице «cust_id», «cust_aadhaar_number», «cust_pan_number» являются ключом-кандидатом, поскольку он может однозначно идентифицировать все строки из таблицы. Эти столбцы также квалифицируют критерии как первичный ключ.

4. Альтернативный ключ

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

Пример: в приведенной выше реляционной таблице «cust_aadhaar_number», «cust_pan_number» являются альтернативным ключом, поскольку оба столбца могут быть первичным ключом, но еще не выбраны для первичного ключа.

5. Композитный ключ

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

Пример: в приведенной выше реляционной таблице, т. Е. Таблице заказов, «cust_id», группе «order_month_year» этих столбцов, используемых в комбинации для уникальной идентификации кортежа в таблице заказов. Отдельный столбец этой таблицы не может однозначно идентифицировать кортеж из таблицы Order.

6. Супер Ключ

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

Пример: в приведенной выше реляционной таблице первичный ключ, ключ-кандидат и уникальный ключ — это супер-ключ. В качестве одного столбца таблицы клиента, т. Е. Cust_id, достаточно для уникальной идентификации кортежей из таблицы. Любой набор столбца, который содержит ‘cust_aadhaar_number’, ‘cust_pan_number’, является Супер Ключом.

7. Внешний ключ

Внешний ключ — это столбец, который известен как Первичный ключ в другой таблице, т. Е. Первичный ключ в таблице может называться Внешним ключом в другой таблице. Внешний ключ может иметь повторяющиеся значения https://ru.education-wiki.com/9733715-sql-keys» target=»_blank»]ru.education-wiki.com[/mask_link]

Ключевые атрибуты

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

Первичный (главный) ключ должен обладать следующими свойствами:

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

При выборе первичного ключа следует отдавать предпочтение несоставным ключам или ключам, составленным из минимального числа атрибутов. Нецелесообразно также использовать ключи с длинными текстовыми значениями (предпочтительнее использовать целочисленные атрибуты). Так, для идентификации студента можно использовать либо уникальный номер зачетной книжки, либо набор из фамилии, имени, отчества, номера группы и может быть дополнительных атрибутов, так как не исключено появление в группе двух студентов (а чаще студенток) с одинаковыми фамилиями, именами и отчествами. Плохо также использовать в качестве ключа не номер блюда, а его название, например, » Закуска из плавленых сырков «Дружба» с ветчиной и соленым огурцом» или «Заяц в сметане с картофельными крокетами и салатом из красной капусты».

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

1. Нормальные формы отношений

Источник: studfile.net