Примеры определений базовых таблиц

Определим таблицы служащих (EMP), отделов (DEPT) и проектов (PRO). Эти таблицы имеют заголовки, показанные нарис. 16.1.

Столбцы EMP_NO, EMP_SAL, DEPT_NO, PRO_NO, DEPT_TOTAL_SAL, DEPT_MNG и PRO_MNG определяются на ранее определенных доменах (определения доменов EMP_NO и SALARY приведены в предыдущей лекции). Первичными ключами отношенийEMP,DEPTи проектовPROявляются столбцыEMP_NO,DEPT_NOиPRO_NOсоответственно. В таблицеEMPстолбцыDEPT_NOиPRO_NOявляются внешними ключами, указывающими на отдел, в котором работает служащий, и на выполняемый им проект соответственно. В таблицеDEPTвнешним ключом является столбецDEPT_NO, указывающий на служащего, являющегося руководителем соответствующего отдела, а в таблицеPROвнешним ключом является столбецPRO_MNG, указывающий на служащего, являющегося менеджером соответствующего проекта. Другие ограничения целостности мы обсудим позже.

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

Рис. 16.1. Заголовки таблиц EMP, DEPT и PRO

Определим таблицу EMP:

(1) CREATE TABLE EMP (

(2) EMP_NO EMP_NO PRIMARY KEY,

(3) EMP_NAME VARCHAR(20) DEFAULT 'Incognito' NOT NULL,

(4) EMP_BDATE DATE DEFAULT NULL CHECK (

VALUE >= DATE '1917-10-24'),

(5) EMP_SAL SALARY,

(6) DEPT_NO DEPT_NO DEFAULT NULL REFERENCES

DEPT ON DELETE SET NULL,

(7) PRO_NO PRO_NO DEFAULT NULL,

(8) FOREIGN KEY PRO_NO REFERENCES PRO (PRO_NO)

ON DELETE SET NULL,

(9) CONSTRAINT PRO_EMP_NO CHECK

((SELECT COUNT (*) FROM EMP E

WHERE E.PRO_NO = PRO_NO) <= 50));

Последовательно обсудим части этого определения. В части (1) указывается, что создается таблица с именем EMP. В части (2) определяется столбецEMP_NOна доменеEMP_NO. У этого столбца не определено значение по умолчанию, и он объявлен первичным ключом таблицы (это ограничение целостности добавляется черезANDк ограничениям, унаследованным столбцом от определения домена). Помимо прочего, это означает неявное указание запрета для данного столбца неопределенных значений. В части (3) определен столбецEMP_NAMEна базовом типе данных символьных строк переменной длины с максимальной длиной 20. Для столбца указано значение по умолчанию – строка'Incognito', и в качестве ограничения целостности запрещены неопределенные значения. В части (4) определяется столбецEMP_BDATE(дата рождения служащего). Он имеет тип данныхDATE, значением по умолчанию являетсяNULL(даты рождения некоторых служащих неизвестны). Кроме того, ограничение столбца запрещает принимать на работу лиц, о которых известно, что они родились до Октябрьского переворота. В части (5) определен столбецEMP_SALна доменеSALARY. Значение по умолчанию и ограничения целостности наследуются из определения домена. В части (6) столбецDEPT_NOопределяется на одноименном домене (для наших целей его определение несущественно), но явно объявляется, что значением по умолчанию этого столбца будетNULL(некоторые служащие не приписаны ни к какому отделу). Кроме того, добавляется ограничение внешнего ключа: столбецDEPT_NOссылается на первичный ключ таблицыDEPT. Определено ссылочное действие: при удалении строки из таблицыDEPTво всех строках таблицыEMP, ссылавшихся на эту строку, столбцуDEPT_NOдолжно быть присвоено неопределенное значение. В части (7) определяется столбецPRO_NO. Его определение аналогично определению столбцаDEPT_NO, но ограничение внешнего ключа вынесено в часть (8), где оно определяется в полной форме как табличное ограничение. Наконец, в части (9) определяется табличное проверочное ограничение с именемPRO_EMP_NO, которое требует, чтобы ни в одном проекте не участвовало больше 50 служащих (правила построения соответствующего условного выражения поясняются в лекции 18).

Определим таблицу DEPT:

