Создание таблиц (инструкция CREATE TABLE)

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

После выполнения инструкции CREATE TABLE в БД появляется новая таблица, которой присваивается имя, указанное в инструкции, и владельцем которой становится создавший её пользователь. Имя таблицы должно быть идентификатором, допустимым в SQL, и не должно конфликтовать с именами других таблиц в схеме. Созданная таблица является пустой, данных записываются в нее с помощью инструкции INSERT INTO.

Определения столбцов. Столбцы новой таблицы задаются в инструкции CREATE TABLE. Определения столбцов представляют собой заключенный в скобки список, элементы которого отделены друг от друга запятыми. Порядок следования определений столбцов в списке соответствует расположению столбцов в таблице. При описании столбца указывается следующая информация:

- имя столбца, которое используется для ссылки на столбец в инструкциях SQL. Каждый столбец в таблице должен иметь уникальное имя, но в разных таблицах имена столбцов могут совпадать.

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

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

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

Определение первичного и внешнего ключей. Кроме определений столбцов таблицы, в инструкции CREATE TABLE указывается информация о первичном ключе таблицы и ее связях с другими таблицами базы данных. Эта информация содержится в предложениях PRIMARY KEY и FOREIGN KEY.

В предложении PRIMARY KEY задается столбец, или столбцы, которые образуют первичный ключ таблицы. Как известно, этот столбец (или комбинация столбцов) служит в качестве уникального идентификатора строк таблицы. СУБД автоматически следит за тем, чтобы первичный ключ каждой строки таблицы имел уникальное значение. Кроме того, в столбцах, использующихся в качестве первичного ключа не должны допускаться значения NULL (имеют ограничение NOT NULL).

В предложении FOREIGN KEY задается внешний ключ таблицы и определяется столбец связи с другой таблицей. В нем указываются:

- необязательное имя связи для этого отношения после ключевого слова CONSTRAINT; оно не используется в инструкциях SQL, но может появляться в сообщениях об ошибках и потребуется, если будет необходимо удалить внешний ключ и разорвать связь между таблицами;

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

- необязательное правило удаления для данного отношения, которое определяет действие, предпринимаемое при удалении строки-предка;

- необязательное правило обновления для данного отношения, которое определяет действие, предпринимаемое при обновлении первичного ключа в строке-предке;

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

В стандартном SQL правил удаления, как и правил обновления всего четыре: RESTRICT, CASCADE, SET NULL и SET DEFAUL. Однако правила SET NULL и SET DEFAULT некоторыми СУБД вообще не поддерживаются, а правило RESTRICT не нуждается в явном указании, так как в большинстве случаев принимается по умолчанию. На синтаксической диаграмме для наглядности задания инструкции CREATE TABLE включено правило каскадного обновления (ON UPDATE CASCADE).

Непосредственно в Oracle существует 3 правила:

- CASCADE удаляет или обновляет родительскую запись и вместе с ней все дочерние.

- SET NULL устанавливает пустые значения для дочерних строк в случае удаления(но не обновления) родительской.

- NO ACTION (установлено по умолчанию) запрещает выполнение операций удаления или обновления родительской записи, если у нее есть дочерние.

Когда СУБД выполняет инструкцию CREATE TABLE, она сравнивает определение каждого внешнего ключа с определениями связанных таблиц. СУБД проверяет, соответствуют ли друг другу внешний ключ и первичный ключ в связанных таблицах, как по числу столбцов, так и по типу данных. Для того чтобы такая проверка была возможна, связанная таблица уже должна быть определена.

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

Условия уникальности и ограничения на значения столбцов. В соответствии со стандартом условие уникальности значений в каком-либо столбце, не выбранном в качестве первичного ключа, задается в предложении UNIQUE инструкции CREATE TABLE. Это дополнительное ограничение, которое позволяет СУБД автоматически следить за уникальностью данных в указанном столбце при заполнении таблицы.

Ограничение CHECK также задает дополнительные ограничения в инструкции CREATE TABLE. Оно содержит условие на значения (идентично условию отбора WHERE в запросе на выборку), проверяемое всякий раз при попытке модификации содержимого таблицы (с помощью инструкций INSERT, UPDATE или DELETE). Если после модификации условие остается истинным, такое изменение допускается; в противном случае СУБД отвергает изменения и выдает сообщение об ошибке.

Пример Создать таблицу orders в соответствии со следующей схемой данных

create table orders (

order_n integer not null,

ord_date date not null,

cust_n integer,

sale_n integer,

prod_n varchar2(10),

qty integer,

amount integer,

primary key (order_n),

constraint placed_by foreignkey (cust_n) references customers,

constraint taken_by foreignkey (sale_n)references salesreps,

constraint is_for foreign key (prod_n) references products);

Пример 2

create table salesreps (

sale_n integernotnull,

name varchar2(15),

position varchar2(10),

sex char(1),

salary float(2),

age integer,

unique (name),

primary key (sale_n),

check (age<=35),

check (sex in (‘m’, ‘f’)),

check (salary<=500.00));

Если первичный или внешний ключ включает в себя только один столбец, либо если условие уникальности или условие на значение касаются одного столбца, стандарт разрешает использовать упрощенную форму записи инструкции create table

create table offices (

office_n integer not null primary key,

city varchar2(15) not null unique,

region_n varchar2(10) not null references regions);