Как создать внешний ключ sql

Row Count Spool используется для подсчета количества строк, возвращаемых оператором Clustered Index Seek, и передачи этой информации в оператор Nested Loops. Этот оператор используется оптимизатором запросов SQL Server для проверки существования строк, но не содержащихся в них данных.

Заключение

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

Материал подготовлен в рамках курса «MS SQL Server Developer». Всех желающих приглашаем на открытый урок «SQL Server и Docker». На открытом уроке мы поговорим о контейнерах, а также рассмотрим развертывание SQL Server в контейнерах.

Create Foreign Key

Внешний ключ Foreign Key служит для связи родительской и дочерних таблиц в базе данных. Т.е. обеспечивает соответствие значений полей одной записи родительской таблицы множеству значений полей записей дочерних таблиц. На этом условии могут быть определены отношения сущностей в базе данных «один-к-одному» и «один-ко-многим». Внешний ключ строится по столбцам дочерней таблицы, значения которых ссылаются на значения записей в родительской таблице.

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

Синтаксис описания внешнего ключа FOREIGN KEY

FOREIGN KEY REFERENCES [ ] [ON DELETE ] [ON UPDATE ];

- columns_table
список столбцов таблицы, входящие во внешний ключ; символ разделения столбцов - запятая.

- pktable
таблица, содержащая родительский ключ.

- columns_ptable
список столбцов родительского ключа.

Списки столбцов внешнего и родительского ключей должны быть совместимы, т.е. :

• иметь одинаковое число столбцов;
• типы столбцов внешнего ключа должны соответствовать типам столбцов родительского ключа.

- rule
правило, которое может определять значение полей внешнего ключа при выполнении DML транзакции в родительской таблице : [ CASCADE | RESTRICT | SET NULL | NO ACTION | SET DEFAULT ]. СУБД контролирует значения полей связанных ("дочерних") таблиц во время обновления или удаления данных в родительской таблице.

Правило CASCADE

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

Правило RESTRICT

Правило SQL RESTRICT следует использовать, если необходимо не допустить удаление записи родительской таблицы при наличии связанных записей в дочерних таблицах.

Правило SET NULL

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

Правило NO ACTION

Правило NO ACTION определяет запрет изменения/удаления записи в родительской таблице при наличии связанных записей в дочерней таблице. Если правило "ON UPDATE" или "ON DELETE" не задано явно при объявлении Foreign Key, то действует по умолчанию правило "NO ACTION".

Правило SET DEFAULT

В столбец (столбцы) внешнего ключа записей дочерней таблицы заносятся значения столбца по-умолчанию, указанное при создании таблицы (параметр DEFAULT). Если значение не определено, то возбуждается исключение.

Создать внешний ключ Foreign Key можно как вместе с таблицей, при уже созданной родительской таблицей, так и отдельно. При создании Foreign Key отдельно от таблицы необходимо использовать оператор "ALTER TABLE table_name ADD CONSTRAINT foreign_key Foreign Key . ".

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

Пример создания Foreign Key

Создадим три таблицы : справочник пользователей users, справочник товаров goods и рабочую таблицу со счетами/накладными invoices. В таблице накладных будут храниться записи, ссылающиеся на идентификаторы пользователей и товара. В качестве СУБД используем MySQL.

SQL скрипты создания таблиц

-- справочник пользователей CREATE TABLE users ( uid INT NOT NULL PRIMARY KEY, -- идентификатор пользователя name varchar(128) NOT NULL ) ENGINE=InnoDB CHARACTER SET=UTF8; -- справочник товаров CREATE TABLE goods ( gid int NOT NULL PRIMARY KEY, -- идентификатор товара name varchar(32) NOT NULL ) ENGINE=InnoDB CHARACTER SET=UTF8; -- таблица накладных CREATE TABLE invoices ( iid int NOT NULL, -- идентификатор счета/накладной uid int NOT NULL, gid int NOT NULL, quantity decimal(9,4) NOT NULL, data date NOT NULL, PRIMARY KEY(uid, iid) ) ENGINE=InnoDB CHARACTER SET=UTF8;

Для таблицы "invoices" создадим внешние ключи, которые обеспечат каскадное обновление записей при обновлении соответствующих записей родительской таблицы, но блокируют удаление "родительских записей". Таким образом, любые изменения в таблицах users и goods автоматически отразятся в таблице "invoices". Но если товар заказан или если у пользователя есть счет, то родительские записи не могут быть удалены.

Create Foreign Key

Внешние ключи Foreign Key можно создать с использованием следующих SQL-скриптов :

ALTER TABLE invoices ADD CONSTRAINT fkInvoicesUsers FOREIGN KEY (uid) REFERENCES test.users(uid) ON UPDATE CASCADE ON DELETE RESTRICT; ALTER TABLE invoices ADD CONSTRAINT fkInvoicesGoods FOREIGN KEY (gid) REFERENCES test.goods(gid) ON UPDATE CASCADE ON DELETE RESTRICT;

Также Foreign Key таблицы "invoices" можно создать изменением скрипта описания таблицы.

CREATE TABLE invoices ( iid int NOT NULL, -- идентификатор счета/накладной uid int NOT NULL, gid int NOT NULL, quantity decimal(9,4) NOT NULL, data date NOT NULL, PRIMARY KEY(uid, iid), CONSTRAINT fkInvoicesUsers FOREIGN KEY (uid) REFERENCES users(uid) ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT fkInvoicesGoods FOREIGN KEY (gid) REFERENCES goods(gid) ON UPDATE CASCADE ON DELETE RESTRICT ) ENGINE=InnoDB CHARACTER SET=UTF8;

Попробуем добавить в таблицу накладных запись :

insert into invoices (iid, uid, gid, quantity, data) values (1, 0, 0, 23, NOW());

Сервер выдаст сообщение :

SQL2.sql: Ошибка: (1,1): Cannot add or update a child row : a foreign key constraint fails (`test`.`invoices`, CONSTRAINT `fkInvoicesUsers` FOREIGN KEY (`uid`) REFERENCES `users` (`uid`) ON UPDATE CASCADE)

Запишем в справочные таблицы несколько тестовых записей :

-- Запишем в таблицу users несколько пользователей insert into users (uid, name) values ( 1, 'Serg' ); insert into users (uid, name) values ( 2, 'Olga' ); -- сообщение сервера SQL2.sql: 1 Строка вставлена [0,012c] SQL2.sql: 1 Строка вставлена [0,010c] -- Запишем в таблицу goods несколько товаров insert into goods (gid, name) values ( 1, 'Кофе' ); insert into goods (gid, name) values ( 2, 'Чай' ); -- сообщение сервера SQL2.sql: 1 Строка вставлена [0,012c] SQL2.sql: 1 Строка вставлена [0,010c]

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

-- Добавим строку в таблицу invoices insert into invoices (iid, uid, gid, quantity, data) values (1, 1, 2, 23, NOW()); -- сообщение сервера SQL2.sql: 1 Строка вставлена [0,022c]

Удаление внешнего ключа, DROP Foreign Key

Для удаления FOREIGN KEY используйте следующий SQL :

-- MySQL ALTER TABLE table_name DROP FOREIGN KEY foreign_key_name; -- Oracle, MSSQL, PostgreSQL ALTER TABLE table_name DROP CONSTRAINT foreign_key_constraint;

Sysadminium

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