Мартин Грубер - Понимание SQL

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

CREATE TABLE Customers

(cnum integer NOT NULL PRIMARY KEY,

cname char(10) NOT NULL,

city char(10),

rating integer,

snum integer REFERENCES Salespeople,

UPDATE OF Salespeople CASCADES,

DELETE OF Salespeople RESTRICTED);

Если вы теперь попробуете удалить Peel из таблицы Продавцов, команда будет не допустима, пока вы не измените значение поля snum заказчиков Hoffman и Clemens для другого назначенного продавца. С другой стороны, вы можете изменить значение поля snum для Peel на 1009, и Hoffman и Clemens будут также автоматически изменены.

Третий эффект - Пустые (NULL) изменения. Бывает, что когда продавцы оставляют компанию, их текущие порядки не передаются другому продавцу.

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

CREATE TABLE Orders

(onum integer NOT NULL PRIMARY KEY,

amt decimal,

odate date NOT NULL

cnum integer NOT NULL REFERENCES Customers

snum integer REFERENCES Salespeople,

UPDATE OF Customers CASCADES,

DELETE OF Customers CASCADES,

UPDATE OF Salespeople CASCADES,

DELETE OF Salespeople NULLS);

Конечно, в команде DELETE с эффектом Пустого изменения в таблице Продавцов, ограничение NOT NULL должно быть удалено из поля snum.

Как было упомянуто ранее, ограничение FOREIGN KEY может представить имя этой частной таблице, как таблицы родительского ключа. Далеко не будучи простой, эта особенность может пригодиться. Предположим, что мы имеем таблицу Employees с полем manager(администратор). Это поле содержит номера каждого из служащих, некоторые из которых являются еще и администраторами. Но так как каждый администратор - в то же время остается служащим, то он естественно будут также представлен в этой таблице. Давайте создадим таблицу, где номер служащего (столбец с именем empno ), объявляется как первичный ключ, а администратор, как внешний ключ, будет ссылаться на нее:

CREATE TABLE Employees

(empno integer NOT NULL PRIMARY KEY,

name char(10) NOT NULL UNIOUE,

manager integer REFERENCES Employees);

( Так как внешний ключ это ссылаемый первичный ключ таблицы, список столбцов может быть исключен. ) Имеется содержание этой таблицы:

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

которое ссылается на таблицу Заказчиков, так, что каждая таблица ссылается на другую, как показано в следующем операторе CREATE TABLE:

CREATE TABLE Salespeople

(snum integer NOT NULL PRIMARY KEY,

sname char(10) NOT NULL,

city char(10),

comm declmal,

cnum integer REFERENCES Customers);

CREATE TABLE Customers

(cnum integer NOT NULL PRIMARY KEY,

cname char(10) NOT NULL,

city char(10),

rating integer,

snum integer REFERENCES Salespeople);

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