Базы Данных - Сибилев, 2007

231

| { FOREIGN KEY (имя_столбца.,..) REFERENCES имя_таблицы

[ (имя_столбца.,..) ] [ ссылочная_спецификация ]}

[[ NOT ] DEFERRABLE

[{ INITIALLY IMMEDIATE } | { INITIALLY DEFERRED }]]

Здесь спецификации PRIMARY KEY и UNIQUE – первичный и альтернативный ключи соответственно.

Предложение FOREIGN KEY определяет составной внешний ключ.

Список столбцов в предложении REFERENCES следует указать, если ссылка ведет не на первичный ключ родительской таблицы. Ссылочным может быть любой её альтернативный ключ. Список столбцов внешнего ключа должен быть эквивалентен списку столбцов ссылочного ключа, т.е.

должны совпадать длины списков, а также типы и длины соответственных столбцов.

Ссылочная спецификация ограничения таблицы подобна ссылочной спецификации ограничения столбца. В дополнение к указанным в выше параметрам, она может содержать параметр MATCH {FULL | PARTIAL}.

Он определяет тип совпадения значений внешнего и родительского клю-

чей.

Если указано MATCH FULL, то частично неопределённые значения внешнего ключа не допускаются. Каждая строка внешнего ключа должна либо целиком состоять из значений NULL, либо полностью совпадать с какой-нибудь строкой родительского ключа.

При использовании MATCH PARTIAL допускаются частично не-

определённые значения внешнего ключа. В этом случае все определённые значения строки внешнего ключа должны совпадать с соответственными значениями какой-либо строки родительского ключа.

Если предложение MATCH не указано, то допустимы любые час-

тично неопределённые значения внешнего ключа, однако если строка

232

внешнего ключа не содержит NULL-значений, то она должна полностью совпадать с какой-либо строкой родительского ключа.

Все прочие параметры ссылочной спецификации ограничения на таблицу полностью совпадают с описаными выше для ограничения на столбец.

Пример 3. Запишем определение таблицы SPJ из нашей «учебной»

БД:

CREATE TABLE SPJ

( Snum Snum NOT NULL

REFERENCES S

ON UPDATE CASCADE,

Pnum Pnum NOT NULL

REFERENCES P

ON UPDATE CASCADE,

Jnum Jnum NOT NULL

REFERENCES J

ON UPDATE CASCADE,

Qt NUMERIC CHECK (Qt BETWEEN 1000 AND 10000),

PRIMARY KEY (Snum, Pnum, Jnum)

);

Здесь мы предполагаем, что в схеме определены домены Snum, Pnum

и Jnum, на которых принимают значения первичные ключи таблиц S, P и J

соответственно. При обновлении значений родительских ключей будут со-

ответственно обновляться совпадающие значения ссылающихся ключей.

Удаление значений родительских ключей не будет выполняться до тех пор,

пока в таблице SPJ существует хотя бы одна ссылка на удаляемое значе-

ние. Система не допустит ввода неопределенных значений в столбцы

Snum, Pnum и Jnum. Кроме того, она запретит ввод неуникальных комби-

наций значений этих столбцов. Ограничение CHECK представляет прави-

233

ло ПО, согласно которому не может быть поставки, объём которой менее

1000 или более 10000 штук деталей одного вида.

7.3.6 Оператор изменения таблицы

Этот оператор применяется для изменения определений постоянных и созданных временных таблиц. Его может использовать только владелец схемы, содержащей изменяемую таблицу. Синтаксическая схема оператора следующая:

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

{ADD [ COLUMN ] определение_столбца }

| { ADD определение_ограничения_таблицы }

| { ALTER [ COLUMN ] имя_столбца изменяющее_действие} | { DROP [ COLUMN ] имя_столбца RESTRICT | CASCADE }

|{DROP CONSTRAINT имя_ограничения RESTRICT |

CASCADE };

Ключевое слово ADD используется для добавления определения столбца или ограничения таблицы. Система в состоянии определить смысл ключевого слова без специальных пояснений, так как определение столбца начинается его именем, а определение ограничения таблицы всегда начи-

нается одним из ключевых слов, которые запрещено использовать в каче-

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

Новый столбец добавляется в конец заголовка таблицы. Определение столбца такое же, как в операторе CREATE TABLE. Если таблица не пус-

та, то новый столбец будет заполнен значениями по умолчанию во всех строках30. Если значение по умолчанию не определено, то столбец примет во всех строках значения NULL.

Предложение ALTER [COLUMN] используется для создания или отмены значения по умолчанию для столбца. Здесь

изменяющее_действие ::=

235

7.3.7 Оператор удаления таблицы

Оператор используется только для удаления определений таблиц,

созданных оператором CREATE TABLE. Его синтаксис таков:

DROP TABLE имя_таблицы CASCADE | RESTRICT;

Если используется параметр RESTRICT, то операция не выполняет-

ся, если существуют представления или ограничения, в определениях ко-

торых имеются ссылки на удаляемую таблицу. Использование CASCADE

приводит к автоматическому удалению таких представлений и ограниче-

ний.

7.3.8 Оператор определения представления

Оператор создаёт виртуальную таблицу – именованный запрос. Ис-

пользуется следующий синтаксис:

CREATE VIEW имя_таблицы [ (имя_столбца.,..) ]

AS запрос

[ WITH [CASCADED | LOCAL] CHECK OPTION ];

Здесь имя_таблицы – имя создаваемого представления. Оно сохраняется в разделе системного каталога, содержащем сведения о таблицах. В запросах ссылки на представления обрабатываются так же, как ссылки на любые другие таблицы.

запрос – оператор SELECT, формирующий тело представления. За-

