Глава 6. Поддержание целостности данных

• Связи между родительскими и порожденными таблицами
• Множественные ограничения FOREIGN KEY
• Управление одновременным доступом, индексы и внешние ключи
• Использование ограничений целостности CHECK
• Условия для ограничений CHECK
• Проектирование ограничений CHECK
• Множественные ограничения CHECK
• Ограничения целостности CHECK и NOT NULL
Определение ограничений целостности
• Определение ограничений целостности в команде CREATE TABLE
• Определение ограничений целостности в команде ALTER TABLE
• Привилегии, требуемые для создания ограничений целостности
• Именование ограничений целостности
• Включение и выключение ограничений целостности при их определении
• Определение ограничений целостности UNIQUE, PRIMARY KEY и FOREIGN KEY
Включение и выключение ограничений целостности
• Зачем включать или выключать ограничения?
• Нарушения ограничений целостности
• Включение и выключение ограничений целостности при их определении
• Выключение ограничений при их определении
• Включение и выключение существующих ограничений целостности
• Включение выключенных ограничений
• Выключение включенных ограничений

Связи между родительскими и порожденными таблицами

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

ВНЕШНИЙ КЛЮЧ БЕЗ ОГРАНИЧЕНИЙ. Если по внешнему ключу не определены никакие дополнительные ограничения, то любое количество строк в порожденной таблице могут ссылаться на одно и то же значение родительского ключа. Эта модель позволяет иметь пустые значения во внешнем ключе.

Эта модель устанавливает такое отношение "один ко многим" между родительским и внешним ключами, которое позволяет иметь неопределенные (пустые) значения во внешнем ключе. Пример такой связи показан на рис.6-3 между таблицами EMP и DEPT; каждый отдел (родительский ключ) имеет много сотрудников (внешний ключ), а некоторые сотрудники могут не относиться ни к какому отделу (пустые значения во внешнем ключе).

ОГРАНИЧЕНИЕ NOT NULL ПО ВНЕШНЕМУ КЛЮЧУ. Когда пустые значения во внешнем ключе не допускаются, каждая строка в порожденной таблице должна явно ссылаться на некоторое значение родительского ключа. Однако по-прежнему любое количество строк в порожденной таблице могут ссылаться на одно и то же значение родительского ключа.

Эта модель устанавливает связь "один ко многим" между родительским и внешним ключами. Однако каждая строка в порожденной таблице должна ссылаться на значение родительского ключа; отсутствие значения (пустота) внешнего ключа не допускается. Пример такой связи также может быть проиллюстрирован рисунком 6-3. Однако в этом случае каждый сотрудник должен быть приписан к определенному отделу.

ОГРАНИЧЕНИЕ UNIQUE ПО ВНЕШНЕМУ КЛЮЧУ. Когда по внешнему ключу определено ограничение UNIQUE, лишь одна строка в порожденной таблице может ссылаться на любое данное значение родительского ключа. Эта модель позволяет иметь пустые значения во внешнем ключе.

Эта модель устанавливает связь "один к одному" между родительским и внешним ключами, позволяющую иметь неопределенные (пустые) значения во внешнем ключе. Например, предположим, что таблица EMP имеет столбец с именем MEMBERNO, который ссылается на членский номер сотрудника в плане страхования компании. Кроме того, существует таблица INSURANCE с первичным ключом MEMBERNO, каждая строка которой содержит информацию, относящуюся к страхованию соответствующего сотрудника. Столбец MEMBERNO в таблице EMP должен быть как внешним ключом, так и уникальным ключом:

• чтобы задействовать правила ссылочной целостности между таблицами EMP и INSURANCE (ограничение FOREIGN KEY)
• чтобы гарантировать, что каждый сотрудник имеет уникальный членский номер (ограничение UNIQUE)

ОГРАНИЧЕНИЯ UNIQUE И NOT NULL ПО ВНЕШНЕМУ КЛЮЧУ. Когда по внешнему ключу определены оба ограничения UNIQUE и NOT NULL, лишь одна строка в порожденной таблице может ссылаться на любое данное значение родительского ключа, и каждая строка в порожденной таблице обязана ссылаться на некоторое значение родительского ключа.

Эта модель устанавливает связь "один к одному" между родительским и внешним ключами, не позволяющую иметь неопределенные (пустые) значения во внешнем ключе. Такая связь также может быть проиллюстрирована приведенным выше примером. Однако в этом случае не допускается отсутствие значения в столбце MEMBERNO таблицы EMP для каждого сотрудника.

Множественные ограничения FOREIGN KEY

ORACLE позволяет, чтобы на данный (родительский) столбец имелись ссылки через множественные ограничения FOREIGN KEY; в действительности не существует ограничения на количество зависимых ключей. Такая ситуация может иметь место, в частности, если один и тот же столбец является частью двух различных составных внешних ключей.

Управление одновременным доступом, индексы и внешние ключи

ORACLE максимизирует степень управления доступом к родительским ключам по отношению к значениям зависимых внешних ключей. Вы можете управлять механизмами, используемыми для поддержания этих связей; в зависимости от ситуации, это может быть весьма эффективно. Следующие секции объясняют возможные ситуации и дают соответствующие рекомендации.

НЕИНДЕКСИРОВАННЫЙ ВНЕШНИЙ КЛЮЧ. Рис.6-4 иллюстрирует механизмы блокировки, используемые ORACLE при вставке новых строк в порожденную таблицу, когда по внешнему ключу не существует индекса.

Рис.6-4. Механизмы блокировки при неиндексированном внешнем ключе

Заметьте, что разделяемая блокировка (чтение разрешено, запись запрещена) по всей родительской таблице требуется до подтверждения транзакции, содержащей предложение INSERT для порожденной таблицы. Поэтому до подтверждения этой транзакции по родительской таблице нельзя выдавать предложений INSERT, UPDATE или DELETE; по родительской таблице разрешены лишь запросы. Эта ситуация терпима, если активность обновлений по родительской таблице низка.

ИНДЕКСИРОВАННЫЙ ВНЕШНИЙ КЛЮЧ. Рис.6-5 иллюстрирует механизмы блокировки, используемые ORACLE при вставке новых строк в порожденную таблицу, когда по внешнему ключу определен индекс.

Рис.6-5. Механизмы блокировки при индексированном внешнем ключе

Заметьте, что ни по родительской таблице, ни по ее индексам не запрашивается никаких блокировок. Поэтому по родительской таблице можно выдавать любые предложения DML, включая INSERT, UPDATE, DELETE или запросы.

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

Использование ограничений целостности CHECK

Используйте ограничения CHECK, когда вам необходимо задействовать правила целостности, которые должны вычисляться на базе логических выражений. Никогда не применяйте ограничений CHECK там, где необходимая проверка может быть осуществлена через другие типы ограничений целостности (см. секцию "Ограничения целостности CHECK и NOT NULL" на странице 6-12). Примеры правильного применения ограничений CHECK включают:

• ограничение CHECK по столбцу SAL таблицы EMP, которое гарантирует, что ни одно значение SAL не превышает 10000
• ограничение CHECK по столбцу LOC таблицы DEPT, которое допускает лишь значения "BOSTON", "NEW YORK" и "DALLAS" в столбце LOC
• ограничение CHECK по столбцам SAL и COMM, которое не допускает, чтобы значение COMM превышало значение SAL в той же строке

Условия для ограничений CHECK

Ограничение целостности CHECK требует, чтобы определенное условие было истинным или неизвестным (пустым) для каждой строки таблицы. Если предложение SQL приводит к ложности этого условия, то это предложение откатывается. Условие ограничения CHECK имеет следующие ограничения:

• Это условие должно быть булевским выражением, вычисляемым над значениями вставляемой или обновляемой строки.
• Это условие не может содержать подзапросы или последовательности.
• Это условие не может включать функции SQL SYSDATE, UID, USER или USERENV.
• Это условие не может содержать псевдостолбцы LEVEL или ROWNUM; обратитесь к документу ORACLE7 Server SQL Language Reference Manual за описанием этих псевдостолбцов.

