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

Создание первичных ключей

Вы можете определить первичный ключ в SQL Server с помощью SQL Server Management Studio или Transact-SQL. Создание первичного ключа автоматически приводит к созданию соответствующего уникального кластеризованного индекса (или некластеризованного при наличии такого указания).

Перед началом

Ограничения

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

Безопасность

Разрешения

Создание новой таблицы с первичным ключом требует разрешения CREATE TABLE в базе данных и разрешения ALTER на схему, в которой создается таблица.

Создание первичного ключа в существующей таблице требует разрешения ALTER на таблицу.

Использование SQL Server Management Studio

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

В обозреватель объектов щелкните правой кнопкой мыши таблицу, к которой нужно добавить уникальное ограничение, и выберите Пункт Конструктор.
В Designer таблицы выберите селектор строк для столбца базы данных, который необходимо определить в качестве первичного ключа. Если вы хотите выбрать несколько столбцов, удерживайте нажатой клавишу CTRL, выбирая селекторы строк для других столбцов.
Щелкните правой кнопкой мыши средство выбора строк столбца и выберите команду Задать первичный ключ.

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

Ключевой столбец-источник идентифицируется символом первичного ключа в соответствующем селекторе строк.

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

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

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

Создание первичного ключа в существующей таблице

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

ALTER TABLE Production.TransactionHistoryArchive ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID);

Создание первичного ключа в новой таблице

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

CREATE TABLE Production.TransactionHistoryArchive1 ( TransactionID int IDENTITY (1,1) NOT NULL , CONSTRAINT PK_TransactionHistoryArchive1_TransactionID PRIMARY KEY CLUSTERED (TransactionID) ) ;

Создание первичного ключа с кластеризованным индексом в новой таблице

В следующем примере создается таблица и определяется первичный ключ для столбца CustomerID и кластеризованного индекса для TransactionID в базе данных AdventureWorks.

-- Create table to add the clustered index CREATE TABLE Production.TransactionHistoryArchive1 ( CustomerID uniqueidentifier DEFAULT NEWSEQUENTIALID() , TransactionID int IDENTITY (1,1) NOT NULL , CONSTRAINT PK_TransactionHistoryArchive1_CustomerID PRIMARY KEY NONCLUSTERED (CustomerID) ) ; -- Now add the clustered index CREATE CLUSTERED INDEX CIX_TransactionID ON Production.TransactionHistoryArchive1 (TransactionID);

Дальнейшие действия

ALTER TABLE
CREATE TABLE
table_constraint

Обратная связь

Были ли сведения на этой странице полезными?

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

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

Отслеживать
user177221
задан 23 апр 2017 в 9:52
73 1 1 золотой знак 1 1 серебряный знак 9 9 бронзовых знаков

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

23 апр 2017 в 11:44
Так изначально было указано в задании.
23 апр 2017 в 11:54

1 ответ 1

Сортировка: Сброс на вариант по умолчанию

Я бы в каждой таблице все-таки держал уникальный айдишник, не люблю составные ключи.

В данном же случае напрашивается составной ключ из полей NDM и KTOV. Пример скрипта:

CREATE TABLE [DOCUMENT2]( KTOV int not null, KOL int not null, CENA decimal(18,3) not null, SORT vnarchar(50) not null, -- вот это поле я бы исключил. денормализация. его всегда можно получить из таблицы TOVAR NDM int not null, CONSTRAINT [DOCUMENT2_PK] PRIMARY KEY CLUSTERED (KTOV ASC, NDM ASC))

UPD: второе поле в ключе должно быть NDM, как ссылка на таблицу DOCUMENT1. ASC — порядок сортировки индекса, во-возростанию. Можно указать по-убыванию одно или оба поля — DESC.

Sysadminium

Из статьи вы узнаете, что такое первичный и внешний ключ в 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:

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 можете почитать тут.

Как создать первичные и внешние ключи MySQL

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

Эта инструкция — часть курса «MySQL для новичков».

Смотреть весь курс

Введение

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

Облачные базы данных

