Поддержка основных правил целостности данных

В нашем примере поле snum из Customers связывает такое же поле из таблицы Salespeople и Orders такой тип связи между таблицами называется ссылочной целостностью. Если поле таблицы ссылается на поле другой таблицы, это поле называется внешним ключом (foreign key). Поле, на которое оно ссылается, называется его родительским ключом (parent key).

Так поле snum в таблице Customers является внешним ключом, а для таблицы Salespeople это поле – родительский ключ. В таблице Orders это поле – внешний ключ.

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

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

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

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

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

CREATE TABLE или ALTER TABLE,

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

Например, можно определить таблицу Customers, в которой поле snum определено как внешний ключ, ссылающийся на таблицу Customers, в которой поле snum определено как ссылка на таблицу Salespeople:

CREATE TABLE Customers

(cnum integer NOT NULL PRIMARY KEY,

cname char(10),

city char(10),

snum integer,

FOREIGN KEY(snum) REFERENCES Salespeople(snum));

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

Например, если на поле snum в таблице Salespeople разместить ограничение PRIMARY KEY, то его можно использовать в таблице Customers следующим образом:

snum integer REFERENCES Salespeople

Следует отметить, что все поля, которые используются, как родительские ключи имеют ограничения PRIMARY KEY или UNIQUE и обязательно NOT NULL. внешний ключ может содержать только реально представленные или нулевые значения. попытки ввести в этот ключ другие значения будут отвергнуты.

Предположим, что все внешние ключи объявлены и для них определены ограничения FOREIGN KEY:

CREATE TABLE Salespeople

(snum integer NOT NULL PRIMARY KEY,

sname char(10) NOT NULL,

city char(10),

comm decimal);

CREATE TABLE Customers

(cnum integer NOT NULL PRIMARY KEY,

cname char(10 NOT NULL,

city char(10),

rating integer,

snum integer,

FOREIGN KEY(snum) REFERENCES Salespeople,

UNIQUE(cnum, snum));

CREATE TABLE Orders

(cnum integer NOT NULL PRIMARY KEY,

amt decimal,

odate date NOT NULL,

cnum integer NOT NULL,

snum integer NOT NULL,

FOREIGN KEY (cnum, snum) REFERENCES Customers(cnum, snum));

Ограничения влияют на выполнение команд обновления языка DML. Если поле определено как внешний ключ, то любые значения, которые вводятся в них с помощью команд INSERT, UPDATE должны быть уже представлены в их родительских ключах. Можно вводить NULL значения во внешний ключ, в родительском ключе такие значения запрещены. Можно удалить любую строчку с внешним ключом. В родительском ключе любое его значение, на которое есть ссылка внешнего ключа, не может быть удалено или изменено. Нельзя удалять информацию о покупателе, пока имеются его заявки в таблице Orders. Предположим, что существует причина для изменения поля snum таблицы Salespeople. Но при изменении номера продавца или при удалении сведений о нем из БД необходимо сохранить связь со всеми его покупателями, сохранить всех его покупателей, назначить им нового продавца. Для этого определяется для команды UPDATE эффект CASCADES, а для команды DELETE эффект RESTRICTED. В этом случае в описании таблицы для поля snum (для создания Customers):

snum integer REFERENCES Salespeople,

UPDATE OF Salespeople CASCADES,

DELETE OF Salespeople RESTRICTED);

Если удалить из таблицы продавца Peel, то команда обновления не будет выполнена до тех пор, пока поле snum в таблице покупатели для этого продавца, имеющего покупателей Hoffman и Clemens не будет изменено на новое. Если таблица продавцы вместо номера 1001:=1009, то в таблице покупатели для этих покупателей будет назначен новый номер продавца.

Пусть имеется таблица Employers, которая содержит поле Manager. Это поле содержит номер менеджера, но поскольку каждый менеджер является служащим, он также представлен в этой таблице. Создадим таблицу, в которой поле empno объявлено первичным ключом, а поле managet – внешний ключ, который ссылается на первичный ключ.

CREATE TABLE Employers

(empno integer NOT NULL PRIMARY KEY,

name char(10) NOT NULL UNIQUE,

manager integer REFERENCES Employers);

empno name manager

1003 Teresa 2007

2007 Adam NULL

1688 Marat 1003

2002 Peel 2007

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

Перекрестная ссылка:

CREATE TABLE Salespeople

(snum integer NOT NULL PRIMARY KEY,

sname char(10) NOT NULL,

city char(10),

comm decimal,

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 такой подход часто используется, но проблема, когда одна из таблиц еще не создана.