Язык SQL

8.3 Язык определения данных. Создание, удаление и изменение таблиц.

Для каждого типа хранимых объектов базы (таблица, последовательность, представление, пользователь) существует "малый джентльменский" набор инструкций CREATE, ALTER, DROP (СОЗДАТЬ, ИЗМЕНИТЬ, УДАЛИТЬ), например:

1. CREATE TABLE — создать таблицу
2. ALTER TABLE — изменить таблицу
3. DROP TABLE — удалить таблицу
4. или
5. CREATE VIEW— создать представление
6. DROP VIEW —удалить представление
7. ALTER VIEW —изменить представление

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

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

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

CREATE    TABLE имя_таблицы (столбец
[,{столбец|именованное_ограничение_целостности}] ... )

где

столбец::=имя_столбца тип
[неименованное_ограничение_целостности] [значение_по_умолчанию] 
неименованное_ограничение_целостности::=NULL   |   NOT NULL   | UNIQUE |   PRIMARY KEY|  CHECK (условие) 
именованное_ограничение_целостности::=CONSTRAINT имя_ограничения {PRIMARY KEY  |   UNIQUE} (список_столбцов) |   FOREIGN KEY (список_столбцов)
REFERENCES имя_табл (список_столбцов) | CHECK (условие)
значение_по_умолчанию::=DEFAULT выражение

Знак "::=", то есть "два двоеточия и равно" означает "есть по определению"

Замечание. Неименованные ограничения целостности называются ограничениями уровня строки. Они не имеют имени заданного пользователем, но СУБД называет их своими именами.

Замечание. Именованные ограничения целостности называются ещё ограничениями уровня таблицы.

Давайте просмотрим инструкцию создания таблицы как можно подробнее. Первые два слова — создать таблицу (CREATE TABLE), за ними записывается имя таблицы. Почему между словами "имя" и "таблицы" стоит подчеркивание? Этим я хочу сказать, что два слова образуют один литерал, единое имя. Если бы я поставил пробел, вы могли бы воспринимать запись "имя таблицы" так: один литерал — "имя", а второй, почему-то "таблицы", ну, может много этих таблиц. Дальше стоят круглые скобки, которые закрываются только в самом конце выражения. В скобках вот такой перечень: обязательный столбец, затем столбец либо именованное ограничение целостности, причём фигурные скобки означают, что обязательно выбирают одно из них. Запятая после предыдущего элемента ставится, если есть следующий элемент. Эта конструкция (столбец или ограничение) может повторяться сколько угодно раз, что условно показано знаком многоточия, после закрывающей прямой скобки, означающим повтор от нуля раз, до любого числа. В итоге, мы указали, что создаётся таблица, содержащая минимум один столбец и, может быть, именованные ограничения целостности, разделяемые запятыми.

Итак, все понятно, вначале обязательно запишем CREATE TABLE, даль- ше обязательно имя таблицы и некоторый перечень столбцов, либо име- нованных ограничений целостности. Термины "столбец" и "именованное ограничение целостности", расшифровываются следующими двумя текстами. Запись

столбец::=имя_столбца тип [неименованное_ограничение_целостности] [значение_по_умолчанию]

означает, что "столбец" определён как разделённая пробелами последовательность "имя_столбца", "тип" и, может быть, "неименованное_ограниче-ние_цело стно сти".

Термин "неименованное ограничение целостности" тоже нуждается в расшифровке:

неименованное_ограничение_целостности NULL   |   NOT NULL   | UNIQUE |   PRIMARY KEY|   CHECK (условие)

Неименованное ограничение целостности есть по определению либо

слово NULL, либо NOT NULL, либо UNIQUE, либо PRIMARY KEY, либо вот такая конструкция — CHECK, у которой в скобках записано проверяемое условие. Используя его, мы можем задать проверку своих ограничивающих условий. Например, мы вводим столбец "зарплата" и приписываем столбцу ограничение CHECK, в котором указано, что зарплата должна быть меньше меньше какого-то значения. Например, CHECK(зарплата<20000).

