Базы данных. Вводный курс

Сергей Кузнецов

Назад Содержание Вперёд

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

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

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

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

Определим таблицу 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¹⁰³⁾. Но при этом явно установлено значение столбца по умолчанию (отличное от значения по умолчанию домена), запрещено наличие неопределенных значений и введено дополнительное проверочное ограничение, определяющее нижний порог объема фонда заработной платы отдела. Еще одно проверочное ограничение – (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));

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

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

Оператор изменения определения базовой таблицы  ALTER TABLE имеет следующий синтаксис:

base_table_alteration ::= ALTER TABLE base_table_name
    column_alteration_action
    |  base_table_constraint_alternation_action

Как видно из этого синтаксического правила, при выполнении одного оператора ALTER TABLE может быть выполнено либо действие по изменению определения столбца, либо действие по изменению определения табличного ограничения целостности.

Добавление, изменение или удаление определения столбца

Действие по изменению определения столбца специфицируется в следующем синтаксисе:

column_alteration_action ::= 
        ADD [ COLUMN ] column_definition 
    | ALTER [ COLUMN ] column_name
        { SET default_definition | DROP DEFAULT }
    | DROP [ COLUMN ] column_name
               { RESTRICT | CASCADE }

Итак, с использованием оператора ALTER TABLE можно добавлять к определению таблицы определение нового столбца (действие ADD) и изменять или отменять определение существующего столбца (действия ALTER и DROP соответственно).

Смысл действия ADD COLUMN почти полностью совпадает со смыслом раздела определения столбца в операторе CREATE TABLE. Указывается имя нового столбца, его тип данных или домен. Могут определяться значение по умолчанию и ограничения целостности. Однако имеется одно существенное отличие: столбец, определяемый в действии ADD оператора ALTER TABLE, добавляется к уже существующей таблице, которая, скорее всего, содержит некоторый набор строк. В каждой из существующих строк новый столбец должен содержать некоторое значение, и считается, что сразу после выполнения действия ADD этим значением является значение столбца по умолчанию. Поэтому столбец, определяемый в действии ADD, обязательно должен иметь значение по умолчанию, т. е. для него недопустима ситуация, когда значением по умолчанию явно или неявно объявлено неопределенное значение (NULL), но среди ограничений целостности столбца присутствует ограничение NOT NULL.

В действии ALTER COLUMN можно изменить (SET default_definition) или отменить определение значения по умолчанию для существующего столбца. Правила определения нового действующего значения столбца по умолчанию совпадают с соответствующими правилами, обсуждавшимися в подразделе определения столбца в операторе CREATE TABLE. Заметим, что изменение значения столбца по умолчанию не оказывает влияния на состояние существующих строк таблицы (даже если в некоторых из них хранится предыдущее значение столбца по умолчанию). Если столбец определен на домене, у которого существует значение по умолчанию, то после отмены определения значения столбца по умолчанию для этого столбца начинает действовать значение по умолчанию домена.

Действие DROP COLUMN отменяет определение существующего столбца (удаляет его из таблицы). Действие DROP COLUMN отвергается, если:

• (a) указанный столбец является единственным столбцом таблицы;
• (b) или в этом действии присутствует спецификация RESTRICT, и данный столбец используется в определении каких-либо представлений или ограничений целостности¹⁰⁵⁾.

Если в действии присутствует спецификация CASCADE, то его выполнение порождает неявное выполнение оператора DROP для всех представлений и ограничений целостности, в определении которых используется данный столбец.

Примеры изменения определения столбца

Предположим, что на предприятии ввели систему премирования служащих. Каждый служащий может дополнительно к зарплате получать ежемесячную премию, не превышающую размер его зарплаты. Тогда разумно добавить к таблице EMP новый столбец EMP_BONUS, используя оператор ALTER TABLE:

ALTER TABLE EMP
ADD EMP_BONUS SALARY DEFAULT NULL 
   CONSTRAINT BONSAL CHECK (VALUE < EMP_SAL);

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

При определении столбца  EMP_SAL таблицы EMP для этого столбца явно не определялось значение по умолчанию (оно наследовалось из определения домена). Если в какой-то момент это стало неправильным (например, повысился размер минимальной зарплаты), можно установить новое значение по умолчанию:

ALTER TABLE EMP ALTER EMP_SAL SET DEFAULT 15000.00.

При определении столбца  DEPT_TOTAL_SAL таблицы DEPT для него было установлено значение по умолчанию 1000000. Главный бухгалтер предприятия может быть недоволен тем, что такие важные данные, как объем фонда зарплаты отделов, могут устанавливаться по умолчанию. Тогда можно отменить это значение по умолчанию:

ALTER TABLE DEPT ALTER DEPT_TOTAL_SAL DROP DEFAULT.

Обратите внимание, что после выполнения этого оператора при вставке новой строки в таблицу DEPT всегда потребуется явно указывать значение столбца DEPT_TOTAL_SAL. Хотя формально у столбца будет существовать значение по умолчанию, наследуемое от домена SALARY (10000.00), оно не может быть занесено в таблицу DEPT, поскольку противоречит ограничению столбца DEPT_TOTAL_SAL CHECK (VALUE >= 100000.00).

Можно задуматься, действительно ли требуется поддерживать в таблице DEPT столбец DEPT_EMP_NO. Как мы видели, для его поддержки требуется проверять громоздкое ограничение целостности, а число служащих в любом отделе можно получить динамически с помощью простого запроса к таблице EMP (собственно, этот запрос входит в ограничение целостности). Поэтому может оказаться разумным отменить определение столбца  DEPT_EMP_NO, выполнив следующий оператор ALTER TABLE:

ALTER TABLE DEPT DROP DEPT_EMP_NO CASCADE.

Напомним, что спецификация CASCADE ведет к тому, что при выполнении оператора будет уничтожено не только определение указанного столбца, но и определения всех ограничений целостности и представлений, в которых используется уничтожаемый столбец. В нашем случае единственное связанное с этим столбцом ограничение целостности, определенное вне определения столбца, было бы отменено, даже если бы в операторе отмены определения столбца  DEPT_EMP_NO содержалась спецификация RESTRICT, поскольку это единственное внешнее определение ограничения является ограничением только столбца DEPT_EMP_NO.

Изменение набора табличных ограничений

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

base_table_constraint_alternation_action ::=
        ADD [ CONSTRAINT ] base_table_constraint_definition
    | DROP CONSTRAINT constraint_name
        { RESTRICT | CASCADE }

Действие ADD [ CONSTRAINT ] позволяет добавить к набору существующих ограничений таблицы новое ограничение целостности. Можно считать, что новое ограничение добавляется через AND к конъюнкции существующих ограничений, как если бы оно определялось в составе оператора CREATE TABLE. Но здесь имеется одно существенное отличие. Если внимательно посмотреть на все возможные виды табличных ограничений, можно убедиться, что любое из них удовлетворяется на пустой таблице. Поэтому, какой бы набор табличных ограничений ни был определен при создании таблицы, это определение является допустимым и не препятствует выполнению оператора CREATE TABLE. При добавлении нового табличного ограничения с использованием действия ADD [ CONSTRAINT ] мы имеем другую ситуацию, поскольку таблица, скорее всего, уже содержит некоторый набор строк, для которого условное выражение нового ограничения может принять значение false. В этом случае выполнение оператора ALTER TABLE, включающего действие ADD [ CONSTRAINT ], отвергается.

Выполнение действия DROP CONSTRAINT приводит к отмене определения существующего табличного ограничения. Можно отменить определение только именованных табличных ограничений. Спецификации RESTRICT и CASCADE осмыслены только в том случае, если отменяемое ограничение является ограничением возможного ключа (UNIQUE или PRIMARY KEY)¹⁰⁶⁾. При указании RESTRICT действие отвергается, если на данный возможный ключ ссылается хотя бы один внешний ключ. При указании CASCADE действие DROP CONSTRAINT выполняется в любом случае, и все определения таких внешних ключей также отменяются.

Примеры изменения набора табличных ограничений

Напомним, что мы добавили к таблице EMP столбец EMP_BONUS, в котором сохраняются размеры ежемесячных премий служащих. Предположим, что премии выплачиваются из фонда заработной платы отдела, в котором работает служащий. Тогда проверочное ограничение столбца  DEPT_TOTAL_SAL, устанавливающее, что объем фонда зарплаты отдела не должен быть меньше суммарной зарплаты служащих этого отдела, становится недостаточным, и нам требуется добавить к набору ограничений таблицы DEPT новое ограничение:

ALTER TABLE DEPT ADD CONSTRAINT TOTAL_INCOME
CHECK (DEPT_TOTAL_SAL >= 
    (SELECT SUM(EMP_SAL + COALESCE(EMP_BONUS,0))
   FROM EMP WHERE EMP.DEPT_NO = DEPT_NO)).

Хотя это ограничение на вид довольно сложное, смысл его очень прост: суммарный доход служащих отдела не должен превышать объем зарплаты отдела. В арифметическом выражении под знаком агрегатной операции SUM используется операция COALRSCE. Эта двуместная операция определяется следующим образом:

COALESCE (x, y) IF x IS NOT NULL THEN x ELSE y,

т. е. значением операции является значение первого операнда, если оно не равно NULL, и значение второго операнда – в противном случае. Нам пришлось воспользоваться этой операцией, поскольку в столбце EMP_BONUS допускается наличие неопределенных значений.

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

Кстати, новому ограничению мы присвоили явное имя. К этому привели следующие рассуждения. Когда создавалась исходная схема базы данных, руководство предприятия ничего не говорило о премиях служащих. Теперь начальство решило, что премии будут выплачиваться из фонда зарплаты. Для этого, мы добавили новый столбец и новое ограничение целостности. Но кто знает, не изменится ли снова решение о премиях? Чтобы не добавлять себе работы в будущем, дадим новому ограничению явное имя и не будем отменять предыдущее ограничение.¹⁰⁷⁾

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

ALTER TABLE EMP DROP CONSTRAINT PRO_EMP_NO;

---

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

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

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

105   Не считая те табличные ограничения целостности, которые (a) определены в составе определения базовой таблицы, содержащей данный столбец и (b) не содержат ссылок на какие-либо другие столбцы.

106   Хотя формально требуется указывать одно из этих ключевых слов в любом действии DROP CONSTRAINT.

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

Назад Содержание Вперёд