Отправить E-Mail
http://www.interface.ru
По техническим вопросам обращайтесь к вебмастеру
Документ опубликован: 17.09.01
| СТАТЬЯ |
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. Отправить E-Mail http://www.interface.ru |
|
| Ваши замечания и предложения отправляйте
автору По техническим вопросам обращайтесь к вебмастеру Документ опубликован: 17.09.01 |