Foreign key references: SQL FOREIGN KEY Constraint

Содержание

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

Редактировать

Твиттер LinkedIn Фейсбук Эл. адрес

Статья
18.11.2022
5 минут на чтение

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

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

Разрешения

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

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

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

Ограничение внешнего ключа не обязательно должно быть связано только с ограничением первичного ключа в другой таблице. Внешние ключи также могут быть определены для ссылки на столбцы ограничения UNIQUE в другой таблице.
Когда в столбец ограничения FOREIGN KEY вводится значение, отличное от NULL, это значение должно существовать в указанном столбце. В противном случае возвращается сообщение об ошибке нарушения внешнего ключа. Чтобы убедиться, что все значения ограничения составного внешнего ключа проверены, укажите NOT NULL во всех участвующих столбцах.
Ограничения FOREIGN KEY могут ссылаться только на таблицы в одной и той же базе данных на одном сервере. Ссылочная целостность между базами данных должна быть реализована с помощью триггеров. Дополнительные сведения см. в разделе СОЗДАНИЕ ТРИГГЕРА.
Ограничения 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.) Повышение имеет следующие ограничения:
- Для операций DELETE и UPDATE DML поддерживается более 253 ссылок на внешние ключи. Операции MERGE не поддерживаются.
- Таблица со ссылкой внешнего ключа на себя по-прежнему ограничена 253 ссылками внешнего ключа.
- Более 253 ссылок на внешние ключи в настоящее время недоступны для индексов columnstore, таблиц, оптимизированных для памяти, или базы данных Stretch.
Важно
База данных Stretch устарела в SQL Server 2022 (16.x). Эта функция будет удалена в будущей версии Microsoft SQL Server. Избегайте использования этой функции в новых разработках и планируйте модифицировать приложения, которые в настоящее время используют эту функцию.
Ограничения FOREIGN KEY не применяются к временным таблицам.
Если внешний ключ определен в столбце пользовательского типа CLR, реализация этого типа должна поддерживать двоичный порядок. Дополнительные сведения см. в разделе Пользовательские типы CLR.
Столбец типа varchar(max) может участвовать в ограничении FOREIGN KEY, только если первичный ключ, на который он ссылается, также определен как тип varchar(max) .

Использовать SQL Server Management Studio

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

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

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

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

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

 CREATE TABLE Sales.TempSalesReason
   (
      TempID int NOT NULL, имя nvarchar(50)
      , CONSTRAINT PK_TempSales ПЕРВИЧНЫЙ КЛЮЧ НЕКЛАСТЕРНЫЙ (TempID)
      , CONSTRAINT FK_TempSales_SalesReason ВНЕШНИЙ КЛЮЧ (TempID)
        ССЫЛКИ Sales.SalesReason (SalesReasonID)
        НА УДАЛЕНИЕ КАСКАД
        НА КАСКАД ОБНОВЛЕНИЙ
   )
;

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

Используйте Transact-SQL

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

 ALTER TABLE Sales.TempSalesReason
   ДОБАВИТЬ ОГРАНИЧЕНИЕ FK_TempSales_SalesReason ВНЕШНИЙ КЛЮЧ (TempID)
      ССЫЛКИ Sales.SalesReason (SalesReasonID)
      НА УДАЛЕНИЕ КАСКАД
      НА КАСКАД ОБНОВЛЕНИЙ
;

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

Ограничения первичного и внешнего ключей
GRANT Права доступа к базе данных
ИЗМЕНИТЬ ТАБЛИЦУ
СОЗДАТЬ ТАБЛИЦУ
ALTER TABLE table_constraint.

Обратная связь

Отправить и просмотреть отзыв для

Этот продукт Эта страница

Просмотреть все отзывы о странице

Просто запустите этот запрос!

Опубликовано 23 мая 2022 г. 30 июня 2022 г. от Josh D

Время чтения: 3 минуты

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

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

Просто запросите системные таблицы sys.foreign_keys и sys.foreign_key_columns!

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

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

Вот запрос:

 ВЫБЕРИТЕ F.NAME как «Имя ограничения внешнего ключа»,
OBJECT_NAME(F.parent_object_id) AS 'Ссылочная/дочерняя таблица',
COL_NAME(FC.parent_object_id, FC.parent_column_id) AS 'Ссылочный/дочерний столбец',
OBJECT_NAME(FC.referenced_object_id) AS 'Ссылочная/Родительская таблица',
COL_NAME(FC.referenced_object_id, FC.referenced_column_id) AS 'Ссылочный/родительский столбец'
ОТ sys.foreign_keys AS F
ВНУТРЕННЕЕ СОЕДИНЕНИЕ sys.foreign_key_columns КАК FC
ON F.OBJECT_ID = FC.constraint_object_id
--WHERE OBJECT_NAME (F.referenced_object_id) = 'table_name'
ORDER BY OBJECT_NAME(F.parent_object_id)

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

‘table_name’ именем таблицы.

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

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

Взгляните на следующую таблицу Orders :

Столбец CustID является внешней ссылкой на таблицу Customers , чтобы сообщить нам , кто разместил заказ . Столбец ProdID является внешней ссылкой на таблицу Products , чтобы сообщить нам какой продукт был приобретен .

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

Мы можем просмотреть детали одного из этих ограничений, записав его в новое окно редактора запросов, например так:

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

Запустить наш запрос стало проще

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

Вот пример:

Вот разбивка столбцов, возвращаемых этим запросом:

Имя ограничения внешнего ключа — это имя ограничения внешнего ключа.
Ссылочная/дочерняя таблица – Ссылочная таблица, иногда называемая дочерней стол.
Ссылочный/дочерний столбец — Ссылочный столбец, иногда называемый дочерним столбцом .
Ссылочная/Родительская таблица — ссылочная таблица, иногда называемая родительской таблицей .
Ссылочный/родительский столбец — ссылочный столбец, иногда называемый родительским столбцом . Это столбец, из которого берутся значения. Столбец ссылок может содержать только те числа, которые существуют в этом столбце.

Штирлиц — Сеть печатных салонов в Перми