Как добавить столбец в таблицу sql oracle

Добавление столбца в oracle

@0xdb, да я сам был таким лет 10-15 назад. 🙂 Позже когда повозился с dbms_redefinition на больших таблицах исключительно по причине приступа нездорового перфектионизма понял что оно того не стоит. 😉

6 окт 2018 в 14:13

3 ответа 3

Сортировка: Сброс на вариант по умолчанию

в Oracle 12c: это пример

CREATE TABLE CUSTOMER_DATA ( CD_ID INTEGER, CUST_NUMBER_ID INTEGER, FIRST_NAME NVARCHAR2(30), LAST_NAME NVARCHAR2(30), FLAG NVARCHAR2(10), CUST_ACTIVE INTEGER ); ALTER TABLE CUSTOMER_DATA ADD (FULL_NAME VARCHAR (50) NULL); ALTER TABLE CUSTOMER_DATA MODIFY (FLAG INVISIBLE, CUST_ACTIVE INVISIBLE); ALTER TABLE CUSTOMER_DATA MODIFY (FLAG VISIBLE, CUST_ACTIVE VISIBLE); 

Отслеживать
ответ дан 19 сен 2018 в 12:18
1,887 9 9 серебряных знаков 23 23 бронзовых знака

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

Если всё-таки в редких случаях порядок столбцов желателен, например, для генераторов кода, то самое простое использовать представление вместо физической таблицы.
Изменение порядка столбцов в представлении не затрагивает структуры физической таблицы (indexes, constraints, triggers и пр.), то есть по-сути пройдёт «безболезнено»:

create table itemstab (id number, name varchar2 (32), changed timestamp); -- возникла необходимость добавить столбец alter table itemstab add (memo varchar2 (64)); create or replace view items as select id, name, memo, changed from itemstab; insert into items values (1, 'item 1', 'memo 1', systimestamp); select rowid, i.* from items i; ROWID ID NAME MEMO CHANGED ------------------ ---------- ---------- ---------- ----------------------------- AAAS+TAAQAAAFrHAAA 1 item 1 memo 1 2018-10-06 12:59:57,373961000 

И только в крайнем случае, можно воспользоваться многочисленными решениями, например, в этой ветке на enSO. Самый, по-моему мнению, правильный путь там отсутствует — выгрузить данные, пересоздать таблицу с «нуля», залить сохранённые данные.

В 12c появилась возможность изменить порядок столбцов изменением видимости уже существующих столбцов. Но основная цель введения невидимых столбцов — упростить миграцию старых, зависимых от порядка столбцов, приложений. Поэтому, этот способ добавить столбец в середину, иначе как «злоупотребленим» назвать нельзя — «от чего пытались уйти, туда и пришли». Подробнее в блоге Тома Кайта.

SQL ALTER TABLE

Команда ALTER TABLE используется для добавления, удаления или модификации колонки в уже существующей таблице.

Команда ALTER TABLE

Команда ALTER TABLE изменяет определение таблицы одним из следующих способов:

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

Условие: Таблица должна быть в схеме пользователя, или пользователь должен иметь системную привилегию ALTER ANY TABLE. Добавляя столбец с ограничением NOT NULL, разработчик или администратор БД должны учесть ряд обстоятельств. Сначала нужно создать столбец без ограничения, а затем ввести значения во все его строки. После того как все значения столбца станут не NULL-значениями, к нему можно применить ограничение NOT NULL. Если столбец с ограничением NOT NULL пытается добавить пользователь, возвращается сообщение об ошибке, говорящее о том, что либо таблица должна быть пустой, либо в столбце должны содержаться значения для каждой существующей строки (напомним, что после наложения на столбец ограничения NOT NULL в нем не могут присутствовать NULL-значения ни в одной из существующих строк). В СУБД Oracle, начиная с версии 8i, можно удалять столбцы. Изменяя типы данных существующих столбцов или добавляя столбцы в таблицу базы данных, нужно соблюдать ряд условий. Общепринято, что увеличение – это хорошо, а уменьшение, как правило, — не очень. Допустимые увеличения:

• Увеличение размера столбца CHAR или VARCHAR2
• Увеличение размера столбца NUMBER
• Добавление новых столбцов в таблицу

Уменьшение различных характеристик таблицы, в том числе некоторых типов данных столбцов и реального числа столбцов таблицы, требует особых действий. Часто перед внесением изменения нужно убедиться в том, что в соответствующем столбце или столбцах все значения являются NULL-значениями. Для выполнения подобных операций над столбцами таблицы, содержащими данные, разработчик должен найти или создать какую-то область для временного хранения этих данных. Например, создать таблицу с помощью команды CREATE TABLE AS SELECT, в которой извлекаются данные из первичного ключа и изменяемого столбца или столбцов. Допустимые изменения:

• Уменьшение размера столбца NUMBER (только при пустом столбце для всех строк)
• Уменьшение размера столбца CHAR или VARCHAR2 (только при пустом столбце для всех строк)
• Изменение типа данных столбца (только при пустом столбце для всех строк)

ALTER TABLE Пример 1

Добавление столбца в таблицу:

ALTER TABLE t1(pole1 char(10));

ALTER TABLE Пример 2

Изменение размера столбца таблицы:

