Внешние ключи sql: примеры объединения таблиц, удаление ограничения foreigh key

Содержание

1.2.5. Первичный и внешний ключ

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

1.2.5. Первичный ключ

Мы уже достаточно много говорили про ключевые поля, но ни разу их не использовали. Самое интересное, что все работало. Это преимущество, а может недостаток базы данных Microsoft SQL Server и MS Access. В таблицах Paradox такой трюк не пройдет и без наличия ключевого поля таблица будет доступна только для чтения.

В какой-то степени ключи являются ограничениями, и их можно было рассматривать вместе с оператором CHECK, потому что объявление происходит схожим образом и даже используется оператор CONSTRAINT. Давайте посмотрим на этот процесс на примере. Для этого создадим таблицу из двух полей «guid» и «vcName». При этом поле «guid» устанавливается как первичный ключ:


CREATE TABLE Globally_Unique_Data
(
 guid uniqueidentifier DEFAULT NEWID(),
 vcName varchar(50),
 CONSTRAINT PK_guid PRIMARY KEY (Guid)
)

Самое вкусное здесь это строка CONSTRAINT. Как мы знаем, после этого ключевого слова идет название ограничения, и объявления ключа не является исключением. Для именования первичного ключа, я рекомендую использовать именование типа PK_имя, где имя – это имя поля, которое должно стать главным ключом. Сокращение PK происходит от Primary Key (первичный ключ).

После этого, вместо ключевого слова CHECK, которое мы использовали в ограничениях, стоит оператор PRIMARY KEY, Именно это указывает на то, что нам необходима не проверка, а первичный ключ. В скобках указывается одно, или несколько полей, которые будут составлять ключ.

Помните, что в ключевом поле не может быть одинакового значения у двух строк, в этом ограничение первичного ключа идентично ограничению уникальности. Это значит, что если сделать поле для хранения фамилии первичным ключом, то в такую таблицу нельзя будет записать двух Ивановых с разными именами. Это нарушает ограничение первичного ключа. Именно поэтому ключи являются ограничениями и объявляются также как и ограничение CHECK. Но это не верно только для первичных ключей и вторичных с уникальностью.

В данном примере, в качестве первичного ключа выступает поле типа uniqueidentifier (GUID). Значение по умолчанию для этого поля – результат выполнения серверной процедуры NEWID.

Внимание

Только один первичный ключ может быть создан для таблицы

Для простоты примеров, в качестве ключа желательно использовать численный тип и если позволяет база данных, то будет лучше, если он будет типа «autoincrement» (автоматически увеличивающееся/уменьшающееся число). В MS SQL Server таким полем является IDENTITY, а в MS Access это поле типа «счетчик».

Следующий пример показывает, как создать таблицу товаров, в которой в качестве первичного ключа выступает целочисленное поле с автоматическим увеличением:


CREATE TABLE Товары
(
  id int IDENTITY(1, 1),
  товар varchar(50),
  Цена money,
  Количество numeric(10, 2),
  CONSTRAINT PK_id PRIMARY KEY (id)
)

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

Первичный ключ может состоять из более, чем одной колонки. Следующий пример создает таблицу, в которой поля «id» и «Товар» образуют первичный ключ, а значит, будет создан индекс уникальности на оба поля:


CREATE TABLE Товары1
(
  id int IDENTITY(1, 1),
  Товар varchar(50),
  Цена money,
  Количество numeric(10, 2),
  CONSTRAINT PK_id PRIMARY KEY 
         (id, [Название товара])
)

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

Единственный недостаток первичного ключа из нескольких колонок – проблемы создания связей. Тут приходиться выкручиваться различными методами, но проблема все же решаема. Достаточно только ввести поле типа uniqueidentifier и производить связь по нему. Да, в этом случае у нас получаются уникальными первичный ключ и поле типа uniqueidentifier, но эта избыточность в результате не будет больше, чем та же таблица, где первичный ключ uniqueidentifier, а на поля, которые должны быть уникальными установлено ограничение уникальности. Что выбрать? Зависит от конкретной задачи и от того, с чем вам удобнее работать.

1.2.6. Внешний ключ

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

Names – содержит имена людей и состоит из полей идентификатора (ключевое поле), имя.

Phones – таблица телефонов, которая состоит из идентификатора (ключевое поле), внешний ключ для связи с таблицей names и строковое поле для хранения номера телефона.

У одного человека может быть несколько телефонов, поэтому мы разделили хранение данных в разные таблицы. На рисунке 1.4 визуально показана связь между двумя таблицами. Если вы уже работали со связанными таблицами, то этого для вас будет достаточно. Если вы слышите о связях впервые, то попробуем посмотреть на проблему поближе.

Рис. 1.4. Связь между таблицами

Для примера возьмем таблицу из трех человек. В таблице 1.3 показано содержимое таблицы «Names». Здесь всего три строки и у каждой свой уникальный главный ключ. Для уникальности, когда будем создавать таблицу, сделаем ключ автоматически увеличиваемым полем.

Таблица 1.3 Содержимое таблицы Names

Главный ключ	Фамилия
1	Петров
2	Иванов
3	Сидоров

Таблица 1.4. Содержимое таблицы Phones

Главный ключ	Внешний ключ	Телефон
1	1	678689687
2	1	2324234
3	2	324234
4	3	32432423
5	3	2
6	3	12312312

В таблице 1.4 находится пять номеров телефонов. В поле главный ключ также уникальный главный ключ, которой также можно сделать автоматически увеличиваемым. Вторичный ключ – это связь с главным ключом таблицы Names. Как работает эта связь? У Петрова в таблице Names в качестве главного ключа стоит число 1. В таблице Phones во вторичном ключе ищем число 1 и получаем номера телефонов Петрова. То же самое и с остальными записями. Визуально связь можно увидеть на рисунке 1.5.

Рис. 1.5. Связь между строками табли

Такое хранение данных очень удобно. Если бы не было возможности создавать связанные таблицы, то в таблице Names пришлось бы забивать все номера телефонов в одно поле. Это неудобно с точки зрения использования, поддержки и поиска данных.

Можно создать в таблице несколько полей Names, но возникает вопрос – сколько. У одного человека может быть только 1 телефон, а у меня, например, их 3, не считая рабочих. Большое количество полей приводит к избыточности данных.

Можно для каждого телефона в таблице Names заводить отдельную строку с фамилией, но это легко только для такого простого примера, когда нужно вводить только фамилию и легко можно внести несколько записей для Петрова с несколькими номерами телефонов. А если полей будет 10 или 20?
Итак, создание двух таблиц связанных внешним ключом можно увидеть в листинге 1.6.

Листинг 1.6. Создание таблиц связанных внешним ключом


CREATE TABLE Names
(
 idName int IDENTITY(1,1), 
 vcName varchar(50),
 CONSTRAINT PK_guid PRIMARY KEY (idName),
)

CREATE TABLE Phones
(
 idPhone int IDENTITY(1,1), 
 idName int, 
 vcPhone varchar(10), 
 CONSTRAINT PK_idPhone PRIMARY KEY (idPhone),
 CONSTRAINT FK_idName FOREIGN KEY (idName)
   REFERENCES Names (idName)
)

Внимательно изучите содержимое листинга. Он достаточно интересен, потому что использует некоторые операторы, которые мы уже рассмотрели и дополнительный пример не помешает. Для обеих таблиц создается ключевое поле, которое стоит первым, имеет тип int и автоматически увеличивается, начиная с 1 с приращением в единицу. Ключевое поле делается главным ключом с помощью ограничение CONSTRAINT.

В описании таблицы Phones последняя строка содержит новое для нас объявление, а именно – объявление внешнего ключа с помощью оператора FOREIGN KEY. Как видите, это тоже ограничение и чуть позже вы увидите почему. В скобках указывается поле таблицы, которое должно быть связано с другой таблицей. После этого идет ключевое слово REFERENCES (ссылка), имя таблицы, с которой должна быть связь (Names) и в скобках имя поля («idName»). Таким образом, мы навели связь, которая отображена на рисунке 1.4.

Внимание!

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

Теперь, если можно наполнять таблицы данными. Следующие три команды добавляют три фамилии, которые мы видели в таблице 1.3:


INSERT INTO Names(vcName) 
VALUES('Петров')

INSERT INTO Names(vcName) 
VALUES('Иванов')

INSERT INTO Names(vcName) 
VALUES('Сидоров')

Если вы уже работали с SQL то сможете добавить записи и для таблицы телефонов. Я опущу эти команды, а вы можете увидеть их в файле foreign_keys.sql директории Chapter1 на компакт диске.

Наша задача сейчас увидеть, в чем заключаются ограничительные действия внешнего ключа, давайте разберемся. Мы указали явную связь между двумя полями в разных таблицах. Если попытаться добавить в таблицу телефонов запись с идентификатором в поле «idName», не существующим в одноименном поле (имя можно было сделать и другим) таблице с фамилиями, то произойдет ошибка. Это нарушит связь между двумя таблицами, а ограничение внешнего ключа не позволит существовать записям без связи.

Ограничение действует и при изменении или удалении записей. Например, если попытаться удалить строку с фамилией Петров, то произойдет ошибка ограничения внешнего ключа. Нельзя удалять записи, для которых существуют внешне связанные строки. Для начала, нужно удалить все телефоны для данной записи и только после этого будет возможно удаление самой строки с фамилией Петров.

Во время создания внешнего ключа, можно указать ON DELETE CASCADE или ON UPDATE CASCADE. В этом случае, если удалить запись Петрова из таблице Names или изменить идентификатор, то все записи в таблице Phones, связанные со строкой Петрова будут автоматически обновлены. Никогда. Нет, нужно написать большими буквами: НИКОГДА не делайте этого. Все должно удаляться или изменяться вручную. Если пользователь случайно удалит запись из таблицы Names, то удаляться и соответствующие телефоны. Смысл тогда создавать внешний ключ, если половина его ограничительных возможностей исчезает! Все необходимо делать только вручную, а идентификаторы изменять не рекомендуется вообще никогда.

Удаление самих таблиц также должно начинаться с подчиненной таблицы, то есть с Phones, и только потом можно удалить главную таблицу Names.

Напоследок покажу, как красиво получить соответствие имен и телефонов из двух таблиц:


SELECT vcName, vcPhone 
FROM Names, Phones
WHERE Names.idName=Phones.idName

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

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

Сама таблица также может иметь максимум 253 внешних ключей. Внешние ключи в таблице встречаются реже, в основном не более 3. Чаще всего в таблице может быть много ссылок на другие таблицы.

Внешний ключ может ссылаться на ту же таблицу, в которой он создается. Например, у вас есть таблица должностей в организации, как показано в таблице 1.5. Таблица состоит из трех полей: первичный ключ, внешний ключ и наименование должности. В любой организации может быть множество должностей, но вполне логичным будет в одной таблице отобразить их названия и структуру подчинения. Для этого внешний ключ нужно связать с первичным ключом таблицы должностей.

Таблица 1.5. Таблица с внутренней связью

Главный ключ	Внешний ключ	Должность
1	NULL	Генеральный директор
2	1	Коммерческий директор
3	1	Директор по общим вопросам
4	2	Начальник отдела снабжения
5	2	Начальник отдела сбыта
6	3	Начальник отдела кадров

В результате мы получаем, что у генерального директора внешний ключ нулевой, т.е. эта должность стоит во главе всех остальных. У коммерческого директора и директора по общим вопросам внешний ключ указывает на строку генерального директора. Это значит, что эти две должности подчиняются непосредственно генеральному директору. И так далее.

Посмотрим, как можно создать все это в виде SQL запроса:


CREATE TABLE Positions
(
 idPosition int IDENTITY(1,1), 
 idParentPosition int, 
 vcName varchar(30),  
 CONSTRAINT PK_idPosition PRIMARY KEY (idPosition),
 CONSTRAINT FK_idParentPosition FOREIGN KEY (idParentPosition)
   REFERENCES Positions (idPosition)
)

Как видите, внешний ключ просто ссылается на ту же таблицу, которую мы создаем. На компакт диске, в директории Chapter1 можно увидеть в файле foreign_keys_to_self.sql пример создания этой таблицы, наполнения его данными и отображения должностей с учетом их подчинения. В следующей главе мы рассмотрим возможность работы с такими таблицами более подробно.

Отношение один к одному

Пока что мы рассмотрели классическую связь, когда одной строке основной таблицы данных соответствует одна строка из связанной таблицы. Такая связь называется один ко многим. Но существуют и другие связи, и сейчас мы рассмотрим еще одну – один к одному, когда одна запись основной таблице связана с одной записью другой. Чтобы это реализовать, достаточно связать первичные ключи обеих таблиц. Так как первичные ключи не могут повторяться, то в обеих таблицах связанными могут быть только одна строка.

Следующий пример создает две таблицы, у которых создана связь между первичными ключами:

 
CREATE TABLE Names
(
 idName uniqueidentifier DEFAULT NEWID(), 
 vcName varchar(50),
 CONSTRAINT PK_guid PRIMARY KEY (idName)
)

CREATE TABLE Phones
(
 idPhone uniqueidentifier DEFAULT NEWID(),
 vcPhone varchar(10), 
 CONSTRAINT PK_idPhone PRIMARY KEY (idPhone),
 CONSTRAINT FK_idPhone FOREIGN KEY (idPhone)
   REFERENCES Names (idName)
)

Внешний ключ нужен только у одной из таблиц. Так как связь идет один к одному, то не имеет значения, в какой таблице создать его.

Многие ко многим

Самая сложная связь – многие ко многим, когда много записей из одной таблицы соответствует многим записям из другой таблицы. Чтобы такое реализовать, двух таблиц мало, необходимо три таблицы.

Для начала нужно понять, когда может использоваться связь многие ко многим? Допустим, что у вас есть две таблицы: список жителей дома и список номеров телефона. В одной квартире может быть более одного номера, а значит, одной фамилии может принадлежать два телефона. Получается, связь один ко многим. С другой стороны, в одной квартире может быть две семьи (коммунальная квартира или просто квартиросъемщик, который пользуется телефоном владельца), а значит, связь между телефоном и жителем тоже один ко многим. И самый сложный вариант – в коммунальной квартире находиться два телефона. В этом случае обоими номерами пользуются несколько жителей квартире. Вот и получается, что «много» семей может пользоваться «многими» телефонами (связь многие ко многим).

Как реализовать связь многие ко многим? На первый взгляд, в реляционной модели это невозможно. Лет 10 назад я долго искал разные варианты и в результате просто создавал одну таблицу, которая была переполнена избыточностью данных. Но однажды, мне досталась одна задача, благодаря которой уже из условия на поверхность вышло отличное решение – нужно создать две таблицы жителей квартир и телефонов и реализовать в них только первичный ключ. Внешние ключи в этой таблице не нужны. А вот связь между таблицами должна быть через третью, связующую таблицу. На первый взгляд это сложно и не понятно, но один раз разобравшись с этим методом, вы увидите всю мощь этого решения.

В таблицах 1.6 и 1.7 показаны примеры таблиц фамилий и телефонов соответственно. А в таблице 1.8 показана связующая таблица.

Таблица 1.6. Таблица фамилий

Ключ	Имя
1	Иванов
2	Петров
3	Сидоров

Таблица 1.7. Таблица телефонов

Ключ	Телефон
1	68768678
2	658756785
3	567575677

Таблица 1.8. Таблица телефонов

Ключ	Связь с именем	Связь с телефоном
1	1	1
2	1	2
3	2	1
4	2	3
5	3	3

Давайте теперь посмотрим, какая будет логика поиска данных при связи многие ко многим. Допустим, что нам нужно найти все телефоны, которые принадлежат Иванову. У Иванова первичный ключ равен 1. Находим в связующей таблице все записи, у которых поле «Связь с именем» равно 1. Это будут записи 1 и 2. В этих записях в поле «Связь с телефоном» находятся идентификаторы 1 и 2 соответственно, а значит, Иванову принадлежат номера из таблицы телефонов, которые расположены в строках 1 и 2.

Теперь решим обратную задачу – определим, кто имеет доступ к номеру телефона 567575677. Этот номер в таблице телефонов имеет ключ 3. Ищем все записи в связующей таблице, где в поле «Связь с телефоном» равно 3. Это записи с номерами 4 и 5, которые в поле «Связь с именем» содержат значения 2 и 3 соответственно. Если теперь посмотреть на таблицу фамилий, то вы увидите под номерами 2 и 3 Петрова и Сидорова. Значит, именно эти два жителя пользуются телефоном с номером 567575677.

Просмотрите все три таблицы и убедитесь, что вы поняли, какие номера телефонов принадлежат каким жителям и наоборот. Если вы увидите эту связь, то поймете, что она проста, как три копейки и сможете быстро реализовать ее в своих проектах.


CREATE TABLE Names
(
 idName uniqueidentifier DEFAULT NEWID(), 
 vcName varchar(50),
 CONSTRAINT PK_guid PRIMARY KEY (idName)
)

CREATE TABLE Phones
(
 idPhone uniqueidentifier DEFAULT NEWID(),
 vcPhone varchar(10), 
 CONSTRAINT PK_idPhone PRIMARY KEY (idPhone)
)

CREATE TABLE LinkTable
(
 idLinkTable uniqueidentifier DEFAULT NEWID(),
 idName uniqueidentifier, 
 idPhone uniqueidentifier, 

 CONSTRAINT PK_idLinkTable PRIMARY KEY (idLinkTable),

 CONSTRAINT FK_idPhone FOREIGN KEY (idPhone)
   REFERENCES Phones (idPhone),
 CONSTRAINT FK_idName FOREIGN KEY (idName)
   REFERENCES Names (idName)
)

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

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

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

На данном этапе мы ограничиваемся только созданием таблиц, а в разделе 2.8 мы вернемся к этой теме и научимся и научимся работать со связанными таблицами. Работа со связью один к одному и один ко многим отличается не сильно, потому что в этой схеме участвует только две таблицы. Связь многие ко многим немного сложнее из-за связующей таблицы, поэтому мы ее рассмотрим отдельно в разделе 2.27.

Взаимные блокировки и внешние ключи в SQL Server / Блог компании OTUS / Хабр

Введение

В реляционных базах данных внешние ключи (foreign key) используются для обеспечения целостности связей между таблицами. Простыми словами, внешний ключ — это столбец (или несколько столбцов), ссылающийся на первичный ключ другой таблицы. Таблица с внешним ключом называется дочерней, а с первичным — родительской. При вставке строки в дочернюю таблицу проверяется наличие значения внешнего ключа в родительской таблице. Эти дополнительные операции иногда могут вызывать проблемы с блокировками и приводить к взаимоблокировкам. В этой статье мы изучим, почему это происходит, и как решать подобные проблемы.

Будем использовать две таблицы: Department (Отдел) и Employee (Сотрудник). Столбец DepId в таблице Employee определен как внешний ключ, поэтому значения этого столбца будут проверяться на наличие соответствующих значений в столбце DepartmentId таблицы Department.

CREATE TABLE [Department](
  [DepartmentId] [int] NOT NULL PRIMARY KEY,
  [DepartmentName] [varchar](10) NULL,
)
GO
 
CREATE TABLE [dbo].[Employee](
  [EmployeeId] [int] NOT NULL PRIMARY KEY,
  [FirstName] [varchar](50) NULL,
  [LastName] [varchar](50) NULL,
  [DepID] [int] NOT NULL,
  [IsActive] [bit] NULL
) ON [PRIMARY]
 
 
ALTER TABLE [dbo].[Employee]  WITH CHECK ADD FOREIGN KEY([DepID])
REFERENCES [dbo].[Department]([DepartmentId])
GO
CREATE NONCLUSTERED INDEX [IX_DepId] ON Employee 
(
  [DepID] ASC
)

Что происходит за кулисами INSERT

Исследуем, какие операции выполняются при вставке данных в дочернюю таблицу (Employee).

Сначала вставим строку в родительскую таблицу (Department).

INSERT INTO Department (DepartmentId ,DepartmentName)
VALUES (1,'Sales')

Перед выполнением следующего запроса включим отображение фактического плана выполнения и вставим строку в таблицу Employee (дочернюю).

INSERT INTO Employee (EmployeeId,FirstName,LastName,DepID,IsActive)
VALUES(1,'Brandon','Lord',1,0)

Clustered Index Insert вставляет данные в кластерный индекс, а также обновляет некластерные индексы. Если внимательно посмотреть на этот оператор, то можно заметить, что для него не указано имя объекта. Причина этого как раз в том, что при вставке данных в кластерный индекс таблицы Employee, эти данные одновременно добавляются в некластерный индекс. Эти два индекса можно увидеть во всплывающей подсказке оператора Clustered Index Insert.

Clustered Index Seek проверяет существование значения внешнего ключа в родительской таблице.

Nested Loops сравнивает вставленные значения внешних ключей со значениями, возвращаемые оператором Clustered Index Seek. В результате этого сравнения на выходе получается результат, который указывает, существует значение в родительской таблице или нет.

Assert оценивает результат оператора Nested Loops. Если Nested Loops возвращает NULL, то результат Assert будет ноль, и запрос вернет ошибку. В противном случае операция INSERT выполнится успешно.

Взаимные блокировки

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

DECLARE @Counter AS INT=1
WHILE @Counter <=10000
BEGIN
SET @Counter = @Counter+1
INSERT INTO Department VALUES(@Counter,CONCAT('Column' , @Counter))
END

После этого создадим глобальную временную таблицу, которая поможет со вставкой строк в Employee.

CREATE TABLE ##Emp (Id INT,EmpFname VARCHAR(50),EmpLname VARCHAR(50),DepId INT,IsActive bit)
 
DECL

FOREIGN KEY, видео урок 13

Содержание статьи:

Внешние ключи (FK) это

Внешние ключи (FK) реляционной базы данных это столбец, а может сочетание столбцов, используемые для принудительного установления связи между данными в двух таблицах. Внешний ключ можно создать, определив ограничение FOREIGN KEY при создании или изменении таблицы.

Внешний ключ и родительский ключ

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

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

Многостолбцовые внешние ключи

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

Смысл внешнего и родительского ключей

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

Каждое значение (каждая строка) внешнего ключа должно недвусмысленно ссылаться к одному и только этому значению (строке) родительского (первичного) ключа. Если это так, то фактически ваша система, как говорится, будет в состоянии справочной целостности.
Понятно, что каждое значение во внешнем ключе должно быть представлено один, и только один раз, в родительском ключе.

Видео урок: Внешние ключи SQL

Полезные ссылки

Учебник по базам данных тут: http://www.sql.ru/docs/sql/u_sql/index.shtml

Все видео уроки SQL

sql — В чем практическая польза FOREIGN KEY в таблицах MySQL?

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

Когда одно поле в таблице ссылается на другое, оно называется внешним ключом, а поле на которое оно ссылается, называется родительским ключом. Имена внешнего ключа и родительского ключа не обязательно должны быть одинаковыми. Внешний ключ может иметь любое число полей, которые все обрабатываются как единый модуль. Внешний ключ и родительский ключ, на который он ссылается, должны иметь одинаковый номер и тип поля, и находиться в одинаковом порядке. Когда поле является внешним ключом, оно определенным образом связано с таблицей, на которую он ссылается. Каждое значение, (каждая строка ) внешнего ключа должно недвусмысленно ссылаться к одному и только этому значению (строке) родительского ключа. Если это условие соблюдается, то база данных находится в состоянии ссылочной целостности.

SQL поддерживает ссылочную целостность с ограничением FOREIGN KEY. Эта функция должна ограничивать значения, которые можно ввести в базу данных, чтобы заставить внешний ключ и родительский ключ соответствовать принципу ссылочной целостности. Одно из действий ограничения FOREIGN KEY — это отбрасывание значений для полей, ограниченных как внешний ключ, который еще не представлен в родительском ключе. Это ограничение также воздействует на способность изменять или удалять значения родительского ключа

