В SQL Server: как создать внешний ключ (на примере)

Что такое ВНЕШНИЙ КЛЮЧ?

Внешний ключ обеспечивает способ обеспечения ссылочной целостности внутри SQL ServerПроще говоря, внешний ключ гарантирует, что значения в одной таблице обязательно будут присутствовать в другой таблице.

Правила для ВНЕШНЕГО КЛЮЧА

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

Приведенная ниже диаграмма суммирует все вышеперечисленные моменты, касающиеся внешнего ключа.

Как создать ВНЕШНИЙ КЛЮЧ в SQL

В SQL Server внешний ключ можно создать двумя способами:

• Студия управления SQL Server
• T-SQL

Студия управления SQL Server

Родительская таблица: Допустим, у нас есть существующая родительская таблица с именем «Course». Course_ID и Course_name — это два столбца, причем Course_ID является первичным ключом.

Дочерняя таблица: Нам нужно создать вторую таблицу в качестве дочерней. Ее столбцами будут 'Course_ID' и 'Course_Strength'. Однако 'Course_ID' будет внешним ключом.

Шаг 1) Щелкните правой кнопкой мыши на «Таблицы» > «Создать» > «Таблица…»

Шаг 2) Введите названия двух столбцов: «Course_ID» и «Course_Strength». Щелкните правой кнопкой мыши по столбцу «Course_ID», затем выберите «Связь».

Шаг 3) В разделе «Связи внешних ключей» нажмите «Добавить».

Шаг 4) В разделе «Спецификация таблиц и столбцов» щелкните значок «…».

Шаг 5) Выберите в раскрывающемся списке «Таблица первичного ключа» как «КУРС», а в качестве создаваемой новой таблицы — «Таблица внешнего ключа».

Шаг 6) В таблице «Первичный ключ» выберите столбец «Course_Id» в качестве столбца первичного ключа.

В поле «Таблица внешних ключей» выберите столбец «Course_Id» в качестве столбца таблицы внешних ключей. Нажмите ОК.

Шаг 7) Нажмите «Добавить».

Шаг 8) Присвойте таблице имя 'Course_Strength' и нажмите ОК.

Результат: Мы установили связь «родитель-потомок» между 'Course' и 'Course_Strength'.

T-SQL: Создание таблицы типа «родитель-потомок» с помощью T-SQL

Родительская таблица: Давайте вспомним, что у нас уже есть родительская таблица с именем «Course». Course_ID и Course_name — это два столбца, причем Course_ID является первичным ключом.

Дочерняя таблица: Нам нужно создать вторую таблицу в качестве дочерней таблицы с именем 'Course_Strength_TSQL'. Ее столбцами будут 'Course_ID' и 'Course_Strength'. Однако 'Course_ID' будет внешним ключом.

Ниже приведён синтаксис для создать таблицу с использованием ИНОСТРАННОГО КЛЮЧА.

Синтаксис:

CREATE TABLE childTable
(
  column_1 datatype [ NULL |NOT NULL ],
  column_2 datatype [ NULL |NOT NULL ],
  ...

  CONSTRAINT fkey_name
    FOREIGN KEY (child_column1, child_column2, ... child_column_n)
    REFERENCES parentTable (parent_column1, parent_column2, ... parent_column_n)
    [ ON DELETE { NO ACTION |CASCADE |SET NULL |SET DEFAULT } ]
    [ ON UPDATE { NO ACTION |CASCADE |SET NULL |SET DEFAULT } ] 
);

Вот описание вышеуказанных параметров:

• childTable — это имя таблицы, которую необходимо создать.
• column_1, column_2 — это столбцы, которые будут добавлены в таблицу.
• fkey_name — это имя создаваемого ограничения внешнего ключа.
• child_column1, child_column2 … child_column_n — это столбцы дочерней таблицы, которые ссылаются на первичный ключ в родительской таблице.
• parentTable — это имя родительской таблицы, ключ которой используется в дочерней таблице.
• parent_column1, parent_column2 … parent_column_n — это столбцы, составляющие первичный ключ родительской таблицы.
• Параметр ON DELETE является необязательным и определяет, что произойдет с данными дочерних элементов после удаления данных родительского элемента. Возможные значения: NO ACTION, SET NULL, CASCADE или SET DEFAULT.
• Параметр ON UPDATE является необязательным и определяет, что происходит с данными дочерних элементов после обновления данных родительского элемента. Возможные значения: NO ACTION, SET NULL, CASCADE или SET DEFAULT.
• «БЕЗ ДЕЙСТВИЙ» означает, что после обновления или удаления родительских данных с данными дочерних элементов ничего не происходит.
• CASCADE означает, что данные дочернего элемента удаляются или обновляются после удаления или обновления данных родительского элемента.
• SET NULL означает, что данные дочернего элемента устанавливаются в значение null после обновления или удаления данных родительского элемента.
• Функция SET DEFAULT означает, что после обновления или удаления родительских данных дочерним данным присваивается значение по умолчанию.

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

Пример использования внешнего ключа в SQL

Запрос:

CREATE TABLE Course_Strength_TSQL
(
Course_ID Int,
Course_Strength Varchar(20) 
CONSTRAINT FK FOREIGN KEY (Course_ID)
REFERENCES COURSE (Course_ID)	
)

Шаг 1) Выполните запрос, нажав кнопку «Выполнить».

Результат: Мы установили связь «родитель-потомок» между 'Course' и 'Course_Strength_TSQL'.

Использование ИЗМЕНИТЬ ТАБЛИЦУ

Теперь мы научимся добавлять внешний ключ в SQL Server к уже существующей таблице с помощью оператора ALTER TABLE. Мы будем использовать синтаксис, приведенный ниже:

ALTER TABLE childTable
ADD CONSTRAINT fkey_name
    FOREIGN KEY (child_column1, child_column2, ... child_column_n)
    REFERENCES parentTable (parent_column1, parent_column2, ... parent_column_n);

Вот описание параметров, использованных выше:

• childTable — это имя таблицы, которую необходимо создать.
• column_1, column_2 — это столбцы, которые будут добавлены в таблицу.
• fkey_name — это имя создаваемого ограничения внешнего ключа.
• child_column1, child_column2 … child_column_n — это столбцы дочерней таблицы, которые ссылаются на первичный ключ в родительской таблице.
• parentTable — это имя родительской таблицы, ключ которой используется в дочерней таблице.
• parent_column1, parent_column2 … parent_column_n — это столбцы, составляющие первичный ключ родительской таблицы.

Пример команды ALTER TABLE add foreign key:

ALTER TABLE department
ADD CONSTRAINT fkey_student_admission
    FOREIGN KEY (admission)
    REFERENCES students (admission);

Мы создали внешний ключ с именем fkey_student_admission в таблице отдела. Этот внешний ключ ссылается на столбец приема в таблице студентов.

Пример запроса FOREIGN KEY

Для начала давайте посмотрим на данные нашей родительской таблицы, КОНЕЧНО.

Запрос:

SELECT * from COURSE;

Теперь давайте вставим несколько строк в дочернюю таблицу 'Course_Strength_TSQL'. Мы попробуем вставить два типа строк:

• Первый тип, для которого Course_Id в дочерней таблице существует в Course_Id родительской таблицы, то есть Course_Id = 1 и 2.
• Второй тип, для которого Course_Id в дочерней таблице отсутствует в Course_Id родительской таблицы, то есть Course_Id = 5.

Запрос:

Insert into COURSE_STRENGTH values (1,'SQL');
Insert into COURSE_STRENGTH values (2,'Python');
Insert into COURSE_STRENGTH values (5,'PERL');

Результат: Давайте вместе выполним запрос, чтобы увидеть наши родительские и дочерние таблицы.

Строки с Course_ID 1 и 2 существуют в таблице Course_Strength. Однако Course_ID 5 является исключением, поскольку для него нет соответствующей строки в родительской таблице.

Часто задаваемые вопросы (FAQ)