Определение связей между таблицами с помощью Access SQL
Связи — это установленные связи между двумя или более таблицами. Связи основаны на общих полях из нескольких таблиц, часто включающими первичный и внешний ключи.
Первичный ключ — это поле (или поля), которое используется для уникальной идентификации каждой записи в таблице. Существует три требования к первичному ключу: он не может иметь значение NULL, он должен быть уникальным и может быть определен только один на таблицу. Первичный ключ можно определить либо путем создания индекса первичного ключа после создания таблицы, либо с помощью предложения CONSTRAINT в объявлении таблицы, как показано в примерах ниже в этом разделе. Ограничение ограничивает (или ограничивает) значения, введенные в поле.
Внешний ключ — это поле (или поля) в одной таблице, которое ссылается на первичный ключ в другой таблице. Данные в полях из обеих таблиц точно одинаковы, и таблица с записью первичного ключа (первичная таблица) должна иметь существующие записи, прежде чем таблица с записью внешнего ключа (внешняя таблица) будет содержать соответствующие или связанные записи. Как и первичные ключи, внешние ключи можно определить в объявлении таблицы с помощью предложения CONSTRAINT .
Существует три типа связей:
- Один к одному Для каждой записи в первичной таблице существует только одна запись во внешней таблице.
- Один ко многим Для каждой записи в первичной таблице есть одна или несколько связанных записей во внешней таблице.
- Многие ко многим Для каждой записи в первичной таблице существует множество связанных записей во внешней таблице, а для каждой записи во внешней таблице — много связанных записей в первичной таблице.
Например, предположим, что вы хотите добавить таблицу счетов в базу данных выставления счетов. Каждый клиент в таблице «Клиенты» может иметь множество счетов в таблице счетов. Это классический сценарий «один ко многим». Вы можете взять первичный ключ из таблицы customers и определить его как внешний ключ в таблице счетов, тем самым установив правильную связь между таблицами.
При определении связей между таблицами необходимо сделать объявления CONSTRAINT на уровне поля. Это означает, что ограничения определяются в инструкции CREATE TABLE . Чтобы применить ограничения, используйте ключевое слово CONSTRAINT после объявления поля, назовите ограничение, назовите таблицу, на которую оно ссылается, и назовите поле или поля в этой таблице, которые будут создавать соответствующий внешний ключ.
В следующей инструкции предполагается, что таблица tblCustomers уже создана и имеет первичный ключ, определенный в поле CustomerID. Теперь инструкция создает таблицу tblInvoices, определяя ее первичный ключ в поле InvoiceID. Он также создает связь «один ко многим» между таблицами tblCustomers и tblInvoices путем определения другого поля CustomerID в таблице tblInvoices. Это поле определяется как внешний ключ, который ссылается на поле CustomerID в таблице customers. Обратите внимание, что имя каждого ограничения следует ключевому слову CONSTRAINT .
CREATE TABLE tblInvoices (InvoiceID INTEGER CONSTRAINT PK_InvoiceID PRIMARY KEY, CustomerID INTEGER NOT NULL CONSTRAINT FK_CustomerID REFERENCES tblCustomers (CustomerID), InvoiceDate DATETIME, Amount CURRENCY) Обратите внимание, что индекс первичного ключа (PK_InvoiceID) для таблицы счетов объявляется в инструкции CREATE TABLE . Чтобы повысить производительность первичного ключа, для него автоматически создается индекс, поэтому нет необходимости использовать отдельную инструкцию CREATE INDEX . Теперь создайте таблицу доставки, которая будет содержать адрес доставки каждого клиента. Предположим, что для каждой записи клиента будет только одна запись доставки, поэтому вы будете устанавливать связь «один к одному».
CREATE TABLE tblShipping (CustomerID INTEGER CONSTRAINT PK_CustomerID PRIMARY KEY REFERENCES tblCustomers (CustomerID), Address TEXT(50), City TEXT(50), State TEXT(2), Zip TEXT(10)) Обратите внимание, что поле CustomerID является первичным ключом для таблицы доставки и ссылкой внешнего ключа на таблицу customers.
Ограничения
Ограничения можно использовать для установления первичных ключей и целостности ссылок, а также для ограничения значений, которые могут быть вставлены в поле. Как правило, ограничения можно использовать для сохранения целостности и согласованности данных в базе данных.
Существует два типа ограничений: ограничение на уровне одного поля или на уровне поля и ограничение на уровне нескольких полей или таблиц. Оба типа ограничений можно использовать в инструкции CREATE TABLE или ALTER TABLE .
Ограничение на одно поле, также известное как ограничение на уровне столбца, объявляется с самим полем после объявления поля и типа данных. Используйте таблицу customers и создайте первичный ключ с одним полем в поле CustomerID. Чтобы добавить ограничение, используйте ключевое слово CONSTRAINT с именем поля.
ALTER TABLE tblCustomers ALTER COLUMN CustomerID INTEGER CONSTRAINT PK_tblCustomers PRIMARY KEY Обратите внимание, что задано имя ограничения. Вы можете использовать ярлык для объявления первичного ключа, который полностью пропускает предложение CONSTRAINT .
ALTER TABLE tblCustomers ALTER COLUMN CustomerID INTEGER PRIMARY KEY Однако использование метода сочетания клавиш приведет к тому, что Access случайным образом создаст имя для ограничения, что усложнит ссылку в коде. Рекомендуется всегда называть ограничения.
Чтобы удалить ограничение, используйте предложение DROP CONSTRAINT с инструкцией ALTER TABLE и укажите имя ограничения.
ALTER TABLE tblCustomers DROP CONSTRAINT PK_tblCustomers Ограничения также можно использовать для ограничения допустимых значений для поля. Значения можно ограничить значением NOT NULL или UNIQUE или определить проверочные ограничения, которые являются типом бизнес-правила, которое может применяться к полю. Предположим, что вы хотите ограничить (или ограничить) значения полей имени и фамилии, чтобы они были уникальными, а это означает, что сочетание имени и фамилии не должно быть одинаковым для любых двух записей в таблице. Так как это ограничение нескольких полей, оно объявляется на уровне таблицы, а не на уровне поля. Используйте предложение ADD CONSTRAINT и определите многополевой список.
ALTER TABLE tblCustomers ADD CONSTRAINT CustomerID UNIQUE ([Last Name], [First Name]) Проверочное ограничение — это мощная функция SQL, которая позволяет добавлять проверку данных в таблицу, создавая выражение, которое может ссылаться на одно поле или несколько полей в одной или нескольких таблицах. Предположим, что вы хотите убедиться, что суммы, указанные в записи счета, всегда больше 0,00 долл. США. Для этого используйте проверочные ограничения, объявив ключевое слово CHECK и выражение проверки в предложении ADD CONSTRAINT инструкции ALTER TABLE .
ALTER TABLE tblInvoices ADD CONSTRAINT CheckAmount CHECK (Amount > 0) Выражение, используемое для определения проверочного ограничения, также может ссылаться на несколько полей в одной таблице или на поля в других таблицах и может использовать любые операции, допустимые в Access SQL, такие как инструкции SELECT , математические операторы и агрегатные функции. Выражение, определяющее проверочные ограничения, может содержать не более 64 символов.
Предположим, что вы хотите проверить кредитный лимит каждого клиента, прежде чем он будет добавлен в таблицу клиентов. Используя инструкцию ALTER TABLE с предложениями ADD COLUMN и CONSTRAINT , создайте ограничение, которое будет искать значение в таблице CreditLimit для проверки кредитного лимита клиента. Используйте следующие инструкции SQL, чтобы создать таблицу tblCreditLimit, добавить поле CustomerLimit в таблицу tblCustomers, добавить проверочные ограничения в таблицу tblCustomers и протестировать проверочные ограничения.
CREATE TABLE tblCreditLimit ( Limit DOUBLE) INSERT INTO tblCreditLimit VALUES (100) ALTER TABLE tblCustomers ADD COLUMN CustomerLimit DOUBLE ALTER TABLE tblCustomers ADD CONSTRAINT LimitRule CHECK (CustomerLimit Обратите внимание, что при выполнении инструкции UPDATE TABLE появляется сообщение о том, что обновление не выполнено, так как оно нарушило проверочные ограничения. Если обновить поле CustomerLimit до значения, равного 100 или меньшего значения, обновление будет выполнено успешно.
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
Ссылочная целостность: внешний ключ (FOREIGN KEY) стр. 1
Внешний ключ – это ограничение, которое поддерживает согласованное состояние данных между двумя таблицами, обеспечивая так называемую ссылочную целостность. Этот тип целостности означает, что всегда есть возможность получить полную информацию об объекте, распределенную по нескольким таблицам. Причины такого распределения, связанные с принципами проектирования реляционной модели, мы рассмотрим в дальнейшем.
Связь между таблицами не является равноправной. В ней всегда есть главная таблица и таблица подчиненная. Связи бывают двух типов: «один к одному» и «один ко многим». Связь «один к одному» означает, что строке главной таблицы соответствует не более одной строки (т.е. одна или ни одной) в подчиненной таблице. Связь «один ко многим» означает, что одной строке главной таблицы отвечает любое число строк (в том числе и 0) в подчиненной таблице.
Связь устанавливается посредством равенства значений определенных столбцов в главной и подчиненной таблицах. При этом столбец (или набор столбцов в случае составного ключа) в подчиненной таблице, который соотносится со столбцом (или набором столбцов) в главной таблице, и называется внешним ключом.
Поскольку главная таблица всегда находится со стороны «один», то столбец, участвующий в связи по внешнему ключу, должен иметь ограничение PRIMARY KEY или UNIQUE . Внешний же ключ задается при создании или изменении структуры подчиненной таблицы при помощи спецификации FOREIGN KEY :
Количество столбцов в списках 1 и 2 должно быть одинаковым, а типы данных этих столбцов должны быть попарно совместимы.
Вот как можно создать внешний ключ в таблице PC:
Замечание. Для главной таблицы можно не указывать столбец в скобках, если он является первичным ключом, т.к. он может быть только один. В нашем случае так и есть, поэтому последнюю строку можно было написать в виде
Аналогичным образом создаются внешние ключи в таблицах Printer и Laptop.
SQL-Ex blog
Внешние ключи, блокировка и конфликты обновления
Добавил Sergey Moiseenko on Среда, 1 декабря. 2021
Большинство баз данных должны использовать внешние ключи для поддержания ссылочной целостности (RI), где это возможно. Однако есть еще кое-что, влияющее на это решение, чем просто решить использовать ограничения FK и создать их. Чтобы ваша база данных работала как можно более гладко, необходимо учесть ряд факторов.
В этой статье рассматривается один такой фактор, которые не получил широкого обсуждения: чтобы минимизировать блокирование вы должны обратить пристальное внимание на индексы, используемые для поддержания уникальности на родительской стороне связей по внешнему ключу.
Это применимо, используете ли вы блокировки read committed (чтение зафиксированных транзакций) или версионную изоляцию снимков read committed snapshot isolation (RCSI). Обе могут приводить к блокировкам, когда связи внешних ключей проверяются ядром SQL Server.
Имеется дополнительное предостережение в случае изоляции снимка (SI). Фактически та же самая проблема может привести к неожиданным (и, возможно, нелогичным) сбоям транзакций из-за явных конфликтов обновления.
Статья состоит из двух частей. В первой части рассматривается блокировка внешних ключей при уровнях изоляции read committed и read committed snapshot isolation. Вторая часть посвящена связанным с обновлением конфликтам при изоляции снимка.
1. Проверки блокировки внешнего ключа
Давайте сначала посмотрим, как структура индекса может повлиять на блокировку из-за проверок внешнего ключа.
Следующий пример должен запускаться при изоляции read committed. По умолчанию для SQL Server используется блокировка read committed; а для Azure SQL Database - RCSI. Выбирайте то, что вам нравится, или выполняйте скрипты по разу для каждой установки, чтобы убедиться, что поведение то же самое.
-- Использование блокировки read committed
ALTER DATABASE CURRENT
SET READ_COMMITTED_SNAPSHOT OFF;
-- Или используйте row-versioning read committed
ALTER DATABASE CURRENT
SET READ_COMMITTED_SNAPSHOT ON;
Создайте две таблицы, связанных по внешнему ключу:
CREATE TABLE dbo.Parent
(
ParentID integer NOT NULL,
ParentNaturalKey varchar(10) NOT NULL,
ParentValue integer NOT NULL,
CONSTRAINT [PK dbo.Parent ParentID]
PRIMARY KEY (ParentID),
CONSTRAINT [AK dbo.Parent ParentNaturalKey]
UNIQUE (ParentNaturalKey)
);
CREATE TABLE dbo.Child
(
ChildID integer NOT NULL,
ChildNaturalKey varchar(10) NOT NULL,
ChildValue integer NOT NULL,
ParentID integer NULL,
CONSTRAINT [PK dbo.Child ChildID]
PRIMARY KEY (ChildID),
CONSTRAINT [AK dbo.Child ChildNaturalKey]
UNIQUE (ChildNaturalKey),
CONSTRAINT [FK dbo.Child to dbo.Parent]
FOREIGN KEY (ParentID)
REFERENCES dbo.Parent (ParentID)
);
Добавьте строку в родительскую таблицу:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
DECLARE
@ParentID integer = 1,
@ParentNaturalKey varchar(10) = 'PNK1',
@ParentValue integer = 100;
INSERT dbo.Parent
(
ParentID,
ParentNaturalKey,
ParentValue
)
VALUES
(
@ParentID,
@ParentNaturalKey,
@ParentValue
);
На втором подключении обновите неключевой атрибут родительской таблицы ParentValue внутри транзакции, но пока не делайте commit:
DECLARE
@ParentID integer = 1,
@ParentNaturalKey varchar(10) = 'PNK1',
@ParentValue integer = 200;
BEGIN TRANSACTION;
UPDATE dbo.Parent
SET ParentValue = @ParentValue
WHERE ParentID = @ParentID;
Если хотите, можете написать в update предикат, используя естественный ключ, это никак не скажется на нашей цели.
Вернитесь на первое подключение и попытайтесь добавить дочернюю запись:
DECLARE
@ChildID integer = 101,
@ChildNaturalKey varchar(10) = 'CNK1',
@ChildValue integer = 999,
@ParentID integer = 1;
INSERT dbo.Child
(
ChildID,
ChildNaturalKey,
ChildValue,
ParentID
)
VALUES
(
@ChildID,
@ChildNaturalKey,
@ChildValue,
@ParentID
);
Этот оператор Insert будет блокироваться вне зависимости от того, используете вы блокирующую или версионную изоляцию транзакций read committed в этом тесте.
Объяснение
План выполнения для этой вставки дочерней записи имеет вид:
После вставки новой строки в дочернюю таблицу план выполнения проверяет ограничение внешнего ключа. Проверка пропускается, если вставляемый родительский id есть null (достигается посредством предиката ‘pass through’ в левом полусоединении). В представленном случае добавляемый родительский id не является null, поэтому проверка внешнего ключа выполняется.
SQL Server проверяет ограничение внешнего ключа поиском соответствующей строки в родительской таблице. Чтобы сделать это, движок не может использовать версионность строки - требуется убедиться, что проверяемые данные являются последними зафиксированными данными, а не некоторой старой версией. Движок гарантирует это, добавляя внутренний табличный хинт READCOMMITTEDLOCK к проверке внешнего ключа на родительской таблице.
Это приводит к тому, что SQL Server пытается запросить разделяемую блокировку на соответствующую строку в родительской таблице, что блокируется, поскольку другая сессия удерживает несовместную эксклюзивную блокировку, т.к. обновление пока еще не зафиксировано.
Поясним, что хинт внутренней блокировки применим только к проверке внешнего ключа. Остальная часть плана все еще использует RCSI, если вы выбрали эту реализацию уровня изоляции read committed.
Избежать блокировки
Зафиксируйте или откатите открытую транзакцию во втором подключении, затем восстановите тестовую среду:
DROP TABLE IF EXISTS
dbo.Child, dbo.Parent;
Снова создайте тестовые таблицы, но теперь вместо принятия значений по умолчанию мы сделаем первичный ключ некластеризованным, а ограничение уникальности кластеризованным:
CREATE TABLE dbo.Parent
(
ParentID integer NOT NULL,
ParentNaturalKey varchar(10) NOT NULL,
ParentValue integer NOT NULL,
CONSTRAINT [PK dbo.Parent ParentID]
PRIMARY KEY NONCLUSTERED (ParentID),
CONSTRAINT [AK dbo.Parent ParentNaturalKey]
UNIQUE CLUSTERED (ParentNaturalKey)
);
CREATE TABLE dbo.Child
(
ChildID integer NOT NULL,
ChildNaturalKey varchar(10) NOT NULL,
ChildValue integer NOT NULL,
ParentID integer NULL,
CONSTRAINT [PK dbo.Child ChildID]
PRIMARY KEY NONCLUSTERED (ChildID),
CONSTRAINT [AK dbo.Child ChildNaturalKey]
UNIQUE CLUSTERED (ChildNaturalKey),
CONSTRAINT [FK dbo.Child to dbo.Parent]
FOREIGN KEY (ParentID)
REFERENCES dbo.Parent (ParentID)
);
Как и раньше добавим строку в родительскую таблицу:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
DECLARE
@ParentID integer = 1,
@ParentNaturalKey varchar(10) = 'PNK1',
@ParentValue integer = 100;
INSERT dbo.Parent
(
ParentID,
ParentNaturalKey,
ParentValue
)
VALUES
(
@ParentID,
@ParentNaturalKey,
@ParentValue
);
Во второй сессии опять выполните обновление без фиксации. Я использую теперь естественный ключ просто для разнообразия - это не оказывает влияния на результат. Используйте суррогатный ключ, если вам нравится.
DECLARE
@ParentID integer = 1,
@ParentNaturalKey varchar(10) = 'PNK1',
@ParentValue integer = 200;
BEGIN TRANSACTION
UPDATE dbo.Parent
SET ParentValue = @ParentValue
WHERE ParentNaturalKey = @ParentNaturalKey;
Теперь снова выполните вставку в дочернюю таблицу в первой сессии:
DECLARE
@ChildID integer = 101,
@ChildNaturalKey varchar(10) = 'CNK1',
@ChildValue integer = 999,
@ParentID integer = 1;
INSERT dbo.Child
(
ChildID,
ChildNaturalKey,
ChildValue,
ParentID
)
VALUES
(
@ChildID,
@ChildNaturalKey,
@ChildValue,
@ParentID
);
Теперь вставка дочерней записи не блокируется. Это справедливо при запуске и блокирующей, и версионной изоляции транзакций read committed. Это не опечатка или ошибка: RCSI ничем не отличается.
Объяснение
План выполнения для вставки дочерней записи теперь немного отличается:
Все так же, как и раньше (включая невидимый хинт READCOMMITTEDLOCK), за исключением того, что проверка внешнего ключа теперь использует некластеризованный уникальный индекс, принуждаемый первичным ключом родительской таблицы. В первом тесте этот индекс был кластеризованным.
Но почему мы не получаем теперь блокировки?
Еще не зафиксированное обновление родительской таблицы во второй сессии накладывает эксклюзивную блокировку на строку в кластеризованном индексе, поскольку обновляется базовая таблица. Изменения в столбце ParentValue не влияют на некластеризованный первичный ключ на ParentID, поэтому эта строка некластеризованного индекса не блокируется.
Проверка внешнего ключа может, следовательно, запросить разделемую блокировку на индекс некластеризованного первичного ключа безпрепятственно, и вставка в дочернюю таблицу происходит сразу.
Когда первичный ключ был кластеризованным, проверка внешнего ключа требовала разделяемую блокировку на тот же ресурс (строка кластеризованного индекса), который был эксклюзивно блокирован оператором обновления.
Это поведение может показаться удивительным, но это не баг. Предоставление проверке внешнего ключа своего собственного оптимизированного метода доступа позволяет избежать логически не являющейся необходимой конкуренции за блокировку. Нет необходимости блокировать поиск внешнего ключа, поскольку на атрибут ParentID не влияет на конкурентное обновление.
2. Избежать конфликтов обновления
Если выполнить предыдущие тесты при уровне изоляции снимка (SI), вывод будет тот же самый. Вставка дочерней строки блокируется, когда внешний ключ определяется кластеризованным индексом, и не блокируется, когда поддержка ключа использует некластеризованный уникальный индекс.
Хотя имеется одно важное потенциальное отличие при использовании SI. При изоляции read committed (блокирующей или RCSI) вставка дочерней строки в конечном итоге происходит после фиксации или отката обновления во второй сессии. При использовании SI существует риск прерывания транзакции из-за явного конфликта обновления.
Тут немного более хитрая демонстрация, поскольку транзакция снимка не начинается с оператора BEGIN TRANSACTION - она начинается с первого доступа пользователя к данным после этой точки.
Первый скрипт устанавливает демонстрацию SI с еще одной фиктивной таблицей, используемой только для того, чтобы убедиться, что транзакция снимка действительно началась. Он использует изменение теста, при котором ссылочный первичный ключ определен уникальным кластеризованным индексом (по умолчанию):
ALTER DATABASE CURRENT SET ALLOW_SNAPSHOT_ISOLATION ON;
GO
DROP TABLE IF EXISTS
dbo.Dummy, dbo.Child, dbo.Parent;
GO
CREATE TABLE dbo.Dummy
(
x integer NULL
);
CREATE TABLE dbo.Parent
(
ParentID integer NOT NULL,
ParentNaturalKey varchar(10) NOT NULL,
ParentValue integer NOT NULL,
CONSTRAINT [PK dbo.Parent ParentID]
PRIMARY KEY (ParentID),
CONSTRAINT [AK dbo.Parent ParentNaturalKey]
UNIQUE (ParentNaturalKey)
);
CREATE TABLE dbo.Child
(
ChildID integer NOT NULL,
ChildNaturalKey varchar(10) NOT NULL,
ChildValue integer NOT NULL,
ParentID integer NULL,
CONSTRAINT [PK dbo.Child ChildID]
PRIMARY KEY (ChildID),
CONSTRAINT [AK dbo.Child ChildNaturalKey]
UNIQUE (ChildNaturalKey),
CONSTRAINT [FK dbo.Child to dbo.Parent]
FOREIGN KEY (ParentID)
REFERENCES dbo.Parent (ParentID)
);
Вставка родительской строки:
DECLARE
@ParentID integer = 1,
@ParentNaturalKey varchar(10) = 'PNK1',
@ParentValue integer = 100;
INSERT dbo.Parent
(
ParentID,
ParentNaturalKey,
ParentValue
)
VALUES
(
@ParentID,
@ParentNaturalKey,
@ParentValue
);
Все еще в первой сессии начинаем транзакцию снимка:
-- Сессия 1
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
-- Проверка, что транзакция снимка началась
SELECT COUNT_BIG(*) FROM dbo.Dummy AS D;
Во второй сессии (выполняется при любом уровне изоляции):
-- Сессия 2
DECLARE
@ParentID integer = 1,
@ParentNaturalKey varchar(10) = 'PNK1',
@ParentValue integer = 200;
BEGIN TRANSACTION;
UPDATE dbo.Parent
SET ParentValue = @ParentValue
WHERE ParentID = @ParentID;
Попытка вставить дочернюю строку в первой сессии блокируется, как и ожидалось:
-- Сессия 1
DECLARE
@ChildID integer = 101,
@ChildNaturalKey varchar(10) = 'CNK1',
@ChildValue integer = 999,
@ParentID integer = 1;
INSERT dbo.Child
(
ChildID,
ChildNaturalKey,
ChildValue,
ParentID
)
VALUES
(
@ChildID,
@ChildNaturalKey,
@ChildValue,
@ParentID
);
Различие имеет место, когда мы завершаем транзакцию во второй сессии. Если мы откатим её, вставка дочерней строки в первой сессии завершается успешно.
Если же мы зафиксируем открытую транзакцию:
-- Сессия 2
COMMIT TRANSACTION;
Первая сессия сообщит о конфликте обновления и откатит транзакцию:
Объяснение
Конфликт обновления происходит, несмотря на тот факт, что проверяемый внешний ключ не менялся при обновлении во второй сессии.
Причина в сущности та же самая, что и в первом наборе тестов. Когда в качестве ссылочного ключа используется кластеризованный индекс, транзакция снимка встречает строку, которая изменилась с момента её запуска. Это не допускается при изоляции снимка.
Когда ключ поддерживается некластеризованным индексом, транзакция снимка видит только немодифицированную строку индекса, поэтому здесь нет блокирования и не обнаруживается никакого конфликта обновления.
Существуют многие другие обстоятельства, при которых транзакция снимка может сообщить о неожиданных конфликтах обновления или о других ошибках. Примеры можно найти в моей предыдущей статье.
Выводы
Имеется много соображений относительно выбора кластеризованного индекса для таблицы с построчным хранением. Описанная здесь проблема - просто еще один фактор, который следует принимать в расчет.
Это особенно справедливо, если вы будете использовать изоляцию снимка. Никому не понравится прерывание транзакции, особенно когда это не кажется логичным. Если вы будете использовать RCSI, блокирование при чтении, когда проверяется ограничение внешнего ключа, может оказаться неожиданным и привести к тупику.
По умолчанию для ограничения PRIMARY KEY выполняется создание поддерживающего его кластеризованного индекса, если явно не определен другой индекс или ограничение вместо кластеризованного. При проектировании является хорошей привычкой явно указывать ваши намерения, поэтому я бы настоятельно советовал писать всякий раз CLUSTERED или NONCLUSTERED.
Дублированные индексы?
Иногда по веским причинам вы серьезно рассматриваете вариант, когда кластеризованный индекс и некластеризованный индекс имеют одни и те же ключи.
Намерением может быть обеспечить оптимальный доступ на чтение для пользовательских запросов посредством кластеризованного индекса (избегая поиска ключа), обеспечивая при этом также минимально блокирующую (и конфликтующую на обновлениях) проверку внешних ключей посредством компактного некластеризованного индекса, как показано здесь.
При наличии более одного подходящего индекса SQL Server не дает способа, гарантирующего использование того или иного индекса для проверки ограничения внешнего ключа.
Dan Guzman задокументировал свои наблюдения в Secrets of Foreign Key Index Binding, но они могут быть несовершенны и в любом случае недокументированы, а значит могут измениться.
CREATE TABLE dbo.Parent
(
ParentID integer NOT NULL UNIQUE CLUSTERED
);
-- Сокращенный (неявный) синтаксис
-- падает с ошибкой 1773
CREATE TABLE dbo.Child
(
ChildID integer NOT NULL PRIMARY KEY NONCLUSTERED,
ParentID integer NOT NULL
REFERENCES dbo.Parent
);
-- Явный синтаксис выполняется успешно
CREATE TABLE dbo.Child
(
ChildID integer NOT NULL PRIMARY KEY NONCLUSTERED,
ParentID integer NOT NULL
REFERENCES dbo.Parent (ParentID)
);
Люди привыкли в значительной степени игнорировать конфликты чтения-записи в RCSI и SI. Надеюсь, что эта статья дала вам дополнительные мысли о применении физического дизайна к таблицам, связанных внешним ключом.
В чем практическая польза FOREIGN KEY в таблицах MySQL?
Логично использовать user_id и invoice_id как внешние ключи из соответствующих таблиц Users и Invoices. На сколько я понимаю, назначение user_id и invoice_id внешними ключами не позволит мне сделать ошибку и добавить в таблицу Orders заказ с несуществующими значениями user_id и invoice_id . На это практическая польза FOREIGN KEY заканчивается? У меня по этому поводу сомнения. Как еще я должен использовать FOREIGN KEY ? Я могу представить, если бы у меня в таблице Orders было поле name , значение которого автоматически обновляется, если изменяется его значение в родительской таблице. В этом бы была польза, но мне кажется не верно создавать дублирующиеся поля в разных таблицах. Поэтому я пытаюсь понять, как используются FOREIGN KEY ?
Отслеживать
66.4k 6 6 золотых знаков 51 51 серебряный знак 112 112 бронзовых знаков
задан 3 ноя 2018 в 18:59
103 1 1 серебряный знак 2 2 бронзовых знака
Не у верен за MySql, но в других СУБД поддерживаются конструкции вида on delete cascade и т.д.
3 ноя 2018 в 19:02
Контроль ссылочной целостности основное и единственное назначение внешних ключей. И да, как заметил @Viktorov дополнительно внешние ключи могут каскадно удалять записи при удалении из родительской таблицы или выставлять ссылки в NULL (если их об этом попросить). Но в общем то это одна из разновидностей контроля целостности. НО этого единственного их назначения более чем достаточно, БД без контроля ссылок очень быстро приходит в противоречивое состояние и после этого невозможно разобраться откуда что взялось
3 ноя 2018 в 19:06
Благодарю за разъяснения. Напишите, пожалуйста, ответ и я его приму.
3 ноя 2018 в 19:11
2 ответа 2
Сортировка: Сброс на вариант по умолчанию
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; Допустим если мы удаляем пользователя то и автоматически удаляем все записи связанные с ним, в данном случае его адрес электронной почты.