Ограничения первичных и внешних ключей
Первичные ключи и внешние ключи — это два типа ограничений, которые можно использовать для обеспечения целостности данных в таблицах SQL Server. Это важные объекты базы данных.
Ограничения первичного ключа
Обычно в таблице есть столбец или сочетание столбцов, содержащих значения, уникально определяющие каждую строку таблицы. Этот столбец, или столбцы, называются первичным ключом (PK) таблицы и обеспечивает целостность сущности таблицы. Так как ограничения первичного ключа гарантируют уникальные данные, они часто определяются в столбце удостоверений.
При указании ограничения первичного ключа для таблицы ядро СУБД обеспечивает уникальность данных путем автоматического создания уникального индекса для столбцов первичного ключа. Этот индекс также обеспечивает быстрый доступ к данным при использовании первичного ключа в запросах. Если ограничение первичного ключа задано более чем для одного столбца, то значения могут дублироваться в пределах одного столбца, но каждое сочетание значений всех столбцов в определении ограничения первичного ключа должно быть уникальным.
Как показано на следующем рисунке, столбцы ProductID и VendorID в таблице Purchasing.ProductVendor формируют составное ограничение первичного ключа для этой таблицы. При этом гарантируется, что каждая строка в таблице ProductVendor имеет уникальное сочетание значений ProductID и VendorID . Это предотвращает вставку повторяющихся строк.
- В таблице возможно наличие только одного ограничения по первичному ключу.
- Первичный ключ не может превышать 16 столбцов и общую длину ключа 900 байт.
- Индекс, созданный ограничением первичного ключа, не может привести к тому, что число индексов таблицы превышает 999 некластеризованных индексов и 1 кластеризованных индексов.
- Если кластеризованный или некластеризованный не указан для ограничения первичного ключа, кластеризованный используется, если в таблице нет кластеризованного индекса.
- Все столбцы с ограничением первичного ключа должны быть определены как не допускающие значения NULL. Если значение NULL не указано, все столбцы, участвующие в ограничении первичного ключа, имеют значение NULL.
- Если первичный ключ определен на столбце определяемого пользователем типа данных CLR, реализация этого типа должна поддерживать двоичную сортировку.
Foreign Key Constraints
Внешний ключ (FK) — это столбец или сочетание столбцов, которое применяется для принудительного установления связи между данными в двух таблицах с целью контроля данных, которые могут храниться в таблице внешнего ключа. Если один или несколько столбцов, в которых находится первичный ключ для одной таблицы, упоминается в одном или нескольких столбцах другой таблицы, то в ссылке внешнего ключа создается связь между двумя таблицами. Этот столбец становится внешним ключом во второй таблице.
Например, таблица имеет ссылку на внешний ключ к Sales.SalesPerson таблице, Sales.SalesOrderHeader так как между заказами на продажу и продавцами существует логическая связь. Столбец SalesPersonID в таблице SalesOrderHeader соответствует столбцу первичного ключа таблицы SalesPerson . Столбец SalesPersonID в таблице SalesOrderHeader является внешним ключом таблицы SalesPerson . Создав эту связь внешнего ключа, невозможно вставить значение SalesPersonID в SalesOrderHeader таблицу, если она еще не существует в SalesPerson таблице.
Максимальное количество таблиц и столбцов, на которые может ссылаться таблица в качестве внешних ключей (исходящих ссылок), равно 253. SQL Server 2016 (13.x) увеличивает ограничение числа других таблиц и столбцов, которые могут ссылаться на столбцы в одной таблице (входящих ссылок), с 253 до 10 000. (Требуется уровень совместимости не менее 130.) Увеличение имеет следующие ограничения:
- Превышение 253 ссылок на внешние ключи поддерживается только для операций DML DELETE. Операции UPDATE и MERGE не поддерживаются.
- Таблица со ссылкой внешнего ключа на саму себя по-прежнему ограничена 253 ссылками на внешние ключи.
- Более 253 ссылок на внешний ключ в настоящее время недоступны для индексов columnstore, оптимизированных для памяти таблиц, Stretch Database или секционированных таблиц внешнего ключа.
Важно! Stretch Database устарел в SQL Server 2022 (16.x) и База данных SQL Azure. Эта функция будет удалена в будущей версии ядро СУБД. Избегайте использования этого компонента в новых разработках и запланируйте изменение существующих приложений, в которых он применяется.
Индексы в ограничениях внешнего ключа
В отличие от ограничений первичного ключа, создание ограничения внешнего ключа не автоматически создает соответствующий индекс. Тем не менее, часто возникает необходимость создания индекса для внешнего ключа вручную по следующим причинам:
- Столбцы внешнего ключа часто используются в критериях соединения при совместном применении в запросах данных из связанных таблиц. Это реализуется путем сопоставления столбца или столбцов в ограничении внешнего ключа в одной таблице с одним или несколькими столбцами первичного или уникального ключа в другой таблице. Индекс позволяет ядро СУБД быстро находить связанные данные в таблице внешнего ключа. Однако создание этого индекса не требуется. Данные из двух связанных таблиц можно комбинировать, даже если между таблицами не определены ограничения первичного ключа или внешнего ключа, но связь по внешнему ключу между двумя таблицами показывает, что эти две таблицы оптимизированы для совместного применения в запросе, где ключи используются в качестве критериев.
- С помощью ограничений внешнего ключа в связанных таблицах проверяются изменения ограничений первичного ключа.
Ссылочная целостность
Главная задача ограничения внешнего ключа состоит в управлении данными, которые могут быть сохранены в таблице внешнего ключа, но это ограничение контролирует также изменение данных в таблице первичного ключа. Например, при удалении строки для менеджера по продажам из таблицы Sales.SalesPerson , идентификатор которого используется в заказах на продажу в таблице Sales.SalesOrderHeader , ссылочная целостность двух таблиц будет нарушена. Заказы на продажу удаленного менеджера в таблице SalesOrderHeader станут недействительными без связи с данными в таблице SalesPerson .
Ограничение внешнего ключа предотвращает возникновение этой ситуации. Ограничение обеспечивает целостность ссылок, гарантируя, что изменения нельзя вносить в данные в таблице первичного ключа, если эти изменения недопустимы для ссылки на данные в таблице внешнего ключа. Если предпринята попытка удалить строку в таблице первичного ключа или изменить значение первичного ключа, действие завершается ошибкой, если значение удаленного или измененного первичного ключа соответствует значению в ограничении внешнего ключа другой таблицы. Для успешного изменения или удаления строки с ограничением внешнего ключа необходимо сначала удалить данные внешнего ключа в таблице внешнего ключа либо изменить в таблице внешнего ключа данные, которые связывают внешний ключ с данными другого первичного ключа.
Каскадная ссылочная целостность
С помощью каскадных ограничений целостности можно определить действия, которые выполняет ядро СУБД, когда пользователь пытается удалить или обновить ключ, на который имеется точка внешних ключей. Могут быть определены следующие каскадные действия.
NO ACTION
Ядро СУБД вызывает ошибку, а действие удаления или обновления строки в родительской таблице откатывается.
CASCADE
Соответствующие строки обновляются или удаляются из ссылающейся таблицы, если данная строка обновляется или удаляется из родительской таблицы. КАСКАД нельзя указать, если столбец метки времени является частью внешнего ключа или ключа, на который ссылается ссылка. ON DELETE CASCADE нельзя указать для таблицы с триггером INSTEAD OF DELETE. ON UPDATE CASCADE нельзя указать для таблиц с триггерами INSTEAD OF UPDATE.
SET NULL
Всем значениям, составляющим внешний ключ, присваивается значение NULL, когда обновляется или удаляется соответствующая строка в родительской таблице. Для выполнения этого ограничения внешние ключевые столбцы должны допускать значения NULL. Невозможно указать для таблиц с триггерами INSTEAD OF UPDATE.
SET DEFAULT
Все значения, составляющие внешний ключ, при удалении или обновлении соответствующей строки родительской таблицы устанавливаются в значение по умолчанию. Для выполнения этого ограничения все внешние ключевые столбцы должны иметь определения по умолчанию. Если столбец имеет значение NULL, и нет явного набора значений по умолчанию, NULL становится неявным значением по умолчанию столбца. Невозможно указать для таблиц с триггерами INSTEAD OF UPDATE.
Ключевые слова CASCADE, SET NULL, SET DEFAULT и NO ACTION можно сочетать в таблицах, имеющих взаимные ссылочные связи. Если ядро СУБД обнаруживает NO ACTION, он останавливается и откатывает связанные действия CASCADE, SET NULL и SET DEFAULT. Если инструкция DELETE вызывает сочетание действий CASCADE, SET NULL, SET DEFAULT и NO ACTION, все действия CASCADE, SET NULL и SET DEFAULT применяются перед ядро СУБД проверка для любых действий NO ACTION.
Триггеры и каскадные ссылочные действия
Каскадные ссылочные действия запускают триггеры AFTER UPDATE или AFTER DELETE следующим образом:
- Все каскадные ссылочные действия, прямо вызванные исходными инструкциями DELETE или UPDATE, выполняются первыми.
- Если есть какие-либо триггеры AFTER, определенные для измененных таблиц, эти триггеры запускаются после выполнения всех каскадных действий. Эти триггеры запускаются в порядке, обратном каскадным действиям. Если в одной таблице существует несколько триггеров, они запускаются в случайном порядке, если для таблицы не существует выделенный первый или последний триггер. Этот порядок определяется процедурой sp_settriggerorder.
- Если последовательности каскадных действий происходят из таблицы, которая была непосредственной целью действий DELETE или UPDATE, порядок запуска триггеров этими последовательностями действий не определен. Однако одна последовательность действий всегда запускает все свои триггеры до того, как это начнет делать следующая.
- Триггер AFTER таблицы, являвшейся непосредственной целью действий DELETE или UPDATE, запускается вне зависимости от того, были ли изменены хоть какие-нибудь строки. В этом случае ни на какие другие таблицы каскадирование не влияет.
- Если один из предыдущих триггеров выполняет операции DELETE или UPDATE над другими таблицами, эти операции могут вызывать собственные последовательности каскадных действий. Эти вторичные последовательности действий обрабатываются для каждой операции DELETE или UPDATE после выполнения всех триггеров первичных последовательностей действий. Этот процесс может рекурсивно повторяться для последующих операций DELETE или UPDATE.
- Выполнение операций CREATE, ALTER, DELETE или других операций языка DDL внутри триггеров может привести к запуску триггеров DDL. Это может привести к дальнейшим операциям DELETE или UPDATE, которые начнут дополнительные последовательности каскадных действий и запустят свои триггеры.
- Если в любой конкретной последовательности каскадных ссылочных действий произойдет ошибка, в этой последовательности не будут запущены никакие триггеры AFTER, а для операций DELETE или UPDATE, создаваемых этой последовательностью, будет выполнен откат.
- Таблица с триггером INSTEAD OF не может также содержать предложение REFERENCES, указывающее каскадное действие. Однако триггер AFTER целевой таблицы каскадного действия может выполнить инструкцию INSERT, UPDATE или DELETE для другой таблицы или представления, которое запустит триггер INSTEAD OF для этого объекта.
Следующие шаги
В следующей таблице перечислены общие задачи, связанные с ограничениями первичного ключа и внешнего ключа.
| Задача | Статья |
|---|---|
| Описывает, как создать первичный ключ. | Создание первичных ключей |
| Описывает, как удалить первичный ключ. | Удаление первичных ключей |
| Описывает, как изменить первичный ключ. | Изменение первичных ключей |
| Описывается создание связей внешнего ключа | Создание связей по внешнему ключу |
| Описывает, как изменить связи внешнего ключа. | Изменение связей по внешнему ключу |
| Описывает, как удалить связи внешнего ключа. | Удаление связей по внешнему ключу |
| Описывает, как просматривать свойства внешнего ключа. | Просмотр свойств внешнего ключа |
| Описывает, как отключить ограничения внешнего ключа для репликации. | Отключение ограничений внешнего ключа для репликации |
| Описывает, как отключить ограничения внешнего ключа на время выполнения инструкций INSERT и UPDATE. | Отключение ограничений внешнего ключа для инструкций INSERT и UPDATE |
Внешний ключ sql что это
Внешние ключи применяются для установки связи между таблицами. Внешний ключ устанавливается для столбцов из зависимой, подчиненной таблицы, и указывает на один из столбцов из главной таблицы. Хотя, как правило, внешний ключ указывает на первичный ключ из связанной главной таблицы, но это необязательно должно быть непременным условием. Внешний ключ также может указывать на какой-то другой столбец, который имеет уникальное значение.
Общий синтаксис установки внешнего ключа на уровне столбца:
[FOREIGN KEY] REFERENCES главная_таблица (столбец_главной_таблицы) [ON DELETE ] [ON UPDATE ]
Для создания ограничения внешнего ключа на уровне столбца после ключевого слова REFERENCES указывается имя связанной таблицы и в круглых скобках имя связанного столбца, на который будет указывать внешний ключ. Также обычно добавляются ключевые слова FOREIGN KEY , но в принципе их необязательно указывать. После выражения REFERENCES идет выражение ON DELETE и ON UPDATE .
Общий синтаксис установки внешнего ключа на уровне таблицы:
FOREIGN KEY (стобец1, столбец2, . столбецN) REFERENCES главная_таблица (столбец_главной_таблицы1, столбец_главной_таблицы2, . столбец_главной_таблицыN) [ON DELETE ] [ON UPDATE ]
Например, определим две таблицы и свяжем их посредством внешнего ключа:
CREATE TABLE Customers ( Id INT PRIMARY KEY IDENTITY, Age INT DEFAULT 18, FirstName NVARCHAR(20) NOT NULL, LastName NVARCHAR(20) NOT NULL, Email VARCHAR(30) UNIQUE, Phone VARCHAR(20) UNIQUE ); CREATE TABLE Orders ( Id INT PRIMARY KEY IDENTITY, CustomerId INT REFERENCES Customers (Id), CreatedAt Date );
Здесь определены таблицы Customers и Orders. Customers является главной и представляет клиента. Orders является зависимой и представляет заказ, сделанный клиентом. Эта таблица через столбец CustomerId связана с таблицей Customers и ее столбцом Id. То есть столбец CustomerId является внешним ключом, который указывает на столбец Id из таблицы Customers.
Определение внешнего ключа на уровне таблицы выглядело бы следующим образом:
CREATE TABLE Orders ( Id INT PRIMARY KEY IDENTITY, CustomerId INT, CreatedAt Date, FOREIGN KEY (CustomerId) REFERENCES Customers (Id) );
С помощью оператора CONSTRAINT можно задать имя для ограничения внешнего ключа. Обычно это имя начинается с префикса «FK_»:
CREATE TABLE Orders ( Id INT PRIMARY KEY IDENTITY, CustomerId INT, CreatedAt Date, CONSTRAINT FK_Orders_To_Customers FOREIGN KEY (CustomerId) REFERENCES Customers (Id) );
В данном случае ограничение внешнего ключа CustomerId называется «FK_Orders_To_Customers».
ON DELETE и ON UPDATE
С помощью выражений ON DELETE и ON UPDATE можно установить действия, которые выполняться соответственно при удалении и изменении связанной строки из главной таблицы. И для определения действия мы можем использовать следующие опции:
- CASCADE : автоматически удаляет или изменяет строки из зависимой таблицы при удалении или изменении связанных строк в главной таблице.
- NO ACTION : предотвращает какие-либо действия в зависимой таблице при удалении или изменении связанных строк в главной таблице. То есть фактически какие-либо действия отсутствуют.
- SET NULL : при удалении связанной строки из главной таблицы устанавливает для столбца внешнего ключа значение NULL.
- SET DEFAULT : при удалении связанной строки из главной таблицы устанавливает для столбца внешнего ключа значение по умолчанию, которое задается с помощью атрибуты DEFAULT. Если для столбца не задано значение по умолчанию, то в качестве него применяется значение NULL.
Каскадное удаление
По умолчанию, если на строку из главной таблицы по внешнему ключу ссылается какая-либо строка из зависимой таблицы, то мы не сможем удалить эту строку из главной таблицы. Вначале нам необходимо будет удалить все связанные строки из зависимой таблицы. И если при удалении строки из главной таблицы необходимо, чтобы были удалены все связанные строки из зависимой таблицы, то применяется каскадное удаление, то есть опция CASCADE :
CREATE TABLE Orders ( Id INT PRIMARY KEY IDENTITY, CustomerId INT, CreatedAt Date, FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE CASCADE )
Аналогично работает выражение ON UPDATE CASCADE . При изменении значения первичного ключа автоматически изменится значение связанного с ним внешнего ключа. Но так как первичные ключи, как правило, изменяются очень редко, да и с принципе не рекомендуется использовать в качестве первичных ключей столбцы с изменяемыми значениями, то на практике выражение ON UPDATE используется редко.
Установка NULL
При установки для внешнего ключа опции SET NULL необходимо, чтобы столбец внешнего ключа допускал значение NULL:
CREATE TABLE Orders ( Id INT PRIMARY KEY IDENTITY, CustomerId INT, CreatedAt Date, FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE SET NULL );
Установка значения по умолчанию
CREATE TABLE Orders ( Id INT PRIMARY KEY IDENTITY, CustomerId INT, CreatedAt Date, FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE SET DEFAULT )
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 можете почитать тут.
В чем практическая польза FOREIGN KEY в таблицах MySQL?
Логично использовать user_id и invoice_id как внешние ключи из соответствующих таблиц Users и Invoices. На сколько я понимаю, назначение user_id и invoice_id внешними ключами не позволит мне сделать ошибку и добавить в таблицу Orders заказ с несуществующими значениями user_id и invoice_id . На это практическая польза FOREIGN KEY заканчивается? У меня по этому поводу сомнения. Как еще я должен использовать FOREIGN KEY ? Я могу представить, если бы у меня в таблице Orders было поле name , значение которого автоматически обновляется, если изменяется его значение в родительской таблице. В этом бы была польза, но мне кажется не верно создавать дублирующиеся поля в разных таблицах. Поэтому я пытаюсь понять, как используются FOREIGN KEY ?
Отслеживать
66.5k 6 6 золотых знаков 53 53 серебряных знака 112 112 бронзовых знаков
задан 3 ноя 2018 в 18:59
103 1 1 серебряный знак 2 2 бронзовых знака
Не у верен за MySql, но в других СУБД поддерживаются конструкции вида on delete cascade и т.д.
3 ноя 2018 в 19:02
Контроль ссылочной целостности основное и единственное назначение внешних ключей. И да, как заметил @Viktorov дополнительно внешние ключи могут каскадно удалять записи при удалении из родительской таблицы или выставлять ссылки в NULL (если их об этом попросить). Но в общем то это одна из разновидностей контроля целостности. НО этого единственного их назначения более чем достаточно, БД без контроля ссылок очень быстро приходит в противоречивое состояние и после этого невозможно разобраться откуда что взялось
3 ноя 2018 в 19:06
Благодарю за разъяснения. Напишите, пожалуйста, ответ и я его приму.
3 ноя 2018 в 19:11
2 ответа 2
Сортировка: Сброс на вариант по умолчанию
FOREIGN KEY используется для ограничения по ссылкам. Когда все значения в одном поле таблицы представлены в поле другой таблицы, говорится, что первое поле ссылается на второе. Это указывает на прямую связь между значениями двух полей.
Когда одно поле в таблице ссылается на другое, оно называется внешним ключом, а поле на которое оно ссылается, называется родительским ключом. Имена внешнего ключа и родительского ключа не обязательно должны быть одинаковыми. Внешний ключ может иметь любое число полей, которые все обрабатываются как единый модуль. Внешний ключ и родительский ключ, на который он ссылается, должны иметь одинаковый номер и тип поля, и находиться в одинаковом порядке. Когда поле является внешним ключом, оно определенным образом связано с таблицей, на которую он ссылается. Каждое значение, (каждая строка ) внешнего ключа должно недвусмысленно ссылаться к одному и только этому значению (строке) родительского ключа. Если это условие соблюдается, то база данных находится в состоянии ссылочной целостности.
SQL поддерживает ссылочную целостность с ограничением FOREIGN KEY. Эта функция должна ограничивать значения, которые можно ввести в базу данных, чтобы заставить внешний ключ и родительский ключ соответствовать принципу ссылочной целостности. Одно из действий ограничения FOREIGN KEY — это отбрасывание значений для полей, ограниченных как внешний ключ, который еще не представлен в родительском ключе. Это ограничение также воздействует на способность изменять или удалять значения родительского ключа
Ограничение FOREIGN KEY используется в команде CREATE TABLE (или ALTER TABLE (предназначена для модификации структуры таблицы), содержащей поле, которое объявлено внешним ключом. Родительскому ключу дается имя, на которое имеется ссылка внутри ограничения FOREIGN KEY.
Подобно большинству ограничений, оно может быть ограничением таблицы или столбца, в форме таблицы позволяющей использовать многочисленные поля как один внешний ключ.
Пример: есть две таблицы users и emails:
CREATE TABLE users ( user_id int(11) NOT NULL AUTO_INCREMENT, user_name varchar(50) DEFAULT NULL, PRIMARY KEY (user_id) ) CREATE TABLE sys.emails ( email_id int(11) NOT NULL AUTO_INCREMENT, email_address varchar(100) NOT NULL, user_id int(11) NOT NULL, PRIMARY KEY (email_id) ) Между ними есть зависимость в виде users.user_id и emails.user_id, и мы хотим что в поле emails.user_id не смогло записываться ничего кроме значения с таблицы users столбец user_id, для этого и создаем ограничение в виде внешнего ключа:
ALTER TABLE emails ADD FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE NO ACTION; Допустим если мы удаляем пользователя то и автоматически удаляем все записи связанные с ним, в данном случае его адрес электронной почты.