СТАТЬЯ
17.09.01

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

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

Содержание части

Управление ограничениями целостности UNIQUE, PRIMARY KEY и FOREIGN KEY

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

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

Если при выдаче предложений CREATE TABLE ... ENABLE или ALTER TABLE ... ENABLE не существует исключений, то соответствующее ограничение включается, и все последующие предложения DML подвергаются проверке на включенное ограничение целостности.

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

Чтобы определить, какие строки нарушают ограничение целостности, включите опцию EXCEPTIONS в фразу ENABLE предложения CREATE

TABLE или ALTER TABLE. Опция EXCEPTIONS помещает в указанную таблицу ROWID, имя владельца таблицы, имя таблицы и имя ограничения для каждой строки таблицы, нарушающей ограничение. Например, следующее предложение пытается включить первичный ключ таблицы DEPT, и, если существуют исключения, поместить информацию о них в таблицу с именем EXCEPTIONS:

ALTER TABLE dept ENABLE PRIMARY KEY EXCEPTIONS INTO exceptions;

Замечание: Вы должны создать соответствующую таблицу исключений для приема информации от опции EXCEPTIONS фразы ENABLE, прежде чем включать ограничение. Можно создать таблицу ограничений, запустив скрипт UTLEXCPT.SQL. Этот скрипт создает таблицу с именем EXCEPTIONS. Вы можете создать дополнительные таблицы исключений с разными именами, модифицируя и перезапуская этот скрипт.

Если в показанном выше примере в таблице DEPT существуют повторяющиеся значения первичного ключа, а ограничение PRIMARY KEY по таблице DEPT имеет имя SYS_C00301, то приведенное выше предложение может поместить в таблицу EXCEPTIONS следующие строки:

SELECT * FROM exceptions;

ROWID                OWNER      TABLE_NAME      CONSTRAINT
───────────────────  ─────────  ──────────────  ───────────
000003A5.000C.0001   SCOTT      DEPT            SYS_C00301
000003A5.000D.0001   SCOTT      DEPT            SYS_C00301

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

SELECT deptno, dname, loc FROM dept, exceptions
    WHERE exceptions.constraint = 'SYS_C00301'
    AND dept.rowid = exceptions.row_id;

DEPTNO     DNAME          LOC
────────── ────────────── ──────────────
        10 ACCOUNTING     NEW YORK
        10 RESEARCH       DALLAS

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

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

UPDATE dept SET deptno = 20 WHERE dname = 'RESEARCH';

DELETE FROM exceptions WHERE constraint = 'SYS_C00301';

COMMIT;

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

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

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

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

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

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

Управление ограничениями целостности FOREIGN KEY

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

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

Следующие вопросы заслуживают интереса при определении ограничений целостности FOREIGN KEY.

Совпадение типов данных

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

Составные внешние ключи

Так как внешние ключи ссылаются на первичные и уникальные ключи родительской таблицы, а соответствующие ограничения PRIMARY KEY и UNIQUE реализуются посредством индексов, составные внешние ключи ограничиваются 16-ю столбцами.

Подразумеваемая ссылка на первичный ключ

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

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

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

В обоих случаях, необходимые привилегии НЕ МОГУТ быть получены через роль; они должны быть явно назначены создателю ограничения.

Эти условия позволяют:

Задание ссылочных действий для внешних ключей

ORACLE позволяет вводить два различных типа действий ссылочной целостности, как специфицировано в определении ограничения FOREIGN KEY:

ОТСУТСТВИЕ ДЕЙСТВИЯ. Отсутствие действия запрещает обновление или удаление родительского ключа, если в порожденной таблице существует строка, которая ссылается на этот ключ. По умолчанию, все ограничения FOREIGN KEY подразумевают отсутствие действия; для специфицирования отсутствия действия не требуется ничего указывать в определении ограничения. Например:

CREATE TABLE emp ( . . . , FOREIGN KEY (deptno) REFERENCES dept);

ДЕЙСТВИЕ ON DELETE CASCADE. Это действие разрешает удалять (но не обновлять) адресуемые данные в родительской таблице. Когда удаляются адресуемые данные в родительской таблице, все строки в порожденной таблице, зависящие от удаляемого родительского ключа, также удаляются. Чтобы специфицировать это ссылочное действие, включите в определение ограничения FOREIGN KEY опцию ON DELETE CASCADE. Например:

CREATE TABLE emp ( . . . , FOREIGN KEY (deptno) REFERENCES dept ON DELETE CASCADE);

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

Ограничение целостности FOREIGN KEY не может быть включено, если ограничение UNIQUE или PRIMARY KEY, определяющее адресуемый родительский ключ, не существует или не включено.

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

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

Обратитесь к приложению D для детальной информации о каждом из этих обзоров.

Примеры

Рассмотрим cледующие предложения 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 ON DELETE CASCADE,
    hiredate DATE,
    sal      NUMBER(7,2),
    comm     NUMBER(5,2),
    deptno   NUMBER(3) NOT NULL
             CONSTRAINT dept_fkey REFERENCES dept);

Пример 1: Вывод всех доступных ограничений

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

SELECT constraint_name, constraint_type, table_name,               r_constraint_name
    FROM user_constraints;

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

CONSTRAINT_NAME  C TABLE_NAME  R_CONSTRAINT_NAME
───────────────  ─ ─────────── ──────────────────
SYS_C00275       P DEPT
DNAME_UKEY       U DEPT
LOC_CHECK1       C DEPT
SYS_C00278       C EMP
SYS_C00279       C EMP
SYS_C00280       P EMP
MGR_FKEY         R EMP         SYS_C00280
DEPT_FKEY        R EMP         SYS_C00275

Заметьте следующее:

Имена некоторых ограничений специфицированы пользователем (такие как DNAME_UKEY), тогда как другие сгенерированы системой (такие как SYS_C00275).

Каждый тип ограничения обозначается собственным символом в столбце CONSTRAINT_TYPE. Следующая таблица суммирует обозначения для всех типов ограничений.

Тип ограничения

PRIMARY KEY
UNIQUE
FOREIGN KEY
CHECK, NOT NULL
Символ

P
U
R
C

Замечание: Существует еще один тип ограничения, который обозначается в столбце CONSTRAINT_TYPE символом "V". Этот тип соответствует ограничениям, созданным опцией WITH CHECK OPTION для обзоров. Обратитесь к главе 2 для дополнительной информации об обзорах с опцией WITH CHECK OPTION.

Пример 2: Распознавание ограничений NOT NULL и CHECK

В предыдущем примере некоторые ограничения были выданы с типом ограничения "C". Чтобы выяснить, какие из этих ограничений по таблицам EMP и DEPT были определены как NOT NULL, а какие как CHECK, выдайте следующий запрос:

SELECT constraint_name, search_condition
    FROM user_constraints
    WHERE (table_name = 'DEPT' OR table_name = 'EMP') AND
           constraint_type = 'C';

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

CONSTRAINT_NAME  SEARCH_CONDITION
───────────────  ──────────────────────────────────────────
LOC_CHECK1       loc IN ('NEW YORK', 'BOSTON', 'CHICAGO')
SYS_C00278       ENAME IS NOT NULL
SYS_C00279       DEPTNO IS NOT NULL

Заметьте следующее:

Пример 3: Вывод имен столбцов, составляющих ограничение целостности

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

SELECT constraint_name, table_name, column_name FROM user_cons_columns;

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

CONSTRAINT_NAME  TABLE_NAME  COLUMN_NAME
───────────────  ─────────── ───────────────
DEPT_FKEY        EMP         DEPTNO
DNAME_UKEY       DEPT        DNAME
DNAME_UKEY       DEPT        LOC
LOC_CHECK1       DEPT        LOC
MGR_FKEY         EMP         MGR
SYS_C00275       DEPT        DEPTNO
SYS_C00278       EMP         ENAME
SYS_C00279       EMP         DEPTNO
SYS_C00280       EMP         EMPNO

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

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

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


Interface Ltd.
Тel/Fax: +7(095) 105-0049 (многоканальный)
Отправить E-Mail
http://www.interface.ru
Ваши замечания и предложения отправляйте автору
По техническим вопросам обращайтесь к вебмастеру
Документ опубликован: 17.09.01