Ограничения Oracle

Илья Дергунов

Илья Дергунов

Автор статьи. ИТ-специалист с 20 летним стажем, автор большого количества публикаций на профильную тематику (разработка ПО, администрирование, новостные заметки). Подробнее.

Ограничения целостности в реляционных базах данных (и Oracle Database в частности) позволяют легко и автоматически применять важные бизнес-правила к таблицам базы данных. Например, в таблице, используемой в системе управления кадрами, нельзя завести сотрудника, не назначив ему руководителя. При создании связанных таблиц можно объявить необходимые ограничения целостности, которые должны быть удовлетворены при каждом вводе или модификации данных в таблице.

Для выполнения бизнес-правил можно также задействовать логику приложения,однако ограничения целостности обычно применять проще, и обычно они хорошо выполняют свою работу, гарантируя, что вставки, обновления и удаления данных таблиц будут соответствовать определенным правилам. С другой стороны, логика приложения обладает тем преимуществом, что позволяет отклонить или подтвердить данные без обращения к содержимому таблицы. Таким образом, выбирать метод выполнения бизнес-правил — логикой приложения или ограничениями целостности — необходимо на основе потребностей конкретного приложения. В любом случае ограничения целостности настолько фундаментальны для операций в реляционных базах данных, что вы обречены на их использование в конкретной базе.

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

Для таблицы Oracle можно установить несколько типов ограничений. Упрощенно их можно разделить на пять основных типов:

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

 

Ограничения первичного ключа

Первичный ключ — очень важный тип ограничения в таблице. Когда необходимо,чтобы значения столбца идентифицировались уникальным образом, это обеспечивается созданием первичного ключа по значению столбца. Столбец, на котором определен первичный ключ, должен быть уникальным и не null.

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

SQL> CREATE TABLE dept
(dept_id number(9) PRIMARY KEY);

Кроме того, можно добавить ограничение и к существующей таблице:

SQL> ALTER TABLE dept
ADD PRIMARY KEY(dept_id); 

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

SQL> ALTER TABLE dept
ADD CONSTRAINT
dept_pk PRIMARY KEY(dept_id);
Table altered.
SQL>

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


На заметку! В обоих предыдущих примерах Oracle автоматически создает индексы на столбце,который назначен первичным ключом.


Ограничения NOT NULL

Обычно таблица имеет один или более столбцов, в которых не допускается значение null — т.е. без значений. Хорошим примером является столбец last_name таблицы employee. Заставить пользователей обязательно вносить значения в этот столбец можно при создании таблицы, указав опцию NOT NULL для столбца, который не должен быть null

SQL> CREATE TABLE employee
(last_name VARCHAR(30) NOT NULL);

Если таблица уже создана, и требуется модифицировать столбец с допускающего null на не допускающий null, для этого можно использовать следующий оператор:

SQL> ALTER TABLE employee MODIFY last_name NOT NULL; 

Проверочные ограничения

используете Проверочные (CHECK) ограничения применяются для обеспечения соответствия данных столбца определенным параметрам, которые вы укажете. Например,предположим, что годовая зарплата сотрудника фирмы не может быть равна или превышать $100 000 при определенных обстоятельствах. Это ограничение можно навязать с помощью следующего оператора, который устанавливает ограничение CHECK на столбце SALARY

SQL> CREATE TABLE employee
(employee_id NUMBER,
last_name VARCHAR2(30),
first_name VARCHAR2(30),
department_id NUMBER,
salary NUMBER CHECK(salary < 100000));

 

Ограничение уникальности

Ограничение уникальности (UNIQUE) очень распространено в реляционных базах данных. Эти ограничения гарантируют уникальность строк в реляционной таблице.В таблице могут существовать сразу несколько таких ограничений. Например, ограничение уникальности на столбце employee_id гарантирует, что ни один сотрудник не появится дважды в таблице employee.

В следующем примере первый оператор специфицирует ограничение уникальности на комбинации столбцов dept_name и location

SQL> CREATE TABLE dept(
dept_no NUMBER(3),
dept_name VARCHAR2(15),
location VARCHAR2(25),
CONSTRAINT dept_name_ukey UNIQUE(dept_Name,location);

Создать уникальное ограничение на таблице department можно также с помощью такого оператора ALTER TABLE:

SQL> ALTER TABLE dept
ADD CONSTRAINT dept_idx UNIQUE(dept_no);
Table altered.
SQL> 

 

Ограничения ссылочной целостности

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

Гарантировать существование действительного департамента можно с помощью ограничения ссылочной целостности. В этом случае столбец departament_id служит первичным ключом таблицы dept, а столбец dept_id в таблице employee, ссылающийся на соответствующий столбец таблицы department, называется внешним ключом. Таблица,содержащая внешний ключ, обычно называется дочерней таблицей, а таблица, содержащая ключ, на который ссылается внешний ключ — родительской таблицей. Как и со всеми прочими типами ограничений, ссылочные ограничения целостности можно создавать во время создания таблицы или позднее, с помощью оператора ALTER TABLE

SQL> CREATE TABLE employee
(employee_id NUMBER(7),
last_name VARCHAR2(30),
first name VARCHAR2(30),
job VARCHAR2(15),
dept_id NUMBER(3) NOT NULL
CONSTRAINT dept_fkey REFERENCES dept(dept_id)); 

База данных назначает столбец dept_id таблицы employee внешним ключом, потому что он ссылается на столбец dept_id таблицы dept. Обратите внимание, что для того, чтобы столбец служил ссылочным (на который ссылаются), он должен быть уникальным или же быть первичным ключом в таблице, на которую установлена ссылка.

 

Состояния ограничений целостности

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

При загрузке больших объемов данных с использованием SQL*Loader или утилиты Import может потребоваться значительное время на загрузку данных, каждую строку которых нужно проверять на предмет нарушения целостности. Более эффективная стратегия предусматривает отключение ограничения, загрузку данных и последующую проверку корректности загруженных данных. После завершения загрузки ограничения вновь вводятся в действие посредством включения. Когда ограничение отключается,как описано здесь, база данных уничтожает индекс. Подобным образом реализуется лучшая стратегия — предварительное создание неуникальных индексов для ограничений, которые база данных не должна уничтожать, поскольку они обрабатывают дублированные записи.


На заметку! Состояние enabled (включено) — это состояние ограничений Oracle по умолчанию


Отключаются ограничения двумя способами: указание в качестве состояния ограничения либо disable validate (отключить с проверкой), либо disable no validate (отключить без проверки) с использованием конструкции DISABLE VALIDATE и DISABLE NO VALIDATE, соответственно. Аналогично, для включения ограничения применяются конструкции ENABLE VALIDATE и ENABLE NO VALIDATE. Ниже кратко обсуждаются различные способы включения и отключения ограничений.

 

Состояние Disable Validate

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

SQL> ALTER TABLE sales_data
ADD CONSTRAINT quantity_unique
UNIQUE (prod_id,customer_id) DISABLE VALIDATE;

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

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

 

Состояние Disable No Validate

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

Состояние Enable Validate

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

SQL> ALTER TABLE sales_data ADD CONSTRAINT sales_region_fk
FOREIGN KEY (sales_region) REFERENCES region(region_id)
ENABLE VALIDATE;

Состояние Enable No Validate

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

SQL> ALTER TABLE sales ADD CONSTRAINT sales_region_fk
FOREIGN KEY (sales_region_id) REFERENCES time(time_id)
ENABLE NOVALIDATE;

Более подходящая стратегия заключалась бы в использовании состояния DISABLE NOVALIDATE при загрузке данных, с переходом к состоянию ENABLE NOVALIDATE и затем к ENABLE VALIDATE в конце цикла извлечения, трансформации и загрузки (extraction,transformation, loading — ETL).

 

Ограничения Rely

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

SQL> ALTER TABLE sales ADD CONSTRAINT sales_region_fk
FOREIGN KEY (sales_region_id) REFERENCES time(region_id)
RELY DISABLE NOVALIDATE;

Отложенные и немедленные ограничения

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

Если хотите, чтобы ограничение проверялось немедленно после модификации данных, выберите опцию not defferable (не откладывая), которая, фактически задает поведение по умолчанию в базах данных Oracle. Если необходимо выполнить едино-временную проверку ограничения после фиксации всей транзакции, выберите опцию deferrable. Все ограничения и внешние ключи могут быть объявлены как deferrable или not deferrable.

В случае выбора опции deferrable появляются еще две дополнительных опции.Ограничение deferrable можно специфицировать либо как initially deferred, либо как initially immediate (изначально отложенное или изначально немедленное). В первом случае база данных откладывает проверки ограничения до окончания транзакции. Если же была выбрана опция initially immediate, то база данных проверит ограничения перед изменением любых данных. Обратите внимание, что если предварительно создается индекс, то он должен быть не уникальным, чтобы обработать дублированные значения.

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

SQL> CREATE TABLE employee
employee_id NUMBER,
last_name VARCHAR2(30),
first_name VARCHAR2(30),
dept VARCHAR2(30) UNIQUE
REFERENCES department(dept_name)
DEFERRABLE INITIALLY DEFERRED;

В Oracle также предусмотрен способ изменения отложенного ограничения с immediate на deferred или наоборот с помощью следующих операторов:

SQL> SET CONSTRAINT constraint_name DEFERRED;
SQL> SET CONSTRAINT constraint_name IMMEDIATE; 

Представления, относящиеся к ограничениям и индексам

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

DBA_CONSTRAINTS

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

SQL> SELECT DISTINCT constraint_type FROM DBA_CONSTRAINTS;
Constraint_type
--------------------
C                      /* check constraints */
                       /* ограничения проверки */
P                      /* primary key constraint */
                       /* ограничение первичного ключа */
R                      /* referential integrity (foreign key) constraint */
                       /* ограничение ссылочной целостности (внешний ключ) */
U                      /* unique key constraint */
                       /* ограничение уникального ключа */
SQL>

Следующий запрос позволит узнать, какие ограничения установлены для таблицы TESTD. Ответ на запрос показывает, что таблица имеет единственное ограничение CHECK. Префикс SYS в столбце NAME отражает тот факт, что CONSTRAINT_NAME — имя по умолчанию, а не явно указанное владельцем таблицы. 

SQL> SELECT constraint_name, constraint_type
2 FROM DBA_CONSTRAINTS
3* WHHERE table_name='TESTD';
CONSTRAINT_NAME CONSTRAINT_TYPE
------------------- ---------------
SYS_C005263 C
SQL>

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

SQL> SELECT constraint_name, constraint_type,
R_constraint_name, delete_rule
FROM dba_constraints
WHERE table_name='ORDERS';
CONSTRAINT_NAME         TYPE   R_CONSTRAINT_NAME  DELETE_RULE
----------------------  ------ -----------------  -----------
ORDER_DATE_NN           C
ORDER_CUSTOMER_ID_NN    C
ORDER_MODE_LOV          C
ORDER_TOTAL_MIN         C
ORDER_PK                P
ORDERS_SALES_REP_FK     R      EMP_EMP_ID_PK      SET NULL
ORDERS_CUSTOMER_ID_FK   R      CUSTOMERS_PK       SET NULL
7 rows selected.
SQL>

DBA_CONS_COLUMNS

Представление DBA_CONS_COLUMNS показывает имя столбца и позицию в таблице,где определено ограничение:

SQL> DESC DBA_CONS_COLUMNS
Name
----------------
OWNER
CONSTRAINT_NAME
TABLE_NAME
COLUMN_NAME
POSITION
SQL> 

 

Вас заинтересует / Intresting for you:

Видеокурс по администрированию...
Видеокурс по администрированию... 10468 просмотров Илья Дергунов Mon, 14 May 2018, 05:08:47
Поддерживаемые Oracle типы дан...
Поддерживаемые Oracle типы дан... 5656 просмотров Валерий Павлюков Wed, 24 Oct 2018, 08:00:37
Обновление до Oracle Database ...
Обновление до Oracle Database ... 5495 просмотров Илья Дергунов Tue, 21 Nov 2017, 13:18:05
Создание базы данных Oracle
Создание базы данных Oracle 18724 просмотров Александров Попков Wed, 14 Nov 2018, 12:44:39
Войдите чтобы комментировать

AlexV аватар
AlexV ответил в теме #8498 19 мая 2017 05:25
Не знал, что ограничений в Oracle Database так много. Просто супер Столько гибких настрое можно применять!