прос не исполняется при обработке оператора CREATE VIEW. Он со-

храняется в системном каталоге и связывается с именем представления.

Исполнение запроса происходит в процессе обработки какого-либо опера-

тора DML, в котором встретилась ссылка на представление.

Запрос в определении представления может ссылаться на любые

именованные таблицы за исключением объявленных временных. Однако не допустимы прямые или косвенные ссылки на определяемое представле-

ние. Кроме того, в запросе нельзя использовать предложение ORDER BY,

т.к. представление есть таблица, а строки таблицы неупорядочены по оп-

ределению.

236

По умолчанию имена столбцов представления совпадают с именами столбцов целевого списка запроса. Если при работе с представлением же-

лательно использование других имён, то следует указать их список в пред-

ложении CREATE VIEW. Число элементов этого списка должно совпа-

дать с числом столбцов в целевом списке запроса. Список имён столбцов обязательно должен быть указан, если целевой список запроса содержит:

−одноимённые столбцы различных таблиц;

−не поименованные предложением AS вычислимые столбцы;

−безымянные столбцы, полученные в результате операций UNION,

EXCEPT, INTERSECT.

Необязательное предложение WITH CHECK OPTION используется при определении так называемых обновляемых представлений. Его смысл подробно обсуждается в п. 7.4.

Пример. Следующий оператор создаёт представление Parts на осно-

ве двух базовых таблиц – P и SPJ. Оно будет обрабатываться операторами

DML как таблица Parts, содержащая данные о деталях и общих объёмах их поставок.

CREATE VIEW Parts (Number, Name, Weight, Color, City,

Quantity)

SELECT P.*, SUMQ.SQT

FROM P JOIN (

SELECT Pnum, SUM(Qt) AS SQT

FROM SPJ

GROUP BY Pnum

)AS SUMQ;

Авот другой способ создания этого представления:

CREATE VIEW SUMQ (Pnum, SQT)

SELECT Pnum, SUM(Qt)

FROM SPJ

237

GROUP BY Pnum;

CREATE VIEW Parts (Number, Name, Weight, Color, City,

Quantity)

SELECT P.*, SUMQ.SQT

FROM P, SUMQ

WHERE P.Pnum = SUMQ.Pnum;

Определение представления можно уничтожить оператором:

DROP VIEW имя_таблицы CASCADE | RESTRICT;

Здесь имя_таблицы есть имя уничтожаемого представления. Смысл параметров CASCADE и RESTRICT такой же, как в операторе уничтоже-

ния таблицы.

7.3.9 Оператор определения утверждения

Этот оператор создаёт объект схемы, содержащий проверку ограни-

чения:

CREATE ASSERTION имя_утверждения

CHECK (предикат) [[ NOT ] DEFERRABLE ]

[

{ INITIALLY IMMEDIATE } | { INITIALLY DEFERRED }

];

Сравните это с синтаксисом определения ограничения в операторе

CREATE DOMAIN.

Ограничение, содержащееся в утверждении, обычно относится не к одному объекту, а к нескольким, и должно проверяться при попытке об-

новления любого из них. Определение такого ограничения нельзя привя-

зать к конкретной таблице или столбцу.

Пример. Пусть в нашей учебной БД определена таблица, в которой хранятся сведения о квотах поставок для конкретных поставщиков:

CREATE TABLE Quota

Lim NUMERIC NOT NULL

DEFAULT 10000

CHECK (Lim BETWEEN 5000 AND 20000),

PRIMARY KEY (Snum, Pnum)

);

В этой таблице столбец Lim содержит значения квот поставок кон-

кретных видов деталей, установленных для конкретных поставщиков. Со-

гласно правилам бизнеса суммарный объём поставок детали Pnum, выпол-

ненных поставщиком Snum, не может превышать квоты, т.е. значения поля

Lim в соответствующей строке таблицы Quota. Это правило может быть нарушено как при обновлении таблицы SPJ, так и при обновлении таблицы

Quota. Для того чтобы обеспечить его проверку при любой попытке обнов-

ления любой из этих таблиц, мы можем определить следующее утвержде-

ние:

CREATE ASSERTION Limit

CHECK (

(Quota.Snum = SPJ.Snum)

AND (Quota.Pnum = SPJ.Pnum)

AND (Quota.Lim >= SUM(SPJ.Qt))

) ;

Это ограничение будет проверяться исполнительной системой авто-

матически при любой попытке обновления хотя бы одной из упомянутых таблиц.

7.3.10 Оператор определения схемы

Оператор создаёт поименованную группу связанных объектов – до-

менов, таблиц, представленй, утверждений и т.п. Схема создаётся от имени

240

DEFAULT 10 CHECK (St BETWEEN 10 AND 100),

Ci City )

CREATE TABLE P

( Pnum Pnum PRIMARY KEY,

Pnam CHAR(15) NOT NULL,

We Weight,

Co CHAR(10),

Ci City )

CREATE TABLE J

( Jnum Jnum PRIMARY KEY,

Jnam CHAR(15) NOT NULL,

Ci City )

CREATE TABLE SPJ

( Snum Snum NOT NULL

REFERENCES S

ON UPDATE CASCADE,

Pnum Pnum NOT NULL

REFERENCES P

ON UPDATE CASCADE,

Jnum Jnum NOT NULL

REFERENCES J

ON UPDATE CASCADE,

Qt NUMERIC CHECK (Qt BETWEEN 1000 AND 10000),

PRIMARY KEY (Snum, Pnum, Jnum) )

;

Обратите внимание на то, что символ ‘;’ в теле оператора CREATE SCHEMA не встречается. Он является признаком окончания оператора в целом, а не отдельного определения объекта.