Создание, удаление и изменение структуры таблиц

Добавление и упразднение ограничений целостности

Выполняется с помощью ключевых слов ADD и DROP или же MODIFY (с отчасти различной областью применимости).

Пример употребления слова MODIFY для добавления и для снятия ограничения NOT NULL:

ALTER TABLE projx MODIFY ( pcode NOT NULL );
ALTER TABLE projx MODIFY ( pcode NULL );

Пример употребления слов ADD и DROP с целью добавления и снятия ограничения первичного ключа:

ALTER TABLE projx ADD PRIMARY KEY ( projno );
ALTER TABLE projx DROP PRIMARY KEY;

Другие примеры и пояснения последуют далее в самостоятельном разделе.

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

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

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

Удаление столбца

Возможно с версии Oracle 8.

Примеры:

ALTER TABLE projx DROP ( pcode );
ALTER TABLE projx DROP COLUMN pcode;

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

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

ALTER TABLE projx DROP ( pcode ) CASCADE CONSTRAINTS;

Значения в столбцах бывшего внешнего ключа остаются, но уже не обремененные ссылочной целостностью.

Средства повышения эффективности удаления столбца

Следующие замечания не касаются удаления виртуальных столбцов (с версии 11), но только реальных. Удаление реального столбца влечет физическое переформатирование всех строк таблицы, что может составить технологическую проблему в случае больших объемов данных. Для нее предлагается два решения.

Следующее указание позволит ускорить удаление столбца за счет выполнения контрольной точки автоматически после правки каждых 1000 строк (и тогда сегмент отмены не будет расти чрезмерно):

ALTER TABLE projx DROP ( pcode ) CHECKPOINT 1000;

Если в процессе удаления произошел сбой, процедуру можно продолжить командой

ALTER TABLE projx DROP COLUMNS CONTINUE;

или командой

ALTER TABLE projx DROP COLUMNS CONTINUE CHECKPOINT 1000;

Другой способ — объявить сначала столбец "неиспользуемым", что не приведет к физическому перебору имеющихся записей:

ALTER TABLE projx SET UNUSED COLUMN pcode CASCADE CONSTRAINTS;

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

ALTER TABLE projx DROP UNUSED COLUMNS;

Переименования

Неудачно или несовременно названную таблицу можно переименовать, например:

ALTER TABLE emp RENAME TO employee;

Другой способ:

RENAME employee TO emp;

Команда RENAME по сравнению с более сфокусированной ALTER TABLE … RENAME является более общей, так как позволяет переименовать помимо таблиц объекты некоторых других типов: представление данных (view), генератор последовательности (sequence) и частный синоним. Ее название унаследовано языком SQL от реляционной модели, где имеется одноименная операция.

Пример переименования столбца:

ALTER TABLE projx RENAME COLUMN pcode TO project_code;

При переименовании объекта СУБД пометит свойства зависимых от данного объектов (например, хранимых процедур на PL/SQL, обращающихся к данной таблице) признаком INVALID, что вызовет потребность их перекомпиляции перед очередным обращением к ним. Ссылки же на таблицу из внешних программ находятся вне компетенции БД; изменение имен пройдет для таких программ незаметно, но в то же время они потеряют свою работоспособность. Это обстоятельство существенно уменьшает ценность операции переименования в реальной практике.

Переименовать таблицу можно и непосредственной правкой таблицы OBJ$ словаря-справочника (см. далее). Такой же подход позволяет переименовать и столбцы таблиц путем внесения правки в таблицу COL$. Однако применять его следует только опытным пользователям и с осторожностью. Он осуществим только для пользователей, имеющих доступ к этим таблицам схемы SYS, и к тому же не изменит состояния зависимых от таблицы подпрограмм на значение INVALID.

Использование синонимов для именования таблиц

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

CREATE SYNONYM members FOR emp;
SELECT * FROM emp;
SELECT * FROM members;

Теперь, обнаружив обращение к MEMBERS, СУБД определит по своей справочной информации, что это синоним имени EMP, и обратится фактически к EMP. Возможность прямого обращения по имени EMP в тексте команды SQL при этом не теряется, и на работе старых программ появление у таблицы синонимов никак не скажется. Это, однако, не касается команд DDL ALTER/DROP TABLE, где ссылаться следует только на истинное имя таблицы (в полном соответствии с синтаксисом, так как в этих командах используется именно ключевое слово TABLE).