Обратите внимание, что CHECK работает только с данными текущей строки.

Остаётся разобрать термин "именованное ограничение целостности":

именованное_ограничение_целостности CONSTRAINT имя_ограничения {PRIMARY KEY  |   UNIQUE} 
(список_столбцов) |   FOREIGN KEY (список_столбцов)
REFERENCES имя_табл (список_столбцов) | CHECK (условие)

Необходимо записать слово CONSTRAINT, за ним следует имя ограничения, а дальше возможны три варианта. В первом записывается либо PRIMARY KEY, либо UNIQUE. За ними следует список столбцов, на которых создаются ограничения.

Во втором варианте, отделённом вертикальной чертой, стоит словосочетание FOREIGN KEY —внешний ключ — и список столбцов, которые этот ключ образуют. За ним обязательно записано слово REFERENCES, указывающее, на что мы ссылаемся. А ссылаемся мы обязательно на имя таблицы через столбцы в обозначенном списке, состоящем не менее чем из одного столбца.

И, наконец, последний вариант — уже известное нам ограничение целостности CHECK с условием.

Обратите внимание, что именованные ограничения целостности записываются после перечисления столбцов.

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

Примеры инструкций CREATE TABLE:

1. Создать таблицу с именем qq и двумя столбцами c1 типа NUMBER(3) и c2 типа CHAR(5), сделав столбец c1 первичным ключом.

   CREATE TABLE qq (c1 NUMBER(3) PRIMARY KEY, c2 CHAR(5))
   

   Заметим, что введённое ограничение целостности не имеет пользовательского имени, но обычно СУБД назначает ему своё имя и, как правило, создаёт индекс на первичный ключ. Если вам так удобно, читайте инструкцию CREATE TABLE как фразу на русском языке "создать таблицу с именем qq, со столбцом c1, ..."

2. Та же таблица с именованным ограничением целостности "первичный ключ"

   CREATE TABLE qq  (c1 NUMBER(3),   c2 CHAR(5),
   CONSTRAINT pk_c1 PRIMARY KEY(c1))
   

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

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

• NOT NULL|NULL — ограничитель NOT NULL запрещает вводить и хранить пустые значения;
• UNIQUE —определяет уникальный ключ; формат для ограничения уровня таблицы:

  [CONSTRAINT имя_ограничения]
  UNIQUE (столбец1,  столбец2, ...)
  

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

  [CONSTRAINT имя_ограничения]
  PRIMARY KEY (столбец1,  столбец2, ...)
  

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

  [CONSTRAINT имя_ограничения]
  FOREIGN KEY (столбец1, столбец2, ...) REFERENCES таблица (столбец1, [столбец2], ...)
  

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

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

Оказывается, что для всех хранимых объектов, кроме таблиц, в инструкции CREATE существует версия CREATE OR REPLACE, то есть создать или заменить. Почему? Если бы работала опция CREATE OR REPLACE TABLE, то таблицу, в которой собирались данные, например, за 10 лет, легко было бы заменить на пустую таблицу с таким же именем и данные были бы утеряны. Конечно, отсутствие опции замены —не гарантия от возможных ошибок, но мы их хотя бы не провоцируем.

А вот теперь вопрос: зачем существуют очень похожие неименованные ограничения целостности и именованные ограничения, тем более, что неименованным ограничениям не даёт имён пользователь, но СУБД их сама назовёт своими именами, правда не очень удобными для человека? Кому это нужно, кроме, конечно, преподавателя, который хочет запутать студентов? Представим себе, что в пустую базу необходимо закачать данные. Обычно эти данные выбираются из существующей базы, в которой когда-то эти ограничения целостности проверялись, но, может быть, не все. При записи данных срабатывают ограничения целостности, и проверка уже приведённых данных может занять много времени. Более того, при некотором порядке записи в связанные таблицы, ограничения не позволят продолжать передачу данных. Например, если не заполнена таблица "отделы", то ограничение FOREIGN KEY не позволит заполнять таблицу "сотрудники", связанную с таблицей "отделы" внешним ключом. Проще временно отключить все или некоторые ограничения целостности, а по окончании заполнения их восстановить. Выборочное отключение ограничений проще сделать по мнемоническим именам. Так что причина сохранения двух возможностей для записи ограничений чисто технологическая, и связана с учётом особенностей восприятия человека-пользователя.

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