(1) CREATE TABLE DEPT (

(2) DEPT_NO DEPT_NO PRIMARY KEY,

(3) DEPT_EMP_NO INTEGER NO NULL CHECK (

VALUE BETWEEN 1 AND 100),

(4) DEPT_NAME VARCHAR(200) DEFAULT 'Nameless' NOT NULL,

(5) DEPT_TOTAL_SAL SALARY DEFAULT 1000000.00

NO NULL CHECK (VALUE > = 100000.00),

(6) DEPT_MNG EMP_NO DEFAULT NULL

REFERENCES EMP ON DELETE SET NULL

CHECK (IF (VALUE IS NOT NULL) THEN

((SELECT COUNT(*) FROM DEPT

WHERE DEPT.DEPT_MNG = VALUE) = 1),

(7) CHECK (DEPT_EMP_NO =

(SELECT COUNT(*) FROM EMP

WHERE DEPT_NO = EMP.DEPT_NO)),

(8) CHECK (DEPT_TOTAL_SAL >=

(SELECT SUM(EMP_SAL) FROM EMP

WHERE DEPT_NO = EMP.DEPT_NO)));

Это определение мы обсудим в менее систематической манере, чем предыдущее. Отметим только наиболее интересные моменты. В части (3) столбецDEPT_EMP_NO(число служащих в отделе) определен на базовом типеINTEGERбез значения по умолчанию, с запретом неопределенного значения и с проверочным ограничением, устанавливающем допустимый диапазон значений числа служащих в отделе. Еще одно проверочное ограничение этого столбца – (7) – вынесено на уровень определения табличного ограничения. Это ограничение устанавливает, что в каждой строке таблицыDEPTзначение столбцаDEPT_EMP_NOдолжно равняться общему числу строк таблицыEMP, в которых значение столбцаDEPT_NOравно значению одноименного столбца данной строки таблицыDEPT.¹⁰²⁾В части (5) для определения столбцаDEPT_TOTAL_SAL(объем фонда заработной платы отдела) используется доменSALARY¹⁰³⁾. Но при этом явно установлено значение столбца по умолчанию (отличное от значения по умолчанию домена), запрещено наличие неопределенных значений и введено дополнительное проверочное ограничение,

102 Другими словами, это естественное ограничение требует, чтобы значения столбца DEPT_EMP_NO были «правильными», т.е. действительно соответствовали числу служащих, работающих в данном отделе.

103 По этой причине мы ввели в предыдущей лекции такую большую верхнюю границу – 20000000.00 – значений домена SALARY.

определяющее нижний порог объема фонда заработной платы отдела. Еще одно проверочное ограничение – (8) – вынесено на уровень определения табличного ограничения. Это ограничение устанавливает, что в каждой строке таблицыDEPTзначение столбцаDEPT_TOTAL_SALдолжно быть не меньше суммы значений столбцаEMP_SALво всех строках таблицыEMP, в которых значение столбцаDEPT_NOравно значению одноименного столбца данной строки таблицыDEPT.¹⁰⁴⁾Обратите внимание на определение столбцаDEPT_MNG– часть (6). Этот столбец объявляется внешним ключом таблицыDEPT. Но мы хотим сказать больше. У отдела могут временно отсутствовать руководители, поэтому в столбце допускаются неопределенные значения. Но если у отдела имеется руководитель, то он должен являться руководителем только этого отдела. На первый взгляд можно было бы воспользоваться ограничением столбцаUNIQUE. Но такое ограничение допускало бы наличие неопределенного столбцаDEPT_MNGтолько в одной строке таблицыDEPT, а мы хотим допустить отсутствие руководителя у нескольких отделов. Поэтому потребовалось ввести более громоздкое проверочное ограничение столбца.

По поводу двух приведенных определений базовых таблиц у читателей могут возникнуть два вопроса:

• (a) почему проверочное ограничение (9) в первом определении и проверочные ограничения (7) и (8) во втором определении вынесены из определений соответствующих столбцов, хотя формально являются именно ограничениями столбцов?

• (b) почему ограничению (9) в первом определении присвоено явное имя, а ограничения (7) и (8) во втором определении оставлены безымянными?

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

Вот ответ на второй вопрос. Ограничение (9) в первом определении и ограничения (7) и (8) во втором определении внешне похожи, но сильно отличаются по своей сути. Ограничения (7) и (8) связаны с агрегатной семантикой столбцов DEPT_EMP_NOиDEPT_TOTAL_SALтаблицыDEPT. Отмена ограничений изменила бы смысл этих столбцов. Ограничение (9) является текущим административным ограничением. Если руководство предприятия примет решение разрешить использовать в одном проекте более 50 служащих, ограничение можно отменить без изменения смысла столбцов таблицыEMP. Имея это в виду, мы ввели явное имя ограничения (9), чтобы при необходимости имелась простая возможность отменить это ограничение с помощью оператораALTER TABLE.

Наконец, определим таблицу PRO.

(1) CREATE TABLE PRO (

(2) PRO_NO PRO_NO PRIMARY KEY,

(3) PRO_TITLE VARCHAR(200)DEFAULT 'No title' NOT NULL,

(4) PRO_SDATE DATE DEFAULT CURRENT_DATE NOT NULL,

(5) PRO_DURAT INTERVAL YEAR DEFAUL INTERVAL '1'

YEAR NOT NULL,

(6) PRO_MNG EMP_NO UNIQUE NOT NULL

REFERENCES EMP ON DELETE NO ACTION,

(7) PRO_DESC CLOB(10M));

104 Другими словами, это естественное ограничение требует, чтобы размер фонда заработной платы отдела никогда не был меньше суммарной зарплаты, получаемой служащими этого отдела.

Столбец PRO_SDATEсодержит дату начала проекта, а столбецPRO_DURAT– продолжительность проекта в годах. В этом определении имеет смысл прокомментировать часть (6). Мы считаем, что если отдел, по крайней мере временно, может существовать без руководителя, то у проекта всегда должен быть менеджер. Поэтому определение столбцаPRO_MNGявляется гораздо более строгим, чем определение столбцаDEPT_MNGв таблицеDEPT. Сочетание ограниченийUNIQUEиNOT NULLпри отсутствии значений по умолчанию приводит к абсолютной уникальности значений столбцаPRO_MNG. Другими словами, этот столбец обладает всеми характеристиками первичного ключа, хотя объявлен только как возможный ключ. Кроме того, он объявлен как внешний ключ с действием при удалении строки таблицыEMPс соответствующим значением первичного ключаNO ACTION, запрещающим такие удаления. В совокупности это гарантирует, что у любого проекта будет существовать менеджер, являющийся служащим предприятия. В части (5) столбецPRO_DESC(описание проекта) определен как большой символьный объект с максимальным размером 10 Мбайт.