Проектирование ограничений CHECK

Используя ограничения CHECK, учитывайте стандарт ANSI/ISO, который утверждает, что ограничение CHECK нарушается лишь в том случае, если его условие вычисляется как ложное; истинные и неизвестные (пустые) значения не нарушают условие ограничения. Поэтому убеждайтесь, что ограничение CHECK, которое вы определяете, действительно реализует правило, которое вы хотели задействовать.

Например, рассмотрим следующее ограничение CHECK:

CHECK (sal > 0 OR comm >= 0)

На первый взгляд, это правило может интерпретироваться так: "допускать строку в таблицу EMP только тогда, когда в ней значение жалованья больше 0 или значение комиссионных не меньше 0". Однако заметьте, что если вставляется строка с пустым значением SAL и отрицательным значением COMM, то такая строка не нарушает ограничения CHECK, потому что все условие ограничения будет вычислено как неизвестное (пустое). В данном конкретном случае вы могли бы предусмотреть такие нарушения, определив ограничения целостности NOT NULL по обоим столбцам SAL и COMM.

Замечание: Если вы не уверены, как отразятся пустые значения на результатах ваших условий, обратитесь к таблицам истинности для логических операторов AND и OR, приведенным в документе ORACLE7 Server SQL Language Reference Manual.

Множественные ограничения CHECK

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

Ограничения целостности CHECK и NOT NULL

Согласно стандарту ANSI/ISO, ограничение целостности NOT NULL является частным случаем ограничения CHECK, в котором условие имеет следующий вид:

CHECK (имя_столбца IS NOT NULL)

Поэтому ограничения NOT NULL для одиночных столбцов можно, на практике, записывать двумя способами: через ограничение NOT NULL или через ограничение CHECK. Для простоты всегда выбирайте ограничение NOT NULL вместо ограничения CHECK с условием "IS NOT NULL".

В случае, когда составной ключ должен допускать только пустоту или непустоту одновременно всех составляющих значений, вы можете использовать только ограничение CHECK. Например, следующее выражение позволяет составному ключу по столбцам C1 и C2 состоять либо из обоих пустых, либо из обоих непустых значений:

CHECK ((c1 IS NULL AND c2 IS NULL) OR (c1 IS NOT NULL AND c2 IS NOT NULL))

Определение ограничений целостности

Определяйте ограничение целостности с помощью фразы CONSTRAINT команды SQL CREATE TABLE или ALTER TABLE. Следующие две секции описывают, как использовать эти команды для определения ограничений целостности.

Существуют дополнительные соображения, если вы используете Trusted ORACLE. Для дополнительной информации обратитесь к документу Trusted ORACLE7 Server Administrator's Guide.

Определение ограничений целостности в команде CREATE TABLE

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

CREATE TABLE dept (
    deptno   NUMBER(3) PRIMARY KEY,
    dname    VARCHAR2(15),
    loc      VARCHAR2(15)
             CONSTRAINT dname_ukey UNIQUE (dname, loc),
             CONSTRAINT loc_check1
             CHECK (loc IN ('NEW YORK', 'BOSTON', 'CHICAGO')));
CREATE TABLE emp (
    empno    NUMBER(5) PRIMARY KEY,
    ename    VARCHAR2(15) NOT NULL,
    job      VARCHAR2(10),
    mgr      NUMBER(5) CONSTRAINT mgr_fkey
               REFERENCES emp,
    hiredate DATE,
    sal      NUMBER(7,2),
    comm     NUMBER(5,2),
    deptno   NUMBER(3) NOT NULL
             CONSTRAINT dept_fkey
               REFERENCES dept ON DELETE CASCADE);

Определение ограничений целостности в команде ALTER TABLE

