Взаимные блокировки и внешние ключи в SQL Server / Хабр
Введение
В реляционных базах данных внешние ключи (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) DECLARE @Counter AS INT=1 DECLARE @RoundNumber AS INT WHILE @Counter <=10000 BEGIN SET @Counter = @Counter+1 SELECT @RoundNumber = ROUND(((10000) * RAND() + 1), 0) INSERT INTO ##Emp VALUES(@Counter,'EmpFname', 'EmpLname',@RoundNumber , 0) END
Следующие запросы выполним в разных сессиях.
Сначала «Часть 1» первого запроса:
--- Запрос-1: --*** Часть 1 ***-- BEGIN TRAN INSERT INTO [dbo].Department (DepartmentId, DepartmentName) VALUES (10000,N'Lauren') --- *** --- -- *** Часть 2 ***-- INSERT INTO [Employee] SELECT * FROM ##Emp WITH(NOLOCK) WHERE DepId <=10000 --- *** ---
И первую часть второго запроса:
--- Запрос-2: --*** Часть 1 ***-- BEGIN TRAN INSERT INTO [dbo].Department (DepartmentId,DepartmentName) VALUES (10001, N'Lauren') --- *** --- --*** Часть 2 ***-- INSERT INTO [Employee] SELECT * FROM ##Emp WITH(NOLOCK) WHERE DepId <=9999 --- *** ---
А теперь — вторые части запросов.
В результате возникла взаимная блокировка.
Давайте проанализируем, что произошло:
Первая часть Запроса-1 открывает транзакцию и вставляет строку в таблицу Department. Страница данных Department блокируется монопольной блокировкой намерения (IX, intent exclusive lock), а вставленная строка — монопольной блокировкой (X, exclusive lock).
Первая часть Запроса-2 также открывает транзакцию и вставляет строку в Department. Страница данных таблицы Department блокируется монопольной блокировкой намерения (IX), а вставленная строка — монопольной блокировкой (X). На данный момент проблем с блокировками нет.
Вторая часть Запроса-1, он начинает сканировать первичные ключи таблицы Department для проверки ссылочной целостности вставленных строк. Однако одна из строк заблокирована монопольной блокировкой в Запросе-2. В этом случае Запрос-1 должен дождаться завершения Запроса-2.
Запрос-2 блокируется при попытке прочитать строки, вставленные в Department в Запросе-1. У нас получилась взаимная блокировка.
Приведенный ниже граф взаимных блокировок иллюстрирует то, о чем мы говорили. Сессия 71 (Запрос-1) получил монопольную блокировку (X) для строк таблицы Employee и хочет получить разделяемую блокировку (S) для строк таблицы Department.
Устранение взаимных блокировок
Мы с вами увидели, что при массовых INSERT проверка целостности внешнего ключа вызывает проблему с блокировками. На самом деле эта проблема связана с методом доступа к данным родительской таблицы. Взглянув на план выполнения второй части запросов, мы увидим оператор Merge Join.
INSERT INTO [Employee] SELECT * FROM ##Emp WITH(NOLOCK) WHERE DepId <=9999
Соединение Merge Join является самым эффективным, но требует предварительной сортировки входных данных. В нашем случае при сканировании родительской таблицы Merge Join сталкивается с заблокированной строкой, и не может продолжить сканирование, пока блокировка не будет снята.
Мы можем изменить метод доступа к данным с помощью OPTION (LOOP JOIN). При использовании хинта LOOP JOIN, оптимизатор запросов SQL Server сгенерирует другой план выполнения и заменит оператор Merge Join оператором Nested Loops, а оператор Clustered Index Scan будет заменен оператором Clustered Index Seek. С помощью Clustered Index Seek доступ к данным родительской таблицы осуществляется напрямую, поэтому не требуется ждать заблокированных строк. С другой стороны, оператор Nested Loops выполняет построчное чтение, а Merge Join — одно последовательное чтение. Эти два изменения метода доступа к данным снижают вероятность блокировки запроса из-за наличия других блокировок.
INSERT INTO [Employee] SELECT * FROM ##Emp WITH(NOLOCK) WHERE DepId <=9999 OPTION (LOOP JOIN)
Row Count Spool используется для подсчета количества строк, возвращаемых оператором Clustered Index Seek, и передачи этой информации в оператор Nested Loops.
Этот оператор используется оптимизатором запросов SQL Server для проверки существования строк, но не содержащихся в них данных.
Заключение
В этой статье мы узнали, как внешние ключи влияют на план запроса INSERT и добавляют некоторые операции в процесс его выполнения. Также мы увидели, что в некоторых ситуациях внешние ключи могут приводить к взаимным блокировкам. Для устранения проблем с блокировками можно использовать хинт LOOP JOIN.
Материал подготовлен в рамках курса «MS SQL Server Developer». Всех желающих приглашаем на открытый урок «SQL Server и Docker». На открытом уроке мы поговорим о контейнерах, а также рассмотрим развертывание SQL Server в контейнерах.
РЕГИСТРАЦИЯ
Для новичков о внешних ключах в базах данных | by Victor Bolshov
Поступил такой вопрос от девушки, которая изучает программирование, а именно PHP+MySQL:
Собственно, вот вопрос…
У меня есть табличка orders и табличка users.
1) При текущем положении дел код пхп такой-то, чтобы достать что то из базы
2) При связности через foreign key код пхп такой-то…
Сейчас в orders есть поле user_id, по которому и осуществляется связь. Вопрос — как правильно увязать их при помощи foreign key? И самое главное, как потом эту связь использовать в php? Идеально было бы получить ответ в виде текста (логики) и кода. То есть, например:Итак, “правильно увязать их при помощи foreign key”:
ALTER TABLE orders ADD FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE RESTRICT ON UPDATE CASCADE;
Теперь подробнее. Что нам дает внешний ключ?
По-русски “согласованность”. Data consistency — это очень большая тема, внешние ключи служат для обеспечения лишь одного ее аспекта, а именно “Referential integrity”, то есть “ссылочная целостность”. В нашем примере, если без внешнего ключа попробовать добавить заказ для несуществующего user_id, база данных спокойно его добавит. При условии наличия внешнего ключа это будет невозможно, БД выдаст ошибку:
Cannot add or update a child row: a foreign key constraint fails
Без внешних ключей ссылочную целостность обеспечивает приложение.
Таким образом, если в приложении что-то пошло не так, вы можете получить странные данные в БД (например, заказы для несуществующего пользователя). Внешние ключи дают железобетонную уверенность в том, что этого не произойдет, причем даже в том случае, если кто-то будет менять данные непосредственно в базе, минуя приложение.
Вернемся к нашему запросу, который добавляет внешний ключ. ON DELETE RESTRICT ON UPDATE CASCADE — это что еще такое? Таким образом мы определяем поведение поля, которое ссылается на другую таблицу, при изменении соответствующих данных в этой таблице. ON DELETE RESTRICT означает, что если попробовать удалить пользователя, у которого в таблице заказов есть данные, БД не даст этого сделать:
Cannot delete or update a parent row: a foreign key constraint fails
В данном случае, поскольку это заказы, то есть данные, которые, скорее всего, достаточно важны, я поставил ограничение (RESTRICT). Если бы я указал ON DELETE CASCADE, БД сама удалила бы все заказы пользователя при его удалении.
Есть еще одна опция — ON DELETE SET NULL. При ее использовании БД запишет NULL в качестве user_id для всех заказов удаленного пользователя.
ON UPDATE CASCADE говорит о том, что в случае если кто-то решит изменить ID пользователя, все его заказы получат новый, измененный ID. Зачем может понадобиться менять ID — это другой вопрос, операция крайне редкая.
Теперь к PHP. Как ни странно, код, достающий данные из БД, никак не изменится при использовании внешних ключей. Внешние ключи лишь накладывают ограничения на данные, так что вы всегда уверены в том, что связи между данными в разных таблицах не нарушены.
Если вы решите, что при удалении пользователя можно удалить все его заказы, вы можете поставить поведение ON DELETE CASCADE, и тогда для удаления пользователя вам достаточно будет удалить один ряд из таблицы users. Об остальном позаботится СУБД.
Резюмируя: внешние ключи не делают ничего магического. СУБД с их помощью может выполнить небольшую часть работы за вас (каскадные удаления и изменения), но в целом на приложение это влияет незначительно.
Зато дает 100% гарантию целостности связей между таблицами.
Стоит также отметить:
- То, что БД делает больше работы, означает больше накладных расходов. Некоторые операции могут замедлиться при использовании внешних ключей.
- В MySQL внешние ключи поддерживаются не всеми движками. Не забудьте проверить, что ваши таблицы имеют Engine=InnoDB. Есть и другие движки с поддержкой внешних ключей, но InnoDB самый распространенный.
Ограничения первичного и внешнего ключа — SQL Server
- Статья
Применимо к: SQL Server 2016 (13.x) и более поздние версии База данных SQL Azure Управляемый экземпляр Azure SQL
Первичные ключи и внешние ключи — это два типа ограничений, которые можно использовать для обеспечения целостности данных в таблицах SQL Server.
Это важные объекты базы данных.
Ограничения первичного ключа
Таблица обычно содержит столбец или комбинацию столбцов, которые содержат значения, однозначно идентифицирующие каждую строку в таблице. Этот столбец или столбцы называются первичным ключом (PK) таблицы и обеспечивают целостность сущности таблицы. Поскольку ограничения первичного ключа гарантируют уникальность данных, они часто определяются в столбце идентификаторов.
Когда вы указываете ограничение первичного ключа для таблицы, Компонент Database Engine обеспечивает уникальность данных, автоматически создавая уникальный индекс для столбцов первичного ключа. Этот индекс также обеспечивает быстрый доступ к данным, когда в запросах используется первичный ключ. Если ограничение первичного ключа определено более чем для одного столбца, значения могут дублироваться в одном столбце, но каждая комбинация значений из всех столбцов в определении ограничения первичного ключа должна быть уникальной.
Как показано на следующем рисунке, столбцы ProductID и VendorID в таблице Purchasing. образуют составное ограничение первичного ключа для этой таблицы. Это гарантирует, что каждая строка в таблице 
ProductVendor ProductVendor имеет уникальную комбинацию ProductID и VendorID . Это предотвращает вставку повторяющихся строк.
Таблица может содержать только одно ограничение первичного ключа.
Первичный ключ не может превышать 16 столбцов, а общая длина ключа не должна превышать 900 байт.
Индекс, сгенерированный ограничением первичного ключа, не может привести к тому, что количество индексов в таблице превысит 999 некластеризованных индексов и 1 кластеризованный индекс.
Если для ограничения первичного ключа не указан кластеризованный или некластеризованный, кластеризованный используется, если для таблицы нет кластеризованного индекса.
Все столбцы, определенные в ограничении первичного ключа, должны быть определены как отличные от нулевых.
Если допустимость значений NULL не указана, все столбцы, участвующие в ограничении первичного ключа, имеют значение, отличное от NULL.Если первичный ключ определен в столбце пользовательского типа CLR, реализация этого типа должна поддерживать двоичный порядок.
Если допустимость значений NULL не указана, все столбцы, участвующие в ограничении первичного ключа, имеют значение, отличное от NULL.Ограничения внешнего ключа
Внешний ключ (FK) — это столбец или комбинация столбцов, которые используются для установления и обеспечения связи между данными в двух таблицах для управления данными, которые могут храниться в таблице внешнего ключа. В ссылке по внешнему ключу связь создается между двумя таблицами, когда на столбец или столбцы, содержащие значение первичного ключа для одной таблицы, ссылается столбец или столбцы в другой таблице. Этот столбец становится внешним ключом во второй таблице.
Например, таблица Sales.SalesOrderHeader имеет ссылку внешнего ключа на таблицу Sales.SalesPerson , так как существует логическая связь между заказами на продажу и продавцами.
Столбец SalesPersonID в таблице SalesOrderHeader соответствует столбцу первичного ключа таблицы SalesPerson . Столбец SalesPersonID в таблице SalesOrderHeader является внешним ключом для SalesPerson 9.0026 стол. При создании этой связи внешнего ключа значение для SalesPersonID не может быть вставлено в таблицу SalesOrderHeader , если оно еще не существует в таблице SalesPerson .
Таблица может ссылаться не более чем на 253 другие таблицы и столбцы как внешние ключи (исходящие ссылки). SQL Server 2016 (13.x) увеличивает ограничение на количество других таблиц и столбцов, которые могут ссылаться на столбцы в одной таблице (входящие ссылки), с 253 до 10 000. (Требуется уровень совместимости не менее 130.) Повышение имеет следующие ограничения:
Более 253 ссылок на внешние ключи поддерживаются только для операций DELETE DML. Операции UPDATE и MERGE не поддерживаются.
Таблица со ссылкой внешнего ключа на себя по-прежнему ограничена 253 ссылками внешнего ключа.
Более 253 ссылок на внешние ключи в настоящее время недоступны для индексов columnstore, таблиц, оптимизированных для памяти, базы данных Stretch или секционированных таблиц внешних ключей.
Важно
База данных Stretch устарела в SQL Server 2022 (16.x). Эта функция будет удалена в будущей версии Microsoft SQL Server. Избегайте использования этой функции в новых разработках и планируйте модифицировать приложения, которые в настоящее время используют эту функцию.
Индексы для ограничений внешнего ключа
В отличие от ограничений первичного ключа, создание ограничения внешнего ключа не создает автоматически соответствующий индекс. Однако ручное создание индекса для внешнего ключа часто полезно по следующим причинам:
Столбцы внешнего ключа часто используются в критериях соединения, когда данные из связанных таблиц объединяются в запросах путем сопоставления столбца или столбцов в ограничении внешнего ключа одной таблицы со столбцом или столбцами первичного или уникального ключа в другой таблице.
. Индекс позволяет компоненту Database Engine быстро находить связанные данные в таблице внешнего ключа. Однако создание этого индекса не требуется. Данные из двух связанных таблиц могут быть объединены, даже если между таблицами не определены ограничения по первичному или внешнему ключу, но связь по внешнему ключу между двумя таблицами указывает на то, что две таблицы были оптимизированы для объединения в запросе, использующем ключи как его критерии.Изменения ограничений первичного ключа проверяются ограничениями внешнего ключа в связанных таблицах.
. Индекс позволяет компоненту Database Engine быстро находить связанные данные в таблице внешнего ключа. Однако создание этого индекса не требуется. Данные из двух связанных таблиц могут быть объединены, даже если между таблицами не определены ограничения по первичному или внешнему ключу, но связь по внешнему ключу между двумя таблицами указывает на то, что две таблицы были оптимизированы для объединения в запросе, использующем ключи как его критерии.Ссылочная целостность
Хотя основной целью ограничения внешнего ключа является управление данными, которые могут храниться в таблице внешнего ключа, оно также контролирует изменения данных в таблице первичного ключа. Например, если строка для продавца удалена из таблицы Sales.SalesPerson , а идентификатор продавца используется для заказов на продажу в Таблица Sales., реляционная целостность между двумя таблицами нарушена; заказы на продажу удаленного продавца теряются в таблице 
SalesOrderHeader SalesOrderHeader без ссылки на данные в таблице SalesPerson .
Ограничение внешнего ключа предотвращает эту ситуацию. Ограничение обеспечивает ссылочную целостность, гарантируя невозможность внесения изменений в данные в таблице первичного ключа, если эти изменения делают недействительной ссылку на данные в таблице внешнего ключа. При попытке удалить строку в таблице первичного ключа или изменить значение первичного ключа действие завершится ошибкой, если удаленное или измененное значение первичного ключа соответствует значению в ограничении внешнего ключа другой таблицы. Чтобы успешно изменить или удалить строку в ограничении внешнего ключа, вы должны сначала либо удалить данные внешнего ключа в таблице внешнего ключа, либо изменить данные внешнего ключа в таблице внешнего ключа, которая связывает внешний ключ с другими данными первичного ключа.
Каскадная ссылочная целостность
С помощью каскадных ограничений ссылочной целостности можно определить действия, которые компонент Database Engine предпринимает, когда пользователь пытается удалить или обновить ключ, на который указывают существующие внешние ключи. Могут быть определены следующие каскадные действия.
НЕТ ДЕЙСТВИЙ
Компонент Database Engine выдает ошибку, и действие удаления или обновления строки в родительской таблице откатывается.
CASCADE
Соответствующие строки обновляются или удаляются в ссылочной таблице, когда эта строка обновляется или удаляется в родительской таблице. CASCADE не может быть указан, если 9Столбец 0013 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, эти триггеры срабатывают после выполнения всех каскадных действий. Эти триггеры срабатывают в порядке, противоположном каскадному действию. Если в одной таблице есть несколько триггеров, они срабатывают в случайном порядке, если только для таблицы нет выделенного первого или последнего триггера. Этот порядок указан с помощью sp_settriggerorder.
Если несколько каскадных цепочек исходят из таблицы, которая была прямой целью действия UPDATE или DELETE, порядок, в котором эти цепочки активируют соответствующие триггеры, не указан. Однако одна цепочка всегда запускает все свои триггеры до того, как начнет срабатывать другая цепочка.
Триггер AFTER для таблицы, которая является прямой целью действия UPDATE или DELETE, срабатывает независимо от того, затронуты ли какие-либо строки. В этом случае нет других таблиц, затронутых каскадированием.
Если какой-либо из предыдущих триггеров выполняет операции UPDATE или DELETE для других таблиц, эти действия могут запускать вторичные каскадные цепочки. Эти вторичные цепочки обрабатываются для каждой операции UPDATE или DELETE одновременно после срабатывания всех триггеров во всех первичных цепочках. Этот процесс может рекурсивно повторяться для последующих операций UPDATE или DELETE.
Выполнение операций CREATE, ALTER, DELETE или других операций языка определения данных (DDL) внутри триггеров может привести к срабатыванию триггеров DDL. Это может впоследствии выполнять операции DELETE или UPDATE, которые запускают дополнительные каскадные цепочки и триггеры.
Если в какой-либо конкретной каскадной цепочке ссылочных действий возникает ошибка, возникает ошибка, триггеры AFTER в этой цепочке не срабатывают, а операция DELETE или UPDATE, создавшая цепочку, откатывается.
Таблица с триггером INSTEAD OF не может также иметь предложение REFERENCES, указывающее каскадное действие. Однако триггер AFTER для таблицы, на которую нацелено каскадное действие, может выполнить оператор INSERT, UPDATE или DELETE для другой таблицы или представления, который запускает триггер INSTEAD OF, определенный для этого объекта.
Следующие шаги
В следующей таблице перечислены общие задачи, связанные с ограничениями первичного и внешнего ключа.
| Задача | Артикул |
|---|---|
| Описывает, как создать первичный ключ. | Создать первичные ключи |
| Описывает, как удалить первичный ключ. | Удалить первичные ключи |
| Описывает, как изменить первичный ключ. | Изменить первичные ключи |
| Описывает, как создавать отношения внешнего ключа | Создание связей по внешнему ключу |
Описывает, как изменить отношения внешнего ключа. | Изменить отношения внешнего ключа |
| Описывает, как удалить отношения внешнего ключа. | Удалить отношения внешнего ключа |
| Описывает, как просматривать свойства внешнего ключа. | Просмотр свойств внешнего ключа |
| Описывает, как отключить ограничения внешнего ключа для репликации. | Отключить ограничения внешнего ключа для репликации |
| Описывает, как отключить ограничения внешнего ключа во время инструкции INSERT или UPDATE. | Отключение ограничений внешнего ключа с помощью инструкций INSERT и UPDATE |
Разница между первичным ключом и внешним ключом в таблице — пример учебника по SQL
Основное различие между первичным ключом и внешним ключом в таблице заключается в том, что это один и тот же столбец, который ведет себя как первичный ключ в родительской таблице и как внешний ключ в дочерней таблице.
Например, в отношениях «Клиент» и «Заказ» customer_id является первичным ключом в таблице «Клиент», но внешним ключом в таблице «Заказ». Кстати, что такое внешний ключ в таблице и разница между первичным и внешним ключом — это одни из популярных вопросов на собеседованиях по SQL, очень похожие на усечение и удаление в SQL или разницу между коррелированным и некоррелированным подзапросом?
Мы изучали ключевые концепции SQL вместе с ответами на эти часто задаваемые вопросы по SQL, и в этом руководстве по SQL мы обсудим, что такое внешний ключ в SQL и назначение внешнего ключа в любой таблице.
Кстати, это третья статья, посвященная первичному ключу в SQL, другая — о разнице между первичным и уникальным ключом и о том, как найти вторую по величине зарплату в SQL. Если вы готовитесь к какому-либо техническому собеседованию, где вы ожидаете несколько вопросов по SQL, ознакомьтесь с этими вопросами, к ним стоит подготовиться.
Что такое внешний ключ в таблице базы данных?
Внешний ключ — это столбец в одной таблице, который является первичным ключом в другой таблице.
Внешний ключ и первичный ключ используются для определения связи между двумя таблицами в реляционной базе данных. Например, в отношениях «Сотрудник и отдел» у нас есть две таблицы «Отдел» (dept_id, dept_name) и «Сотрудник» (emp_id, emp_name, dept_id). dept_id — это первичный ключ в таблице "Отдел" и внешний ключ в таблице "Сотрудник".
Хотя не требуется, чтобы имя внешнего ключа совпадало с первичным ключом, мы сохранили его таким же, как в стандартных рекомендациях SQL. Внешний ключ в таблице обеспечивает ограничение ссылочной целостности, которое можно использовать для реализации бизнес-правил, например. ссылочная целостность может помешать вам создать сотрудника с несуществующим отделом.
Этот вид проверки поддерживает целостность данных в отношении. Как обсуждалось в нашем посте Что такое ссылочная целостность в базе данных MySQL, мы видели, что она реализована как ограничение внешнего ключа и может разрешать КАСКАДНОЕ ОБНОВЛЕНИЕ и УДАЛЕНИЕ.
Эти ссылочные действия удаляют или обновляют соответствующий столбец в дочерней таблице (таблице внешнего ключа), когда соответствующая строка из родительской таблицы (таблица первичного ключа) удаляется или обновляется для поддержания целостности данных.
Вот некоторые важные различия между первичным и внешним ключами в таблице, которые стоит помнить как с точки зрения собеседования по SQL, так и с точки зрения знаний.
1) Имя внешнего ключа может отличаться от имени первичного ключа , которое он представляет в другой таблице. Например, в наших отношениях «Сотрудник» и «Отдел» первичный ключ в таблице «Отдел» — это «dept_id», и мы использовали то же имя в таблице «Сотрудник» для создания внешнего ключа. Это могло быть по-другому, т.е. ИД отдела или ИД отдела t и т. д.
2) Другое различие между первичным и внешним ключом заключается в том, что, в отличие от первичного ключа, внешний ключ может быть нулевым , например.
в нашем примере у вас может быть запись «Сотрудник», для которой dept_id может быть нулевым, это показывает, что в таблице «Отдел» нет соответствующей записи.
3) Еще одно отличие между первичным ключом и внешним ключом заключается в том, что внешний ключ может быть дубликатом напротив первичного ключа, который всегда уникален.
4) Используя ограничения внешнего ключа, мы можем ввести ссылочную целостность в отношения нескольких таблиц в SQL. Ссылочная целостность гарантирует целостность данных. Чтобы узнать больше, ознакомьтесь с преимуществами ссылочной целостности в SQL.
5) Внешний ключ в основном работает как связь между двумя таблицами, когда мы соединяем таблицы с помощью INNER JOIN и OUTER JOIN. Например, когда мы ВНУТРЕННЕЕ СОЕДИНЯЕМ обе таблицы «Сотрудник» и «Отдел», мы можем использовать dept_id в качестве столбца соединения. Дополнительные сведения см. в разделе Как объединить три таблицы в SQL.
6) Таблица, в которой столбец объявлен как первичный ключ, называется родительской таблицей в отношении, а таблица внешнего ключа называется дочерней таблицей в отношении.
Например, в отношениях «Сотрудник и отдел» «Отдел» является родительской таблицей, поскольку dept_id является там первичным ключом, а «Сотрудник» является дочерней таблицей, поскольку dept_id является внешним ключом в этой таблице.
Одним из лучших примеров для понимания первичного и внешнего ключа в таблице является связь сотрудника и отдела или отношения клиента и заказа. Вы можете создать таблицу заказов и клиентов в MySQL в качестве следующего для создания первичных и иностранных ключей:
Создание Таблица (CUST_ID int Не NULL ,
CUST_NAME (256), .0147 ОСНОВНОЙ КЛЮЧ (cust_id)) ENGINE=INNODB;
Create Таблица Приказ (Order_id Int Не NULL ,
Сумма int Не NULL ,
CUSH_ID INT,
EIRNDE 3 (3 (43 (43 (3 (43 (3 (3 ( 3 (3 ( ().
cust_id)
ON УДАЛИТЬ КАСКАД) ДВИГАТЕЛЬ=INNODB;
Теперь cust_id является первичным ключом в таблице Customer и внешним ключом в таблице Order. Если мы попытаемся вставить Заказ, для которого cust_id является чем-то недопустимым в таблице Customer, база данных MySQL отклонит такую INSERT или UPDATE. Это одно из преимуществ использования Referential Integrity.
Это также позволяет выполнять операции CASCADE UPDATE и DELETE, которые сначала удаляют или обновляют строку в родительской таблице, например. Клиент, а затем удалите или обновите все соответствующие строки в дочерней таблице, например. Стол заказов.
Вот и все по что такое внешний ключ в таблице и разница между первичным и внешним ключом в SQL. Я предлагаю создать таблицу самостоятельно и попытаться проверить ограничение внешнего ключа, нарушив его, и посмотреть, как база данных, например. Oracle, MySQL или SQL Server ведут себя. Чтобы узнать больше, попробуйте ON DELETE CASCADE и ON DELETE UPDATE, чтобы увидеть, как база данных поддерживает ограничение внешнего ключа.