Модификация столбцов таблицы

Функция

Определение оператора изменения структуры и/или атрибутов столбцов базовой таблицы.

Спецификация
             
< переименование столбца >::=
< изменение длины столбца >::=
ALTER [COLUMN] имя столбца SIZE длина
< добавление столбца >::=
< добавление группы столбцов >::=
< удаление одиночного столбца >::=
DROP [COLUMN] имя столбца [CASCADE]
< удаление группы столбцов >::=
DROP (имя столбца[, …]) [CASCADE]
< изменение типа данных столбца >::=
ALTER [COLUMN] имя столбца SET DATA TYPE тип данных
< добавление CHECK-условия столбца >::=
< удаление CHECK-условия столбца >::=
ALTER [COLUMN] имя столбца DROP CHECK
< установка значения по умолчанию >::=
< удаление значения по умолчанию >::=
ALTER [COLUMN] имя столбца DROP DEFAULT
< установка фильтра по умолчанию >::=
ALTER [COLUMN] имя столбца SET DEFAULT FILTER идентификатор
< добавление диапазона автоинкрементных значений >::=
< установка каталога внешних файлов >::=
< установка последовательности значений >::=
< опция последовательности >::=
[SET][{START|RESTART} WITH начальное значение]
[SET][INCREMENT BY шаг]
[SET][MAXVALUE верхняя граница|NO MAXVALUE]
[SET][MINVALUE нижняя граница|NO MINVALUE]
[SET][CYCLE|NO CYCLE]
< отмены/разрешения NULL-значений >::=
ALTER [COLUMN] имя столбца {ENABLE NULL|DISABLE NULL}
< изменение уровня мандатного контроля доступа столбца >::=
SET COLUMN имя столбца LEVEL (RAL, WAL)
< текущее имя столбца >::=
< новое имя столбца >::=
Синтаксические правила переименования столбца таблицы
  1. Переименование предопределенных столбцов таблицы (ROWID, ROWTIME, DBROWTIME) не допускается.

Общие правила переименования столбца таблицы
  1. Переименование столбцов системных таблиц не допускается.

  2. Операция переименования столбца не проверяет возможное использование заменяемого имени столбца в других объектах БД (представлениях, хранимых процедурах), поэтому, если такие объекты существуют, они перестанут работать.

Пример
create or replace table tst (i int, ch char(20));
alter table tst alter i rename to Id;
alter table tst alter column ch rename to "Наименование";
Синтаксические правила изменения длины столбца
  1. Изменение длины столбца допускается только для строковых и байтовых типов данных.

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

  3. Изменение длины столбцов типов данных CHAR, BYTE, NCHAR допускается только в том случае, если им не назначена опция умолчания (DEFAULT).

  4. Не допускается изменение длины столбца, имеющего DEFAULT-значение, кроме столбцов переменной длины (VARCHAR, VARBYTE, NCHAR VARYING). В этом случае необходимо сначала удалить значение по умолчанию, потом изменить длину значений столбца и затем вернуть старое или установить новое DEFAULT-значение.

    create or replace table tst (c char(10) default 'qwerty123');
    
    alter table tst alter c drop default;
    alter table tst alter c size 20;
    alter table tst alter c set default 'qwerty123';
Общие правила изменения длины столбца
  1. Изменение длины значений столбца допускается только для столбца, который не индексирован и не входит в составной индекс. Если столбец индексирован (или не индексирован, но объявлен как PRIMARY KEY, UNIQUE или FOREIGN KEY) или входит в составной индекс, то сначала надо удалить индекс (или удалить свойство PRIMARY KEY, UNIQUE или FOREIGN KEY), затем изменить размер значений столбца и снова проиндексировать столбец (или добавить удаленное свойство).

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

  3. Допускается как явное изменение длины значений столбца, так и косвенное (через присвоение столбцу нового типа данных, см. конструкцию < изменение типа данных столбца >).

    create or replace table tst (i int);
    
    alter table tst alter column i size 8;

    эквивалентная конструкция:

    alter table tst alter column i set data type bigint;
    
Синтаксические правила добавления столбцов
  1. См. < определение столбца > в пункте «Создание таблицы».

  2. Запрещено добавлять столбцы типа BLOB, EXTFILE.

  3. Столбец с атрибутом AUTOROWID добавить в таблицу нельзя.

Общие правила добавления столбцов
  1. Добавляемые столбцы присоединяются в конец таблицы (становятся ее последними столбцами).

  2. Все существующие записи после операции ADD COLUMN всегда получают во всех добавленных столбцах NULL-значения.

  3. Сразу после добавления нового столбца с default можно выполнить запрос

    update имя_таблицы set имя_столбца=default;

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

Пример
create or replace table tst (i int);

alter table tst add column c char(10) default '*****';
alter table tst add (dt boolean, d date not null);
Синтаксические правила изменения типа данных столбца
  1. Допустимые изменения типов данных столбца приведены в таблице 3.

Таблица 3. Допустимые изменения типов данных
Текущий тип данныхДопустимы преобразования
char(n)char(m), где m  > nvarchar(m), где m  >= n
varchar(n)char(m), где m  >= nvarchar(m), где m  > n
nchar(n)nchar(m), где m  > nnvarchar(m), где m  >= n
nvarchar(n)nchar(m), где m  >= nnvarchar(m), где m  > n
byte(n)byte(m), где m  > nvarbyte(m), где m  >= n
varbyte(n)byte(m), где m  >= nvarbyte(m), где m  > n
smallintintbigint1)
intbigint1) 

1)Среди текущих значений столбца не должно быть отрицательных значений.


Общие правила изменения типа данных столбца
  1. Параметр < тип данных > определяет новый < тип данных > столбца, например, вместо текущего < типа данных > int устанавливается < тип данных > bigint.

  2. Операция < изменения типа данных столбца > не проверяет возможное использование заменяемого типа данных столбца в других объектах БД (например, в представлениях, триггерах), поэтому, если такие объекты существуют, то работа с ними может быть некорректной.

  3. При попытке изменения типа данных столбца на точно такой же (совпадают и название типа и длина) выдается ошибка.

Пример
create or replace table tst (ch char(10));
alter table tst alter column ch set data type varchar(20);
alter table tst alter ch set data type varchar(30);
Синтаксические правила добавления CHECK-условия столбца
  1. Синтаксис < логического выражения > приведен в описании команды CREATE TABLE.

  2. Использование столбцов с атрибутом AUTOROWID, псевдостолбцов ROWID, ROWTIME и DBROWTIME и предиката [NOT] CONTAINS (см. документ «СУБД ЛИНТЕР. Полнотекстовый поиск в базе данных», пункт «Предикат полнотекстового поиска») в < логическом выражении > запрещено.

Общие правила добавления CHECK-условия столбца
  1. Добавленное CHECK-условие присоединяется по логическому «И» ко всем ранее заданным условиям CHECK-условиям.

  2. < Логическое выражение > в конструкции ADD CHECK может содержать LIKE-выражение, например:

    alter table tab1 alter column ch add check(ch like 'aaa/_%' escape '/');
Пример
ALTER TABLE "Банк" ALTER COLUMN "Корр. счет" ADD CHECK (length("Корр. счет")=20);
Синтаксические правила установки значения по умолчанию
  1. Параметр < значение > должен быть литералом, тип данных которого соответствует типу данных модифицируемого столбца.

    create or replace table tst (i int, ch char(10));
    insert into tst(i) values (100);
    alter table tst alter column i set default 394006;
    alter table tst alter column ch set default 'РЕЛЭКС';
    insert into tst;
    alter table tst alter column i set default (999);
    insert into tst;
    select * from tst;
    I       CH
    100     NULL
    394006  РЕЛЭКС
    999     РЕЛЭКС
  2. Параметр < значение > не должен быть NULL-значением (хотя синтаксически NULL-значение допустимо, оно не будет восприниматься как значение по умолчанию). NULL-значение является стандартным значением для отсутствующих при операции добавления данных значений.

  3. < Значимое выражение > должно содержать только константные значения и/или ссылки на столбцы, не имеющие значений по умолчанию.

    create or replace table tst (i int, ch char(10));
    alter table tst alter column i set default (25+sqrt(100));
    insert into tst;
    select * from tst;
    I CH
    35  NULL
    
    create or replace table tst (i int, ch char(10));
    alter table tst alter column ch set default (to_char(i)+' руб.');
    insert into tst(i) values(500);
    select ch from tst;
    CH
    500 руб.