CREATE GLOBAL TEMPORARY TABLE ...

В Cache такая таблица доступна всем процессам. Она будет уничтожена только, когда все процессы прекратятся. Можно ещё указать, что таблица доступна только создающему процессу, набрав PRIVATE вместо GLOBAL.

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

FOREIGN KEY (список_столбцов)
REFERENCES имя_табл (список_столбцов) ON   [DELETE   | UPDATE]
[NO ACTION   |   SET DEFAULT   |   SET NULL   | CASCADE]

Поскольку определение внешнего ключа в стандартах SQL достаточно сложно, мы будем рассматривать упрощённую версию, достаточную для работы со многими СУБД.

Ограничение целостности "внешний ключ" допускает, чтобы один или несколько столбцов внешнего ключа содержали NULL. Выполняется оно, если в таблице S, связанной с определяемой таблицей T с помощью внешнего ключа таблицы T, имеется в точности одна строка s такая, что значение её возможного ключа совпадает со значением внешнего ключа в некоторой строке t. Говорят, что строка t ссылается на строку s (английское название referencing table). Заметьте, что проверяются только имеющиеся значения (не NULL).

По умолчанию в SQL предполагается именно этот вариант внешнего ключа, хотя он не вполне соответствует реляционной модели.

При удалении (ON DELETE) возможны следующие варианты ссылочных действий:

• NO ACTION — удаление отвергается, если оно может вызвать нарушение ограничения "внешний ключ". В Cache NO ACTION это значение по умолчанию.
• SET DEFAULT — строка s удаляется, и во всех столбцах внешнего ключа строк t, ссылающихся на удалённую строку, проставляется заданное значение по умолчанию.
• SET NULL — строка s удаляется, и во всех столбцах внешнего ключа строк t, ссылающихся на удалённую строку, проставляется
• NULL.
• CASCADE — если строка s удаляется, то удаляются все строки t ссылающиеся на s.

При обновлении (ON UPDATE) возможны следующие варианты ссылочных действий:

1. NO ACTION — обновление отвергается, если оно может вызвать нарушение ограничения "внешний ключ".
2. SET DEFAULT — строка s обновляется и во всех столбцах внешнего ключа строк t, ссылающихся на изменяемую строку, проставляется заданное значение по умолчанию.
3. SET NULL — строка s обновляется и во всех столбцах внешнего ключа строк t, ссылающихся на обновлённую строку, проставляется
4. NULL.
5. CASCADE — если строка s обновляется, то обновляются все строки t, ссылающиеся на s.

8.3.2 Удаление и изменение таблиц

Синтаксис инструкции удаления таблицы:

DROP TABLE имя_таблицы

В таблице можно изменять столбцы и ограничения:

ALTER TABLE имя_таблицы
изменение_столбца  | изменение_ограничения

Несколько упрощенные спецификации столбцов и ограничений:

изменение_столбца ::=
ADD (спецификации_столбцов)
| MODIFY (спецификации_столбцов)
{SET определение_умолчания | DROP DEFAULT}
|DROP спецификация_столбца {RESTRICT | CASCADE}

изменение_ограничения ::=
ADD CONSTRAINT
именованное_ограничение_целостности
|DROP CONSTRAINT имя_ограничения

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

Столбец может быть изменён или удалён. Удаление единственного столбца таблицы невозможно. Опция CASCADE заставляет удалять все ограничения целостности, как-то связанные с удаляемым столбцом. При использовании RESTRICT будут удалены только ограничения, в которых используется только удаляемый столбец.

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

Удалять можно и именованные, и неименованные ограничения.

Для последних необходимо найти имя, присвоенное им системой.

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