Вы можете также определять ограничения целостности с помощью фразы CONSTRAINT команды ALTER TABLE. Следующие примеры предложений ALTER TABLE показывают определения нескольких ограничений целостности:

ALTER TABLE dept
    ADD PRIMARY KEY (deptno);

ALTER TABLE emp
   ADD CONSTRAINT dept_fkey FOREIGN KEY (deptno) REFERENCES dept
   MODIFY (ename VARCHAR2(15) NOT NULL);

Ограничения на команду ALTER TABLE

Так как в момент выдачи команды ALTER TABLE в таблице уже могут быть данные, существуют несколько ограничений на применение этой команды, о которых вы должны знать. Табл.6-1 показывает каждый тип ограничений целостности и соответствующие ограничения на их использование в команде ALTER TABLE.

Рис.6-6. Условия создания ограничений с помощью команды ALTER TABLE

*В предположении, что фраза DISABLE не включена в предложение.

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

Привилегии, требуемые для создания ограничений целостности

Создатель ограничения должен иметь право создания таблиц (т.е. системную привилегию CREATE TABLE или CREATE ANY TABLE) или право на изменение таблицы (т.е. объектную привилегию ALTER для данной таблицы или системную привилегию ALTER ANY TABLE). Кроме того, ограничения целостности UNIQUE и PRIMARY KEY требуют, чтобы владелец таблицы имел либо квоту для табличного пространства, в котором должен быть создан ассоциированный индекс, либо системную привилегию UNLIMITED TABLESPACE. Ограничения целостности также требуют некоторых дополнительных привилегий; для более подробной информации обратитесь к секции "Привилегии, требуемые для ограничений целостности FOREIGN KEY" на странице 6-21.

Именование ограничений целостности

Определяя ограничения целостности NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY и CHECK, назначайте им имена в опции CONSTRAINT. Имя ограничения должно быть уникальным среди всех принадлежащих вам имен ограничений. Если вы не специфицируете имя ограничения, ORACLE сам назначает имя.

Приведенные выше примеры команд CREATE TABLE и ALTER TABLE показывают, как назначаются имена ограничений целостности через опцию CONSTRAINT. Заметьте, что имя ограничения сохраняется в словаре данных вместе с определением этого ограничения. Обратитесь к секции "Вывод определений ограничений целостности" на странице 6-23 за примерами соответствующих обзоров словаря данных.

Включение и выключение ограничений целостности при их определении

По умолчанию, когда ограничение целостности определяется в предложении CREATE TABLE или ALTER TABLE, оно автоматически включается (вводится в действие) ORACLE, если для него явно не специфицировано выключенное состояние посредством фразы DISABLE. Обратитесь к секции "Включение и выключение ограничений целостности" на странице 6-15 для дополнительной информации о важных вопросах, касающихся включения и выключения ограничений целостности.

Определение ограничений целостности UNIQUE, PRIMARY KEY и FOREIGN KEY

При определении ограничений целостности UNIQUE, PRIMARY KEY и FOREIGN KEY вы должны учитывать некоторые существенные моменты и необходимые условия. Для дополнительной информации об определении и сопровождении ограничений FOREIGN KEY обратитесь к секции "Привилегии, требуемые для ограничений целостности FOREIGN KEY" на странице 6-21. Ограничения UNIQUE и PRIMARY KEY обычно включаются администратором базы данных; для дополнительной информации обратитесь к документу ORACLE7 Server Administrator's Guide.

Включение и выключение ограничений целостности

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

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

Зачем включать или выключать ограничения?

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

• при загрузке больших массивов данных в таблицу с помощью SQL*Loader
• при выполнении пакетных операций, осуществляющих массированные изменения в таблице (например, замену номера каждого сотрудника путем прибавления 1000 к существующему номеру)
• при импорте или экспорте таблицы

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

Нарушения ограничений целостности