Общие правила установки значения по умолчанию
  1. Конструкция устанавливает значение по умолчанию (если оно ранее не было установлено) либо меняет ранее установленное значение по умолчанию на новое значение. Ранее добавленные записи остаются без изменений.

    Установка значения по умолчанию:
    create or replace table tst (i int);
    insert into tst(i) values (100);
    alter table tst alter column i set default (800);
    insert into tst;
    select * from tst;
    I
    100
    800
    
    Изменение ранее установленного значения по умолчанию:
    create or replace table tst (i int);
    insert into tst(i) values (100);
    alter table tst alter column i set default (800);
    insert into tst;
    alter table tst alter column i set default (999);
    insert into tst;
    select * from tst;
    I
    100
    800
    999
  2. Столбцу присваивается стандартное значение (NULL-значение) если при добавлении записи в таблицу для столбца:

    • не задано значение;

    • он не имеет значения по умолчанию;

    • для него не установлено ограничение NOT NULL.

Синтаксические правила установки фильтра по умолчанию
  1. < Идентификатор > задает имя внутреннего фильтра СУБД (конвертера, преобразующего символьные данные разных форматов в стандартный вид, пригодный для выполнения в нем полнотекстового поиска). Имя фильтра должно быть определено в системной таблице $$$FILTER (см. документ «СУБД ЛИНТЕР. Полнотекстовый поиск в базе данных», подраздел «Фильтры»).

    select $$$name from $$$filter;
    $$$NAME
    ASCTEXT2TEXT
    ASCXML2TEXT
    UNITEXT2TEXT
    UNIXML2TEXT
    DOCRTF2TEXT
    NOTEXT2TEXT
    ANSI2TEXT
    KOI8R2TEXT
    RUSTEXT2TEXT
    UTF82TEXT
Общие правила установки фильтра по умолчанию
  1. Указанный фильтр будет использоваться по умолчанию при индексировании данных данного столбца.

Пример
create or replace table full_search (doc char(4000) default filter "DOCRTF2TEXT", utf blob  default filter "UTF82TEXT");

alter table full_search alter column doc set default filter "KOI8R2TEXT";
Синтаксические правила добавления диапазона автоинкрементых значений
  1. Добавлять новые диапазоны можно только для столбцов с атрибутом AUTOINC RANGE.

Общие правила добавления диапазона автоинкрементых значений
  1. Атрибут столбца AUTOINC RANGE задает список диапазонов допустимых значений. После исчерпания диапазонов значений добавление новых записей прекращается.

    create or replace table tst (i int autoinc range
    (1:3, 10:12));
    insert into tst; // 1
    insert into tst; // 2
    insert into tst; // 3
    insert into tst; // 10
    insert into tst; // 11
    insert into tst; // 12
    insert into tst; // переполнение автоинкрементного столбца
    
    select * from tst;
    I
    1
    2
    3
    10
    11
    12
  2. Добавляемые диапазоны границы должны быть расположены за последним существующим диапазоном.

    create or replace table tst (i int autoinc range (10:100, 500:1000, 2000:5000));
    alter table tst alter column i add range (6000:7000);
    create or replace table tst (i int autoinc range (1:2, 3:4));
    alter table tst alter column i add range (5:6,10:15,100:500);
  3. Вновь добавляемый диапазон не проверяется на отсутствие в столбце таблицы значений из этого диапазона.

  4. Явно можно задавать любые значения вне границ диапазонов.

Синтаксические правила установки каталога внешних файлов
  1. Конструкция применима только к столбцам типа данных EXTFILE.

  2. < Символьный литерал > определяет каталог, в котором размещаются внешние файлы для данного столбца.

