Учебники

10) SQL Server FOREIGN KEY

Март 23, 2020

Что такое внешний ключ?

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

Правила для внешнего ключа

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

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

Как работает внешний ключ

В этом уроке вы узнаете

Как создать внешний ключ

Мы можем создать Foreign Key в 2 способами:

  1. SQL Server Management Studio
  2. T-SQL

SQL Server Management Studio

Родительская таблица. Скажем, у нас есть родительская таблица «Курс». 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’ и нажмите OK.

Результат: мы установили отношения между родителями и детьми между «Course» и «Course_strength».

T-SQL: создайте таблицу Parent-child, используя T-SQL

Родительская таблица: Пересмотрим, у нас есть родительская таблица с именем таблицы «Курс».

Course_ID и Course_name — это два столбца с Course_Id в качестве первичного ключа.

Дочерняя таблица: нам нужно создать вторую таблицу как дочернюю таблицу с именем «Course_Strength_TSQL».

«Course_ID» и «Course_Strength» как два столбца для дочерней таблицы Course_Strength_TSQL. ‘ Однако ‘Course_ID’ должен быть внешним ключом.

Ниже приведен синтаксис для создания таблицы с FOREIGN KEY

Синтаксис: 

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 — имя столбцов chidTable для ссылки на первичный ключ в parentTable.
  • parentTable — имя родительской таблицы, ключ которой должен указываться в дочерней таблице.
  • parent_column1, parent_column2, … parent_column3 — столбцы, составляющие первичный ключ родительской таблицы.
  • НА УДАЛЕНИИ. Необязательный параметр. Он указывает, что происходит с дочерними данными после удаления родительских данных. Некоторые из значений для этого параметра включают NO ACTION, SET NULL, CASCADE или SET DEFAULT.
  • ON UPDATE — необязательный параметр. Он указывает, что происходит с дочерними данными после обновления родительских данных. Некоторые из значений для этого параметра включают NO ACTION, SET NULL, CASCADE или SET DEFAULT.
  • NO ACTION- используется вместе с ON DELETE и ON UPDATE. Это означает, что ничего не произойдет с дочерними данными после обновления или удаления родительских данных.
  • CASCADE — используется вместе с ON DELETE и ON UPDATE. Дочерние данные будут удалены или обновлены после удаления или обновления родительских данных.
  • SET NULL — используется вместе с ON DELETE и ON UPDATE. Дочерний элемент будет установлен в нуль после того, как родительские данные были обновлены или удалены.
  • SET DEFAULT — используется вместе с ON DELETE и ON UPDATE. Дочерние данные будут установлены в значения по умолчанию после обновления или удаления родительских данных.

Давайте создадим таблицу с одним столбцом в качестве внешнего ключа.

Запрос: 

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».

Использование ALTER TABLE

Чтобы создать внешний ключ с помощью инструкции 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 — имя столбцов chidTable для ссылки на первичный ключ в parentTable.
  • parentTable — имя родительской таблицы, ключ которой должен указываться в дочерней таблице.
  • parent_column1, parent_column2, … parent_column3 — столбцы, составляющие первичный ключ родительской таблицы.

Например: 

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

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

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

Во-первых, давайте посмотрим наши данные родительской таблицы, курс.

Запрос: 

SELECT * from COURSE;

Теперь давайте вставим некоторую строку в дочернюю таблицу: ‘Course_strength_TSQL.’

Мы попытаемся вставить два типа строк.

  1. Первый тип, для которого Course_Id в дочерней таблице будет существовать в Course_Id родительской таблицы. т.е. Course_Id = 1 и 2
  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');

Результат: давайте запустим Query вместе, чтобы увидеть нашу таблицу Parent и Child

Строка с Course_ID 1 и 2 существует в таблице Course_strength. Принимая во внимание, что Course_ID 5 является исключением.

Резюме:

  • Каждое значение внешнего ключа должно быть частью первичного ключа других таблиц.
  • Внешний ключ может ссылаться на другой столбец в той же таблице. Эта ссылка известна как самостоятельная ссылка.
  • Вы можете создать внешний ключ, используя Create Table, Alter Table или SQL Server Management Studio.

 

164
Share: