Информационные системы

Петров Владимир Николаевич

Отчество VARCHAR(25),

Дата_рождения DATE,

Адрес VARCHAR(50),

Телефон VARCHAR(25))

CREATE TABLE Должности (

Код_должности INTEGER NOT NULL PRIMARY KEY,

Должность VARCHAR(50) NOT NULL UNIQUE,

Разряд INTEGER NOT NULL,

Зарплата DECIMAL(7,2) NOT NULL)

CREATE TABLE Сотрудники (

Код_сотрудника INTEGER NOT NULL PRIMARY KEY,

Код_должности INTEGER,

Код_физического_лица INTEGER NOT NULL,

Рейтинг DECIMAL(4,2),

Дата_приема DATE NOT NULL,

Дата_увольнения DATE,

FOREIGN KEY

Физ_ВК (Код_физического_лица)

REFERENCES Физические_лица (Код_физического_лица),

FOREIGN KEY Должн_ВК (Код_должности)

REFERENCES Должности (Код_должности))

Внешний ключ может быть добавлен и после создания таблицы – с помощью оператора ALTER TABLE (естественно, только в том случае, если используемая реализация SQL поддерживает данный оператор). Синтаксис оператора ALTER TABLE для создания внешнего ключа:

ALTER TABLE имя_таблицы

ADD CONSTRAINT имя_внешнего_ключа FOREIGN KEY (список полей внешнего ключа)

REFERENCE имя_родительской_таблицы (список полей родительского ключа)

Примечание.

Следует иметь в виду, что при использовании оператора ALTER TABLE для создания связи между таблицами необходимо, чтобы связываемые таблицы находились в состоянии ссылочной целостности. Иначе при попытке выполнения оператора будет выдано сообщение об ошибке.

Внешний ключ ограничивает значения, которые можно ввести в таблицу. Чтобы в поля, составляющие внешний ключ, можно было ввести некоторое значение, необходимо, чтобы это значение уже было введено в родительской таблице. Например, чтобы занести в таблицу СОТРУДНИКИ данные о новом сотруднике, необходимо, чтобы в таблице ФИЗИЧЕСКИЕ ЛИЦА уже существовала запись о его личных данных – иначе невозможно будет заполнить обязательное поле Код_физического_лица.

Для внешнего ключа может быть задано ограничение NOT NULL, но это необязательно, а в некоторых случаях даже нежелательно. Например, предположим, что в организацию принимается на работу новый сотрудник, но еще не определена однозначно должность, которую он займет. В этом случае можно занести все необходимые данные о нем в таблицы ФИЗИЧЕСКИЕ ЛИЦА и СОТРУДНИКИ, ничего не указывая в поле Код_должности, которое будет заполнено позже.

Ограничение внешнего ключа также оказывает влияние на удаление и модификацию записей родительской таблицы. Значение родительского ключа, на которое ссылается какой-либо внешний ключ, не может быть удалено или изменено. Это означает, например, что нельзя удалить из таблицы ФИЗИЧЕСКИЕ ЛИЦА запись о сотруднике, если она связана с записью в таблице СОТРУДНИКИ. Это вполне понятно – если в таблице СОТРУДНИКИ присутствует запись о сотруднике фирмы, а из таблицы ФИЗИЧЕСКИЕ ЛИЦА запись об этом сотруднике удалена, то информация о его личных данных будет потеряна. Если же сотрудник уволился, и запись о нем из таблицы СОТРУДНИКИ удалена, то нет необходимости хранить информацию о его личных данных, и соответствующая запись из таблицы ФИЗИЧЕСКИЕ ЛИЦА также может быть удалена.

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

Примечание.

В некоторых реализациях SQL имеется возможность задавать для внешних ключей каскадное удаление и каскадное обновление. Это означает, что при попытке удалить или модифицировать значение родительского ключа, на которое ссылается внешний ключ, соответствующие записи внешнего ключа также будут удалены (каскадное удаление) или изменены (каскадное обновление). Данные возможности в стандарте ANSI SQL-92 не прописаны.

Один из синтаксических вариантов задания каскадного обновления и удаления выглядит так:

UPDATE OF имя_родительской_таблицы CASCADES

DELETE OF имя_родительской_таблицы CASCADES

Ключевые фразы UPDATE OF и DELETE OF указываются в операторе CREATE TABLE. Вместо ключевого слова CASCADES можно указать слово RESTRICTED – в этом случае изменение и удаление значений родительского ключа, на которые ссылается внешний ключ из данной таблицы, будет запрещено. Пример:

CREATE TABLE Сотрудники (

Код_сотрудника INTEGER NOT NULL PRIMARY KEY,

Код_должности INTEGER,

Код_физического_лица INTEGER NOT NULL,

Рейтинг DECIMAL(4,2),

Дата_приема DATE NOT NULL,

Дата_увольнения DATE,

FOREIGN KEY Физ_ВК (Код_физического_лица)

REFERENCES Физические_лица (Код_физического_лица),

FOREIGN KEY Должн_ВК (Код_должности)

REFERENCES Должности (Код_должности),

UPDATE OF Физические_лица CASCADES

DELETE OF Физические_лица RESTRICTED)

Ограничение CHECK

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

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

Примечание.

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

Задание ограничения CHECK производится при создании таблицы. Для этого после описания полей таблицы указывается ключевая фраза: CONSTRAINT имя_ограничения CHECK (ограничение)

В рассматриваемом нами примере базы данных сотрудников организации ограничение может быть задано, например, для поля Разряд таблицы ДОЛЖНОСТИ. Допустим, разряд не может превышать 20. Тогда оператор создания таблицы ДОЛЖНОСТИ, в котором задано это ограничение, будет иметь следующий вид: