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

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

Введение

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

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

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

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

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

Это гарантирует, что каждая строка в таблице ProductVendor содержит уникальную комбинацию ProductID и VendorID. Это предотвращает вставку повторяющихся строк.

Некоторые ключевые факты о первичных ключах (SQL Server)

• Таблица может содержать только одно ограничение первичного ключа.
• Первичный ключ не может превышать 16 столбцов и иметь общую длину ключа 900 байт.
• Индекс, сгенерированный ограничением первичного ключа, не может привести к тому, что количество индексов в таблице превысит 999 некластеризованных индексов и 1 кластеризованный индекс.
• Если для ограничения первичного ключа не указано clustered или nonclustered, используется clustered, если в таблице нет кластеризованного индекса.
• Все столбцы, определенные в ограничении первичного ключа, должны быть определены как отличные от нулевых. Если допустимость значений NULL не указана, все столбцы, участвующие в ограничении первичного ключа, имеют значение, отличное от NULL.

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

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

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

значение для SalesPersonID нельзя вставить в таблицу SalesOrderHeader , если оно еще не существует в таблице SalesPerson .

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

Ручное создание индекса по внешнему ключу часто полезно.

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

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

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

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

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

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

НЕТ ДЕЙСТВИЙ
Компонент Database Engine выдает ошибку, и действие удаления или обновления строки в родительской таблице откатывается.

КАСКАД
Соответствующие строки обновляются или удаляются в ссылочной таблице, когда эта строка обновляется или удаляется в родительской таблице.

CASCADE нельзя указать, если столбец метки времени является частью внешнего ключа или ключа, на который указывает ссылка. ON DELETE CASCADE нельзя указать для таблицы с триггером INSTEAD OF DELETE.

ON UPDATE CASCADE нельзя указать для таблиц с триггерами INSTEAD OF UPDATE.

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

УСТАНОВИТЬ ПО УМОЛЧАНИЮ
Все значения, составляющие внешний ключ, устанавливаются в значения по умолчанию, если соответствующая строка в родительской таблице обновляется или удаляется. Чтобы это ограничение выполнялось, все столбцы внешнего ключа должны иметь определения по умолчанию.

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

Нельзя указать для таблиц с триггерами INSTEAD OF UPDATE.

Создание первичного ключа (T-SQL)

CREATE TABLE Production.TransactionHistoryArchive1
   (
      TransactionID int IDENTITY (1,1) NOT NULL
      , CONSTRAINT PK_TransactionHistoryArchive1_TransactionID PRIMARY KEY CLUSTERED (TransactionID)
   )
;

Удаление первичного ключа

USE AdventureWorks2012;  
GO  
-- Return the name of primary key.  
SELECT name  
FROM sys.key_constraints  
WHERE type = 'PK' AND OBJECT_NAME(parent_object_id) = N'TransactionHistoryArchive';  
GO  
-- Delete the primary key constraint.  
ALTER TABLE Production.TransactionHistoryArchive  
DROP CONSTRAINT PK_TransactionHistoryArchive_TransactionID;   
GO

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

CREATE TABLE Sales.TempSalesReason 
   (
      TempID int NOT NULL, Name nvarchar(50)
      , CONSTRAINT PK_TempSales PRIMARY KEY NONCLUSTERED (TempID)
      , CONSTRAINT FK_TempSales_SalesReason FOREIGN KEY (TempID)
        REFERENCES Sales.SalesReason (SalesReasonID)
        ON DELETE CASCADE
        ON UPDATE CASCADE
   )
;