Создание, модификация и удаление таблиц и представлений

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

CREATE TABLE

Как мы уже знаем из прошлой лекции, создание таблиц осуществляется запросом CREATE TABLE, который можно выполнить с помощью утилиты IBConsole. Полный синтаксис запроса такой:

CREATE TABLE Имя_таблицы [EXTERNAL [FILE] Имя_файла] 
   (<описание_столбца_1> [, …, <описание_столбца_n>] | 
    <ограничение_таблицы> …);

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

EXTERNAL [FILE] Имя_файла - необязательное определение того, что создается внешняя по отношению к базе данных таблица. "Имя_файла" при этом указывает адрес и имя файла создаваемой таблицы. Файла с таким именем не должно существовать на момент создания таблицы. В результате применения этого оператора будет создана внешняя по отношению к базе данных таблица, в текстовом формате ASCII. Обычно поля в таких файлах разделяются символом табуляции, а в конце записи ставится символ перевода строки. При этом созданная во внешнем файле таблица будет доступна в списке таблиц базы данных в утилите IBConsole. В основном, такие таблицы могут использоваться для обмена данными между разными БД, для сбора и обработки статистических данных, для сложной сортировки и т.п. Пример создания таблицы во внешнем файле (не забывайте, что служба InterBase должна быть запущена, утилита IBConsole загружена, и база данных First открыта):

CREATE TABLE VneshTable EXTERNAL FILE 'C:\DataBases\VneshFile.tbl'(
  ID INTEGER,
  NAME VARCHAR(30))

<описание_столбца> - Описание столбца таблицы, которое может иметь как простой, так и достаточно сложный формат. Описание столбца имеет свой синтаксис:

<описание_столбца> = имя_столбца {<тип_данных> | COMPUTED [BY]  <выражение> | <домен>}
   [DEFAULT {<литерал> | NULL | USER}]
   [NOT NULL] [<ограничение_столбца>]
   [COLLATE collation]

Давайте по порядку разберемся со всеми этими определениями. Как вы, вероятно, знаете, в синтаксисе различных языков программирования в фигурные скобки принято помещать список возможных параметров, из которого нужно выбрать. То есть, мы должны выбрать либо <тип_данных>, либо COMPUTED [BY], либо заранее созданный домен. О типах данных и их описаниях, равно как и о доменах, мы говорили на прошлой лекции. Теперь рассмотрим создание вычисляемого столбца.

Вычисляемые столбцы

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

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

CREATE TABLE Sdelki (
   ID INTEGER,
   TOVAR VARCHAR(20) CHARACTER SET WIN1251 COLLATE PXW_CYRL,
   ED_IZM VARCHAR(7) CHARACTER SET WIN1251,
   STOIMOST DOUBLE PRECISION,
   KOLVO SMALLINT,
   SUMMA COMPUTED BY (STOIMOST * KOLVO))

Значения по умолчанию

Значения столбцов по умолчанию задаются необязательным параметром DEFAULT:

[DEFAULT {<литерал> | NULL | USER}]

Если вы задали этот параметр, то указанное значение будет подставляться в столбец автоматически, если пользователь не введет в него другого значения. Здесь:

<литерал> - заданный по умолчанию символ или текст, целое или вещественное число, дата и (или) время, в зависимости от типа столбца. Применяется соответственно с текстовыми, числовыми полями и полями с датами. Значение даты указывается в кавычках. В примере ниже мы создаем логическое поле, которое должно содержать один из двух символов: Y (истина) или N (ложь). По умолчанию, в поле должен помещаться символ N. Кроме того, мы создаем числовое поле, которое по умолчанию "обнуляем", а также поле дат, которое по умолчанию будет содержать дату 1 Января 2010 года. Вот как можно этого добиться:

CREATE TABLE MyDefault(
   Bool_col CHAR(1) DEFAULT 'N',
   Int_col INTEGER DEFAULT 0,
   Date_col DATE DEFAULT '01.01.2010' )

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

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

CREATE TABLE UsersTable(
   ID INTEGER,
   ZAPIS VARCHAR(50),
   LOG_USERS VARCHAR(10) DEFAULT USER)

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

Параметр NOT NULL

