Глава 5. Определение структур данных

5.1. Структуры данных

База данных может содержать различные структуры данных, например:

• таблица (Table) – основная структура для хранения данных;
• представление (View) – подмножество данных из одной или нескольких таблиц;
• последовательность (Sequence) – структура для генерации значения первичного ключа;
• индекс (Index) – структура специализированных данных для повышения производительности некоторых запросов и т.д.

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

Таблица 17. Команды DDL

После выполнения этих команд DDL происходит автоматическая фиксация транзакций.

5.2. Создание таблиц

Синтаксис:

где

Параметр DEFAULT

Задает значение столбца по умолчанию при вставке строк.  Допускаются строковые константы, выражения и такие функции SQL, как SYSDATE и USER. Недопустимыми значениями являются имена других столбцов и псевдостолбцы.  Тип данных значения, используемого по умолчанию,  должен совпадать с типом данных, допустимым для этого столбца.

Ограничения

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

Существуют следующие виды ограничений:

• NOT NULL – обязательность значений для столбца;
• UNIQUE – уникальность значений в столбце;
• PRIMARY KEY – первичный ключ;
• FOREIGN KEY – внешний ключ;
• CHECK – дополнительное ограничение на вид значений столбца.

По умолчанию сервер Oracle задает имя любому ограничению в формате SYS_Cn.  Для того, чтобы в дальнейшем можно было без особых неудобств изменять ограничения, необходимо присвоить им имена. Рекомендуется задавать имена ограничениям в следующем формате: [схема_[таблица_[столбец_]]]тип_ограничения (например, s_emp_id_pk).  Видно, что ограничение с этим именем было создано для таблицы «s_emp» на уровне столбца «id», причем тип ограничения первичный ключ «pk» (сокращение от “Primary Key”).

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

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

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

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

Запрещает наличие неопределенных значений в столбце.  Задается только на уровне столбца.

Пример. В этом примере ограничение NOT NULL задано для столбца PHONE, поскольку имя ограничению не присвоено, Oracle создает имя сам.

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

Задает один или несколько столбцов, значение или комбинация значений в которых не могут повторяться в двух строках таблицы.  Может быть задано как для таблицы, так и для столбца. Ограничение допускает наличие неопределенных значений, если задано на уровне столбца.  Автоматически создает уникальный индекс.

Пример.

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

Ограничение создает первичный ключ таблицы, значения которого должны быть уникальны (UNIQUE) и обязательно определены (NOT NULL).  Каждая таблица может иметь только один первичный ключ. Может быть задано как на уровне таблицы, так и на уровне столбца.  Автоматически создает уникальный индекс.

Пример.

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

Определяет столбец или набор столбцов в качестве внешнего ключа таблицы.  Устанавливает связь с первичным или уникальным ключом в той же самой таблице или между таблицами. Значение внешнего ключа должно совпадать с существующим значением первичного ключа в родительской таблице или быть неопределенным (NULL).  Может быть задано как на уровне таблицы, так и на уровне столбца.

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

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

Для ограничения FOREIGN KEY могут быть использованы ключевые слова:

• FOREIGN KEY – определяет столбец в дочерней таблице как внешний ключ.
• REFERENCES – указывает таблицу и столбец в родительской таблице.
• ON DELETE CASCADE – при удалении строки в родительской таблице разрешает удаление зависимых строк в дочерней таблице.

Пример.

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

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

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

Пример.

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

При создании таблицы многие производители СУБД рекомендуют использовать специальный «Бланк экземпляра таблицы», в который записываются все параметры создаваемой таблицы.  На основе этого бланка затем легко составить правильную команду CREATE TABLE. Ниже приводится рекомендуемая последовательность шагов, которые необходимо предпринять при создании таблицы с помощью бланка экземпляра:

1. Создать командный файл. Включить команду CREATE TABLE имя_таблицы.
2. Отобразить имена столбцов, типы данных и их длину.
3. Ограничение NOT NULL задавать на уровне столбцов во всех случаях, кроме главного ключа (PRIMARY KEY).
4. Задать ограничение PRIMARY KEY.
5. Задать ограничения UNIQUE, CHECK и FOREIGN KEY.
6. Сохранить и выполнить командный файл.

Пример.  На основании бланка экземпляра таблицы s_dept создается таблица с этим же именем.

Таблица 18. Бланк экземпляра таблицы S_DEPT

Соответствующая команда для создания таблицы 

Рисунок 9. Создание таблицы на основе бланка

Пример.  Создание таблицы с именем S_EMP.  Обратите внимание, что в таблице уже есть один первичный ключ, поэтому для столбца userid определяются два ограничения – NOT NULL и UNIQUE.

Создание таблицы посредством подзапроса

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

Синтаксис:

где

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

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

Пример. Создание таблицы с данными о всех сотрудниках отдела номер 41 из таблицы S_EMP:

С помощью команды DESCRIBE среды SQL*Plus можно убедиться в существовании любой таблицы базы данных и проверить определение ее столбцов.

Для закрепления материала рекомендуется выполнить Практическое занятие 7.

5.3. Изменение таблиц и ограничений

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

Добавление столбца

Синтаксис:

где

Новый столбец становится в таблице последним.

Пример. Добавление столбца COMMENTS в таблицу S_REGION.

Изменение столбца

Можно изменить (при соблюдении определенных условий) такие параметры столбца, как тип данных, размер, значение по умолчанию, ограничение NOT NULL.

Синтаксис:

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

Пример. Увеличение максимальной ширины столбца TITLE таблицы S_EMP до 50 символов.

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

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

Синтаксис:

Пример. Добавление ограничения FOREIGN KEY для таблицы S_EMP.

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

Синтаксис:

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

Пример. Удаление ограничения PRIMARY KEY для таблицы S_DEPT и связанного с ним ограничения FOREIGN KEY для столбца S_EMP.DEPT_ID.

Запрет и разрешение ограничений

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

Синтаксис:

Разрешение ранее запрещенного ограничения достигается с помощью предложения ENABLE.  Разрешение ограничений UNIQUE и PRIMARY KEY вызывает автоматическое создание индексов UNIQUE и PRIMARY KEY.

Примеры.

5.4. Удаление таблицы

Синтаксис:

где

Команда удаляет все данные из таблицы, все незафиксированные транзакции фиксируются, все индексы удаляются.  Откат этой команды невозможен.

5.5. Изменение имени объекта

Для изменения имени таблицы, представления, последовательности или синонима используется команда RENAME.

Синтаксис:

где

Пример.  Таблица S_ORD переименовывается в S_ORDER. При выполнении этой команды Вы должны быть владельцем объекта.

5.6. Усечение таблицы

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

Синтаксис:

Пример.  Удаляются все строки из таблицы S_ITEM, структура таблицы сохраняется.

Примечание. Вернуть удаленные строки после выполнения команды TRUNCATE невозможно.  Удалять строки можно и командой DELETE.

5.7. Добавление комментариев к таблице

Синтаксис:

где

Пример.  Добавляется комментарий к таблице S_EMP.

Для отмены комментария используется пустая строка символов.  Увидеть комментарии можно с помощью следующих представлений словаря данных: ALL_COL_COMMENTS, USER_COL_COMMENTS, ALL_TAB_COMMENTS и USER_TAB_COMMENTS.

Для закрепления материала рекомендуется выполнить Практическое занятие 8.