Внешние ключи SQL

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

Общий синтаксис установки внешнего ключа на уровне столбца:

1 2 3

[FOREIGN KEY] REFERENCES главная_таблица (столбец_главной_таблицы)     [ON DELETE {CASCADE|NO ACTION}]     [ON UPDATE {CASCADE|NO ACTION}]

Для создания ограничения внешнего ключа на уровне столбца после ключевого слова REFERENCES указывается имя связанной таблицы и в круглых скобках имя связанного столбца, на который будет указывать внешний ключ. Также обычно добавляются ключевые слова FOREIGN KEY, но в принципе их необязательно указывать. После выражения REFERENCES идет выражение ON DELETE и ON UPDATE.

Общий синтаксис установки внешнего ключа на уровне таблицы:

1 2 3 4

FOREIGN KEY (стобец1, столбец2, ... столбецN)     REFERENCES главная_таблица (столбец_главной_таблицы1, столбец_главной_таблицы2, ... столбец_главной_таблицыN)     [ON DELETE {CASCADE|NO ACTION}]     [ON UPDATE {CASCADE|NO ACTION}]

Например, определим две таблицы и свяжем их посредством внешнего ключа:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

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.

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

1 2 3 4 5 6 7

CREATE TABLE Orders (     Id INT PRIMARY KEY IDENTITY,     CustomerId INT,     CreatedAt Date,     FOREIGN KEY (CustomerId)  REFERENCES Customers (Id) );

С помощью оператора CONSTRAINT можно задать имя для ограничения внешнего ключа. Обычно это имя начинается с префикса "FK_":

1 2 3 4 5 6 7

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:

1 2 3 4 5 6 7

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:

1 2 3 4 5 6 7

CREATE TABLE Orders (     Id INT PRIMARY KEY IDENTITY,     CustomerId INT,     CreatedAt Date,     FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE SET NULL );

Установка значения по умолчанию

1 2 3 4 5 6 7

CREATE TABLE Orders (     Id INT PRIMARY KEY IDENTITY,     CustomerId INT,     CreatedAt Date,     FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE SET DEFAULT )