Geum.ru

Курс лекций "Базы данных и субд" Ульянов В. С. Лекция Язык sql. Создание таблиц и ограничений

Вид материалаКурс лекций

Содержание


DEFAULT выражение
Primary key
Foreign key
Подобный материал:

Курс лекций “Базы данных и СУБД” Ульянов В.С.



Лекция 7. Язык SQL. Создание таблиц и ограничений.


Объекты схемы пользователя БД

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

Объект
Описание

Таблица
Хранит данные.

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

Последовательность
Генерирует целые числа. Последовательности будут рассмотрены в Лекции 14.

Индекс
Ускоряет производительность некоторых запросов. Индексы будут рассмотрены в Лекции 16.

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

Для создания таблиц используется команда CREATE TABLE языка SQL. Ее синтаксис:

CREATE TABLE [ имя_схемы. ] имя_таблицы

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

где:

имя_схемы
Имя схемы, в которой создается таблица.

имя_таблицы
Имя таблицы.

Столбец
Имя столбца.

тип_данных
Тип данных соответствующего столбца.

DEFAULT выражение
Значение по умолчанию столбца.

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

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



Имя схемы

Пользователи могут работать не только со своими объектами, но и с объектами схем других пользователей. При обращении к объекту схемы другого пользователя имя объекта предваряется префиксом в виде имени схемы. Имя схемы и имя объекта разделяются точкой. Соответственно, пользователи могут создавать объекты в схемах других пользователей. Если требуется создать таблицу в схеме другого пользователя, в команде CREATE TABLE необходимо перед именем создаваемой таблицы указать имя схемы. Если имя схемы опущено, таблица создается в схеме пользователя.

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

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

При задании имен таблиц и столбцов Вы должны помнить о том, что таблица должна иметь уникальное имя в пределах схемы, а столбец – уникальное имя в пределах таблицы. Кроме того, необходимо помнить о следующих правилах именования объектов в Oracle:
  1. Имена должны начинаться с буквы и могут включать от 1 до 30 символов.
  2. Имена не должны совпадать с зарезервированными словами Oracle (select, table и т.п.)
  3. Имена должны содержать только символы A-Z, a-z, 0-9, _(подчеркивание). Использование специальных символов допустимо, но не рекомендуется. При использовании специальных символов в имени во время создания объекта и при дальнейшей работе с ним имя необходимо заключать в кавычки.

Типы данных в Oracle

Каждый литерал и каждое значение столбца, с которыми манипулирует Oracle, имеют тип данных. Тип данных значения ассоциирует с этим значением фиксированный набор свойств. Эти свойства заставляют Oracle трактовать значения одного типа данных иначе, чем значения другого типа данных. Например, вы можете перемножить два значения типа данных NUMBER, но не значения типа данных DATE.

Когда вы создаете таблицу, вы должны указать тип данных для каждого из столбцов этой таблицы. Этот тип данных определяет домен, т.е. область допустимых значений, которые может содержать столбец. Например, столбцы DATE не могут содержать дату 29 февраля (если год не високосный), или значение 2, или значение 'Vasya'. Каждое значение, помещаемое в столбец, принимает тип данных этого столбца. Например, если вы вставляете значение '01-JAN-92' в столбец DATE, то Oracle трактует символьную строку '01-JAN-92' как значение DATE, после того как проверит, что оно транслируется в действительную дату.

Следующая таблица сводит наиболее часто используемые внутренние типы данных Oracle.

Тип данных
Описание

VARCHAR2(размер)
Символьные значения переменной длины, не превышающей заданного размера. Минимальная длина – 1, максимальная – 2000. Размер должен быть обязательно указан.

CHAR(размер)
Символьные значения фиксированной длины размер. Минимальная длина – 1, максимальная – 255. Если размер не указан, по умолчанию устанавливается 1.

В дальнейшем, если Вы вставляете в столбец значение, которое короче, чем длина столбца, то оно дополняется пробелами до длины столбца, если Вы вставляете значение, которое длиннее, чем длина столбца, то Oracle возвращает ошибку.

NUMBER(p, s)
Число, имеющее точность p и масштаб s. Точность – это общее количество десятичных цифр, а масштаб – количество цифр после десятичной точки. Точность p может варьироваться от 1 до 38.

Можно также использовать одну из альтернативных форм:
NUMBER(p) – число с фиксированной точкой с точностью p и масштабом 0.
NUMBER – число с плавающей точкой с точностью 38.

DATE
Значения даты в интервале между 1 января 4712 г. до н.э. и 31 декабря 4712 г.н.э.

LONG
Символьные значения переменной длины размером до 2 гигабайт.

RAW(размер) и LONG RAW
Аналогичны, соответственно, типам данных VARCHAR2 и LONG, но используются для хранения байт-ориентированных данных или двоичных данных, которые не должны интерпретироваться сервером Oracle.



Параметр DEFAULT

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

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

Ограничения

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

Тип ограничения
Описание

NOT NULL
Означает, что данный столбец не может содержать пустых значений.

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

PRIMARY KEY
Определяет столбец (или комбинацию столбцов) как первичный ключ. Данное ограничение направлено на обеспечение целостности сущностей. Чтобы удовлетворять ограничению PRIMARY KEY, никакое значение первичного ключа не может появляться в более чем одной строке таблицы и никакой столбец, входящий в первичный ключ, не может быть пустым. Один и тот же столбец или комбинация столбцов не может быть обозначена одновременно как первичный ключ и уникальный ключ. Однако можно обозначить один и тот же столбец или комбинацию столбцов одновременно как первичный ключ и внешний ключ.

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

CHECK
Ограничение пользователя. Ограничение CHECK явно определяет условие. Чтобы удовлетворять этому ограничению, каждая строка в таблице должна делать это условие либо истинным (TRUE), либо неопределенным (NULL).

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

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

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

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

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

Как на уровне одного столбца, так и на уровне таблицы могут быть задано несколько ограничений. Если несколько ограничений задаются на уровне одного столбца, они разделяются пробелом, если на уровне таблицы – запятой.

Синтаксис задания ограничения на уровне столбца:

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

Синтаксис задания ограничения на уровне таблицы:

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

Все ограничения хранятся в словаре данных Oracle. Вы можете присвоить имя ограничению с помощью ключевого слова CONSTRAINT. При этом имя должно удовлетворять общим правилам именования в Oracle. Если при создании ограничения Вы опустите его имя, оно будет присвоено ограничению автоматически сервером в формате SYS_Cn, где n – целое число, генерируемое сервером и обеспечивающее уникальность имени ограничения.

Пример: Необходимо создать таблицу phones, которая будет содержать некоторые имена, фамилии и телефоны.

Предположим, в данной таблице будет 3 столбца: name – имя, surname – фамилия и phone – номер телефона. Все три столбца будут содержать символьные данные, причем столбец phone не может быть пустым, а сочетание имени и фамилии должно быть уникально в пределах всей таблицы. Такую таблицу можно создать с помощью следующей команды:

CREATE TABLE phones
(name VARCHAR2(15),
surname VARCHAR2(30),
phone VARCHAR2(10) NOT NULL,
CONSTRAINT phones_name_surname_uk UNIQUE(name, surname));

В этой таблице будет одно простое ограничение NOT NULL, имя которому будет присвоено сервером. Кроме того, в таблице будет составное ограничение уникальности с именем phones_name_surname_uk. Это – составное ограничение, то есть в таблице может храниться информация о людях с одинаковыми именами или фамилиями, но сочетание (имя, фамилия) должно быть уникально в пределах таблицы.

Синтаксис ограничений FOREIGN KEY и CHECK отличается от общей схемы.

Синтаксис ограничения FOREIGN KEY на уровне столбца:

описание_столбца [CONSTRAINT имя_ограничения] REFERENCES имя_родительской_таблицы (столбец) [ON DELETE CASCADE]

Синтаксис ограничения FOREIGN KEY на уровне таблицы:

описание_столбцов_таблицы,
[CONSTRAINT имя_ограничения] FOREIGN KEY (столбец, …)
REFERENCES имя_родительской_таблицы (столбец, …) [ON DELETE CASCADE]

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

Ключевая фраза ON DELETE CASCADE задает соответствующую стратегию поддержания ссылочной целостности (cм. Лекцию 3) при удалении строк родительской таблицы. По умолчанию используется стратегия RESTRICT (ограничить), то есть удаление строк родительской таблицы, на которую ссылаются строки дочерней таблицы запрещено. Если указать фразу ON DELETE CASCADE, будет использоваться стратегия CASCADE (каскадировать), то есть при удалении строк родительской таблицы, но которые есть ссылки из дочерней таблицы, соответствующие строки дочерней таблицы также будут удалены.

Синтаксис ограничения CHECK на уровне столбца:

описание_столбца [CONSTRAINT имя_ограничения] CHECK (условие)

Синтаксис ограничения CHECK на уровне таблицы:

описание_столбцов_таблицы,
[CONSTRAINT имя_ограничения] CHECK (условие)

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

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

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

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

Имя столбца
ID
LAST_NAME
FIRST_NAME
USERID
START_DATE
COMMENTS

Тип ключа
PK















Null, уникальность



NN



NN, U






Ограничение пользователя


















Таблица FK


















Столбец FK


















Тип данных
NUMBER
VARCHAR2
VARCHAR2
VARCHAR2
DATE
VARCHAR2