Ограничение FOREIGN KEY используется в команде CREATE TABLE (или ALTER TABLE (предназначена для модификации структуры таблицы), содержащей поле, которое объявлено внешним ключом. Родительскому ключу дается имя, на которое имеется ссылка внутри ограничения FOREIGN KEY.

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

Пример: есть две таблицы users и emails:

CREATE TABLE users (
  user_id int(11) NOT NULL AUTO_INCREMENT,
  user_name varchar(50) DEFAULT NULL,
  PRIMARY KEY (user_id)
)

CREATE TABLE sys.emails (
  email_id int(11) NOT NULL AUTO_INCREMENT,
  email_address varchar(100) NOT NULL,
  user_id int(11) NOT NULL,
  PRIMARY KEY (email_id)
)

Между ними есть зависимость в виде users.user_id и emails.user_id, и мы хотим что в поле emails.user_id не смогло записываться ничего кроме значения с таблицы users столбец user_id, для этого и создаем ограничение в виде внешнего ключа:

ALTER TABLE emails 
  ADD FOREIGN KEY (user_id) 
  REFERENCES users (user_id) 
  ON DELETE CASCADE 
  ON UPDATE NO ACTION;

Допустим если мы удаляем пользователя то и автоматически удаляем все записи связанные с ним, в данном случае его адрес электронной почты.

Что такое внешние ключи sql. Что это

ПРИМЕНЯЕТСЯ К:
SQL Server (начиная с 2016)База данных SQL AzureХранилище данных SQL AzureParallel Data Warehouse

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

Эта тема описана в следующих разделах.

Ограничения первичного ключа

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

Связанные задачи

Обычно в таблице есть столбец или сочетание столбцов, содержащих значения, уникально определяющие каждую строку таблицы. Этот столбец, или столбцы, называются первичным ключом (PK) таблицы и обеспечивает целостность сущности таблицы. Ограничения первичного ключа часто определяются в столбце идентификаторов, поскольку гарантируют уникальность данных.

При задании ограничения первичного ключа для таблицы компонента Компонент Database Engine гарантирует уникальность данных путем автоматического создания уникального индекса для первичных ключевых столбцов. Этот индекс также обеспечивает быстрый доступ к данным при использовании первичного ключа в запросах. Если ограничение первичного ключа задано более чем для одного столбца, то значения могут дублироваться в пределах одного столбца, но каждое сочетание значений всех столбцов в определении ограничения первичного ключа должно быть уникальным.

Как показано на следующем рисунке, столбцы ProductID
и VendorID
в таблице Purchasing.ProductVendor
формируют составное ограничение первичного ключа для данной таблицы. При этом гарантируется, что каждая строка в таблице ProductVendor
имеет уникальное сочетание значений ProductID
и VendorID
. Это предотвращает вставку повторяющихся строк.

В таблице возможно наличие только одного ограничения по первичному ключу.

Первичный ключ не может включать больше 16 столбцов, а общая длина ключа не может превышать 900 байт.

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

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

Все столбцы с ограничением первичного ключа должны быть определены как не допускающие значения NULL. Если допустимость значения NULL не указана, то все столбцы c ограничением первичного ключа устанавливаются как не допускающие значения NULL.

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

Внешний ключ (FK) — это столбец или сочетание столбцов, которое применяется для принудительного установления связи между данными в двух таблицах с целью контроля данных, которые могут храниться в таблице внешнего ключа. Если один или несколько столбцов, в которых находится первичный ключ для одной таблицы, упоминается в одном или нескольких столбцах другой таблицы, то в ссылке внешнего ключа создается связь между двумя таблицами. Этот столбец становится внешним ключом во второй таблице.

Например, таблица Sales.SalesOrderHeader
связана с таблицей Sales.SalesPerson
с помощью внешнего ключа, так как существует логическая связь между заказами на продажу и менеджерами по продажам. Столбец SalesPersonID
в таблице Sales.SalesOrderHeader
соответствует первичному ключевому столбцу в таблице SalesPerson
. Столбец SalesPersonID
в таблице Sales.SalesOrderHeader
является внешним ключом для таблицы SalesPerson
. С помощью установления данной связи по внешнему ключу значение для SalesPersonID
не может быть вставлено в таблицу SalesOrderHeader
, если оно в настоящий момент не содержится в таблице SalesPerson
.

Максимальное количество таблиц и столбцов, на которые может ссылаться таблица в качестве внешних ключей (исходящих ссылок), равно 253. SQL Server 2016 увеличивает ограничение на количество других таблиц и столбцов, которые могут ссылаться на столбцы в одной таблице (входящие ссылки), с 253 до 10 000. (Требуется уровень совместимости не менее 130.) Увеличение имеет следующие ограничения:

Превышение 253 ссылок на внешние ключи поддерживается только для операций DML DELETE. Операции UPDATE и MERGE не поддерживаются.

Превышение 253 ссылок на внешние ключи в настоящее время недоступно для индексов columnstore, оптимизированных для памяти таблиц, базы данных Stretch или секционированных таблиц внешнего ключа.

Индексы в ограничениях внешнего ключа

В отличие от ограничений первичного ключа, при создании ограничения внешнего ключа соответствующий индекс автоматически не создается. Тем не менее, часто возникает необходимость создания индекса для внешнего ключа вручную по следующим причинам:

Столбцы внешнего ключа часто используются в критериях соединения при совместном применении в запросах данных из связанных таблиц. Это реализуется путем сопоставления столбца или столбцов в ограничении внешнего ключа в одной таблице с одним или несколькими столбцами первичного или уникального ключа в другой таблице. Индекс позволяет компоненту Компонент Database Engine быстро находить связанные данные в таблице внешних ключей. Впрочем, создание индекса не является обязательным. Данные из двух связанных таблиц можно комбинировать, даже если между таблицами не определены ограничения первичного ключа или внешнего ключа, но связь по внешнему ключу между двумя таблицами показывает, что эти две таблицы оптимизированы для совместного применения в запросе, где ключи используются в качестве критериев.

С помощью ограничений внешнего ключа в связанных таблицах проверяются изменения ограничений первичного ключа.

Ссылочная целостность

Главная задача ограничения внешнего ключа состоит в управлении данными, которые могут быть сохранены в таблице внешнего ключа, но это ограничение контролирует также изменение данных в таблице первичного ключа. Например, при удалении строки для менеджера по продажам из таблицы Sales.SalesPerson
, идентификатор которого используется в заказах на продажу в таблице Sales.SalesOrderHeader
, ссылочная целостность двух таблиц будет нарушена. Заказы на продажу удаленного менеджера в таблице SalesOrderHeader
станут недействительными без связи с данными в таблице SalesPerson
.

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

Каскадная ссылочная целостность

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

NO ACTION
Компонент Компонент Database Engine формирует ошибку, после чего выполняется откат операции удаления или обновления строки в родительской таблице.

CASCADE
Соответствующие строки обновляются или удаляются из ссылающейся таблицы, если данная строка обновляется или удаляется из родительской таблицы. Значение CASCADE не может быть указано, если столбец типа timestamp
является частью внешнего или ссылочного ключа. Действие ON DELETE CASCADE не может быть указано в таблице, для которой определен триггер INSTEAD OF DELETE. Предложение ON UPDATE CASCADE не может быть задано применительно к таблицам, для которых определены триггеры INSTEAD OF UPDATE.

SET NULL
Всем значениям, составляющим внешний ключ, присваивается значение NULL, когда обновляется или удаляется соответствующая строка в родительской таблице. Для выполнения этого ограничения внешние ключевые столбцы должны допускать значения NULL. Не может быть задано применительно к таблицам, для которых определены триггеры INSTEAD OF UPDATE.

SET DEFAULT
Все значения, составляющие внешний ключ, при удалении или обновлении соответствующей строки родительской таблицы устанавливаются в значение по умолчанию. Для выполнения этого ограничения все внешние ключевые столбцы должны иметь определения по умолчанию. Если столбец допускает значения NULL и значение по умолчанию явно не определено, значением столбца по умолчанию становится NULL. Не может быть задано применительно к таблицам, для которых определены триггеры INSTEAD OF UPDATE.

Ключевые слова CASCADE, SET NULL, SET DEFAULT и NO ACTION можно сочетать в таблицах, имеющих взаимные ссылочные связи. Если компонент Компонент Database Engine обнаруживает ключевое слово NO ACTION, оно остановит и произведет откат связанных операций CASCADE, SET NULL и SET DEFAULT. Если инструкция DELETE содержит сочетание ключевых слов CASCADE, SET NULL, SET DEFAULT и NO ACTION, то все операции CASCADE, SET NULL и SET DEFAULT выполняются перед поиском компонентом Компонент Database Engine операции NO ACTION.

Триггеры и каскадные ссылочные действия

Каскадные ссылочные действия запускают триггеры AFTER UPDATE или AFTER DELETE следующим образом:

Все каскадные ссылочные действия, прямо вызванные исходными инструкциями DELETE или UPDATE, выполняются первыми.

Если есть какие-либо триггеры AFTER, определенные для измененных таблиц, эти триггеры запускаются после выполнения всех каскадных действий. Эти триггеры запускаются в порядке, обратном каскадным действиям. Если для одной таблицы определены несколько триггеров, они запускаются в случайном порядке, если только не указаны выделенные первый и последний триггеры таблицы. Этот порядок определяется процедурой
.

Если последовательности каскадных действий происходят из таблицы, которая была непосредственной целью действий DELETE или UPDATE, порядок запуска триггеров этими последовательностями действий не определен. Однако одна последовательность действий всегда запускает все свои триггеры до того, как это начнет делать следующая.

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

Если один из предыдущих триггеров выполняет операции DELETE или UPDATE над другими таблицами, эти операции могут вызывать собственные последовательности каскадных действий. Эти вторичные последовательности действий обрабатываются для каждой операции DELETE или UPDATE после выполнения всех триггеров первичных последовательностей действий. Этот процесс может рекурсивно повторяться для последующих операций DELETE или UPDATE.

Выполнение операций CREATE, ALTER, DELETE или других операций языка DDL внутри триггеров может привести к запуску триггеров DDL. Это может привести к дальнейшим операциям DELETE или UPDATE, которые начнут дополнительные последовательности каскадных действий и запустят свои триггеры.

Если в любой конкретной последовательности каскадных ссылочных действий произойдет ошибка, в этой последовательности не будут запущены никакие триггеры AFTER, а для операций DELETE или UPDATE, создаваемых этой последовательностью, будет выполнен откат.

У таблицы, для которой определен триггер INSTEAD OF, может также быть предложение REFERENCES, указывающее конкретное каскадное действие. Однако триггер AFTER целевой таблицы каскадного действия может выполнить инструкцию INSERT, UPDATE или DELETE для другой таблицы или представления, которое запустит триггер INSTEAD OF для этого объекта.

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

1.2.5. Первичный ключ

CREATE TABLE Globally_Unique_Data
(guid uniqueidentifier DEFAULT NEWID(),
vcName varchar(50),
CONSTRAINT PK_guid PRIMARY KEY (Guid))

Внимание

Только один первичный ключ может быть создан для таблицы

CREATE TABLE Товары
(id int IDENTITY(1, 1),
товар varchar(50),
Цена money,
Количество numeric(10, 2),
CONSTRAINT PK_id PRIMARY KEY (id))

CREATE TABLE Товары1
(id int IDENTITY(1, 1),
Товар varchar(50),
Цена money,
Количество numeric(10, 2),
CONSTRAINT PK_id PRIMARY KEY
(id, [Название товара]))

1.2.6. Внешний ключ

Names – содержит имена людей и состоит из полей идентификатора (ключевое поле), имя.
Phones – таблица телефонов, которая состоит из идентификатора (ключевое поле), внешний ключ для связи с таблицей names и строковое поле для хранения номера телефона.

Таблица 1.3 Содержимое таблицы Names

Таблица 1.4. Содержимое таблицы Phones

Листинг 1.6. Создание таблиц связанных внешним ключом

CREATE TABLE Names
(idName int IDENTITY(1,1),
vcName varchar(50),
CONSTRAINT PK_guid PRIMARY KEY (idName),)
CREATE TABLE Phones
(idPhone int IDENTITY(1,1),
idName int,
vcPhone varchar(10),
CONSTRAINT PK_idPhone PRIMARY KEY (idPhone),
CONSTRAINT FK_idName FOREIGN KEY (idName)
REFERENCES Names (idName))

Внимание!

INSERT INTO Names(vcName)
VALUES(«Петров»)
INSERT INTO Names(vcName)
VALUES(«Иванов»)
INSERT INTO Names(vcName)
VALUES(«Сидоров»)

Напоследок покажу, как красиво получить соответствие имен и телефонов из двух таблиц:

SELECT vcName, vcPhone
FROM Names, Phones
WHERE Names.idName=Phones.idName

Таблица 1.5. Таблица с внутренней связью

Посмотрим, как можно создать все это в виде SQL запроса:

CREATE TABLE Positions
(idPosition int IDENTITY(1,1),
idParentPosition int,
vcName varchar(30),
CONSTRAINT PK_idPosition PRIMARY KEY (idPosition),
CONSTRAINT FK_idParentPosition FOREIGN KEY (idParentPosition)
REFERENCES Positions (idPosition))

Отношение один к одному

Следующий пример создает две таблицы, у которых создана связь между первичными ключами:

Многие ко многим

Таблица 1.6. Таблица фамилий

Таблица 1.7. Таблица телефонов

Таблица 1.8. Таблица телефонов

CREATE TABLE Names
(idName uniqueidentifier DEFAULT NEWID(),
vcName varchar(50),
CONSTRAINT PK_guid PRIMARY KEY (idName))
CREATE TABLE Phones
(idPhone uniqueidentifier DEFAULT NEWID(),
vcPhone varchar(10),
CONSTRAINT PK_idPhone PRIMARY KEY (idPhone))
CREATE TABLE LinkTable
(idLinkTable uniqueidentifier DEFAULT NEWID(),
idName uniqueidentifier,
idPhone uniqueidentifier,
CONSTRAINT PK_idLinkTable PRIMARY KEY (idLinkTable),
CONSTRAINT FK_idPhone FOREIGN KEY (idPhone)
REFERENCES Phones (idPhone),
CONSTRAINT FK_idName FOREIGN KEY (idName)
REFERENCES Names (idName))

Ключи являются фундаментальными элементами реляционной базы данных, поскольку они устанавливают связь между парой таблиц и обеспечивают уникальную идентификацию каждой записи в таблице. Ключи имеют более важное значение, чем установление отношений; они также помогают с ссылочной целостностью, и они являются основным компонентом целостности на уровне таблиц. Таблицы хранят в них огромные куски данных, которые обычно распространяются на тысячи записей, все из которых несортированы и дезорганизованы. Получение определенных данных из этих многочисленных записей может быть затруднительным порой или иногда невозможным. Именно здесь появляются Ключи. Здесь мы рассмотрим два очень важных ключа схемы реляционной базы данных и разницу между ними: Первичный ключ и Внешний ключ.

Что такое первичный ключ?

Первичный ключ — это специальный ключ, который однозначно идентифицирует каждую запись в таблице. В реляционной базе данных очень важно иметь уникальный идентификатор в каждой строке таблицы, а первичный ключ — это просто то, что вам нужно, чтобы однозначно идентифицировать кортеж в таблице. Кортеж представляет собой набор атрибутов value в реляционной базе данных. Первичный ключ может ссылаться на столбец или набор столбцов в таблице реляционной базы данных, используемый для неявной идентификации всех записей в таблице. Первичный ключ должен быть уникальным для каждой записи, поскольку он действует как уникальный идентификатор и не должен содержать значений Null. Каждая база данных должна иметь один и только один первичный ключ.

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

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

Разница между основным ключом и внешним ключом

Основы первичного ключа и внешнего ключа

Первичный ключ — это специальный ключ в реляционной базе данных, который действует как уникальный идентификатор для каждой записи, что означает, что он однозначно идентифицирует каждую строку / запись в таблице, и ее значение должно быть уникальным для каждой строки таблицы. С другой стороны, внешний ключ представляет собой поле в одной таблице, которое связывает две таблицы вместе. Он относится к столбцу или группе столбцов, которые однозначно идентифицируют строку другой таблицы или той же таблицы.

Отношение первичного ключа и внешнего ключа

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

Дублирующие значения первичного ключа и внешнего ключа

Первичный ключ представляет собой комбинацию ограничений UNIQUE и Not Null, поэтому в поле первичного ключа в таблице реляционных баз данных нельзя допускать дублирования значений. Никакие две строки не могут переносить повторяющиеся значения для атрибута первичного ключа. В отличие от первичного ключа, внешний ключ может содержать повторяющиеся значения, а таблица в реляционной базе данных может содержать более чем внешний ключ.

NULL первичного ключа и внешнего ключа

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

Временная таблица первичного ключа и внешнего ключа

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

Удаление основного ключа и внешнего ключа

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

Первичный ключ или внешний ключ: сравнительная таблица

Резюме основных ключей

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

На Рисунке представлена таблица (отношение степени 5), содержащая некоторые сведения о работниках гипотетического предприятия. Строки таблицы соответствуют кортежам. Каждая строка фактически представляет собой описание одного объекта реального мира (в данном случае работника), характеристики которого содержатся в столбцах. Реляционные отношения соответствуют наборам сущностей, а кортежи — сущностям. Столбцы в таблице, представляющей реляционное отношение, называют атрибутами
.

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

Атрибут, значение которого однозначно идентифицирует кортежи, называется ключевым

(или просто ключом
). Ключом является атрибут «Табельный номер», поскольку его значение уникально для каждого работника предприятия. Если кортежи идентифицируются только сцеплением значений нескольких атрибутов, то говорят, что отношение имеет составной ключ.

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

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

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

ключей в других отношениях либо для создания кластерного индекса. Поэтому в качестве первичного ключа как правило выбирают тот, который имеет наименьший размер (физического хранения) и/или включает наименьшее количество атрибутов.

Если первичный ключ
состоит из единственного атрибута, его называют простым ключом
.

Если первичный ключ
состоит из двух и более атрибутов, его называют составным ключом
. Так, имя, фамилия, отчество, номер паспорта, серия паспорта не могут быть первичными ключами по отдельности, так как могут оказаться одинаковыми у двух и более людей. Но не бывает двух личных документов одного типа с одинаковыми серией и номером. Поэтому в отношении, содержащем данные о людях, первичным ключом может быть подмножество атрибутов, состоящее из типа личного документа, его серии и номера.

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

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

Например, связь между отношениями ОТДЕЛ и СОТРУДНИК создается путем копирования первичного ключа «Номер_отдела»

из первого отношения во второе. Таким образом, для того, чтобы получить список работников данного подразделения, необходимо: 1) Из таблицы ОТДЕЛ установить значение атрибута «Номер_отдела»

, соответствующее данному «Наименованию_отдела». 2) выбрать из таблицы СОТРУДНИК все записи, значение атрибута «Номер_отдела»
которых равно полученному на предыдушем шаге. Для того, чтобы узнать в каком отделе работает сотрудник, нужно выполнить обратную операцию: 1) Определяем «Номер_отдела»
из таблицы СОТРУДНИК. 2) По полученному значению находим запись в таблице ОТДЕЛ.

18. Нормализация в реляционных БД, понятие нормальной формы при проектировании баз данных.

Нормальная форма

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

Процесс преобразования базы данных к виду, отвечающему нормальным формам, называется нормализацией

. Нормализация предназначена для приведения структуры базы данных к виду, обеспечивающему минимальную избыточность, то есть нормализация не имеет целью уменьшение или увеличение производительности работы или же уменьшение или увеличение объёма БД. Конечной целью нормализации является уменьшение потенциальной противоречивости хранимой в БД информации.

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

Функциональные зависимости.

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

19. 1НФ: Основные определения и правила преобразования.

Для обсуждения первой нормальной формы необходимо дать два определения:

Простой атрибут

— атрибут, значения которого атомарны (неделимы).

Сложный атрибут

— получается соединением нескольких атомарных атрибутов, которые могут быть определены на одном или разных доменах (его также называют вектор или агрегат данных).

Определение первой нормальной формы:

отношение находится в 1NF если значения всех его атрибутов атомарны. . В противном случае это вообще не таблица и такие атрибуты необходимо декомпозировать.

Рассмотрим пример:

В базе данных отдела кадров предприятия необходимо хранить сведения о служащих, которые можно попытаться представить в отношении

СЛУЖАЩИЙ(НОМЕР_СЛУЖАЩЕГО, ИМЯ, ДАТА_РОЖДЕНИЯ, ИСТОРИЯ_РАБОТЫ, ДЕТИ).

Из внимательного рассмотрения этого отношения следует, что атрибуты «история_работы»
и «дети»
являются сложными, более того, атрибут «история_работы»
включает еще один сложный атрибут «история_зарплаты».

Данные агрегаты выглядят следующим образом:

 ИСТОРИЯ_РАБОТЫ (ДАТА_ПРИЕМА, НАЗВАНИЕ, ИСТОРИЯ_ЗАРПЛАТЫ),

 ИСТОРИЯ_ЗАРПЛАТЫ (ДАТА_НАЗНАЧЕНИЯ, ЗАРПЛАТА),

 ДЕТИ (ИМЯ_РЕБЕНКА, ГОД_РОЖДЕНИЯ).

Их связь представлена на рис. 3.3.

Рис.3.3. Исходное отношение.

Для приведения исходного отношения СЛУЖАЩИЙ к первой нормальной форме необходимо декомпозировать его на четыре отношения, так как это показано на следующем рисунке:

Рис.3.4. Нормализованное множество отношений.

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

Алгоритм нормализации описан Е.Ф.Коддом следующим образом:

Начиная с отношения, находящегося на верху дерева (рис. 3.3.), берется его первичный ключ, и каждое непосредственно подчиненное отношение расширяется путем вставки домена или комбинации доменов этого первичного ключа.
Первичный Ключ каждого расширенного таким образом отношения состоит из Первичного Ключа, который был у этого отношения до расширения и добавленного Первичного Ключа родительского отношения.
После этого из родительского отношения вычеркиваются все непростые домены, удаляется верхний узел дерева, и эта же процедура повторяется для каждого из оставшихся поддеревьев.

20. 2НФ: Основные определения
и правила преобразования.

Очень часто первичный ключ отношения включает несколько атрибутов (в таком случае его называют составным
) — см., например, отношение ДЕТИ, показанное на рис. 3.4 вопрос 19. При этом вводится понятие полной функциональной зависимости
.

Определение:

неключевой атрибут функционально полно зависит от составного ключа если он функционально зависит от всего ключа в целом, но не находится в функциональной зависимости от какого-либо из входящих в него атрибутов.

Пример:

Пусть имеется отношение ПОСТАВКИ (N_ПОСТАВЩИКА, ТОВАР, ЦЕНА).
Поставщик может поставлять различные товары, а один и тот же товар может поставляться разными поставщиками. Тогда ключ отношения — «N_поставщика + товар»
. Пусть все поставщики поставляют товар по одной и той же цене. Тогда имеем следующие функциональные зависимости:

N_поставщика, товар
-> цена
товар
-> цена

Неполная функциональная зависимость атрибута «цена» от ключа приводит к следующей аномалии: при изменении цены товара необходим полный просмотр отношения для того, чтобы изменить все записи о его поставщиках. Данная аномалия является следствием того факта, что в одной структуре данных объединены два семантических факта. Следующее разложение дает отношения во 2НФ:

ПОСТАВКИ (N_ПОСТАВЩИКА, ТОВАР)
ЦЕНА_ТОВАРА (ТОВАР, ЦЕНА)

Таким образом, можно дать

Определение второй нормальной формы:

Отношение находится во 2НФ, если оно находится в 1НФ и каждый неключевой атрибут функционально полно зависит от ключа.

21. 3НФ: Основные определения
и правила преобразования.

Перед обсуждением третьей нормальной формы необходимо ввести понятие: транзитивная функциональная зависимость
.

Определение:

Пусть X, Y, Z — три атрибута некоторого отношения. При этом X —> Y и Y —> Z, но обратное соответствие отсутствует, т.е. Z -/-> Y и Y -/-> X. Тогда Z транзитивно зависит от X.
Пусть имеется отношение ХРАНЕНИЕ (ФИРМА
, СКЛАД, ОБЪЕМ), которое содержит информацию о фирмах, получающих товары со складов, и объемах этих складов. Ключевой атрибут — «фирма»
. Если каждая фирма может получать товар только с одного склада, то в данном отношении имеются следующие функциональные зависимости:

фирма
-> склад
склад
-> объем

При этом возникают аномалии:

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

Для устранения этих аномалий необходимо декомпозировать исходное отношение на два:

ХРАНЕНИЕ (ФИРМА
, СКЛАД)
ОБЪЕМ_СКЛАДА (СКЛАД
, ОБЪЕМ)

Определение третьей нормальной формы:

Отношение находится в 3НФ, если оно находится во 2НФ и каждый не ключевой атрибут не транзитивно зависит от первичного ключа.

InterBase

могут использовать следующие виды ограничений:

PRIMARY KEY
— первичный ключ
таблицы.
UNIQUE
— уникальный ключ
таблицы.
FOREIGN KEY
— внешний ключ
, обеспечивает ссылку на другую таблицу и гарантирует ссылочную целостность между родительской и дочерней таблицами
.