ALTER TABLE t1 MODIFY (pole1 char(20));

ALTER TABLE Пример 3

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

ALTER TABLE t1 DROP COLUMN pole1;

С помощью команды ALTER TABLE можно изменить имя таблицы без реального переноса физической информации в БД:

ALTER TABLE t1 RENAME TO t2;

Аналогичную операцию можно выполнить с помощью команды RENAME:

RENAME t1 TO t2;

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

ALTER TABLE Пример 4

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

ALTER TABLE t1 MODIFY (pole1 NOT NULL); CREATE TABLE t2 (pole1 CHAR(10) PRIMARY KEY); ALTER TABLE t1 ADD(CONSTRAINT fk_t1 FOREIGN KEY (pole1) REFERENCES t2 (pole1)); ALTER TABLE t1 ADD (UNIQUE (p_name)); ALTER TABLE t1 ADD (p_size CHAR(4) CHECK(p_size IN (‘P’,’S’,’M’,’L’,’XL’,’XXL’,’XXXL’)));

В первой из приведенных выше команд для добавления ограничения NOT NULL для столбца используется конструкция MODIFY, а для добавления всех табличных ограничений целостности других типов – конструкция ADD. Столбец, для которого добавляется ограничение, должен уже существовать в таблице БД; в противном случае создать ограничение не удастся.

ALTER TABLE Пример 5

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

ALTER TABLE t1 ADD FOREIGN KEY (pole1) REFERENCES t2 (pole1);

Существует ряд условий создания ограничений:

• Первичные ключи: в столбцах не могут содержаться NULL-значения, и все значения должны быть уникальны.
• Внешние ключи: в тех столбцах других таблиц, на которые производятся ссылки, должны содержаться значения, соответствующие всем значениям ссылающихся столбцов, либо значения этих последних должны быть NULL-значениями.
• Ограничения UNIQUE: все значения столбцов должны быть уникальными или NULL-значениями.
• Ограничения CHECK: новое ограничение будет применяться только по отношению к данным, добавляемым или модифицируемым после его создания.
• NOT NULL: NULL-значения в столбцах запрещены.

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

ALTER TABLE Пример 6

ALTER TABLE t1 DISABLE PRIMARY KEY; ALTER TABLE t1 DISABLE UNIQUE (p_name);

ALTER TABLE Пример 7

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

ALTER TABLE t2 DISABLE PRIMARY KEY;Error at line 1: Cannot disable constraint …. – dependencies exist (невозможно запретить ограничение – существуют зависимости)

Для удаления первичного ключа при наличии зависящих от него внешних ключей в команде ALTER TABLE DISABLE обязательна конструкция CASCADE:

ALTER TABLE t2 DISABLE PRIMARY KEY CASCADE;

ALTER TABLE Пример 8

Запрещенное ограничение разрешается следующим образом:

ALTER TABLE t1 ENABLE PRIMARY KEY; ALTER TABLE t1 ENABLE UNIQUE (p_name);

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

Добавление столбцов в набор данных

Добавляя столбцы, можно разработать собственные наборы данных. Например, можно создать новый столбец, объединяющий столбцы ADDRESS_LINE_1, ADDRESS_LINE_2 и ADDRESS_LINE_3.

1. На главной странице выберите набор данных и в меню Действия выберите Открыть . На диаграмме данных или диаграмме объединения правой кнопкой мыши нажмите имя источника данных и нажмите Открыть , чтобы отобразить редактор преобразований.
2. В редакторе преобразований нажмите Добавить этап подготовки .
3. В разделе Создание запросов введите имя столбца и в поле «Выражение» определите выражение для добавления значений в столбец. Например, чтобы объединить значения в столбцах Категория и Подкатегория , можно указать выражение: CONCAT(Category, Sub Category) .

Используйте средство выбора функций f(x) для построения выражения с использованием операторов, математических, строковых функций и функций преобразования.

Oracle Analytics добавляет шаг на панель Сценарий подготовки.

Если вы работаете с одним набором данных таблицы или набором данных на основе файлов, нажмите Применить сценарий .

ALTER TABLE в SQL

Мы можем изменить структуру таблицы с помощью команды ALTER TABLE. Мы можем:

Добавить столбец в таблицу

Мы можем добавить столбцы в таблицу с помощью команды ALTER TABLE с оператором ADD. Например:

ALTER TABLE Customers
ADD phone varchar ( 10 ) ;

Здесь мы добавили столбец с именем phone в таблицу Customers.

Добавить несколько столбцов в таблицу

Мы также можем добавить сразу несколько столбцов в таблицу. Например:

ALTER TABLE Customers
ADD phone varchar ( 10 ) , age int ;

Здесь мы добавили столбцы phone и age в таблицу Customers.

Переименовать столбец в таблице

Мы можем переименовать столбцы в таблице с помощью команды ALTER TABLE с оператором RENAME COLUMN. Например:

ALTER TABLE Customers
RENAME COLUMN customer_id TO c_id ;

Здесь мы изменили имя столбца customer_id на c_id в таблице Customers.

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

Мы также можем изменить тип данных столбца с помощью команды ALTER TABLE с оператором MODIFY или ALTER COLUMN. Например:

SQL Server