Что такое первичный и внешний ключи и зачем они нужны

Начнем рассмотрение данного вопроса с двух самых главных элементов: первичного и внешнего ключей.

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

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

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

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

Внешний ключ нужен для того, чтобы связать две разные SQL-таблицы между собой. Внешний ключ таблицы должен соответствует значению первичного ключа таблицы, с которой он связан. Это помогает сохранять согласованность базы данных путем обеспечения так называемой «ссылочной целостности» (referential integrity).

Давайте рассмотрим все это на примере простой базы данных. Для начала нам понадобится создать базу данных. Заходим в MySQL и создаем базу данных, в которой будут храниться записи о книгах в библиотеке:

create database slcbookshelf;

пример БД

Так как дальше мы будем работать с этой базой, вводим команду:

use slcbookshelf;

И создаем таблицу, в которой будут храниться записи о книгах в библиотеке:

CREATE TABLE books ( book_id INT NOT NULL, book_name VARCHAR(255) NOT NULL, book_category VARCHAR(255));

Создание первичного ключа при создании таблицы и с помощью ALTER TABLE

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

DESC books;

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

mysql> DESC books; +---------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+-----+---------+-------+ | book_id | int | NO | | NULL | | | book_name | varchar(255) | NO | | NULL | | | book_category | varchar(255) | YES | | NULL | | +---------------+--------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)

Первичный ключ при создании таблицы

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

PRIMARY KEY (book_id)

В таком случае поле book_id после создания таблицы будет являться первичным ключом для таблицы books.

Создание первичного ключа при помощи ALTER TABLE

Если таблица уже создана, а первичный ключ в ней не указан, вы можете создать ключевое поле, с помощью команды ALTER TABLE. Команда ALTER TABLE помогает изменять уже существующие столбцы, удалять их или добавлять новые. Чтобы определить первичный ключ в поле book_id, выполните команду:

ALTER TABLE books ADD PRIMARY KEY (book_id); Проверяем: mysql> DESC books; +---------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+-----+---------+-------+ | book_id | int | NO | PRI | NULL | | | book_name | varchar(255) | NO | | NULL | | | book_category | varchar(255) | YES | | NULL | | +---------------+--------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)

Установка внешнего ключа MySQL при создании таблицы и с помощью ALTER TABLE

Предположим, у нас есть еще одна таблица под названием authors, которую нам необходимо связать с текущей таблицей books с помощью внешнего ключа author_id.

Внешний ключ при создании таблицы

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

FOREIGN KEY (author_id) REFERENCES authors(author_id)

В итоге запрос, которым создается такая таблица будет выглядеть следующим образом:

CREATE TABLE books ( book_id INT NOT NULL, book_name VARCHAR(255) NOT NULL, book_category VARCHAR(255), FOREIGN KEY (author_id) REFERENCES authors(author_id));

Создание внешнего ключа при помощи ALTER TABLE

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

ALTER TABLE books ADD FOREIGN KEY (author_id) REFERENCES authors(author_id);

Сценарии использования внешнего ключа

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

Каскадное удаление или CASCADE

Каскадное удаление позволит вам одновременно удалить строки из главной таблицы, а вместе с ними удалить все связанные строки в других таблицах. Задается каскадное удаление таким запросом:

CREATE TABLE Orders( Id INT PRIMARY KEY AUTO_INCREMENT, CustomerId INT, CreatedAt Date, FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE CASCADE);

Аналогично работает метод ON UPDATE CASCADE. При попытке изменить значение, записанное в поле первичного ключа, изменение будет применено к внешнему ключу, связанному с данным полем. Этот метод используется крайне редко, так как первичные ключи практически не являются изменяемыми полями.

RESTRICT

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

CREATE TABLE Orders( Id INT PRIMARY KEY AUTO_INCREMENT, Customer_Id INT, CreatedAt Date, FOREIGN KEY (Customer_Id) REFERENCES Customers (Id) ON DELETE RESTRICT);

Заключение

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

Как установить и использовать MySQL Workbench