Общие правила установки каталога внешних файлов
  1. Если при создании столбца с типом данных EXTFILE каталог внешних файлов не указан, то по умолчанию предполагается, что внешние файлы находятся в каталоге БД. При этом функция DEFAULT, предоставляющая информацию о каталогах внешних файлов, в этом случае возвращает NULL-значение. Однако функция FILESIZE, предоставляющая информацию о размере внешних файлов, ищет внешний файл в каталоге БД и, если находит, возвращает его длину.

    create or replace table tst ("Слова" extfile, "Музыка" extfile root 'd:\Program Files\Music\shanson');
    
    insert into tst("Музыка", "Слова") values (ef('Чубчик.mp4'), ef('Чубчик.txt'));
    
    select default ("Музыка"), default ("Слова") from tst;
    d:\Program Files\Music\shanson  NULL
    
    select filesize ("Музыка"), filesize ("Слова") from tst;
    325482353 669
  2. Новый каталог внешних файлов заменяет ранее установленный (старый) каталог. Файлы, хранящиеся в старом каталоге внешних файлов, в новый каталог не перемещаются. Чтобы иметь доступ к ним, необходимо вручную переместить их в новый каталог.

    create or replace table tst ("Слова" extfile, "Музыка" extfile root 'd:\Program Files\Music\shanson');
    
    insert into tst("Музыка", "Слова") values (ef('music.mp4'), ef('readme.txt'));
    
    select default ("Музыка"), default ("Слова") from tst;
      d:\Program Files\Music\shanson  NULL
    alter table tst alter  column "Слова" set root 'd:\Program Files\Text\shanson';
    select default ("Музыка"), default ("Слова") from tst;
    
    d:\Program Files\Music\shanson  d:\Program Files\Text\shanson
Синтаксические правила установки последовательности значений
  1. Конструкция < установка последовательности значений > применима только к < идентификационным столбцам >.

    create or replace table tst (i int generated always as identity (start with 1));
    insert into tst;
    select * from tst; //1
    alter table  tst alter column i set start with 99;
    insert into tst;
    select * from tst; //99
    I
    1
    99
  2. Модификатор SET можно не указывать.

    create or replace table tst (i int GENERATED ALWAYS AS IDENTITY (start with 1));
    
    // все опции последовательности заданы с модификатором SET
    alter table tst alter column i set restart with 10 set increment by 10 set minvalue 10 set maxvalue 100000 set no cycle;
    
    // все опции последовательности заданы без модификатора SET
    alter table tst alter column i restart with 10 increment by 10 minvalue 10 maxvalue 100000 no cycle;
    
    // с модификатором SET  заданы некоторые опции последовательности
    alter table tst alter column i restart with 30 set increment by 10 minvalue 10 set maxvalue 100000 no cycle;
Общие правила установки последовательности значений
  1. При указании новых параметров < идентификационного столбца > ранее введенные значения столбца не меняются.

    create or replace table tst (i int GENERATED ALWAYS AS IDENTITY (start with 1));
    insert into tst;
    insert into tst;
    select * from tst;
    I
    1
    2
    
    alter table tst alter column i set restart with 99 set increment by 100 ;
    
    insert into tst;
    insert into tst;
    select * from tst;
    I
    1
    2
    99
    199
Общие правила отмены/разрешения NULL-значений
  1. < Разрешение NULL-значений > допускается для столбцов с атрибутом NOT NULL.

    create or replace table tst (i int not null);
    insert into tst; // ошибка
    alter table tst alter column i enable null;
    insert into tst; // нормальное завершение
  2. < Запрет NULL-значений > допускается для столбцов с атрибутом NULL и только в том случае, если к моменту запрета NULL-значений в столбце нет NULL-значений.

    create or replace table tst (i int null);
    alter table tst alter column i disable null; //нормальное завершение
    
    create or replace table tst (i int null);
    insert into tst;
    select * from tst;
    I
    NULL
    alter table tst alter column i disable null; // ошибка
Общие правила изменения уровня мандатного контроля доступа столбца
  1. Устанавливаемые уровни доступа должны быть декларированы в БД.

Пример
select * from $$$level;
$$$ID $$$NAME    $$$DESCR
1     НЕСЕКРЕТНО
2     ДСП
3     СЕКРЕТНО

create or replace user "USER1" identified by '12345678' level ("СЕКРЕТНО","СЕКРЕТНО");

