Внешний ключ 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 )
FOREIGN KEY в SQL
FOREIGN KEY используется для ограничения по ссылкам. Когда все значения в одном поле таблицы представлены в поле другой таблицы, говорится, что первое поле ссылается на второе. Это указывает на прямую связь между значениями двух полей.
Когда одно поле в таблице ссылается на другое, оно называется внешним ключом; а поле на которое оно ссылается, называется родительским ключом. Имена внешнего ключа и родительского ключа не обязательно должны быть одинаковыми. Внешний ключ может иметь любое число полей, которые все обрабатываются как единый модуль. Внешний ключ и родительский ключ, на который он ссылается, должны иметь одинаковый номер и тип поля, и находиться в одинаковом порядке. Когда поле является внешним ключом, оно определенным образом связано с таблицей, на которую он ссылается. Каждое значение, (каждая строка ) внешнего ключа должно недвусмысленно ссылаться к одному и только этому значению (строке) родительского ключа. Если это условие соблюдается, то база данных находится в состоянии ссылочной целостности.
SQL поддерживает ссылочную целостность с ограничением FOREIGN KEY. Эта функция должна ограничивать значения, которые можно ввести в базу данных, чтобы заставить внешний ключ и родительский ключ соответствовать принципу ссылочной целостности. Одно из действий ограничения FOREIGN KEY — это отбрасывание значений для полей, ограниченных как внешний ключ, который еще не представлен в родительском ключе. Это ограничение также воздействует на способность изменять или удалять значения родительского ключа.
Ограничение FOREIGN KEY используется в команде CREATE TABLE (или ALTER TABLE (предназначена для модификации структуры таблицы), содержащей поле, которое объявлено внешним ключом. Родительскому ключу дается имя, на которое имеется ссылка внутри ограничения FOREIGN KEY.
Подобно большинству ограничений, оно может быть ограничением таблицы или столбца, в форме таблицы позволяющей использовать многочисленные поля как один внешний ключ. Синтаксис ограничения таблицы FOREIGN KEY: FOREIGN KEY REFERENCES [ ] Первый список столбцов — это список из одного или более столбцов таблицы, которые отделены запятыми и будут созданы или изменены этой командой. Pktable — это таблица содержащая родительский ключ. Она может быть таблицей, которая создается или изменяется текущей командой. Второй список столбцов — это список столбцов, которые будут составлять родительский ключ. Списки двух столбцов должны быть совместимы, т.е.:
- иметь одинаковое число столбцов
- в данной последовательности, первый, второй, третий, и т.д., столбцы списка столбцов внешнего ключа, должны иметь одинаковые типы данных и размеры, что и первый, второй, третий, и т.д., столбцы списка столбцов родительского ключа.
- столбцы в списках обоих столбцов не должны иметь одинаковых имен.
FOREIGN KEY SQL. Пример 1
CREATE TABLE Student ( Kod_stud integer NOT NULL PRIMARY KEY, Kod_spec integer NOT NULL, Fam char(30) NOT NULL UNIQUE, Adres char(50), Ball decimal), FOREIGN KEY (Kod_spec) REFERENCES Spec (Kod_spec) );
При использовании ALTER TABLE вместо CREATE TABLE, для применения ограничения FOREIGN KEY, значения, указываемые во внешнем ключе и родительском ключе, должны быть в состоянии ссылочной целостности. Иначе команда будет отклонена.
Используя ограничение FOREIGN KEY таблицы или столбца, можно не указывать список столбцов родительского ключа, если родительский ключ имеет ограничение PRIMARY KEY. Естественно, в случае ключей со многими полями, порядок столбцов во внешних и первичных ключах должен совпадать, и, в любом случае, принцип совместимости между двумя ключами все еще применим.
FOREIGN KEY. Пример 2
CREATE TABLE Student ( Kod_stud integer NOT NULL PRIMARY KEY, Fam char(30) NOT NULL UNIQUE, Adres char(50), Ball decimal), Kod_spec integer REFERENCES Spec );
Поддержание ссылочной целостности требует некоторых ограничений на значения, которые могут быть представлены в полях, объявленных как внешний ключ и родительский ключ. Родительский ключ должен быть структурен, чтобы гарантировать, что каждое значение внешнего ключа будет соответствовать одной указанной строке. Это означает, что он (ключ) должен быть уникальным и не содержать никаких пустых значений(NULL).
Этого не достаточно для родительского ключа в случае выполнения такого требования, как при объявлении внешнего ключа. SQL должен быть уверен, что двойные значения или пустые значения (NULL) не были введены в родительский ключ. Следовательно необходимо убедиться, что все поля, которые используются как родительские ключи, имеют или ограничение PRIMARY KEY или ограничение UNIQUE, наподобие ограничения NOT NULL.
Ссылка внешних ключей только на первичные ключи — хорошая стратегия. Когда используются внешние ключи, они связываются не просто с родительскими ключами, на которые они ссылаются; они связываются с определенной строкой таблицы, где этот родительский ключ будет найден. Сам по себе родительский ключ не обеспечивает никакой информации, которая бы не была уже представлена во внешнем ключе.
Так как цель первичного ключа состоит в том, чтобы идентифицировать уникальность строки, это более логичный и менее неоднозначный выбор для внешнего ключа. Для любого внешнего ключа, который использует уникальный ключ как родительский ключ, необходимо создать внешний ключ, который бы использовал первичный ключ той же самой таблицы для того же самого действия. Внешний ключ, который не имеет никакой другой цели кроме связывания строк, напоминает первичный ключ, используемый исключительно для идентификации строк, и является хорошим средством сохранения структуры базы данных ясной и простой. Внешний ключ может содержать только те значения, которые фактически представлены в родительском ключе или пустые (NULL). Попытка ввести другие значения в этот ключ будет отклонена.
FOREIGN KEY. Пример 3
CREATE TABLE payment ( sh_payout integer, sh_eml integer, date_payout date, summ_payout real, FOREIGN KEY (sh_eml) REFERENCES k_sotr2 (eid) );
В данном примере FOREIGN KEY столбец sh_eml связывается со столбцом eid из таблицы k_sotr2.
Основные ключевые слова, используемые в статье:
foreign key, foreign key mysql, foreign key oracle, foreign key sql, foreign key references
Внешние ключи SQL, урок 13
Внешние ключи (FK) реляционной базы данных это столбец, а может сочетание столбцов, используемые для принудительного установления связи между данными в двух таблицах. Внешний ключ можно создать, определив ограничение FOREIGN KEY при создании или изменении таблицы.
Внешний ключ и родительский ключ
Когда все значения в одном поле таблицы представлены в поле другой таблицы, мы говорим, что первое поле ссылается на второе. Это указывает на прямую связь между значениями двух полей.
Когда одно поле в таблице ссылается на другое, оно называется – внешним ключом, а поле, на которое оно ссылается, называется родительским ключом.
Имена внешнего ключа и родительского ключа не обязательно должны быть одинаковыми, это только соглашение, которому мы следуем, чтобы делать соединение более понятным.
Многостолбцовые внешние ключи
В действительности, внешний ключ не обязательно состоит только из одного поля. Подобно первичному ключу, внешний ключ может иметь любое число полей, которые все обрабатываются как единый модуль.
Смысл внешнего и родительского ключей
Когда поле является внешним ключом, оно определенным образом связано с таблицей, на которую он ссылается. Каждое значение в этом поле (внешнем ключе) непосредственно привязано к значению в другом поле (первичном ключе).
Статьи по теме: Урок 3, Установка MySQL
Каждое значение (каждая строка) внешнего ключа должно недвусмысленно ссылаться к одному и только этому значению (строке) родительского (первичного) ключа. Если это так, то фактически ваша система, как говорится, будет в состоянии справочной целостности.
Понятно, что каждое значение во внешнем ключе должно быть представлено один, и только один раз, в родительском ключе.
Видео урок: Внешние ключи SQL
Полезные ссылки
Учебник по базам данных тут:
Все видео уроки SQL
- Введение в SQL, видео урок 1
- Лекция о языке SQL
- Урок 3, Установка MySQL
- 4 Урок, Базовые команды SQL
- 5 Видеоурок, Команда SQL SELECT
- 6 Видео Урок, команды DELETE и UPDATE, удалять и обновлять записи, языка SQL
- Урок 7. Понятие нормализации в теории БД
- SQL ALTER TABLE — sql запрос на модификацию таблицы базы данных
- Строковые функции SQL, УРОК 9.
- Урок 10, Оператор Case и сортировка данных в алфавитном порядке
Внешний ключ (FOREIGN KEY) в SQL
Внешний ключ (FOREIGN KEY) нужен для того, чтобы связать две разные таблицы между собой. Внешний ключ может ссылаться на любой столбец в родительской таблице. Однако общепринятой практикой является ссылка внешнего ключа на первичный ключ (primary key) родительской таблицы. Например:
Здесь поле customer_id в таблице Orders является FOREIGN KEY , который ссылается на поле id в таблице Customers. Это означает, что значением customer_id (таблицы Orders) должно быть значение из столбца id (таблицы Customers).
Создание внешнего ключа
Теперь давайте посмотрим, как мы можем добавить ограничение FOREIGN KEY :
— Эта таблица не имеет внешнего ключа
CREATE TABLE Customers (
first_name VARCHAR ( 40 ) ,
last_name VARCHAR ( 40 ) ,
country VARCHAR ( 10 ) ,
CONSTRAINT CustomersPK PRIMARY KEY ( id )
— Добавляем внешний ключ к полю customer_id.
— Внешний ключ ссылается на поле id таблицы Customers
CREATE TABLE Orders (
order_id INT ,
item VARCHAR ( 40 ) ,
amount INT ,
customer_id INT REFERENCES Customers ( id ) ,
CONSTRAINT OrdersPK PRIMARY KEY ( order_id )
Здесь столбец customer_id таблицы Orders ссылается на столбец id таблицы Customers.
Примечание: Вышеприведенный код создания внешнего ключа может отличаться в некоторых СУБД.
Вставка данных в таблицу с внешним ключом
Попробуем вставить данные в таблицу с внешним ключом.
— Сначала вставляем данные в таблицу без внешнего ключа
INSERT INTO Customers
( 1 , ‘John’ , ‘Doe’ , 31 , ‘USA’ ) ,
( 2 , ‘Robert’ , ‘Luna’ , 22 , ‘USA’ ) ;
— Первая операция вставки данных проходит успешно
INSERT INTO Orders
( 1 , ‘Keyboard’ , 400 , 2 ) ,
( 2 , ‘Mouse’ , 300 , 2 ) ,
( 3 , ‘Monitor’ , 12000 , 1 ) ;
— Вторая операция вставки данных приводит к ошибке, поскольку customer_id со значением 7 не существует
INSERT INTO Orders
VALUES ( 4 , ‘Keyboard’ , 400 , 7 ) ;
Зачем использовать внешний ключ?
Две главные причины:
Нормализация данных. FOREIGN KEY помогает нормализовать данные в нескольких таблицах и уменьшить избыточность. Это означает, что в базе данных может быть несколько таблиц, связанных друг с другом.
Предотвращение вставки некорректных данных. Если две таблицы в базе данных связаны через поле (атрибут), использование FOREIGN KEY гарантирует, что в это поле не будут вставлены неверные данные. Это помогает устранить ошибки на уровне базы данных.
FOREIGN KEY с оператором ALTER TABLE
Можно добавить ограничение FOREIGN KEY к существующей таблице с помощью оператора ALTER TABLE. Например: