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

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

База данных Oracle7 может содержать многочисленные структуры данных, например, таблица (Table) – основная структура для хранения данных, представление (View) - подмножество данных из одной или нескольких таблиц, последовательность (Sequence) – структура для генерации значения первичного ключа, индекс (Index) – структура специализированных данных для повышения производительности некоторых запросов и т.д. Определять структуры объектов следует на этапе проектирования базы данных. Структуры данных могут быть созданы в любой момент и могут быть изменены в оперативном режиме. SQL содержит ряд команд (группы DDL), которые позволяют создавать, изменять, удалять и переименовывать структуры объектов для хранения данных:

Команда	Описание
CREATE	Создает структуры для хранения данных (таблицы, представления, индексы, последовательности).
ALTER	Изменяет структуры данных или ограничения.
DROP	Удаляет структуры данных.
TRUNCATE	Усекает объекты, удаляя все записи и оставляя при этом саму структуру без изменений.
COMMENT	Добавляет комментарии к объекту.
RENAME	Переименовывает объект.

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

Создание таблиц
Синтаксис

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

где

схема	То же, что имя владельца.
таблица	Имя таблицы.
DEFAULT выраж	Задает значение по умолчанию, используемое при отсутствии значения в команде INSERT.
столбец	Имя столбца.
тип_данных	Тип данных и длина столбца.
ограничение_столбца	Правило целостности как часть определения столбца.
ограничение_таблицы	Правило целостности как часть определения таблицы.

Параметр DEFAULT.

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

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

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

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

NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK

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

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

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

...

column [CONSTRAINT constraint_name] constraint_type,

...

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

column,...

[CONSTRAINT constraint_name] constraint_type (column,...),

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

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

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

Ограничение 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. Ниже приводится последовательность шагов, которые необходимо предпринять при создании таблицы с помощью бланка экземпляра:

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

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

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

Имя столбца	ID	NAME	REGION_ID
Тип ключа	PK		FK
NN/UK	NN, U	NN, U2	U2
Таблица FK			REGION
Столбец FK			ID
Тип данных	NUMBER	CHAR	NUMBER
Длина	7	25	7
Пример данных	10 31 32 33	Finance Sales Sales Sales	1 1 2 3

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

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

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

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

Синтаксис

CREATE TABLE таблица [(столбец, столбец...)]

AS подзапрос;

где

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

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

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

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

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

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