Пока ограничение включено, никакая строка, нарушающая это ограничение, не может быть вставлена в таблицу. Когда ограничение выключено, однако, такие строки могут быть вставлены; каждая такая строка известна как ИСКЛЮЧЕНИЕ для данного ограничения. Пока в таблице существуют исключения ограничения, ЭТО ОГРАНИЧЕНИЕ НЕ МОЖЕТ БЫТЬ ВКЛЮЧЕНО. Строки, нарушающие ограничение, должны быть либо удалены, либо обновлены, чтобы ограничение можно было включить.

Вы можете идентифицировать исключения для конкретного ограничения целостности при попытке включить это ограничение. См. "Вывод исключений ограничений" на странице 6-18.

Включение и выключение ограничений целостности при их определении

При определении ограничения целостности предложением CREATE TABLE или ALTER TABLE, оно может быть либо включено с помощью опции ENABLE, либо выключено с помощью опции DISABLE. Если ни одна из этих опций не указана при определении ограничения, ORACLE автоматически включает это ограничение.

Включение ограничений при их определении

Следующие предложения CREATE TABLE и ALTER TABLE одновременно определяют и включают ограничения целостности:

CREATE TABLE emp (
    empno NUMBER(5) PRIMARY KEY, . . . );

ALTER TABLE emp
    ADD PRIMARY KEY (empno);

Предложение ALTER TABLE, которое определяет и пытается включить ограничение, может сбиться, потому что существующие строки таблицы могут нарушать это ограничение целостности. В этом случае предложение откатывается, а определение ограничения не сохраняется и не включается. Обратитесь к секции "Вывод исключений ограничений" на странице 6-18 для дополнительной информации о строках, нарушающих ограничения целостности.

Выключение ограничений при их определении

Следующие предложения CREATE TABLE и ALTER TABLE одновременно определяют и выключают ограничения целостности:

CREATE TABLE emp (
    empno NUMBER(5) PRIMARY KEY DISABLE, . . . );

ALTER TABLE emp
    ADD PRIMARY KEY (empno) DISABLE;

Предложение ALTER TABLE, которое определяет и выключает ограничение, никогда не сбивается из-за строк таблицы, нарушающих это ограничение целостности. Определение ограничения разрешается, так как его правило остается выключенным.

Включение и выключение существующих ограничений целостности

С помощью команды ALTER TABLE можно:

• включить выключенное ограничение (фразой ENABLE)
• выключить включенное ограничение (фразой DISABLE)

Включение выключенных ограничений

Следующие предложения включают выключенные ограничения целостности:

ALTER TABLE dept
    ENABLE CONSTRAINT dname_ukey;

ALTER TABLE dept
    ENABLE PRIMARY KEY,
    ENABLE UNIQUE (dname, loc);

Предложение ALTER TABLE, которое пытается включить выключенное ограничение, может сбиться, потому что существующие строки таблицы могут нарушать это ограничение целостности. В этом случае предложение откатывается, а ограничение не включается. Обратитесь к секции "Вывод исключений ограничений" на странице 6-18 для дополнительной информации о строках, нарушающих ограничения целостности.

Выключение включенных ограничений

Следующие предложения выключают включенные ограничения целостности:

ALTER TABLE dept
    DISABLE CONSTRAINT dname_ukey;

ALTER TABLE dept
    DISABLE PRIMARY KEY,
    DISABLE UNIQUE (dname, loc);

Совет: Используйте словарь данных как справочник

Примеры предложений в предыдущих секциях требуют, чтобы вы знали некоторую информацию об ограничении для его включения или выключения. Так, первое предложение в каждой секции требует, чтобы вы знали имя ограничения; второе предложение требует, чтобы вы знали список столбцов уникального ключа. Чтобы узнать эти сведения, вы можете опросить один из обзоров словаря данных, связанных с ограничениями; для дополнительной информации об этих обзорах обратитесь к секции "Вывод определений ограничений целостности" на странице 6-23, а также к приложению D.

предыдущая часть | содержание | следующая часть

Дополнительную информацию Вы можете получить в компании Interface Ltd.

Обсудить на форуме Oracle
Отправить ссылку на страницу по e-mail