Темы Что такое ограничения?

• Включение ограничений

• 11

• Темы

• Что такое ограничения?

• Создание и сопровождение ограничений

Ограничения обеспечивают выполнение правил на уровне таблицы

• Что такое ограничения?

• Ограничения обеспечивают выполнение правил на уровне таблицы
• Ограничения предотвращают удаление таблицы при наличии подчиненных данных в других таблицах
• В Oracle допускаются следующие виды ограничений:
• NOT NULL
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY
• CHECK

• Указания по ограничениям

• Присваивайте имена ограничениям сами; в противном случае сервер Oracle присвоит имя в формате SYS_Cn.
• Создавайте ограничения:
• при создании таблицы;
• после создания таблицы.
• Устанавливайте ограничения на уровне столбца или таблицы.
• Просматривайте ограничения в словаре данных.

• Определение ограничений

• CREATE TABLE [схема.]таблица
• (столбец тип_данных [DEFAULT выражение]
• [ограничение_на_уровне_столбца],
• ...
• [ограничение_на_уровне_таблицы][,...]);

• CREATE TABLE employees(
• employee_id NUMBER(6),
• first_name VARCHAR2(20),
• ...
• job_id VARCHAR2(10) NOT NULL,
• CONSTRAINT emp_emp_id_pk
• PRIMARY KEY (EMPLOYEE_ID));

• Определение ограничений

• Ограничение на уровне столбца
• Ограничение на уровне таблицы

• столбец [CONSTRAINT имя_ограничения] тип_ограничения,

• столбец,...
• [CONSTRAINT имя_ограничения] тип_ограничения
• (столбец, ...),

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

• Предотвращает появление неопределенных значений в столбце

• Ограничение NOT NULL (ни одна строка не может содержать неопределенное значение в этом столбце)

• Отсутствие ограничения NOT NULL (любая строка может содержать неопределенное значение в этом столбце)

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

• …

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

• CREATE TABLE employees(
• employee_id NUMBER(6),
• last_name VARCHAR2(25) NOT NULL,
• salary NUMBER(8,2),
• commission_pct NUMBER(2,2),
• hire_date DATE
• CONSTRAINT emp_hire_date_nn
• NOT NULL,
• ...

• Может быть задано только для столбца

• Системное
• имя

• Пользовательское имя

• Ограничение UNIQUE

• EMPLOYEES

• Ограничение UNIQUE

• INSERT INTO

• Не разрешается (уже существует)

• Разрешается

• …

Может быть задано на уровне столбца или таблицы

• Ограничение UNIQUE

• Может быть задано на уровне столбца или таблицы

• CREATE TABLE employees(
• employee_id NUMBER(6),
• last_name VARCHAR2(25) NOT NULL,
• email VARCHAR2(25),
• salary NUMBER(8,2),
• commission_pct NUMBER(2,2),
• hire_date DATE NOT NULL,
• ...
• CONSTRAINT emp_email_uk UNIQUE(email));

• Ограничение PRIMARY KEY

• DEPARTMENTS

• ГЛАВНЫЙ КЛЮЧ

• INSERT INTO

• Не разрешается
• (неопределенное значение)

• Не разрешается
• (50 уже существует)

• …

• Ограничение PRIMARY KEY

• CREATE TABLE departments(
• department_id NUMBER(4),
• department_name VARCHAR2(30)
• CONSTRAINT dept_name_nn NOT NULL,
• manager_id NUMBER(6),
• location_id NUMBER(4),
• CONSTRAINT dept_id_pk PRIMARY KEY(department_id));

• Может быть задано на уровне таблицы или столбца

• Ограничение FOREIGN KEY

• DEPARTMENTS

• EMPLOYEES

• ВНЕШНИЙ КЛЮЧ

• INSERT INTO

• Не разрешается (9 не существует)

• ГЛАВНЫЙ КЛЮЧ

• …

• …

• Ограничение FOREIGN KEY

• Может быть задано на уровне таблицы или столбца

• CREATE TABLE employees(
• employee_id NUMBER(6),
• last_name VARCHAR2(25) NOT NULL,
• email VARCHAR2(25),
• salary NUMBER(8,2),
• commission_pct NUMBER(2,2),
• hire_date DATE NOT NULL,
• ...
• department_id NUMBER(4),
• CONSTRAINT emp_dept_fk FOREIGN KEY (department_id)
• REFERENCES departments(department_id),
• CONSTRAINT emp_email_uk UNIQUE(email));

FOREIGN KEY

• Ключевые слова ограничения
• FOREIGN KEY

• FOREIGN KEY
• Определяет на уровне таблицы столбец в подчиненной таблице, используемый в качестве внешнего ключа
• REFERENCES
• Определяет родительскую таблицу и столбец в ней
• ON DELETE CASCADE
• Разрешает удаление в родительской таблице с одновременным удалением зависимых строк в подчиненной таблице
• ON DELETE SET NULL
• При удалении строк в родительской таблице преобразует зависимое значение внешнего ключа в неопределенное

Задает условие, которому должна удовлетворять каждая строка

• Ограничение CHECK

• Задает условие, которому должна удовлетворять каждая строка
• Не допускаются:
• ссылки на псевдостолбцы CURRVAL, NEXTVAL, LEVEL и ROWNUM;
• вызовы функций SYSDATE, UID, USER и USERENV;
• запросы, ссылающиеся на другие значения в других строках.

• ..., salary NUMBER(2)
• CONSTRAINT emp_salary_min
• CHECK (salary > 0),...

Синтаксис команды добавления ограничения

• Используйте команду ALTER TABLE для:
• добавления или удаления ограничения (изменить структуру ограничения нельзя).
• включения и выключения ограничений;
• добавления ограничения NOT NULL с помощью предложения MODIFY.

• ALTER TABLE таблица
• ADD [CONSTRAINT ограничение] тип (столбец);

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

• Добавление ограничения

• Добавление ограничения FOREIGN KEY для таблицы EMPLOYEES. Это ограничение означает, что менеджер уже должен существовать как служащий в таблице EMPLOYEES.

• ALTER TABLE employees
• ADD CONSTRAINT emp_manager_fk
• FOREIGN KEY(manager_id)
• REFERENCES employees(employee_id);
• Table altered.

Удаление ограничения для менеджеров из таблицы EMPLOYEES

• Удаление ограничения

• Удаление ограничения для менеджеров из таблицы EMPLOYEES
• Удаление ограничения PRIMARY KEY для таблицы DEPARTMENTS и соответствующего ограничения FOREIGN KEY для столбца EMPLOYEES.DEPARTMENT_ID

• ALTER TABLE employees
• DROP CONSTRAINT emp_manager_fk;
• Table altered.

• ALTER TABLE departments
• DROP PRIMARY KEY CASCADE;
• Table altered.

Для выключения ограничения используется команда ALTER TABLE с предложением DISABLE.

• Выключение ограничения

• Для выключения ограничения используется команда ALTER TABLE с предложением DISABLE.
• Для отмены ограничения, связанного с зависимыми строками, используется опция CASCADE.

• ALTER TABLE employees
• DISABLE CONSTRAINT emp_emp_id_pk CASCADE;
• Table altered.

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

• Включение ограничения

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

• ALTER TABLE employees
• ENABLE CONSTRAINT emp_emp_id_pk;
• Table altered.

Каскадное удаление ограничений целостности

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

Каскадное удаление ограничений целостности

• Пример:

• ALTER TABLE test1
• DROP (pk) CASCADE CONSTRAINTS;
• Table altered.

• ALTER TABLE test1
• DROP (pk, fk, col1) CASCADE CONSTRAINTS;
• Table altered.

Просмотреть все определения и имена ограничений можно путем запроса к таблице USER_CONSTRAINTS

• Просмотр ограничений

• SELECT constraint_name, constraint_type,
• search_condition
• FROM user_constraints
• WHERE table_name = 'EMPLOYEES';

• Просмотреть все определения и имена ограничений можно путем запроса к таблице USER_CONSTRAINTS

• …

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

• SELECT constraint_name, column_name
• FROM user_cons_columns
• WHERE table_name = 'EMPLOYEES';

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

• …

• Итоги

• Типы ограничений:
• NOT NULL
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY
• CHECK
• Просмотреть все ограничения и их имена можно путем запроса к представлению USER_CONSTRAINTS.

• Обзор практического занятия

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