Примечание о терминологии

Если вы похожи на автора данного курса в том, что любите искать ответы на интересующий вас вопрос комплексно, в разных трудах разных авторов, то вы не могли не заметить некоторую путаницу в определениях главная (master)
-> подчиненная (detail)
таблицы. Напомним, что главную таблицу часто называют родительской, а подчиненную — дочерней.

Связано это, вероятно, с тем, как интерпретируются эти определения в локальных и SQL
-серверных СУБД
.

В локальных СУБД
главной называется та таблица
, которая содержит основные данные, а подчиненной — дополнительные. Возьмем, к примеру, три связанные таблицы. Первая содержит данные о продажах, вторая — о товарах и третья — о покупателях:

Рис.
18.1.

Здесь основные сведения хранятся в таблице продаж, следовательно, она главная (родительская). Дополнительные сведения хранятся в таблицах товаров и покупателей, значит они дочерние. Это и понятно: одна дочь не может иметь двух биологических матерей, зато одна мать вполне способна родить двух дочерей.

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

. А поле
, на которое оно ссылается, называется родительским или
первичным ключом
. Таблицу, которая имеет внешний ключ
(ссылку на запись
другой таблицы) нередко называют дочерней, а таблицу с родительским ключом
— родительской. Еще в определении связей говорят, что родитель может иметь только одну уникальную запись
, на которую могут ссылаться несколько записей дочерней таблицы
.

Так что в приведенном выше примере таблица
продаж имеет два внешних ключа: идентификатор
товара, и идентификатор
покупателя. А обе таблицы в правой части рисунка имеют родительский ключ
» Идентификатор
«. Поскольку один покупатель
или товар
могут неоднократно встречаться в таблице продаж, то получается, что обе таблицы в правой части рисунка — родители, а таблица
слева — дочерняя. Поскольку сейчас мы изучаем InterBase
— SQL
сервер
БД
, этими определениями мы и будем руководствоваться в последующих лекциях. Чтобы далее не ломать голову над этой путаницей, сразу договоримся: дочерняя таблица
имеет внешний ключ
(FOREIGN KEY
) на другую таблицу.

PRIMARY KEY

PRIMARY KEY

— первичный ключ
, является одним из основных видов ограничений в базе данных. Первичный ключ
предназначен для однозначной идентификации записи в таблице, и должен быть уникальным. Первичные ключи PRIMARY KEY
находятся в таблицах, которые принято называть родительскими (Parent
). Не стоит путать первичный ключ
с первичными индексами локальных баз данных, первичный ключ
является не индексом, а именно ограничением. При создании первичного ключа InterBase

автоматически создает для него уникальный индекс
. Однако если мы создадим уникальный индекс
, это не приведет к созданию ограничения первичного ключа
. Таблица
может иметь только один первичный ключ
PRIMARY KEY
.

Предположим, имеется таблица
со списком сотрудников. Поле
«Фамилия» может содержать одинаковые значения (однофамильцы), поэтому его нельзя использовать в качестве первичного ключа. Редко, но встречаются однофамильцы, которые вдобавок имеют и одинаковые имена. Еще реже, но встречаются полные тезки, поэтому даже все три поля «Фамилия» + «Имя» + «Отчество» не могут гарантировать уникальности записи, и не могут быть первичным ключом. В данном случае выход
, как и прежде, в том, чтобы добавить поле
— идентификатор
, которое содержит порядковый номер данного лица. Такие поля обычно делают автоинкрементными (об организации автоинкрементных полей поговорим на следующих лекциях). Итак,

Первичный ключ

— это одно или несколько полей в таблице, сочетание которых уникально для каждой записи
.

Если в первичный ключ
входит единственный столбец (как чаще всего и бывает), спецификатор
PRIMARY KEY
ставится при определении столбца
:

CREATE TABLE Prim_1(Stolbec1 INT NOT NULL PRIMARY KEY,
Stolbec2 VARCHAR(50))

Если первичный ключ
строится по нескольким столбцам, то спецификатор
ставится после определения всех полей:

CREATE TABLE Prim_2(Stolbec1 INT NOT NULL,
Stolbec2 VARCHAR(50) NOT NULL,
PRIMARY KEY (Stolbec1, Stolbec2))

Как видно из примеров, первичный ключ
обязательно должен иметь ограничение столбца (столбцов) NOT NULL
.

UNIQUE

UNIQUE

— уникальный ключ
. Спецификатор
UNIQUE
указывает, что все значения данного поля должны быть уникальными, в связи с этим такие поля также не могут содержать значения NULL
. Можно сказать, что уникальный ключ
UNIQUE
является альтернативным вариантом первичного ключа, однако имеются различия.
Главное различие в том, что первичный ключ
должен быть только один, тогда как уникальных ключей может быть несколько. Кроме того, ограничение UNIQUE
не может быть построено по тому же набору столбцов, который был использован для ограничения PRIMARY KEY
или другого UNIQUE
. Уникальные ключи, как и первичные, находятся в таблицах, которые являются родительскими по отношению к другим таблицам.

Столбец, объявленный с ограничением UNIQUE
, как и первичный ключ
, может применяться для обеспечения ссылочной целостности между родительской и дочерней таблицами
. При этом внешний ключ
дочерней таблицы
будет ссылаться на это поле
(поля). Как и в случае первичного ключа, при создании уникального ключа, для него автоматически будет создан уникальный индекс
. Но не наоборот. Пример создания таблицы с одним первичным и двумя уникальными ключами:

CREATE TABLE Prim_3(Stolbec1 INT NOT NULL PRIMARY KEY,
Stolbec2 VARCHAR(50) NOT NULL UNIQUE,
Stolbec3 FLOAT NOT NULL UNIQUE)

FOREIGN KEY

FOREIGN KEY

— внешний ключ
. Это очень мощное средство для обеспечения ссылочной целостности между таблицами, которое позволяет не только следить за наличиями правильных ссылок, но и автоматически управлять ими. Внешние ключи содержатся в таблицах, которые являются дочерними (Child
) по отношению к другим таблицам. Ссылочная целостность
обеспечивается именно внешним ключом, который ссылается на первичный или

Как найти зависимости внешнего ключа в SQL Server?

После долгих поисков я нашел рабочее решение. Моя база данных не использует sys.foreign_key_columns, а information_schema.key_column_usage содержит только первичные ключи.

Я использую SQL Server 2015

РЕШЕНИЕ 1 (редко используется)

Если другие решения не работают, это будет работать нормально:

        WITH CTE AS
        (
            SELECT 
                TAB.schema_id,
                TAB.name,
                COL.name AS COLNAME,
                COl.is_identity
            FROM 
                sys.tables TAB INNER JOIN sys.columns COL 
                    ON TAB.object_id = COL.object_id
        )
        SELECT 
            DB_NAME() AS [Database], 
            SCHEMA_NAME(Child.schema_id) AS 'Schema',
            Child.name AS 'ChildTable',
            Child.COLNAME AS 'ChildColumn',
            Parent.name AS 'ParentTable',
            Parent.COLNAME AS 'ParentColumn'
        FROM 
            cte Child INNER JOIN CTE Parent
                ON 
                    Child.COLNAME=Parent.COLNAME AND 
                    Child.name<>Parent.name AND 
                    Child.is_identity+1=Parent.is_identity

РЕШЕНИЕ 2 (обычно используется)

В большинстве случаев это будет работать просто отлично:

        SELECT
            DB_NAME() AS [Database], 
            SCHEMA_NAME(fk.schema_id) AS 'Schema',
            fk.name 'Name',
            tp.name 'ParentTable',
            cp.name 'ParentColumn',
            cp.column_id,
            tr.name 'ChildTable',
            cr.name 'ChildColumn',
            cr.column_id
        FROM
            sys.foreign_keys fk
        INNER JOIN
            sys.tables tp ON fk.parent_object_id = tp.object_id
        INNER JOIN
            sys.tables tr ON fk.referenced_object_id = tr.object_id
        INNER JOIN
            sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
        INNER JOIN
            sys.columns cp ON fkc.parent_column_id = cp.column_id AND fkc.parent_object_id = cp.object_id
        INNER JOIN
            sys.columns cr ON fkc.referenced_column_id = cr.column_id AND fkc.referenced_object_id = cr.object_id
        WHERE 
            -- CONCAT(SCHEMA_NAME(fk.schema_id), '.', tp.name, '.', cp.name) LIKE '%my_table_name%' OR
            -- CONCAT(SCHEMA_NAME(fk.schema_id), '.', tr.name, '.', cr.name) LIKE '%my_table_name%' 
        ORDER BY
            tp.name, cp.column_id

НОУ ИНТУИТ | Лекция | Определение ограничений целостности

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

Таблицы с ограничениями в стандарте языка

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

обязательные данные;
ограничения для доменов полей;
целостность сущностей;
ссылочная целостность ;
требования конкретного предприятия.

Большая часть перечисленных ограничений задается в операторах CREATE TABLE и ALTER TABLE.

Создание таблицы

В стандарте SQL дано несколько вариантов определения оператора создания таблицы, однако его базовый формат имеет следующий вид:

<определение_таблицы> ::=
CREATE TABLE имя_таблицы
{(имя_столбца тип_данных [ NOT NULL ][ UNIQUE]
[DEFAULT <значение>]
[ CHECK (<условие_выбора>)][,...n]}
[CONSTRAINT имя_ограничения]
[PRIMARY KEY (имя_столбца [,...n])
{[UNIQUE (имя_столбца [,...n])}
[FOREIGN KEY (имя_столбца_внешнего_ключа 
    [,...n])
REFERENCES имя_род_таблицы 
    [(имя_столбца_род_таблицы [,...n])],
[MATCH {PARTIAL | FULL}]
[ON UPDATE {CASCADE| SET NULL |SET DEFAULT 
    |NO ACTION}]                        
[ON DELETE {CASCADE| SET NULL |SET DEFAULT
   	|NO ACTION}]
{[CHECK(<условие_выбора>)][,...n]})

Ограничения

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

Обязательные данные

Для некоторых столбцов требуется наличие в каждой строке таблицы конкретного и допустимого значения, отличного от опущенного значения или значения NULL. Для заданий ограничений подобного типа стандарт SQL предусматривает использование спецификации NOT NULL.

Требования конкретного предприятия

Обновления данных в таблицах могут быть ограничены существующими в организации требованиями (бизнес-правилами). Стандарт SQL позволяет реализовать бизнес-правила предприятий с помощью предложения CHECK и ключевого слова UNIQUE.

Ограничения для доменов полей

Каждый столбец имеет собственный домен — некоторый набор допустимых значений. Стандарт SQL предусматривает два различных механизма определения доменов. Первый состоит в использовании предложения CHECK, позволяющего задать требуемые ограничения для столбца или таблицы в целом, а второй предполагает применение оператора CREATE DOMAIN.

Целостность сущностей

Первичный ключ таблицы должен иметь уникальное непустое значение в каждой строке. Стандарт SQL позволяет задавать подобные требования поддержки целостности данных с помощью фразы PRIMARY KEY. В пределах таблицы она может указываться только один раз. Однако существует возможность гарантировать уникальность значений и для любых альтернативных ключей таблицы, что обеспечивает ключевое слово UNIQUE. Кроме того, при определении альтернативных ключей рекомендуется использовать и спецификаторы NOT NULL.

Ссылочная целостность

Внешние ключи представляют собой столбцы или наборы столбцов, предназначенные для связывания каждой из строк дочерней таблицы, содержащей этот внешний ключ, со строкой родительской таблицы, содержащей соответствующее значение потенциального ключа. Стандарт SQL предусматривает механизм определения внешних ключей с помощью предложения FOREIGN KEY, а фраза REFERENCES определяет имя родительской таблицы, т.е. таблицы, где находится соответствующий потенциальный ключ. При использовании этого предложения система отклонит выполнение любых операторов INSERT или UPDATE, с помощью которых будет предпринята попытка создать в дочерней таблице значение внешнего ключа, не соответствующее одному из уже существующих значений потенциального ключа родительской таблицы. Когда действия системы выполняются при поступлении операторов UPDATE и DELETE, содержащих попытку обновить или удалить значение потенциального ключа в родительской таблице, которому соответствует одна или более строк дочерней таблицы, то они зависят от правил поддержки ссылочной целостности, указанных во фразах ON UPDATE и ON DELETE предложения FOREIGN KEY. Если пользователь предпринимает попытку удалить из родительской таблицы строку, на которую ссылается одна или более строк дочерней таблицы, язык SQL предоставляет следующие возможности:

CASCADE — выполняется удаление строки из родительской таблицы, сопровождающееся автоматическим удалением всех ссылающихся на нее строк дочерней таблицы;
SET NULL — выполняется удаление строки из родительской таблицы, а во внешние ключи всех ссылающихся на нее строк дочерней таблицы записывается значение NULL ;
SET DEFAULT — выполняется удаление строки из родительской таблицы, а во внешние ключи всех ссылающихся на нее строк дочерней таблицы заносится значение, принимаемое по умолчанию;
NO ACTION — операция удаления строки из родительской таблицы отменяется. Именно это значение используется по умолчанию в тех случаях, когда в описании внешнего ключа фраза ON DELETE опущена.

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

Определитель MATCH позволяет уточнить способ обработки значения NULL во внешнем ключе.

При определении таблицы предложение FOREIGN KEY может указываться произвольное количество раз.

В операторе CREATE TABLE используется необязательная фраза DEFAULT, которая предназначена для задания принимаемого по умолчанию значения, когда в операторе INSERT значение в данном столбце будет отсутствовать.

Фраза CONSTRAINT позволяет задать имя ограничению, что позволит впоследствии отменить то или иное ограничение с помощью оператора ALTER TABLE.

Изменение и удаление таблицы

Для внесения изменений в уже созданные таблицы стандартом SQL предусмотрен оператор ALTER TABLE, предназначенный для выполнения следующих действий:

добавление в таблицу нового столбца;
удаление столбца из таблицы;
добавление в определение таблицы нового ограничения;
удаление из определения таблицы существующего ограничения;
задание для столбца значения по умолчанию;
отмена для столбца значения по умолчанию.

Оператор изменения таблицы имеет следующий обобщенный формат:

<изменение_таблицы> ::=
ALTER TABLE имя_таблицы
[ADD [COLUMN]имя_столбца тип_данных 
  	[ NOT NULL ][UNIQUE]
[DEFAULT <значение>][ CHECK (<условие_выбора>)]]
[DROP [COLUMN] имя_столбца [RESTRICT | CASCADE ]]
[ADD [CONSTRAINT [имя_ограничения]]
[{PRIMARY KEY (имя_столбца [,...n])
    |[UNIQUE (имя_столбца [,...n])}
|[FOREIGN KEY (имя_столбца_внешнего_ключа [,...n])
    REFERENCES имя_род_таблицы 
       [(имя_столбца_род_таблицы [,...n])],
[ MATCH {PARTIAL | FULL}
    [ON UPDATE {CASCADE| SET NULL |
        SET DEFAULT | NO ACTION}]
    [ON DELETE {CASCADE| SET NULL |
        SET DEFAULT | NO ACTION}]
    |[CHECK(<условие_выбора>)][,...n]}]
[DROP CONSTRAINT имя_ограничения 
     [RESTRICT | CASCADE]]
[ALTER [COLUMN] SET DEFAULT <значение>]
[ALTER [COLUMN] DROP DEFAULT]

Здесь параметры имеют то же самое назначение, что и в определении оператора CREATE TABLE.

Оператор ALTER TABLE реализован не во всех диалектах языка SQL. В некоторых диалектах он поддерживается, однако не позволяет удалять из таблицы уже существующие столбцы.

Для удаления таблицы используется команда DROP TABLE.

Создание отношений по внешнему ключу — SQL Server

19.06.2020
4 минуты на чтение

В этой статье

Применимо к: SQL Server 2016 (13.x) и более поздних версий База данных SQL Azure Управляемый экземпляр SQL Azure

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

Разрешения

Для создания новой таблицы с внешним ключом требуется разрешение CREATE TABLE в базе данных и разрешение ALTER для схемы, в которой создается таблица.

Для создания внешнего ключа в существующей таблице требуется разрешение ALTER для таблицы.

Пределы и ограничения

Ограничение внешнего ключа не обязательно должно быть связано только с ограничением первичного ключа в другой таблице.Внешние ключи также могут быть определены для ссылки на столбцы ограничения UNIQUE в другой таблице.
Когда значение, отличное от NULL, вводится в столбец ограничения FOREIGN KEY, это значение должно существовать в столбце, на который указывает ссылка. В противном случае возвращается сообщение об ошибке нарушения внешнего ключа. Чтобы убедиться, что все значения ограничения составного внешнего ключа проверены, укажите NOT NULL для всех участвующих столбцов.
Ограничения FOREIGN KEY могут ссылаться только на таблицы в одной базе данных на одном сервере.Ссылочная целостность между базами данных должна быть реализована с помощью триггеров. Для получения дополнительной информации см. CREATE TRIGGER.
Ограничения FOREIGN KEY могут ссылаться на другой столбец в той же таблице, и это называется ссылкой на себя.
Ограничение FOREIGN KEY, указанное на уровне столбца, может содержать только один ссылочный столбец. Этот столбец должен иметь тот же тип данных, что и столбец, для которого определено ограничение.
Ограничение FOREIGN KEY, указанное на уровне таблицы, должно иметь такое же количество ссылочных столбцов, что и количество столбцов в списке столбцов ограничения.Тип данных каждого ссылочного столбца также должен совпадать с типом данных соответствующего столбца в списке столбцов.
Компонент Database Engine не имеет предопределенного ограничения на количество ограничений FOREIGN KEY, которые таблица может содержать, ссылаясь на другие таблицы. Компонент Database Engine также не ограничивает количество ограничений FOREIGN KEY, принадлежащих другим таблицам, которые ссылаются на конкретную таблицу. Однако фактическое количество используемых ограничений FOREIGN KEY ограничено конфигурацией оборудования, а также конструкцией базы данных и приложения.Таблица может ссылаться максимум на 253 другие таблицы и столбцы как внешние ключи (исходящие ссылки). SQL Server 2016 (13.x) и более поздние версии увеличивают ограничение на количество других таблиц и столбцов, которые могут ссылаться на столбцы в одной таблице (входящие ссылки), с 253 до 10 000. (Требуется не менее 130 уровня совместимости.) Повышение имеет следующие ограничения:
- Более 253 ссылок на внешние ключи поддерживаются для операций DELETE и UPDATE DML. Операции MERGE не поддерживаются.
- Таблица со ссылкой внешнего ключа на себя по-прежнему ограничена 253 ссылками на внешний ключ.
- Более 253 ссылок на внешние ключи в настоящее время недоступны для индексов columnstore, таблиц, оптимизированных для памяти, или Stretch Database.
Ограничения FOREIGN KEY не применяются для временных таблиц.
Если внешний ключ определен в столбце определяемого пользователем типа среды CLR, реализация типа должна поддерживать двоичное упорядочение.Дополнительные сведения см. В разделе Типы, определяемые пользователем в среде CLR.
Столбец типа varchar (max) может участвовать в ограничении FOREIGN KEY, только если первичный ключ, на который он ссылается, также определен как тип varchar (max) .

Создание отношения внешнего ключа в конструкторе таблиц

Использование SQL Server Management Studio

В обозревателе объектов щелкните правой кнопкой мыши таблицу, которая будет находиться на стороне внешнего ключа связи, и выберите Design .
Таблица открывается в Table Designer .
В меню Конструктор таблиц щелкните Отношения .
В диалоговом окне Отношения внешнего ключа щелкните Добавить .
Связь отображается в списке Selected Relationship с предоставленным системой именем в формате FK_ < имя таблицы > _ < имя таблицы >, где имя таблицы — имя таблицы внешнего ключа.
Щелкните отношение в списке Выбранное отношение .
Щелкните Спецификация таблиц и столбцов в сетке справа и щелкните многоточие ( … ) справа от свойства.
В диалоговом окне Таблицы и столбцы в раскрывающемся списке Первичный ключ выберите таблицу, которая будет на стороне первичного ключа связи.
В таблице ниже выберите столбцы, составляющие первичный ключ таблицы.В соседней ячейке сетки справа от каждого столбца выберите соответствующий столбец внешнего ключа таблицы внешнего ключа.
Дизайнер таблиц предлагает имя для связи. Чтобы изменить это имя, отредактируйте содержимое текстового поля Имя отношения .
Выберите ОК , чтобы создать отношение.
Закройте окно конструктора таблиц и сохраните ваши изменения, чтобы изменение отношения внешнего ключа вступило в силу.

Создать внешний ключ в новой таблице

с использованием Transact-SQL

В следующем примере создается таблица и определяется ограничение внешнего ключа для столбца TempID , который ссылается на столбец SalesReasonID в таблице Sales.SalesReason в базе данных AdventureWorks. Предложения ON DELETE CASCADE и ON UPDATE CASCADE используются для обеспечения того, чтобы изменения, внесенные в таблицу Sales.SalesReason , автоматически распространялись на Sales.Таблица TempSalesReason .

  СОЗДАТЬ ТАБЛИЦУ Sales.TempSalesReason
   (
      TempID int NOT NULL, имя nvarchar (50)
      , ОГРАНИЧЕНИЕ PK_TempSales PRIMARY KEY NONCLUSTERED (TempID)
      , ОГРАНИЧЕНИЕ FK_TempSales_SalesReason ВНЕШНИЙ КЛЮЧ (TempID)
        ССЫЛКИ Sales.SalesReason (SalesReasonID)
        НА КАСКАДЕ УДАЛЕНИЯ
        НА КАСКАДЕ ОБНОВЛЕНИЙ
   )
;

Создать внешний ключ в существующей таблице

с использованием Transact-SQL

В следующем примере создается внешний ключ в столбце TempID и ссылается на столбец SalesReasonID в файле Sales.Таблица SalesReason в базе данных AdventureWorks.

  ИЗМЕНИТЬ ТАБЛИЦУ Sales.TempSalesReason
   ДОБАВИТЬ ОГРАНИЧЕНИЕ FK_TempSales_SalesReason FOREIGN KEY (TempID)
      ССЫЛКИ Sales.SalesReason (SalesReasonID)
      НА КАСКАДЕ УДАЛЕНИЯ
      НА КАСКАДЕ ОБНОВЛЕНИЙ
;

Следующие шаги

Для получения дополнительной информации см .:

ИНОСТРАННЫЙ КЛЮЧ, ПРОВЕРКА и ПО УМОЛЧАНИЮ

В предыдущей статье «Часто используемые ограничения SQL Server: NOT NULL, UNIQUE и PRIMARY KEY» мы подробно описали первые три типа ограничений SQL Server; НЕ NULL, UNIQUE и PRIMARY KEY.В этой статье мы обсудим три других ограничения; FOREIGN KEY, CHECK и DEFAULT, кратко описав каждый из них и предоставив практические примеры.

Ограничение внешнего ключа

Внешний ключ — это ключ базы данных, который используется для связывания двух таблиц вместе. Ограничение FOREIGN KEY определяет отношения между таблицами базы данных путем ссылки на столбец или набор столбцов в таблице Child , которая содержит внешний ключ, на столбец PRIMARY KEY или набор столбцов в таблице Parent .

Связь между дочерней и родительской таблицами поддерживается путем проверки существования значений FOREIGN KEY дочерней таблицы в PRIMARY KEY родительской таблицы перед вставкой этих значений в дочернюю таблицу. Таким образом, ограничение FOREIGN KEY в дочерней таблице, которое ссылается на PRIMARY KEY в родительской таблице, будет обеспечивать ссылочную целостность базы данных. Ссылочная целостность гарантирует, что связь между таблицами базы данных сохраняется во время процесса вставки данных.Напомним, что ограничение PRIMARY KEY гарантирует, что в эту таблицу не будут вставлены NULL или повторяющиеся значения для выбранного столбца или столбцов, что обеспечивает целостность объекта для этой таблицы. Целостность объекта, обеспечиваемая ПЕРВИЧНЫМ КЛЮЧОМ, и ссылочная целостность, обеспечиваемая ИНОСТРАННЫМ КЛЮЧОМ, вместе образуют целостность ключа.

Ограничение FOREIGN KEY отличается от ограничения PRIMARY KEY тем, что вы можете создать только один PRIMARY KEY для каждой таблицы с возможностью создания нескольких ограничений FOREIGN KEY в каждой таблице, ссылаясь на несколько родительских таблиц.Другое отличие состоит в том, что FOREIGN KEY позволяет вставлять значения NULL, если для этого ключа не задано ограничение NOT NULL, но PRIMARY KEY не принимает значения NULL.

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

NO ACTION : Если для предложений ON UPDATE или ON DELETE задано значение NO ACTION, выполнение операции обновления или удаления в родительской таблице завершится ошибкой.
CASCADE : Если для предложений ON UPDATE или ON DELETE задано значение CASCADE, то же действие, выполняемое над ссылочными значениями родительской таблицы, будет отражено в связанных значениях в дочерней таблице. Например, если указанное значение удаляется в родительской таблице, все связанные строки в дочерней таблице также удаляются.
SET NULL : С этой опцией предложений ON UPDATE и ON DELETE, если ссылочные значения в родительской таблице удаляются или изменяются, все связанные значения в дочерней таблице устанавливаются в значение NULL.
SET DEFAULT : Использование опции SET DEFAULT в предложениях ON UPDATE и ON DELETE указывает, что при обновлении или удалении ссылочных значений в родительской таблице для связанных значений в дочерней таблице со столбцами FOREIGN KEY будут установлены значения его значение по умолчанию.

Вы можете добавить ограничение FOREIGN KEY при определении столбца с помощью оператора CREATE TABLE T-SQL или добавить его после создания таблицы с помощью оператора ALTER TABLE T-SQL.Мы создадим две новые таблицы, чтобы понять функциональность ограничения FOREIGN KEY. Первая таблица будет действовать как родительская таблица со столбцом ID, определенным как столбец PRIMARY KEY. Вторая таблица будет действовать как дочерняя таблица со столбцом ID, определенным как столбец FOREIGN KEY, который ссылается на столбец ID в родительской таблице. Этого можно добиться с помощью приведенного ниже сценария T-SQL:

ИСПОЛЬЗОВАТЬ SQLShackDemo

СОЗДАТЬ ТАБЛИЦУ ConstraintDemoParent

(

ID INT PRIMARY KEY,

Имя VARCHAR (50) NULL

)

CREATE TABLE

CREATE TABLE ConstraintDemo

ПЕРВИЧНЫЙ КЛЮЧ,

ID INT ИНОСТРАННЫЕ КЛЮЧЕВЫЕ ССЫЛКИ ConstraintDemoParent (ID)

)

После создания двух таблиц мы вставим три записи в родительскую таблицу и две записи в дочернюю таблицу, используя следующие инструкции INSERT:

INSERT INTO ConstraintDemoParent ([ID], [NAME]) VALUES (1, ‘John’), (2, ‘Mika’), (3, ‘Sanya’)

INSERT INTO ConstraintDemoChild (CID , ID) ЗНАЧЕНИЯ (1,1)

ВСТАВИТЬ В ConstraintDemoChild (CID, ID) ЗНАЧЕНИЯ (2,4)

Результат покажет вам, что три записи успешно вставлены в родительскую таблицу.Первая запись, которую мы пытались вставить в дочернюю таблицу, вставляется без каких-либо ошибок, поскольку значение идентификатора 1 уже существует в родительской таблице. Попытка вставить вторую запись в дочернюю таблицу не удастся, потому что значение идентификатора 4 не существует в родительской таблице, и из-за ограничения FOREIGN KEY вы не сможете вставить значение идентификатора в дочернюю таблицу, которая не существует в родительской таблице:

Проверив содержимое родительской и дочерней таблиц, вы увидите, что в дочернюю таблицу вставлена только одна запись, как вы можете видеть ниже:

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

SELECT CONSTRAINT_NAME,

TABLE_SCHEMA,

TABLE_NAME,

CONSTRAINT_TYPE

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

WHERE TABLE_NAME = ‘ConstraintDemo14hild’

Результат в нашем случае для дочерней таблицы следующий:

Затем мы можем легко использовать предыдущий результат, чтобы отбросить ограничение FOREIGN KEY, используя следующий оператор T-SQL
ALTER TABEL… DROP CONSTRAINT:

ALTER TABLE ConstraintDemoChild

DROP CONSTRAINT FK__ConstraintDe__ID__0B91BA14;

При повторной попытке вставить вторую запись в дочернюю таблицу операция вставки завершится успешно без каких-либо ошибок, как показано ниже:

Но если мы попытаемся снова создать ограничение FOREIGN KEY в столбце ID дочерней таблицы, используя следующий оператор ALTER TABLE T-SQL:

ALTER TABLE ConstraintDemoChild

ДОБАВИТЬ ОГРАНИЧЕНИЕ FK__ConstraintDe__ID

ИНОСТРАННЫЙ КЛЮЧ (ID) ССЫЛКИ ConstraintDemoParent (ID);

Операция завершится неудачно, поскольку значение идентификатора 4 не существует в родительской таблице и нарушает ссылочную целостность между дочерней и родительской таблицами, как показано в следующем сообщении об ошибке:

Чтобы иметь возможность создать ограничение FOREIGN KEY в дочерней таблице, мы должны сначала устранить этот конфликт, удалив или обновив эту запись.Здесь мы попытаемся изменить значение ID с помощью следующей команды UPDATE:

ОБНОВЛЕНИЕ [dbo]. [ConstraintDemoChild] SET ID = 2 WHERE ID = 4

Теперь ограничение FOREIGN KEY будет успешно создано без ошибок, как показано ниже:

Ограничение FOREIGN KEY можно определить с помощью инструмента SQL Server Management Studio.Щелкните правой кнопкой мыши нужную таблицу и выберите параметр «Дизайн». Когда откроется окно «Дизайн», щелкните его правой кнопкой мыши и выберите параметр « Relationships »:

В отображаемом окне вы можете легко указать имя ограничения FOREIGN KEY, таблицы и столбцы, которые будут участвовать в этом отношении, щелкнув Tables And Columns Specification , если требуется проверка существующих данных, и, наконец, действие, выполняемое при удалении или изменении указанной записи в родительской таблице, как показано ниже:

Ограничение ПРОВЕРИТЬ

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

Определение условия ограничения CHECK чем-то похоже на написание предложения WHERE запроса с использованием различных операторов сравнения, таких как AND, OR, BETWEEN, IN, LIKE и IS NULL, для написания логического выражения, которое вернет TRUE, FALSE или UNKNOWN. .Ограничение CHECK вернет значение UNKNOWN, если в условии присутствует значение NULL. Ограничение CHECK используется в основном для обеспечения целостности домена путем ограничения вставляемых значений теми, которые соответствуют определенным значениям, диапазонам или правилам формата.

Давайте создадим новую простую таблицу с тремя столбцами; столбец ID, который считается ПЕРВИЧНЫМ КЛЮЧОМ этой таблицы, Имя и Заработок. Для столбца Salary определено ограничение CHECK, чтобы гарантировать, что в этот столбец не вставлены нулевые или отрицательные значения.Ограничение CHECK определяется в операторе CREATE TABLE T-SQL ниже:

CREATE TABLE ConstraintDemo4

(

ID INT PRIMARY KEY,

Имя VARCHAR (50) NULL,

Salary INT CHECK (Salary> 0)

)

Если вы выполните следующие три оператора INSERT:

ВСТАВИТЬ В ConstraintDemo4 ([ID], [NAME], Salary) VALUES (1, ‘John’, 350)

INSERT INTO ConstraintDemo4 ([ID], [NAME], Salary) VALUES (2 , ‘Mike’, 0)

INSERT INTO ConstraintDemo4 ([ID], [NAME], Salary) VALUES (3, ‘Nikola’, — 72)

Из сгенерированного результата видно, что первая запись вставляется без ошибок, поскольку указанное значение Salary соответствует условию проверки.Второй и третий операторы INSERT завершатся ошибкой, поскольку указанные значения Salary не соответствуют условию ограничения CHECK из-за вставки нулевых и отрицательных значений Salary, как вы можете видеть в следующем сообщении об ошибке:

Проверка данных таблицы показывает, что только первая строка, прошедшая условие ограничения CHECK, будет вставлена в таблицу, как показано ниже:

Ранее в этой статье мы упоминали, что ограничение CHECK позволяет вставлять значения NULL, если участвующие столбцы допускают NULL, и значения NULL будут оцениваться как UNKNOWN без выдачи каких-либо ошибок.Это видно из приведенной ниже записи, которая вставлена успешно, хотя предоставленное значение столбца Salary — NULL:

Если вы просмотрите определение ограничения CHECK в предыдущем операторе CREATE TABLE, вы увидите, что мы не упомянули имя определенного ограничения. В этом случае SQL Server присвоит этому ограничению уникальное имя, которое можно отобразить, запросив системное представление INFORMATION_SCHEMA.TABLE_CONSTRAINTS для таблицы ConstraintDemo4.Результат будет таким, как показано ниже:

Ограничение CHECK можно сбросить с помощью оператора ALTER TABLE T-SQL. Используя имя ограничения CHCEK, которое мы получили ранее, следующую команду можно использовать для удаления ограничения CHECK в таблице ConstraintDemo4:

ALTER TABLE ConstraintDemo4

DROP CONSTRAINT CK__Constrain__Salar__0F624AF8;

После того, как ограничение CHECK будет отброшено, невыполненные два оператора INSERT будут выполнены успешно без каких-либо ошибок, так как нет ограничений на вставленные значения Salary, а именно:

Но если вы попытаетесь определить ограничение CHECK сейчас с помощью следующего оператора T-SQL ALTER DATABASE… ADD CONSTRAINT:

ALTER TABLE ConstraintDemo4

ДОБАВИТЬ ОГРАНИЧЕНИЕ CK__Constrain__Salar

CHECK (Salary> 0);

Добавление ограничения CHECK завершится ошибкой.Это связано с тем, что пока SQL Server проверяет уже существующие данные на предмет условия ограничения CHECK, обнаруживаются одно или несколько значений, не соответствующих условию ограничения, как вы можете видеть в следующем сообщении об ошибке:

Чтобы снова определить ограничение CHECK, мы должны изменить или удалить данные, которые препятствуют созданию ограничения. Мы попробуем изменить сумму Salary для двух записей, которые нарушают условие ограничения, на допустимое положительное значение, как в следующем операторе UPDATE:

ОБНОВЛЕНИЕ ConstraintDemo4 SET Salary = 300 WHERE ID IN (2,3)

Теперь ограничение CHECK может быть определено без проблем, как показано ниже:

SQL Server позволяет отключить ограничение CHECK для особых случаев, таких как вставка огромного количества данных, не заботясь о соблюдении условия ограничения для целей тестирования или из-за изменения бизнес-логики.Этот случай действителен только для ограничений CHECK и FOREIGN KEY, которые можно временно отключить. Отключить ранее определенное ограничение CHECK можно с помощью приведенной ниже команды ALTER TABLE T-SQL:

ALTER TABLE ConstraintDemo4 NOCHECK CONSTRAINT CK__Constrain__Salar

Попытка вставить отрицательное значение Salary не завершится неудачей, поскольку в этом столбце не выполняется проверка, как показано ниже:

Вы также можете отключить все ограничения таблицы с помощью одной команды, указав для команды ALTER TABLE… NOCHECK CONSTRAINT ALL вместо записи имени ограничения, как в команде T-SQL ниже:

ALTER TABLE ConstraintDemo4 NOCHECK CONSTRAINT ALL

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

Если вы попытаетесь отбросить ограничение CHECK и снова создать его с помощью параметра NOCHECK, вы увидите, что ограничение создается без проверки существующих данных, как показано ниже:

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

Чтобы снова включить ограничение CHECK, вы можете использовать команду ALTER TABLE T-SQL, но на этот раз с помощью оператора CHECK CONSTRAINT, как в сценарии ниже:

ALTER TABLE ConstraintDemo4 ПРОВЕРИТЬ ОГРАНИЧЕНИЕ CK__Constrain__Salar

Кроме того, вы можете включить все ограничения CHECK одновременно, используя команду T-SQL ниже:

ALTER TABLE ConstraintDemo4 ПРОВЕРИТЬ ВСЕ ОГРАНИЧЕНИЯ

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

Чтобы устранить эту проблему, команду DBCC CHECKCONSTRAINTS можно легко использовать для идентификации данных, которые нарушают условие ограничения в указанной таблице или ограничении, с учетом того, что эту команду нельзя запускать в часы пик, так как это повлияет на SQL Производительность сервера из-за того, что не используется моментальный снимок базы данных.

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

ОГРАНИЧЕНИЯ ПРОВЕРКИ DBCC (ConstraintDemo4)

В результате вы увидите два значения Salary, которые нарушают условие ограничения CHECK, как показано ниже:

Ограничение CHECK также можно создать с помощью инструмента SQL Server Management Studio, щелкнув правой кнопкой мыши нужную таблицу и выбрав параметр «Дизайн».В открывшемся окне «Дизайн» щелкните правой кнопкой мыши и выберите Проверить ограничения следующим образом:

В окне Проверить ограничения вы можете указать имя ограничения CHECK, выражение и, если требуется проверка существующих данных, как показано ниже:

Ограничение ПО УМОЛЧАНИЮ

Ограничение DEFAULT используется для предоставления значения столбца по умолчанию для вставленных строк, если значение для этого столбца не указано в инструкции INSERT.Ограничение по умолчанию помогает поддерживать целостность домена, предоставляя правильные значения для столбца в случае, если пользователь не предоставил для него значение. Значение по умолчанию может быть постоянным значением, значением системной функции или NULL.

В приведенном ниже операторе CREATE TABLE для простой таблицы с тремя столбцами ограничение DEFAULT определено для столбца EmployeeDate, которое присваивает значение системной функции GETDATE () для этого столбца на случай, если мы не укажем его в операторе INSERT:

CREATE TABLE ConstraintDemo5

(

ID INT PRIMARY KEY,

Имя VARCHAR (50) NULL,

EmployeeDate DATETIME NOT NULL DEFAULT GETDATE ()

)

9024

Если мы выполним два оператора INSERT ниже:

INSERT INTO ConstraintDemo5 ([ID], [NAME], EmployeeDate) VALUES (1, ‘Lorance’, ‘2016/10/22’)

INSERT INTO ConstraintDemo5 ([ID], [NAME] ) VALUES (2, ‘Shady’)

И проверьте вставленные записи, вы увидите, что значение столбца EmployeeDate для второй записи, которую мы не упомянули в инструкции INSERT, присвоено текущему значению даты и времени, как показано ниже:

Раскрытие узла ограничений под текущей таблицей покажет нам созданное имя ограничения DEFAULT, напоминая, что SQL Server присвоит ему уникальное имя, если мы не предоставим ему имя, как показано ниже:

Ограничение DEFAULT можно легко отбросить с помощью команды T-SQL ALTER TABLE… DROP CONSTRAINT ниже:

ALTER TABLE ConstraintDemo5

DROP CONSTRAINT DF__Constrain__Emplo__1332DBDC;

И создан с помощью приведенной ниже команды T-SQL ALTER TABLE… ADD CONSTRAINT:

ALTER TABLE ConstraintDemo5

Добавить ограничение DF__Constrain__Emplo DEFAULT (GETDATE ()) FOR EmployeeDate

Кроме того, ограничение DEFAULT можно определить с помощью SQL Server Management Studio, щелкнув правой кнопкой мыши нужную таблицу и выбрав параметр «Дизайн».Затем выберите столбец, которому вы назначите значение по умолчанию, просмотрев окно свойств столбца, как показано ниже:

Я надеялся, что эта и предыдущие статьи помогли объяснить шесть типов ограничений SQL Server. Пожалуйста, не стесняйтесь задавать любые вопросы в комментариях ниже.

Предыдущая статья из этой серии:

Полезные ссылки

Ахмад Ясин (Ahmad Yaseen) — инженер Microsoft по большим данным с глубокими знаниями и опытом в областях SQL BI, администрирования баз данных SQL Server и разработки.

Он является сертифицированным специалистом по решениям Microsoft в области управления данными и аналитикой, сертифицированным партнером по решениям Microsoft в области администрирования и разработки баз данных SQL, партнером разработчика Azure и сертифицированным инструктором Microsoft.

Кроме того, он публикует свои советы по SQL во многих блогах.

Посмотреть все сообщения от Ahmad Yaseen

Последние сообщения от Ahmad Yaseen (посмотреть все)

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

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

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

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

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

Чтобы создать ограничение внешнего ключа SQL, родительская таблица должна иметь столбец или столбец первичного ключа с ограничением UNIQUE.

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

CREATE TABLE Dept (

DeptID INT CONSTRAINT PK_Dept PRIMARY KEY

, DeptName VARCHAR (10)

)

Создать внешний ключ при создании таблицы

Ниже приведен пример сценария T-SQL для создания внешнего ключа SQL при создании таблицы без определения имени ограничения.Если мы не указываем имя ограничения, SQL-сервер создает ограничение внешнего ключа со случайным именем.

СОЗДАТЬ ТАБЛИЦУ Emp (

EmpID INT

, EmpFname VARCHAR (50)

, EmpLname VARCHAR (50)

, DeptID INT Департамент внешних ключевых ссылок (DeptID)

, IsActive BIT

)

Ниже приведен пример сценария T-SQL для создания внешнего ключа при создании таблицы с определенным именем ограничения.

СОЗДАТЬ ТАБЛИЦУ Emp (

EmpID INT

, EmpFname VARCHAR (50)

, EmpLname VARCHAR (50)

, DeptID INT CONSTRAINT FK_Emp ИНОСТРАННЫЕ КЛЮЧЕВЫЕ ССЫЛКИ Отдел (DeptID)

, IsActive BIT17

)

Создать внешний ключ после создания таблицы

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

СОЗДАТЬ ТАБЛИЦУ Emp (

EmpID INT

, EmpFname VARCHAR (50)

, EmpLname VARCHAR (50)

, DeptID INT

, IsActive BIT

)

ALTER TABLE [dbo]. [Dbo]. Emp] С ПРОВЕРКОЙ ДОБАВИТЬ ОГРАНИЧЕНИЕ [FK_Emp] ИНОСТРАННЫЙ КЛЮЧ ([DeptID])

ССЫЛКИ [dbo].[Dept] ([DeptID])

ИЗМЕНИТЬ ТАБЛИЦУ [dbo]. [Emp] ПРОВЕРИТЬ ОГРАНИЧЕНИЕ [FK_Emp]

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

ИЗМЕНИТЬ ТАБЛИЦУ [dbo]. [Emp] С ПРОВЕРОМ ДОБАВЛЕНИЯ ИНОСТРАННОГО КЛЮЧА ([DeptID])

ССЫЛКИ [dbo]. [Dept] ([DeptID])

ALTER TABLE [dbo].[Emp] ПРОВЕРИТЬ ОГРАНИЧЕНИЕ [FK_Emp]

Создать внешний ключ без проверки существующих данных

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

Если мы создадим ограничение с помощью проверки, это вызовет ошибку, указанную ниже, поскольку уже существующие данные нарушают правило.

Если вы по-прежнему хотите создать ограничение внешнего ключа, игнорируя существующие данные и проверяя правило для дальнейших изменений, используйте «WITH NOCHECK».Созданное вами ограничение помечено как ненадежное.

ALTER TABLE [dbo]. [Emp] WITH NOCHECK ADD CONSTRAINT [FK_Emp] FOREIGN KEY ([DeptID])

ССЫЛКИ [dbo]. [Dept] ([DeptID])

ALTER TABLE [ dbo]. [Emp] ПРОВЕРИТЬ ОГРАНИЧЕНИЕ [FK_Emp]

Создать внешний ключ с правилами УДАЛЕНИЯ / ОБНОВЛЕНИЯ

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

Мы видим, что данные существуют как в родительской, так и в дочерней таблице.

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

ALTER TABLE [dbo]. [Emp] WITH CHECK ADD CONSTRAINT [FK_Emp2] FOREIGN KEY ([DeptID])

REFERENCES [dbo]. [DeptID]) ON DELETE CASCADE

ИЗМЕНИТЬ ТАБЛИЦУ [dbo].[Emp] ПРОВЕРИТЬ ОГРАНИЧЕНИЕ [FK_Emp2]

удалить из отдела, где DeptID = 1

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

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

ИЗМЕНИТЬ ТАБЛИЦУ [dbo].[Emp] С ПРОВЕРКОЙ ДОБАВИТЬ ОГРАНИЧЕНИЕ [FK_Emp3] ИНОСТРАННЫЙ КЛЮЧ ([DeptID])

ССЫЛКИ [dbo]. [Dept] ([DeptID])

В КАСКАДЕ ОБНОВЛЕНИЯ

ALTER TABLE [dbo]. [Emp] ПРОВЕРИТЬ ОГРАНИЧЕНИЕ [FK_Emp3]

обновить Dept set DeptID = 3, где DeptID = 2

После выполнения вышеуказанного оператора мы видим, что DeptID обновляется до 3 для записей, имеющих DeptID 2 в дочерней таблице.

Аналогично у нас есть следующие действия.

SET NULL — устанавливает для значения столбца внешнего ключа SQL значение NULL, когда значение первичного ключа либо удаляется, либо обновляется до нового значения. Если столбец не допускает нулевых значений, обновление / удаление столбца первичного ключа завершается ошибкой и вызывает ошибку.
SET DEFAULT — устанавливает значение по умолчанию для столбца внешнего ключа при обновлении или удалении значения первичного ключа.Если ограничение по умолчанию не определено и столбец допускает значение NULL, тогда значение столбца внешнего ключа устанавливается в NULL. Если ограничение по умолчанию не определено и столбец не допускает значения NULL, возникает ошибка ниже, и изменение столбца первичного ключа откатывается.
НЕТ ДЕЙСТВИЙ — если обновление или удаление столбца первичного ключа не соответствует правилу ограничения, то изменение откатывается.

Чтобы изменить существующий внешний ключ с помощью T-SQL, мы должны сначала удалить ограничение внешнего ключа, а затем воссоздать его с новыми изменениями.

Отключение и включение ограничения внешнего ключа

Отключение ограничения

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

ALTER TABLE [Emp] NOCHECK CONSTRAINT [FK_Emp2]

Включить ограничение

Чтобы включить ограничение обратно, используйте инструкцию ниже.

ИЗМЕНИТЬ ТАБЛИЦУ [Emp] ПРОВЕРИТЬ ОГРАНИЧЕНИЕ [FK_Emp2]

Включить ограничение с проверкой существующих данных

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

ИЗМЕНИТЬ ТАБЛИЦУ [Emp]

WITH CHECK CHECK CONSTRAINT [FK_Emp2]

Не для тиражирования

Когда внешний ключ установлен «НЕ ДЛЯ ПОВТОРЕНИЯ», проверка выполняется только тогда, когда пользователь вставляет, удаляет или обновляет данные.

Проверка не запускается, когда агенты репликации синхронизируют изменения, сделанные пользователем, с другим концом (то есть подписчиком в случае транзакции и подписчиком и издателем в случае репликации слиянием).

Чтобы создать внешний ключ SQL с «НЕ ДЛЯ ПОВТОРЕНИЯ», используйте приведенный ниже сценарий. По умолчанию ограничение помечено как ненадежное.

ИЗМЕНИТЬ ТАБЛИЦУ [dbo].[Emp] С ПРОВЕРИТЬ ДОБАВИТЬ ОГРАНИЧЕНИЕ [FK_Emp2] ИНОСТРАННЫЙ КЛЮЧ ([DeptID])

ССЫЛКИ [dbo]. [Dept] ([DeptID])

НЕ ДЛЯ ПОВТОРЕНИЯ

Несмотря на то, что мы создаем внешний ключ с «FOR REPLICATION» на издателе, агент моментальных снимков создает сценарий как «NOT FOR REPLICATION», и внешний ключ создается как не для репликации в подписчике, когда применяется моментальный снимок.

Индексирование столбцов внешних ключей

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

В таких случаях помогает создание индекса для столбца внешнего ключа.

Администратор баз данных SQL Server, разработчик с большим опытом администрирования, разработки, настройки производительности, мониторинга, обеспечения высокой доступности и аварийного восстановления SQL Server

Последние сообщения Ранги Бабу (посмотреть все)

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

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

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

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

Внешние ключи и их реализация сложнее первичных ключей.

Techopedia объясняет внешний ключ

Хотя первичный ключ может существовать сам по себе, внешний ключ всегда должен где-то ссылаться на первичный ключ. Исходная таблица, содержащая первичный ключ, является родительской таблицей (также известной как ссылочная таблица). На этот ключ могут ссылаться несколько внешних ключей из других таблиц, известных как «дочерние» таблицы.

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

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

Например, если есть две таблицы, customer и order, связь между ними может быть создана путем введения внешнего ключа в таблицу заказов, которая ссылается на CUSTOMER_ID в таблице клиентов. Столбец CUSTOMER_ID существует как в таблицах клиентов, так и в таблицах заказов.

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

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

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

Некоторые ссылочные действия, связанные с действием внешнего ключа, включают следующее:

Каскад

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

Установить ноль

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

Триггеры

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

Установить по умолчанию

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

Ограничить

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

Нет действий

Это ссылочное действие аналогично действию «ограничения», за исключением того, что выполняется проверка отсутствия действий только после попытки переделать таблицу.

ИНОСТРАННЫЙ КЛЮЧ

SQL — javatpoint

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

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

Давайте рассмотрим пример, чтобы объяснить это:

Вот две таблицы, первая — это таблица студентов, а вторая — таблица заказов.

Здесь заказы отдают студенты.

Первый стол:

s_id	ЬавЬЫата	ПгвЬЫата	ГОРОД
1	Маурьих	Ajeet	Аллахабадских
2	Джайсвал	Ратан	Ghāziābād
3	ARORA	САУМЯ	МОДИНАГАР

Второй стол:

O_Id	Номер заказа	S_Id
1	99586465	2
2	78466588	2	9048 57698656	1

Здесь вы видите, что столбец «S_Id» в таблице «Заказы» указывает на столбец «S_Id» в таблице «Студенты».

Столбец «S_Id» в таблице «Студенты» является ПЕРВИЧНЫМ КЛЮЧОМ в таблице «Студенты».
Столбец «S_Id» в таблице «Заказы» является ИНОСТРАННЫМ КЛЮЧОМ в таблице «Заказы».

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

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

Ограничение SQL FOREIGN KEY НА СОЗДАТЬ ТАБЛИЦУ:

(определение ограничения внешнего ключа для одного столбца)

Для создания внешнего ключа в столбце «S_Id» при создании таблицы «Заказы»:

MySQL:

СОЗДАТЬ ТАБЛИЦЫ заказы
(
O_Id int NOT NULL,
Order_No int NOT NULL,
S_Id int,
ПЕРВИЧНЫЙ КЛЮЧ (O_Id),
ИНОСТРАННЫЙ КЛЮЧ (S_Id) ССЫЛКИ Лица (S_Id)
)

SQL Server / Oracle / MS Доступ:

СОЗДАТЬ ТАБЛИЦЫ Заказы
(
O_Id int NOT NULL PRIMAY KEY,
Order_No int NOT NULL,
S_Id int ИНОСТРАННЫЕ КЛЮЧЕВЫЕ ССЫЛКИ люди (S_Id)
)

Ограничение SQL FOREIGN KEY для ALTER TABLE:

Если таблица заказов уже создана и вы хотите создать ограничение FOREIGN KEY для столбца «S_Id», вы должны написать следующий синтаксис:

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

MySQL / SQL Server / Oracle / MS Доступ:

ALTER TABLE Заказы
ДОБАВИТЬ ОГРАНИЧЕНИЕ fk_PerOrders
ИНОСТРАННЫЙ КЛЮЧ (S_Id)
СПРАВОЧНАЯ ИНФОРМАЦИЯ Студенты (S_Id)

DROP SYNTAX для FOREIGN KEY COSTRAINT:

Если вы хотите удалить ограничение FOREIGN KEY, используйте следующий синтаксис:

MySQL:

ALTER TABLE Заказы
УДАЛИТЬ ИНОСТРАННЫЙ КЛЮЧ fk_PerOrders

SQL Server / Oracle / MS Доступ:

ALTER TABLE Заказы
УДАЛИТЬ ОГРАНИЧЕНИЕ fk_PerOrders

Разница между первичным и внешним ключом в SQL:

Это важное различие между первичным ключом и внешним ключом в SQL-

Первичный ключ не может быть нулевым, с другой стороны, внешний ключ может быть нулевым.

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

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

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

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

ИНОСТРАННЫЙ КЛЮЧ

SQL — полное руководство для новичков

Новичок? Тогда внешний ключ SQL может быть для вас чужим, но он необходим в реляционных базах данных. Тем не менее, некоторые разработчики могут удалять или игнорировать внешние ключи, когда сталкиваются с некоторыми осложнениями. Так что делать? Использовать внешний ключ или не использовать? Это руководство предназначено для вас, чтобы увидеть, насколько это важно и как найти и исправить некоторые проблемы в коде.

Содержание

Что такое внешний ключ SQL
Какие таблицы должны иметь ограничения внешнего ключа SQL
Преимущества использования внешних ключей в SQL Server
Когда внешний ключ SQL может быть проблемой (плюс исправление)
Создание, изменение и удаление ограничений внешнего ключа с помощью SQL Server Management Studio
Добавление, изменение и удаление внешнего ключа SQL с использованием схемы базы данных
Добавление, изменение и удаление внешнего ключа SQL Server с использованием T-SQL
Примеры ограничений внешнего ключа SQL Server
Проблемы и исправления

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

Короче говоря, внешний ключ SQL — это ключ, который связывает 2 таблицы.Однако в базах данных SQL внешний ключ также обеспечивает взаимосвязь. Вот почему это называется ограничением внешнего ключа.

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

Какие таблицы должны иметь ограничения внешнего ключа SQL?

Дочерние таблицы могут иметь внешние ключи. Один внешний ключ может ссылаться на другую таблицу.Также в «дочерней» таблице может быть несколько внешних ключей. В SQL Server внешний ключ может ссылаться на первичный ключ или уникальный ключ в другой таблице.

Как насчет саморекламы?

Это выходит за рамки общего определения внешнего ключа. Ссылка на себя означает, что вы можете назначить внешний ключ, который ссылается на , другой столбец в той же таблице . SQL Server, MySQL и Oracle поддерживают это.

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

Позже у нас будут примеры.

4 преимущества использования внешних ключей в SQL Server

Что делает внешние ключи обязательными для базы данных SQL? Давайте рассмотрим 4 пункта (здесь синтаксис ограничения не имеет значения).

1. Избегайте «пропавших без вести» данных

«Отсутствующие» данные — это значения внешнего ключа из дочерних таблиц без связанных значений первичного ключа из родительской таблицы.Их также называют осиротевшими рядами. Когда это происходит, мы можем сказать, что база данных имеет незначительную ссылочную целостность или ее нет.

При принудительном применении ограничений внешнего ключа «пропущенных» данных вообще не будет. Ядро базы данных не позволит удалить значение первичного ключа, на которое ссылается другая таблица. Аналогичным образом, вставка внешнего ключа в дочернюю таблицу, которого нет в первичных ключах родительской таблицы, вызовет ошибку.

Что самое худшее, что может случиться, если вы не используете ограничения внешнего ключа? Вот несколько:

Покупатели не получат товары, за которые они заплатили.
Больным не назначают лечение.
Отсутствующие контрольные списки пропускают меры предосторожности.

Вы можете обрабатывать этот материал вне базы данных, но вы должны его кодировать. Подробнее об этом позже.

Допустим, разработчик в вашей организации справился с тем же ограничением вне базы данных. Будет ли он отвечать и исправлять проблему в продакшене, если код не работает? Я так не думаю. А что, если вы администратор базы данных? Тогда вам придется убрать их беспорядок.Не очень обнадеживает, если вы спросите меня.

2. Избегайте противоречивых отчетов

Это относится к первому пункту. Если некоторые данные «отсутствуют», в разных отчетах появляются несовместимые итоги. Детали не соответствуют резюме. Строки-сироты складываются в итоги сводок. Между тем, подробный отчет не захватил потерянные строки из-за внутреннего соединения с родительскими таблицами.

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

3. Код не требуется, чтобы избежать осиротевших рядов

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

Хотите блестящую базу данных без потерянных строк? Конечно, вы делаете. Если вы когда-нибудь захотите проанализировать данные, вы будете рады, что использовали внешние ключи.Эта база данных станет хорошим источником для копирования необходимых данных в вашу промежуточную область.

4. Быстрое понимание взаимосвязей таблиц в диаграмме

SQL Server Management Studio имеет встроенный инструмент построения диаграмм для вашей базы данных. Первичные и внешние ключи делают схему базы данных информативной с первого взгляда. Однако это будет зависеть от того, сколько таблиц со связями вы включили в диаграмму.

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

Когда внешний ключ SQL может быть «проблемой» (плюс исправление)

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

Есть исправление? У вас есть 2 варианта.

Обязательно сначала заполните справочные или родительские таблицы.После этого заполните дочерние таблицы. Одно осложнение работает очень медленно. В других случаях возникает больше ошибок ограничения внешнего ключа. В последнем случае необходимо повторно оценить последовательность вставок и убедиться, что первичные ключи вставлены первыми. Если есть «медленная» проблема, рассмотрите следующий вариант.
Временно отключить внешние ключи и включить их после завершения миграции (и очистки). Вы можете сделать это в SQL Server Management Studio или использовать T-SQL ALTER TABLE.Однако легче сказать, чем сделать. На этом этапе вам понадобится больше терпения в дополнение к сообразительности и силе воли. Позже мы найдем синтаксис для отключения и повторного включения внешних ключей.

Еще одна вещь, которую следует учитывать, — это использование базы данных в качестве промежуточной области для OLAP или анализа данных. Предположим, что исходная транзакционная база данных очищена от потерянных строк. Или вы можете избежать этих строк с помощью кода. Затем вы можете отказаться от использования внешних ключей. Внешние ключи замедляют массовую вставку и обновление, особенно в огромных наборах данных.

Способы создания, редактирования и удаления взаимосвязей внешних ключей

Что нужно для добавления, редактирования или удаления внешних ключей? С этими 3 советами это легко.

Первые два шага используют графический интерфейс пользователя. Такие инструменты, как SQL Server Management Studio или dbForge Studio для SQL Server, являются очень хорошими кандидатами. Третий будет использовать код T-SQL. Выбор кода GUI или T-SQL зависит от ситуации.

1. Создание, изменение и удаление ограничения внешнего ключа с помощью SQL Server Management Studio

Вы можете добавить ограничения внешнего ключа при создании или изменении структуры таблицы с помощью Table Designer в SSMS.На рисунке 1 ниже показано, как получить к нему доступ из главного меню, когда структура таблицы открыта.

Рисунок 1. Доступ к отношениям внешнего ключа из главного меню.

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

Рисунок 2. Доступ к отношениям внешнего ключа из контекстного меню внутри конструктора таблиц.

После того, как вы выбрали Relationships , появится окно External Key Relationships :

Рисунок 3.Окно «Связи внешнего ключа» для добавления, редактирования и удаления связей внешнего ключа в таблице.

В окне Взаимосвязи внешнего ключа вы можете добавить новый внешний ключ или отредактировать / удалить существующий.

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

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

Рисунок 4. Окно «Таблицы и столбцы» для определения таблиц и столбцов первичного и внешнего ключей.

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

2. Добавление, изменение и удаление внешнего ключа SQL с помощью схемы базы данных

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

Рисунок 5. Щелкните таблицу внешних ключей и перетащите мышь к таблице первичных ключей.

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

Чтобы изменить существующее отношение, щелкните правой кнопкой мыши отношение на диаграмме. Затем выберите Свойства :

Рисунок 6. Первый шаг для редактирования отношения — щелкнуть правой кнопкой мыши существующее отношение на диаграмме.Затем выберите Свойства.

Затем в окне Свойства разверните Таблицы и столбцы и нажмите кнопку с многоточием :

Рисунок 7. Разверните Спецификации таблиц и столбцов и нажмите кнопку с многоточием.

После нажатия кнопки с многоточием появится окно Таблицы и столбцы . Вы можете изменить столбцы первичного и внешнего ключей (снова см. Рисунок 4 выше).

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

Рисунок 8. Удаление связи таблицы / внешнего ключа.

3. Добавление, изменение и удаление внешнего ключа SQL Server с помощью T-SQL

Третий способ добавить внешний ключ — через код T-SQL. Вы можете использовать CREATE TABLE и добавить ограничение внешнего ключа. Или вы также можете использовать ALTER TABLE, чтобы добавить это ограничение после создания таблицы.

Вот синтаксис для использования CREATE TABLE:

  - Внешний ключ с одним столбцом
СОЗДАТЬ ТАБЛИЦУ Table2
(ID INT IDENTITY (1,1) NOT NULL PRIMARY KEY CLUSTERED,
 col1 INT NULL ССЫЛКИ Table1 (col1)
)
ИДТИ

После определения имени и типа столбца вы можете добавить ССЫЛКИ в таблицу и столбец.Приведенный выше синтаксис показывает таблицу Table1 в столбце col1 . Обратите внимание, что имена столбцов в обеих таблицах должны быть одинаковыми, чтобы их можно было использовать для внешних ключей.

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

  - Внешний ключ с несколькими столбцами
СОЗДАТЬ ТАБЛИЦУ Table2
(ID INT IDENTITY (1,1) NOT NULL PRIMARY KEY CLUSTERED,
 col1 INT NOT NULL,
 col2 INT NOT NULL,
 col3 VARCHAR (10) NULL
 ОГРАНИЧЕНИЕ FK_Table1_Table2 FOREIGN KEY (col1, col2)
ССЫЛКИ Таблица1 (столбец1, столбец2)
)
ИДТИ

После создания таблицы вы можете добавить внешние ключи с помощью ALTER TABLE.Вот синтаксис:

  ALTER TABLE Table2 WITH CHECK ADD CONSTRAINT FK_Table1_Table2_2 FOREIGN KEY (col3)
ЛИТЕРАТУРА Таблица 3 (столбец 1)
ИДТИ

Чтобы удалить ограничение внешнего ключа, вы можете использовать ALTER TABLE с DROP CONSTRAINT:

  ИЗМЕНИТЬ ТАБЛИЦУ Table2
УДАЛИТЬ ОГРАНИЧЕНИЕ FK_Table1_Table2_2
ИДТИ

Теперь мы можем резюмировать 3 способа добавления, редактирования и удаления внешних ключей:

Примеры ограничения внешнего ключа SQL Server

Дочерняя таблица, имеющая 1 ссылку на родительскую таблицу

  - Одиночная ссылка
СОЗДАТЬ ТАБЛИЦУ [dbo].[Страны] (
[CountryID] [int] IDENTITY (1,1) NOT NULL,
[Country] [nvarchar] (50) NOT NULL,
[ContinentID] [int] NULL,
[Изменено] [datetime] НЕ ПУСТО,
 ОГРАНИЧЕНИЕ [PK_Country] ПЕРВИЧНЫЙ КЛЮЧ КЛАСТЕРИРОВАН
(
[CountryID] ASC
))
ИДТИ

ALTER TABLE [dbo]. [Country] WITH CHECK ADD CONSTRAINT [FK_Countries_Continent] FOREIGN KEY ([ContinentID])
ССЫЛКИ [dbo]. [Continent] ([ContinentID])

ИДТИ

ИЗМЕНИТЬ ТАБЛИЦУ [dbo]. [Страны] ПРОВЕРИТЬ ОГРАНИЧЕНИЕ [FK_Countries_Continent]
ИДТИ

Чтобы наглядно представить эту взаимосвязь, взгляните на рисунок 9 ниже:

Рисунок 9.Таблица с 1 внешним ключом.

ContinentID — это ключ, который связывает две таблицы вместе.

Дочерняя таблица с несколькими ссылками

Таблица SportsCar имеет несколько ссылок на три разные таблицы:

  - Множественные ссылки
СОЗДАТЬ ТАБЛИЦУ [dbo]. [SportsCars] (
[SportsCarID] [int] IDENTITY (1,1) NOT NULL,
[ManufacturerID] [int] NULL,
[StyleID] [int] NULL,
[CountryID] [int] NULL,
[Модель] [nvarchar] (50) NOT NULL,
[Годы] [varchar] (50) НЕ NULL,
[Примечания] [varchar] (255) NOT NULL,
[Изменено] [datetime] НЕ ПУСТО,
 ОГРАНИЧЕНИЕ [PK_SportsCars] ПЕРВИЧНЫЙ КЛЮЧ КЛАСТЕРИРОВАН
(
[SportsCarID] ASC
))
ИДТИ

ИЗМЕНИТЬ ТАБЛИЦУ [dbo].[SportsCars] С ПРОВЕРКОЙ ДОБАВИТЬ ОГРАНИЧЕНИЕ [FK_SportsCars_Country] ИНОСТРАННЫЙ КЛЮЧ ([CountryID])
ССЫЛКИ [dbo]. [Страны] ([CountryID])
ИДТИ

ИЗМЕНИТЬ ТАБЛИЦУ [dbo]. [SportsCars] ПРОВЕРИТЬ ОГРАНИЧЕНИЕ [FK_SportsCars_Country]
ИДТИ

ИЗМЕНИТЕ ТАБЛИЦУ [dbo]. [SportsCars] С ПРОВЕРКОЙ ДОБАВИТЬ ОГРАНИЧЕНИЕ [FK_SportsCars_Manufacturer] ИНОСТРАННЫЙ КЛЮЧ ([ManufacturerID])
ССЫЛКИ [dbo]. [Производители] ([ManufacturerID])
ИДТИ

ИЗМЕНИТЬ ТАБЛИЦУ [dbo]. [SportsCars] ПРОВЕРИТЬ ОГРАНИЧЕНИЕ [FK_SportsCars_Manufacturer]
ИДТИ

ИЗМЕНИТЕ ТАБЛИЦУ [dbo]. [SportsCars] С ПРОВЕРКОЙ ДОБАВИТЬ ОГРАНИЧЕНИЕ [FK_SportsCars_Styles] ИНОСТРАННЫЙ КЛЮЧ ([StyleID])
СПИСОК ЛИТЕРАТУРЫ [dbo].[Стили] ([StyleID])
ИДТИ

ИЗМЕНИТЬ ТАБЛИЦУ [dbo]. [SportsCars] ПРОВЕРИТЬ ОГРАНИЧЕНИЕ [FK_SportsCars_Styles]
ИДТИ

Вот как это отображается на диаграмме базы данных:

Рисунок 10. Таблица SportsCars имеет 3 ссылки на таблицы или внешние ключи.

Самостоятельно ссылающийся внешний ключ в SQL Server

Иерархия позиций показывает самореференцию в следующей таблице:

  СОЗДАТЬ ТАБЛИЦУ [dbo]. [Ranks] (
[RankId] [int] IDENTITY (1,1) NOT NULL,
[Rank] [varchar] (50) NOT NULL,
[RankLevel] [smallint] НЕ NULL,
[RankParentId] [int] NULL,
 ОГРАНИЧЕНИЕ [PK_Ranks] ПЕРВИЧНЫЙ КЛЮЧ КЛАСТЕРИРОВАН
(
[RankId] ASC
)) НА [ПЕРВИЧНОМ]
ИДТИ

ИЗМЕНИТЬ ТАБЛИЦУ [dbo].[Ranks] WITH CHECK ADD CONSTRAINT [FK_Ranks_Ranks] FOREIGN KEY ([RankParentId])
ССЫЛКИ [dbo]. [Ranks] ([RankId])
ИДТИ

ALTER TABLE [dbo]. [Ranks] ПРОВЕРИТЬ ОГРАНИЧЕНИЕ [FK_Ranks_Ranks]
ИДТИ

Схема этой ссылки проста. Линия указывает на ту же таблицу в самостоятельной ссылке.

Рисунок 11. Пример ссылки на себя на диаграмме базы данных.

С ВКЛЮЧЕНИЕМ ОБНОВЛЕНИЯ и ПРИ УДАЛЕНИИ

При ON UPDATE CASCADE при обновлении значения столбца первичного ключа обновляются и значения внешнего ключа в связанных таблицах.Между тем, когда вы используете ON DELETE CASCADE, удаление первичного ключа также удаляет внешние ключи. По умолчанию для ON UPDATE и ON DELETE НЕ ДЕЙСТВИЕ.

Вот пример КАСКАДА ОБНОВЛЕНИЯ и УДАЛЕНИЯ:

  ALTER TABLE [dbo]. [Country] WITH CHECK ADD CONSTRAINT [FK_Countries_Continent] FOREIGN KEY ([ContinentID])
ССЫЛКИ [dbo]. [Continent] ([ContinentID])
НА КАСКАДЕ ОБНОВЛЕНИЙ
НА КАСКАДЕ УДАЛЕНИЯ
ИДТИ

Отключение ограничения внешнего ключа SQL с помощью операторов INSERT и UPDATE

Следующее приведет к отключению существующего ограничения внешнего ключа.Обратите внимание, что отношения все еще существуют.

  ИЗМЕНИТЬ ТАБЛИЦУ [dbo]. [SportsCars] NOCHECK CONSTRAINT [FK_SportsCars_Country]
ИДТИ

Это не по умолчанию и не рекомендуется. Но для ускорения массовых вставок и обновлений вы можете временно отключить внешний ключ, как показано выше. После того, как вы закончите, вы должны переключить его обратно с помощью ПРОВЕРИТЬ ОГРАНИЧЕНИЕ.

  ИЗМЕНИТЬ ТАБЛИЦУ [dbo]. [SportsCars] ПРОВЕРИТЬ ОГРАНИЧЕНИЕ [FK_SportsCars_Country]
ИДТИ

Проблемы и исправления

В этом разделе показано, что происходит, когда вы ВСТАВЛЯЕТЕ, ОБНОВЛЯЕТЕ или УДАЛЯЕТЕ записи с внешними ключами.Это также предполагает, что внешние ключи не отключены с NOCHECK CONSTRAINT. Это поможет вам, когда вы столкнетесь с этими типичными проблемами.

ВСТАВИТЬ

  - это вызовет ошибку, потому что countryID = 47 не существует в таблице стран.
ВСТАВИТЬ В SportsCars
(ManufacturerID, StyleID, CountryID, Model, Years, Notes)
ЦЕННОСТИ (108, 10, 47, F2, 2021, Limited Edition)
ИДТИ

Вот сообщение об ошибке:

  Сообщение 547, уровень 16, состояние 0, строка 56
Оператор INSERT конфликтует с ограничением FOREIGN KEY «FK_SportsCars_Country».Конфликт произошел в базе данных «Транспортные средства», таблица «dbo.Countries», столбец «CountryID».
Заявление было прекращено.

Исправление : сначала добавьте CountryID = 47 в таблицу стран . Затем повторно запустите указанную выше инструкцию INSERT. Последовательность начинается со вставки записей в родительскую таблицу, а затем в дочернюю таблицу.

В ОБНОВЛЕНИИ

  - Обновление CountryID до 47 вызовет ошибку.
ОБНОВЛЕНИЕ SportsCars
УСТАНОВИТЬ CountryID = 47
ГДЕ ManufacturerID = 108
ИДТИ

Вот ошибка ОБНОВЛЕНИЯ:

  Сообщение 547, уровень 16, состояние 0, строка 60
Оператор UPDATE конфликтует с ограничением FOREIGN KEY «FK_SportsCars_Country».Конфликт произошел в базе данных «Транспортные средства», таблица «dbo.Countries», столбец «CountryID».
Заявление было прекращено.

Исправление : добавьте CountryID = 47 в таблицу стран . Затем повторно запустите инструкцию UPDATE.

ПРИ УДАЛЕНИИ

  - Это вызовет ошибку, потому что ManufacturerID = 108 указан в таблице SportsCars
УДАЛИТЬ ОТ производителей
ГДЕ ManufacturerID = 108

Этот код вызовет ошибку, как показано ниже:

  Сообщение 547, уровень 16, состояние 0, строка 64
Оператор DELETE конфликтует с ограничением REFERENCE «FK_SportsCars_Manufacturer».Конфликт произошел в базе данных «Транспортные средства», таблица «dbo.SportsCars», столбец «ManufacturerID».
Заявление было прекращено.

Исправление : удалите соответствующие записи из таблицы SportsCars с ManufacturerID = 108. Затем повторно запустите оператор DELETE, указанный выше. Другой способ — включить КАСКАД УДАЛЕНИЯ, если это возможно. Последовательность начинается с удаления записей из дочерних таблиц, а затем — из родительской.

Еда на вынос

Итак, внешние ключи все еще чужды вам?

Давайте подведем итоги того, что мы узнали на данный момент.

Внешние ключи связывают две таблицы (или одну таблицу при использовании ссылки на себя). Они нужны вам для обеспечения ссылочной целостности.
Для добавления, редактирования или удаления ограничений внешнего ключа можно использовать инструмент с графическим пользовательским интерфейсом или T-SQL.
Для инструментов с графическим интерфейсом вы можете использовать SQL Server Management Studio или dbForge Studio для SQL Server. Оба предлагают схемы баз данных и конструкторы таблиц для создания таблиц с первичными и внешними ключами.
CREATE TABLE и ALTER TABLE подходят для добавления и удаления ограничений внешнего ключа.
Вы можете временно отключить внешние ключи с помощью NOCHECK CONSTRAINT в ALTER TABLE. Это ускорит массовую вставку и обновление. Но не забудьте снова включить его с помощью ПРОВЕРИТЬ ОГРАНИЧЕНИЕ.
Чтобы избежать ошибок с внешними ключами, соблюдайте правильную последовательность. Для INSERT и UPDATE вставьте сначала в родительскую таблицу, а затем в дочерние таблицы. Для DELETE сначала удалите дочерние записи, а затем удалите родительские записи.

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

Разработчик программного обеспечения и менеджер проектов с более чем 20-летним опытом разработки программного обеспечения. Его последние технологические предпочтения включают C #, SQL Server BI Stack, Power BI и Sharepoint. Эдвин сочетает свои технические знания с новейшими навыками написания контента, чтобы помочь новому поколению энтузиастов технологий.

Последние сообщения Эдвина Санчеса (посмотреть все)

Значения внешнего и первичного ключей — в чем разница?

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

Все примеры этого урока основаны на Microsoft SQL Server Management Studio и базе данных AdventureWorks. Воспользуйтесь моим руководством «Начало работы с SQL Server », чтобы изучить SQL Server уже сегодня!

В чем разница между первичным ключом и внешним ключом?

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

Первичные ключи

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

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

Чтобы быть первичным ключом, должны выполняться несколько условий:

Столбцы, определяющие первичный ключ, уникальны.
Каждый столбец может содержать повторяющиеся значения; однако комбинация значений столбца уникальна.
В столбцах первичного ключа нет значения NULL. Я бы также расширил это, включив в него «пустые» значения.
При определении таблицы вы указываете первичный ключ. Таблица имеет только один первичный ключ, и его определение обязательно.
Первичные ключи хранятся в индексе.
Индекс поддерживает требование уникальности первичного ключа. Это также упрощает обратную связь значений внешнего ключа с соответствующими значениями первичного ключа, о чем мы узнаем в следующем разделе.

Хотите узнать больше о проектировании баз данных? Одно из первых препятствий, которое вам нужно преодолеть, — это нормализация базы данных. Получите наше руководство, которое поможет вам с проектированием и нормализацией базы данных. Мы берем сверхсложную тему и делаем ее легкой для понимания!

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

Внешний ключ — это набор из одного или нескольких столбцов в таблице, который ссылается на первичный ключ в другой таблице. Нет никакого специального кода, конфигураций или определений таблиц, которые вам нужно разместить, чтобы официально «обозначить» внешний ключ.

На схеме ниже посмотрите на таблицу SalesOrderHeader. Столбец SalesOrderHeader.CurrencyRateID является внешним ключом, поскольку он связан с CurrencyRate.CurrencyRateID. Этот столбец CurrencyRate.CurrencyRateID является первичным ключом таблицы CurrencyRate. Пример внешнего ключа

Ключи, работающие вместе

Посмотрите на следующую диаграмму. Какой столбец является внешним ключом? Значение внешнего ключа, являющееся частью PK

Если вы сказали, что это был PersonPhone.BusinessEntityID, то вы правы.Причина, по которой это внешний ключ, заключается в том, что он ссылается на первичный ключ, Person.BusinessEntityID, в другой таблице.

По совпадению, PersonPhone.BusinessEntityID — это не только внешний ключ, но и часть первичного ключа PersonPhone. Первичный ключ таблицы PersonPhone представляет собой комбинацию BusinessEntityID, PhoneNumber и PhoneNumberTypeID.

Вот идеи внешнего ключа, о которых следует помнить:

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

Хотите узнать об индексах базы данных? Вы можете узнать больше о том, как работают индексы здесь!

Можно ли в таблице PersonPhone найти другой внешний ключ (см. Ответ в конце статьи)?

Использование обозревателя объектов для поиска ключей базы данных

При использовании SSMS в проводнике объектов можно найти различную полезную информацию.Вам не нужно глубоко копать, чтобы найти первичные ключи. Когда вы показываете столбцы таблицы, но щелкаете папку «Столбцы», столбцы первичного ключа имеют золотые ключи рядом с ними. Поиск первичных и внешних ключей в обозревателе объектов

Кроме того, если какой-либо из внешних ключей определен в ограничениях внешнего ключа, который мы узнаем в следующем разделе, то после этих столбцов стоит FK. На приведенной выше диаграмме они обведены зеленым кружком.

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

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

Используя первую диаграмму в качестве нашего примера, вы не можете ввести SalesOrderHeader.CurrencyRateID, если он еще не существует в таблице CurrencyRate.

Эти ограничения вступают в силу несколькими способами:

Они запрещают вам изменять значение внешнего ключа на значение, которое не существует в качестве значения в первичном ключе связанной таблицы.
Они не дают вам удалить строку из таблицы первичного ключа. Это мешает вам создавать потерянные записи. Сиротские записи описываются как «дочерние записи без родителей».
Они не позволяют добавлять значение внешнего ключа, которого нет в первичном ключе.

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

Сравнение первичных ключей с внешними ключами

Подведем итог — сравнение первичных ключей с внешними ключами

Ответ на вопрос: Ранее мы просили вас идентифицировать другой внешний ключ в таблице PersonPhone.Правильный ответ — PhoneNumberTypeID.

Заключение

Первичный ключ необходим для определения таблицы реляционной базы данных.

Внешние ключи sql: примеры объединения таблиц, удаление ограничения foreigh key

1.2.5. Первичный и внешний ключ

1.2.5. Первичный ключ

1.2.6. Внешний ключ

Отношение один к одному

Многие ко многим

Взаимные блокировки и внешние ключи в SQL Server / Блог компании OTUS / Хабр

Введение

Что происходит за кулисами INSERT

Взаимные блокировки

FOREIGN KEY, видео урок 13

Внешние ключи (FK) это

Внешний ключ и родительский ключ

Многостолбцовые внешние ключи

Смысл внешнего и родительского ключей

Видео урок: Внешние ключи SQL

Полезные ссылки

Все видео уроки SQL

Похожие статьи:

sql — В чем практическая польза FOREIGN KEY в таблицах MySQL?

Что такое внешние ключи sql. Что это

Индексы в ограничениях внешнего ключа

Ссылочная целостность

Каскадная ссылочная целостность

Триггеры и каскадные ссылочные действия

1.2.5. Первичный ключ

1.2.6. Внешний ключ

Отношение один к одному

Многие ко многим

Что такое первичный ключ?

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

Разница между основным ключом и внешним ключом

Основы первичного ключа и внешнего ключа

Отношение первичного ключа и внешнего ключа

Дублирующие значения первичного ключа и внешнего ключа

NULL первичного ключа и внешнего ключа

Временная таблица первичного ключа и внешнего ключа

Удаление основного ключа и внешнего ключа

Первичный ключ или внешний ключ: сравнительная таблица

Резюме основных ключей

Примечание о терминологии

PRIMARY KEY

UNIQUE

FOREIGN KEY

Как найти зависимости внешнего ключа в SQL Server?

НОУ ИНТУИТ | Лекция | Определение ограничений целостности

Таблицы с ограничениями в стандарте языка

Создание таблицы

Ограничения

Обязательные данные

Требования конкретного предприятия

Ограничения для доменов полей

Целостность сущностей

Ссылочная целостность

Изменение и удаление таблицы

Создание отношений по внешнему ключу — SQL Server

В этой статье

Разрешения

Пределы и ограничения

Создание отношения внешнего ключа в конструкторе таблиц

Использование SQL Server Management Studio

Создать внешний ключ в новой таблице

с использованием Transact-SQL

Создать внешний ключ в существующей таблице

с использованием Transact-SQL

Следующие шаги

ИНОСТРАННЫЙ КЛЮЧ, ПРОВЕРКА и ПО УМОЛЧАНИЮ

Ограничение внешнего ключа

Ограничение ПРОВЕРИТЬ

Ограничение ПО УМОЛЧАНИЮ

Полезные ссылки

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

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

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

Создать внешний ключ при создании таблицы

Создать внешний ключ после создания таблицы

Создать внешний ключ без проверки существующих данных

Создать внешний ключ с правилами УДАЛЕНИЯ / ОБНОВЛЕНИЯ

Отключение и включение ограничения внешнего ключа

Отключение ограничения