grant dba to USER1;
username "USER1"/12345678
create or replace table tst (i int);
alter table tst set column  i level ("СЕКРЕТНО", "СЕКРЕТНО");
insert into tst;
insert into tst(i) values(100);
insert into tst(i##"СЕКРЕТНО"#"СЕКРЕТНО") values(200);
insert into tst(i##3#3) values(300);
select * from tst;
 I
 -
|           |
|        100|
|        200|
|        300|

select security(i, 'R'), security(i, 'W')  from tst;

|          3|          3|
|          3|          3|
|          3|          3|
|          3|          3|
Синтаксические правила удаления столбцов
  1. < Имя столбца > не должно ссылаться на псевдостолбец таблицы (ROWID, ROWTIME и т. п.).

Общие правила удаления столбцов
  1. Для удаления столбцов необходимы привилегии, аналогичные привилегиям для удаления всей таблицы (см. конструкцию DROP TABLE).

  2. При удалении столбца удаляются сам столбец и все содержащиеся в нем данные.

  3. Столбец с указанным < именем столбца > не должен быть:

    • единственным столбцом таблицы;

    • первичным ключом;

    • внешним ключом;

    • индексированным столбцом;

    • элементом составного индекса;

    • имеющим CHECK-условие.

  4. Для удаления столбца с меткой мандатного доступа необходимо иметь соответствующий уровень доступа.

  5. После того как столбец удален, его невозможно восстановить (например, по команде ROLLBACK). В этом случае необходимо создавать столбец заново и загружать в него удаленные данные (если они предварительно были сохранены).

  6. При удалении столбца типа EXTFILE удаляются только ссылки на внешние файлы, сами файлы не удаляются.

  7. Запрещено удаление столбцов:

    • в БД с мандатной защитой (кроме случая, когда таблица не содержит записей);

    • из системной таблицы;

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

    • из таблицы, которая содержит расширенные описания файлов;

    • из таблиц-представлений, таблиц-синонимов или таблиц «в памяти»;

      Примечание

      Если необходимо удалить столбец из таблицы «в памяти» ее необходимо предварительно преобразовать в базовую таблицу.

    • если для столбца (столбцов) добавлен комментарий.

      Примечание

      Для удаления столбца (столбцов) с комментарием необходимо использовать опцию CASCADE.

    • всей таблицы.

  8. Разрешено удаление столбцов при указании модификатора CASCADE:

    • входящих в составной индекс;

    • являющихся первичным или уникальным ключом;

    • являющихся внешним ключом (направленным и «к» и «от» таблицы);

    • входящим в триггер с условием срабатывания «update of < имя столбца >»;

    • имеющим ограничение целостности CHECK;

    • имеющим именованный одностолбцовый индекс.

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

    Примечание

    При обнаружении перечисленных конструкций, относящихся к удаляемым столбцам, и при отсутствии модификатора CASCADE выдается код завершения 1530 «Столбец не может быть удален (возможно, он имеет зависимости)».

  9. Одностолбцовые неименованные и фразовые индексы у удаляемых столбцов удаляются автоматически.

Примеры
create or replace table tst(i int, ch char(10), d float);
alter table tst drop column i;
alter table tst drop d;

create or replace table tst(i int, ch char(10), d float);
alter table tst drop (i, d);
Синтаксические правила удаления значения по умолчанию
  1. Конструкция применима только для столбцов с атрибутом GENERATED BY DEFAULT AS или DEFAULT.

    create or replace table tst (i int default 0);
    ! ОК
    alter table tst alter column i drop default;
    ! Нет значения по умолчанию
    alter table tst alter column i drop default;
    
Общие правила удаления значения по умолчанию
  1. Конструкция отменяет ранее установленное значение по умолчанию.

Пример
alter table tst alter column  i drop default;
Синтаксические правила удаления CHECK-условий столбца
  1. < Имя столбца > должно ссылаться на столбец, у которого есть хотя бы одно CHECK-условие.

Общие правила удаления CHECK-условий столбца
  1. Удалить можно только сразу все CHECK-условия, привязанные к столбцу. Выборочное удаление CHECK-условий не поддерживается.

Пример
alter table "Банк" alter column "Корр. счет" drop check;