Параметр NOT NULL указывает, что столбец не должен быть пустым. В случае если вы попытаетесь сохранить запись с таким столбцом, не введя в него какого-нибудь значения, произойдет ошибка, и InterBase не позволит сохранить такую запись. Если вы описываете поле, которое будет ключевым, указание такого параметра является обязательным. Пример:

CREATE TABLE Key_col(
  ID INTEGER NOT NULL)

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

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

CHECK (<условие_поиска>);

  где <условие_поиска> =

  <значение> <оператор> {<значение1> | <выбор_одного>}
  | <значение> [NOT] BEETWEN <значение1> AND <значение2>
  | <значение> [NOT] LIKE <маска> [ESCAPE <символ>]
  | <значение> [NOT] IN (<значение1> [, <значение2> ...]) 
  | <список_выбора>
  | <значение> IS [NOT] NULL
  | <значение> {[NOT] {= | < | >} | >= | <=} 
    {ALL | SOME | ANY} {список_выбора}
  | EXISTS (<выражение_выбора>)
  | SINGULAR (<выражение_выбора>)
  | <значение> [NOT] CONSTAINING <значение1>
  | <значение> [NOT] STARTING [WITH] <строка>
  | NOT <условие_поиска>
  | <условие_поиска> OR <условие_поиска>
  | <условие_поиска> AND <условие_поиска>

Напомним, что символом "|" в описании синтаксиса языков программирования принято разделять альтернативные значения. То есть, "|" смело можно заменить на "или". А в квадратные скобки заключаются необязательные параметры.

Диапазон возможностей оператора CHECK весьма широк - теоретически в нем можно указать почти любое условие поиска. В ограничениях можно сравнивать вводимое значение с другим указанным значением, со значением другого столбца или даже другого столбца другой таблицы. Кроме того, в качестве сравниваемого значения можно использовать выборку оператором SELECT из другой таблицы. Практически все параметры отвечают стандартам языка SQL и могут встречаться в операторах SELECT. Приведем несколько примеров:

CREATE TABLE Check_table(
   /*Столбец должен содержать только положительные числа или ноль:*/
   Col_1 INT CHECK (Col_1 >= 0),
   
   /*Столбец должен содержать значение в диапазоне от 10 до 50:*/
   Col_2 INT CHECK (Col_2 BETWEEN 10 AND 50),

   /*Столбец должен оканчиваться символами "руб."*/
   Col_3 VARCHAR(20) CHECK (Col_3 LIKE '% руб.'),

   /*Столбец должен содержать либо "муж", либо "жен".*/
   Col_4 VARCHAR(3) CHECK(Col_4 IN ('муж','жен')),

   /*Столбец не может быть пустым.*/
   Col_5 VARCHAR(5) CHECK (Col_5 IS NOT NULL),

   /*Столбец 6 не может иметь такое же значение, как столбец 2*/
   Col_6 INT CHECK (NOT Col_6 = Col_2),

   /* Значение столбца 7 должно совпадать с одним или несколькими из */
   /* значений столбца DLINNOE таблицы Table_cel*/
   Col_7 INT CHECK (EXISTS(SELECT DLINNOE FROM Table_cel
      WHERE Table_cel.DLINNOE = Check_table.Col_7)),

   /* Значение столбца 8 должно совпадать лишь с одним из */
   /* значений столбца DLINNOE таблицы Table_cel*/
   Col_8 INT CHECK (SINGULAR(SELECT DLINNOE FROM Table_cel
      WHERE Table_cel.DLINNOE = Check_table.Col_8)),

   /*Столбец обязательно должен содержать подстроку "мир".*/
   /*Например, "Мы за мир!", */
/*"мировая экономика", "эмират"*/
   Col_9 VARCHAR(30) CHECK ( Col_9 CONTAINING 'мир'),

   /*Столбец обязательно должен начинаться с подстроки "мир".*/
   /*Например, "мировая экономика", "мираж", */
  /*"мир в объективе"*/
   Col_10 VARCHAR(30) CHECK ( Col_10 STARTING WITH 'мир')
)

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

Ограничения CHECK в доменах

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

Пример:

CREATE DOMAIN Poloj_Cel
AS INT CHECK(VALUE >= 0)