10. Создание реляционных таблиц (с использованием фраз для поддержания целостности данных).

Примечание:

Синтаксис expr и condition (которые длее присутствуют во фразах создания и ограничения целостности) практически соответствует синтаксису элемент_SELECT и WHERE_условие соответственно (см. файл "Предложение SELECT"), кроме того condition могут быть объединены: [NOT] condition [[AND|OR][NOT] condition]....

Дополнительная информация:

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

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

• всякому столбцу таблицы присвоено имя, которое должно быть уникальным для этой таблицы;

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

• строки таблицы не упорядочены (их последовательность определяется лишь последовательностью ввода в таблицу);

• в поле на пересечении строки и столбца любой таблицы всегда имеется только одно значение данных и никогда не должно быть множества значений (правда, это "атомарное" значение может быть достаточно объемным, например, таким, как рецепт блюда);

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

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

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

Почему же база данных, составленная из таких таблиц, называется реляционной? А потому, что отношение - relation - просто математический термин для обозначения неупорядоченной совокупности однотипных записей или таблиц определенного специфического вида, описанного выше. Таким образом, можно, например, сказать, что база данных ПАНСИОН состоит из одиннадцати отношений.

Для создания описания таблиц в языке SQL существует оператор CREATE TABLE, сокращенный синтаксис которого имеет вид:

CREATE TABLE [schema.]table

( { column [datatype] [DEFAULT expr] [column_constraint] ... }

[, { column [datatype] [DEFAULT expr] [column_constraint] ... } ]

[ {, table_constraint } ... ] )

[AS subquery] ;

где

schema — необязательный параметр, идентифицирующий схему, в которую надо поместить создаваемую таблицу (по умолчанию - схема пользователя);

table — имя создаваемой таблицы: до 30 символов (буквы, цифры и символы "$", "#" или "_"), начинающееся с буквы или "_"; возможно также использование в имени любых символов (кроме двойных кавычек) или составлять имя из нескольких слов, если такое имя будет заключено в кавычки (например, "Новая таблица");

column — уникальное для данной таблицы имя столбца, формируемое по тем же правилам, что и имя таблицы;

datatype — тип данных значений столбца (NUMBER, DATE, VARCHAR2, CHAR, LONG, RAW, LONG RAW), который можно не указывать, если столбец является внешним ключом или частью такого ключа (тип данных будет определен по соответствующему столбцу в первичном ключе);

DEFAULT expr — необязательный параметр, используемый для задания с помощью выражения expr значение столбца по умолчанию, если оператор INSERT "забудет" при вставке строки занести значение в данный столбец; выражение (expr) не может содержать ссылок на другие столбцы, псевдостолбцов (CURRVAL, NEXTVAL, LEVEL или ROWNUM), дат и не полностью определенных констант;

column_constraint — необязательный параметр, с помощью которого задается одно или несколько (указано с помощью "...") ограничений целостности на значения столбца (синтаксис column_constraint приведен ниже);

table_constraint — необязательный параметр, с помощью которого задаются, разделенные запятыми, ограничения целостности таблицы (синтаксис table_constraint приведен ниже);

AS subquery — необязательный параметр, с помощью которого можно вставить в создаваемую таблицу строки данных в соответствии с подзапросом (subquery); может также использоваться для копирования как описания (без ограничений целостности), так и содержимого таблицы;

точка с запятой (;) — завершающий элемент любого оператор языка SQL.

Пример. Создать описание таблицы Вып_каф:

CREATE TABLE Вып_каф

(Кафедра NUMBER(3),

Факульт CHAR(5),

Специал VARCHAR(6),

Статус CHAR(9),

Курс NUMBER(1),

Вариант CHAR(3),

Начало DATE,

Конец DATE,

Форма VARCHAR(8),

Нач_дипл DATE,

Ном_план NUMBER(4),

Пользов NUMBER(4),

Измен DATE);

Как уже отмечалось выше, ограничения целостности задаются в операторе CREATE TABLE с помощью параметров column_constraint и table_constraint. Они описывают правила, применимые к таблицам во время и (или) после их создания, которые оговаривают допустимые значения столбцов и (или) допустимые сочетания этих значений.

Синтаксис параметра column_constraint имеет вид:

[CONSTRAINT constraint]

{ [NOT] NULL | {UNIQUE | PRIMARY KEY}

| REFERENCES [schema.]table [(column)] [ON DELETE CASCADE]

| CHECK (condition) }

где

CONSTRAINT constraint — необязательный параметр, используемый для задания имени (на месте constraint) ограничения (имя формируется по тем же правилам, что и имена таблиц); если имя не задано, то Oracle присваивает ограничению свое имя, вывод которого на экран при ошибке (например, отсутствии уникальности вводимого значения) резко усложняет процесс идентификации места ошибки;

PRIMARY KEY — первичный ключ (не может быть назначен для столбцов с типом данных LONG, RAW, LONG RAW); в столбце, объявленном первичным ключом, значения должны быть уникальными и не пустыми (NOT NULL); по значениям такого столбца автоматически строится индекс;

UNIQUE — отличается от PRIMARY KEY тем, что в связанном с ним столбце допускаются пустые (NULL) значения; фигурные скобки, в которые заключены UNIQUE и PRIMARY KEY, оговаривают, что столбец может иметь лишь одно из этих ограничений;

[NOT] NULL — указывает, что в этот столбец надо поместить пустое (NULL) или некоторое непустое (NOT NULL) значение;

REFERENCES — неявное объявление внешнего ключа, т.е. ссылка на первичный или уникальный ключ, ранее объявленный в таблице [schema.]table для столбца column (когда имя столбца опущено, автоматически выбирается первичный ключ); если опция ON DELETE CASCADE опущена, то строки с ключевыми значениями в таблице с первичным или уникальным ключом не могут быть удалены, пока не будут удалены все ссылающиеся на них строки из данной таблицы (таблицы с внешним ключом); REFERENCES допускает ввод пустых значений (для их исключения надо одновременно вводить ограничение NOT NULL);

CHECK — используется для контроля данных по условию, текст которого размещается в скобках; CHECK не может содержать: запросов на обращение к другим таблицам или другим строкам данной таблицы; обращений к системным переменным и константам (например, SYSDATE - текущая дата). CHECK допускает ввод пустых (NULL) значений.

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

[CONSTRAINT constraint]

{ {UNIQUE | PRIMARY KEY} (column [,column] ...)

| FOREIGN KEY (column [,column] ...)

REFERENCES [schema.]table [(column [,column] ...)]

[ON DELETE CASCADE]

| CHECK (condition) }

где

CONSTRAINT constraint, UNIQUE и PRIMARY KEY имеют тот же смысл, что и в ограничениях для одного столбца, однако здесь для UNIQUE или PRIMARY KEY должен задаваться в скобках перечень столбцов, входящих в комбинацию;

FOREIGN KEY — внешний ключ, состоящий из одного или нескольких столбцов (column [,column] ...), ссылающийся (REFERENCES) на первичный или уникальный ключ, ранее объявленный в таблице [schema.]table для столбцов (column [,column] ...); отсутствие последнего перечня и (или) опции ON DELETE CASCADE приводят к тем же результатам, что и для описанных выше столбцовых ограничениях; для удовлетворения этого ограничения значения всех столбцов внешнего ключа должны совпадать со значениями соответствующих столбцов первичного (уникального) ключа или иметь пустое (NULL) значение хотя бы в одном из столбцов;

CHECK — используется для контроля данных по условию, текст которого размещается в скобках.

Пример. Создать описание таблицы Вып_каф с ограничениями:

CREATE TABLE Вып_каф

(Кафедра NUMBER(3) NOT NULL CONSTRAINT "Такой кафедры нет !"

REFERENCES Отделы(Отдел),

Факульт CHAR(5) NOT NULL CONSTRAINT "Код факультета в Вып_каф?"

REFERENCES Факультеты (Факульт),

Специал VARCHAR(6) NOT NULL,

Статус CHAR(9) NOT NULL,

Курс NUMBER(1) NOT NULL CONSTRAINT "Курс должен быть 1-6 !"

CHECK (Курс IN (1,2,3,4,5,6)),

Вариант CHAR(3) NOT NULL CONSTRAINT "Должен быть Осн,Ин1,... !"

CHECK (Вариант IN ('Осн','Ин1','Ин2','Ин3','Ин4',

'Ин5','Ин6','Ин7','Ин8','Ин9')),

Начало DATE NOT NULL,

Конец DATE NOT NULL,

Форма VARCHAR(8) NOT NULL CONSTRAINT "Вечерняя, дневная, заочная !"

CHECK (Форма IN ('вечерняя', 'дневная', 'заочная')),

Нач_дипл DATE,

Ном_план NUMBER(4) CONSTRAINT "Номер плана введен неверно!" PRIMARY KEY,

Пользов NUMBER(4),

Измен DATE,

CONSTRAINT "Не между Начало и Конец !"

CHECK (Нач_дипл BETWEEN Начало AND Конец),

CONSTRAINT "Начало Вып_каф > конца !" CHECK (Начало <= Конец),

CONSTRAINT "Этих Специал-Статус нет!" FOREIGN KEY (Специал, Статус)

REFERENCES Специал(Специал, Статус),

CONSTRAINT "Уникальность строк Вып_каф ?"

UNIQUE (Кафедра, Специал, Статус, Вариант, Начало, Конец, Курс, Форма));

Следует отметить, что в данном примере используются имена ограничений, написанные на русском языке (по правилам создания имен они заключены в кавычки и ограничены 30 символами). Это сделано для облегчения идентификации ошибок, сообщения о которых (вместе с таким именем) выводятся на экран при некорректном вводе или изменении данных таблицы Вып_каф.

Продолжение…

Примеры создания таблиц базы автошколы:

CREATE TABLE ЛЮДИ

(Ид_человек NUMBER(9) CONSTRAINT "Повтор кода человека" PRIMARY KEY,

Фамилия VARCHAR2(30) NOT NULL,

Имя VARCHAR2(30) NOT NULL,

Отчество VARCHAR2(30),

Пол CHAR(1) NOT NULL CONSTRAINT "М, Ж"

CHECK (Пол IN ('м', 'ж')),

Место_рождения VARCHAR2(120) NOT NULL,

Дата_рождения DATE NOT NULL,

Серия_паспорта NUMBER(4) NOT NULL,

Номер_паспорта NUMBER(6) NOT NULL,

Кем_выдан VARCHAR2(120) NOT NULL,

Когда_выдан DATE NOT NULL,

Место_прописки VARCHAR2(120) NOT NULL);

CREATE TABLE ДОЛЖНОСТИ

(Ид_должность NUMBER(3) CONSTRAINT "Повтор кода должности" PRIMARY KEY,

Наименование_должности

VARCHAR2(320) NOT NULL UNIQUE,

Заработная_плата NUMBER(6) NOT NULL CONSTRAINT "Неверная з/п"

CHECK(Заработная_плата > 0));

CREATE TABLE КАТЕГОРИИ

(Категория VARCHAR2(2) NOT NULL CONSTRAINT " Категория A или B или АВ"

CHECK (Категория IN ('A', 'B', 'AB', 'C', 'D', 'E')) PRIMARY KEY,

Описание VARCHAR2(320),

Стоимость_обучения NUMBER(6) NOT NULL CONSTRAINT "Неверная стоимость"

CHECK(Стоимость_обучения > 0));