На практике синонимы заводятся с разными целями:

• присвоить таблице имя, больше подходящее ее содержанию;
• упростить имя таблицы в запросе, например, OBJECTS вместо SYS.OBJ$;
• замаскировать обращение к таблице в другой БД или в другой схеме для придания гибкости кода.

Удаление выполняется командой DROP SYNONYM:

DROP SYNONYM members;

Синоним, заведенный в схеме (например, SCOTT), как и таблица, сам становится объектом схемы, доступным изначально только пользователю — хозяину схемы или же администратору с соответствующим полномочием. Однако Oracle позволяет создавать еще и PUBLIC SYNONYM: "внесхемный", общедоступный синоним. Публичные синонимы активно используются в административной части БД Oracle, но нередко и обычными разработчиками в своих целях. В силу того, что пространства имен публичных и схемных синонимов разные, возможны "спорные" ситуации:

CREATE PUBLIC SYNONYM syn1 FOR dept;
-- публичный синоним
CREATE SYNONYM syn1 FOR emp;
-- собственный синоним схемы
SELECT * FROM syn1;
-- DEPT или EMP ?

По существующему правилу разрешения имен Oracle отдаст в последнем запросе предпочтение схемному ("частному") синониму. Получается, что появление частного синонима в некоторых случаях способно изменять смысл существовавшего в программе до этого запроса, о чем не следует забывать разработчику.

Создание синонимов в Oracle требует привилегий (полномочий) CREATE SYNONYM и CREATE PUBLIC SYNONYM, изначально отсутствующих у пользователя SCOTT. В жизни, чтобы обеспечить пользователя синонимами, не обязательно выдавать ему эти привилегии. Создать пользователю Oracle синоним способен, например, администратор.

Использование синонимов для таблиц — это частный случай. В общем синонимы можно создавать и для некоторых прочих хранимых в БД объектов, например, для процедур или функций.

Справочная информация о таблицах и прочих объектах в БД

Место хранения справочной информации об объектах, составляющих БД ("хранимых в БД"), называется в базах данных словарем-справочником (data dictionary). Русский термин-перевод пришел в эту область ИТ из более старой лексики, где обозначает "справочную книгу, которая содержит собрание слов (словосочетаний, идиом и т. д.), расположенных по определенному принципу, и дает сведения об их значениях, употреблении, происхождении; информацию о понятиях и предметах, ими обозначаемых и др.". В БД этот термин, не утратив самой общей сути, стал значительно более конкретен. Иногда вместо него пользуются термином системный каталог.

В Oracle словарь-справочник реализован набором таблиц в составе самой БД, составляющих "справочную часть" БД. Количество таких таблиц в любой БД Oracle — несколько сотен. Официальный их перечень приведен в документации по Oracle. Вот два примера таблиц словаря-справочника:

• USER_TABLES — перечень всех таблиц схемы пользователя и их одиночных (не множественных) свойств;
• USER_TAB_COLUMNS — перечень столбцов всех таблиц схемы пользователя и одиночных свойств столбцов.

Следующие два запроса к таблицам словаря-справочника предоставляют основные сведения о всех имеющихся в схеме пользователя таблицах и основные сведения о столбцах таблицы PROJ. Три команды COLUMN предназначены для SQL*Plus и задают приемлемый формат выдачи на экран:

COLUMN table_name FORMAT A30
SELECT
  table_name
, status 
FROM
  user_tables
;
COLUMN column_name FORMAT A30
COLUMN data_type   FORMAT A15
SELECT
  column_name
, data_type
, data_length
, data_precision
FROM
  user_tab_columns
WHERE
  table_name = 'PROJ'
;

Возможности словаря-справочника дополняются способностью Oracle хранить в нем "комментарии", краткие пояснения к сведениям о таблицах и столбцах. Для заведения комментария в Oracle SQL имеется особая команда COMMENT:

COMMENT ON TABLE proj IS 'Проекты в фирме';
COMMENT ON COLUMN proj.budget IS 'Утвержденный бюджет проекта';

Наблюдать имеющиеся комментарии можно через таблицы словаря-справочника USER_COL_COMMENTS и USER_TAB_COMMENTS:

SELECT comments 
FROM   user_tab_comments 
WHERE  table_name = 'PROJ'
;
SELECT comments 
FROM   user_col_comments 
WHERE  table_name = 'PROJ' 
 AND   column_name = 'BUDGET'
;