Что означает CONSTRAINT и REFERENCES в данном запросе?
Внешний ключ, говорящий, что в поле TeamId может быть либо NULL либо значение, которое присутствует в таблице Teams в поле id. Причем при удалении записи из Teams если у каких то записей Players есть id удаляемой записи, он будет заменен на NULL. А constraint просто назначает имя для этого ключа
20 авг 2017 в 13:49
2 ответа 2
Сортировка: Сброс на вариант по умолчанию
CONSTRAINT — ключевое слово, которое указывает, что в данной секции описывается ограничение, которое налагается на данные в таблице, и которое будет проверяться подсистемой контроля целостности и непротиворечивости данных сервера, что в свою очередь не позволит внести или изменить данные в таблице так, чтобы это условие не выполнялось, а при выявлении факта наличия не соответствующих этому условию данных в таблице она будет считаться повреждённой. Сразу после этого ключевого слова указывается уникальный идентификатор (имя) этого ограничения (он может быть пропущен, тогда сервер сгенерирует его автоматически).
REFERENCES — ключевое слово, которое указывает, что для каждой записи таблицы значение выражения описанного непосредственно перед ним индекса будет контролироваться на наличие в точности совпадающего по значению, включая деление на фрагменты при наличии такового, значения в уникальном индексном выражении таблицы, указанном непосредственно после этого ключевого слова. Контроль выполняется подсистемой контроля целостности и непротиворечивости данных сервера. Любые действия, которые приведут к нарушению такого соответствия, будут блокироваться. При выявлении любого несоответствия данные будут считаться повреждёнными.
References sql что это
Внешние ключи позволяют установить связи между таблицами. Внешний ключ устанавливается для столбцов из зависимой, подчиненной таблицы, и указывает на один из столбцов из главной таблицы. Как правило, внешний ключ указывает на первичный ключ из связанной главной таблицы.
Общий синтаксис установки внешнего ключа на уровне таблицы:
[CONSTRAINT имя_ограничения] FOREIGN KEY (столбец1, столбец2, . столбецN) REFERENCES главная_таблица (столбец_главной_таблицы1, столбец_главной_таблицы2, . столбец_главной_таблицыN) [ON DELETE действие] [ON UPDATE действие]
Для создания ограничения внешнего ключа после FOREIGN KEY указывается столбец таблицы, который будет представляет внешний ключ. А после ключевого слова REFERENCES указывается имя связанной таблицы, а затем в скобках имя связанного столбца, на который будет указывать внешний ключ. После выражения REFERENCES идут выражения ON DELETE и ON UPDATE , которые задают действие при удалении и обновлении строки из главной таблицы соответственно.
Например, определим две таблицы и свяжем их посредством внешнего ключа:
CREATE TABLE Customers ( Id INT PRIMARY KEY AUTO_INCREMENT, Age INT, FirstName VARCHAR(20) NOT NULL, LastName VARCHAR(20) NOT NULL, Phone VARCHAR(20) NOT NULL UNIQUE ); CREATE TABLE Orders ( Id INT PRIMARY KEY AUTO_INCREMENT, CustomerId INT, CreatedAt Date, FOREIGN KEY (CustomerId) REFERENCES Customers (Id) );
В данном случае определены таблицы Customers и Orders. Customers является главной и представляет клиента. Orders является зависимой и представляет заказ, сделанный клиентом. Таблица Orders через столбец CustomerId связана с таблицей Customers и ее столбцом Id. То есть столбец CustomerId является внешним ключом, который указывает на столбец Id из таблицы Customers.
С помощью оператора CONSTRAINT можно задать имя для ограничения внешнего ключа:
CREATE TABLE Orders ( Id INT PRIMARY KEY AUTO_INCREMENT, CustomerId INT, CreatedAt Date, CONSTRAINT orders_custonmers_fk FOREIGN KEY (CustomerId) REFERENCES Customers (Id) );
ON DELETE и ON UPDATE
С помощью выражений ON DELETE и ON UPDATE можно установить действия, которые выполняются соответственно при удалении и изменении связанной строки из главной таблицы. В качестве действия могут использоваться следующие опции:
- CASCADE : автоматически удаляет или изменяет строки из зависимой таблицы при удалении или изменении связанных строк в главной таблице.
- SET NULL : при удалении или обновлении связанной строки из главной таблицы устанавливает для столбца внешнего ключа значение NULL . (В этом случае столбец внешнего ключа должен поддерживать установку NULL)
- RESTRICT : отклоняет удаление или изменение строк в главной таблице при наличии связанных строк в зависимой таблице.
- NO ACTION : то же самое, что и RESTRICT .
- SET DEFAULT : при удалении связанной строки из главной таблицы устанавливает для столбца внешнего ключа значение по умолчанию, которое задается с помощью атрибуты DEFAULT. Несмотря на то, что данная опция в принципе доступна, однако движок InnoDB не поддерживает данное выражение.
Каскадное удаление
Каскадное удаление позволяет при удалении строки из главной таблицы автоматически удалить все связанные строки из зависимой таблицы. Для этого применяется опция 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 . При изменении значения первичного ключа автоматически изменится значение связанного с ним внешнего ключа. Однако поскольку первичные ключи изменяются очень редко, да и с принципе не рекомендуется использовать в качестве первичных ключей столбцы с изменяемыми значениями, то на практике выражение ON UPDATE используется редко.
Установка NULL
При установки для внешнего ключа опции SET NULL необходимо, чтобы столбец внешнего ключа допускал значение NULL:
CREATE TABLE Orders ( Id INT PRIMARY KEY AUTO_INCREMENT, CustomerId INT, CreatedAt Date, FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE SET NULL );
Как создать первичные и внешние ключи 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
Оператор SQL FOREIGN KEY
Оператор SQL FOREIGN KEY (Внешний ключ) служит для указания в одной таблице на Первичный ключ (оператор SQL PRIMARY KEY) в другой.
Оператор SQL FOREIGN KEY имеет следующий синтаксис:
CREATE TABLE table_1 ( ID_1 int NOT NULL ID_2 int PRIMARY KEY (ID_1) FOREIGN KEY (ID_2) REFERENCES table_2(ID_2) )
Для MS SQL Server, Oracle, MS Access:
CREATE TABLE table_1 ( ID_1 int NOT NULL PRIMARY KEY ID_2 int FOREIGN KEY REFERENCES table_2(ID_2) )
Примеры оператора SQL FOREIGN KEY. Имеются две таблицы:
Authors — содержит в себе информацию об авторах книг:
| AuthorID | AuthorName |
| 1 | Bruce Eckel |
| 2 | Robert Lafore |
| 3 | Andrew Tanenbaum |
Books — содержит в себе информацию о названии книг:
| BookID | BookName |
| 3 | Modern Operating System |
| 2 | Object-Oriented Programming in C++ |
| 1 | Thinking in Java |
| 3 | Computer Architecture |
В таблице Books поле BookID являются Внешним ключом и ссылаются на таблицу Authors. Для организации таблиц с подобной структурой используется следующий код (для MS SQL Server) с использованием оператора SQL FOREIGN KEY:
CREATE TABLE Authors ( AuthorID int NOT NULL PRIMARY KEY, AuthorName varchar(50) ) CREATE TABLE Books ( BookID int FOREIGN KEY REFERENCES Authors(AuthorID), BookName varchar(100) )