Длина
7
25
25
8



255




Имя столбца
MANAGER_ID
TITLE
DEPT_ID
SALARY
COMMISSION_PCT

Тип ключа
FK



FK






Null, уникальность















Ограничение пользователя












commission_pct IN
(10, 12.5, 15, 17.5, 20)

Таблица FK
s_emp



s_dept






Столбец FK
id



id






Тип данных
NUMBER
VARCHAR2
NUMBER
NUMBER
NUMBER

Длина
7
25
7
11,2
4,2



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

CREATE TABLE s_emp

(id NUMBER(7) CONSTRAINT s_emp_id_pk PRIMARY KEY,

last_name VARCHAR2(25) CONSTRAINT s_emp_last_name_nn NOT NULL,

first_name VARCHAR2(25),

userid VARCHAR2(8) CONSTRAINT s_emp_userid_nn NOT NULL
CONSTRAINT s_emp_userid_uk UNIQUE,

start_date DATE,

comments VARCHAR2(255),

manager_id NUMBER(7),

title VARCHAR2(25),

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

salary NUMBER(11, 2),

commission_pct NUMBER(4, 2),

CONSTRAINT s_emp_commission_pct_ck CHECK
(commission_pct IN (10,12.5,15,17.5,20)),

CONSTRAINT s_emp_manager_id_fk FOREIGN KEY (manager_id)
REFERENCES s_emp (id));

Всем ограничениям этой таблицы были заданы имена. Вообще рекомендуется всегда задавать имена ограничениям, причем выбирать их осмысленными. Это упрощает дальнейшую работу с таблицей. В данном примере:
  • ограничение s_emp_id_pk, заданное на уровне столбца, делает столбец id первичным ключом таблицы s_emp (данное ограничение могло быть задано и на уровне таблицы);
  • ограничение s_emp_last_name_nn, заданное на уровне столбца означает, что для каждого служащего обязательно должна быть указана фамилия;
  • ограничение s_emp_userid_nn, заданное на уровне столбца означает, что для каждого служащего обязательно должно быть указано имя пользователя;
  • ограничение s_emp_userid_uk, заданное на уровне столбца означает, что имя пользователя должно быть уникально;
  • ограничение s_emp_dept_id_fk, заданное на уровне столбца означает, что каждый номер отдела dept_id служащего должен совпадать с номером отдела id из таблицы отделов s_dept либо быть полностью пустым (данное ограничение могло быть задано и на уровне таблицы);
  • ограничение s_emp_commission_pct_ck, заданное на уровне таблицы означает, что ставка комиссионного процента может быть равной 10, 12.5, 15, 17.5 или 20 (данное ограничение могло быть задано и на уровне столбца);
  • ограничение s_emp_manager_id_fk, заданное на уровне таблицы означает, что каждый номер начальника manager_id служащего должен совпадать с номером какого-либо служащего id из таблицы служащих s_emp либо быть полностью пустым (данное ограничение могло быть задано и на уровне столбца).

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

Есть еще один метод создания таблиц. Можно создать таблицу на основе уже существующей таблицы, при этом заполнив ее строками из этой таблицы. Это достигается использованием подзапроса в команде CREATE TABLE. В этом случае синтаксис команды тот же, за исключением добавления предложения “AS подзапрос”:

CREATE TABLE [ имя_схемы. ] имя_таблицы

[(столбец [DEFAULT выражение] [ограничение_столбца …], …
[ограничение_таблицы, …])]
AS подзапрос;

Указание списка столбцов в данной команде не обязательно. Если в команде приводится список столбцов, их значения по умолчанию и ограничения, то новая таблица будет содержать столбцы с указанными параметрами. При этом можно задавать ограничения любого типа, кроме FOREIGN KEY (в дальнейшем оно может быть добавлено). Необходимо учесть, что количество столбцов, указанное в команде и количество столбцов, возвращаемое подзапросом должно совпадать. Описание столбца не может содержать типа данных столбца, так как он наследуется от столбца из подзапроса.

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

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

Пример: Создать таблицу dept_41, которую заполнить данными о служащих отдела №41. Для каждого служащего таблица dept_41 должна содержать следующую информацию: № служащего, его имя, фамилию и заработную плату.

CREATE TABLE dept_41
as SELECT id, first_name, last_name, salary
FROM s_emp
WHERE dept_id=41;

После создания таблицы выполним следующий запрос:

SELECT * FROM dept_41;

Результат:

ID FIRST_NAME LAST_NAME SALARY

---- ---------- ---------- ---------

2 LaDoris Ngao 1450

6 Molly Urguhart 1200

16 Elena Maduro 1400

17 George Smith 940



Л екция 7. Язык SQL. Создание таблиц и ограничений.