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

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

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

База данных Oracle7 может содержать многочисленные структуры данных. Таблица (Table) Хранит данные Представление Подмножество данных из (View) одной или нескольких таблиц Последователь- Генерирует значения ность (Sequence) первичного ключа Индекс (Index) Повышает производительность некоторых запросов Определять структуры таблиц следует на этапе проектирования базы данных.

А.М. Гудов

2

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

CREATE TABLE [schema.]table (column datatype [DEFAULT expr] [column_constraint], ... [table_constraint]);

Параметр DEFAULT задает значение столбца по умолчанию при вставке строк.

... start_date DATE DEFAULT SYSDATE,...

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

А.М. Гудов

3

Ссылки на таблицы других пользователей

Правила присвоения имен

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

Должны начинаться с буквы. Могут включать от 1 до 30 символов. Могут содержать только символы A-Z, a-z, 0-9, _ (подчеркивание), $ и #. Не могут совпадать с именем другого объекта, принадлежащего этому же пользователю. Не могут совпадать с зарезервированным словом сервера Oracle7.

А.М. Гудов

4

Типы данных

Типы данных VARCHAR2(размер) CHAR(размер) NUMBER NUMBER(p,s) DATE LONG RAW и LONG RAW

Описание Символьные значения переменной длины Символьные значения фиксированной длины Числа с плавающей точкой Числовые значения Значения даты и времени Символьные значения переменной длины размером до 2Гб Эквиваленты VARCHAR2 и LONG для двоичных данных

А.М. Гудов

5

Ограничения

Реализуют правила по обеспечению целостности данных на уровне таблицы. Предотвращают удаление таблицы при наличии взаимозависимостей. В Oracle7 существуют следующие виды ограничений: NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY CHECK

Имена ограничениям присваивайте сами. В противном случае сервер может создать имя в формате SYS_Cn. Создавайте ограничения: В момент создания таблицы. После создания таблицы. Используйте ограничения на уровне столбца или таблицы.

А.М. Гудов

6

Ограничение: синтаксис

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

column [CONSTRAINT constraint_name] constraint_type,

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

А.М. Гудов

7

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

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

CREATE TABLE friend... phone VARCHAR2(15) NOT NULL,... last_name VARCHAR2(25) CONSTRAINT friend_last_name_nn NOT NULL,...

А.М. Гудов

8

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

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

... phone VARCHAR2(10) CONSTRAINT s_emp_phone_uk UNIQUE,...

А.М. Гудов

9

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

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

... id NUMBER(7) CONSTRAINT s_emp_id_pk PRIMARY KEY,...

А.М. Гудов

10

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

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

... dept_id NUMBER(7) CONSTRAINT s_emp_dept_id_fk REFERENCES s_dept(id),...

А.М. Гудов

11

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

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

А.М. Гудов

12

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

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

А.М. Гудов

13

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

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

А.М. Гудов

14

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

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

А.М. Гудов

15

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

SQL> CREATE TABLE s_dept 2 (id NUMBER(7) 3 CONSTRAINT s_dept_id_pk PRIMARY KEY, 4 name VARCHAR2(25) 5 CONSTRAINT s_dept_name_nn NOT NULL, 6 region_id NUMBER(7) 7 CONSTRAINT s_dept_region_id_fk REFERENCES 8 s_region (id), 9 CONSTRAINT s_dept_name_region_id_uk UNIQUE 10 (name, region_id));

А.М. Гудов

16

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

SQL> CREATE TABLE s_emp 2 (id NUMBER(7) 3 CONSTRAINT s_emp_id_pk PRIMARY KEY, 4 last_name VARCHAR2(25) 5 CONSTRAINT s_emp_last_name_nn NOT NULL, 6 first_name VARCHAR2(25), 7 userid VARCHAR2(8) 8 CONSTRAINT s_emp_userid_nn NOT NULL 9 CONSTRAINT s_emp_userid_uk UNIQUE, 10 start_date DATE DEFAULT SYSDATE, 11 comments VARCHAR2(25), 12 manager_id NUMBER(7), 13 title VARCHAR2(25), 14 dept_id NUMBER(7) 15 CONSTRAINT s_emp_dept_id_fk REFERENCES 16 s_dept (id), 17 salary NUMBER(11,2), 18 commission_pct NUMBER(4,2) 19 CONSTRAINT s_emp_commission_pct_ck CHECK 20 (commission_pct IN(10,12.5,15,17.5,20)));

А.М. Гудов

17

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

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

Создание таблицы и вставка строк с помощью команды CREATE TABLE и параметра “AS подзапрос“.

CREATE TABLE table [column(, column...)] AS subquery;

А.М. Гудов

18

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

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

CREATE TABLE emp_41 AS SELECT id, last_name, userid, start_date FROM s_emp WHERE dept_id = 41;

А.М. Гудов

19

Заключение

Таблицы базы данных создаются с помощью команды CREATE TABLE SQL. При создании таблицы указывается следующее: Имя таблицы. Имена столбцов, типы данных и их длина. Ограничения, направленные на сохранение целостности данных.

CREATE TABLE [schema.]table (column datatype [column_constraint], ... [table_constraint]);

А.М. Гудов

20

Заключение

Типы ограничений NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY CHECK Индексы по уникальным ключам (UNIQUE) Создаются автоматически при наличии ограничений PRIMARY KEY и UNIQUE. Команда DESCRIBE SQL*Plus Показывает структуру таблицы.

А.М. Гудов

21

Задания для практического занятия

А.М. Гудов

22