Добавление столбца в 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 появилась возможность изменить порядок столбцов изменением видимости уже существующих столбцов. Но основная цель введения невидимых столбцов — упростить миграцию старых, зависимых от порядка столбцов, приложений. Поэтому, этот способ добавить столбец в середину, иначе как «злоупотребленим» назвать нельзя — «от чего пытались уйти, туда и пришли». Подробнее в блоге Тома Кайта.
Добавление столбцов в набор данных
Добавляя столбцы, можно разработать собственные наборы данных. Например, можно создать новый столбец, объединяющий столбцы ADDRESS_LINE_1, ADDRESS_LINE_2 и ADDRESS_LINE_3.
- На главной странице выберите набор данных и в меню Действия выберите Открыть . На диаграмме данных или диаграмме объединения правой кнопкой мыши нажмите имя источника данных и нажмите Открыть , чтобы отобразить редактор преобразований.
- В редакторе преобразований нажмите Добавить этап подготовки .
- В разделе Создание запросов введите имя столбца и в поле «Выражение» определите выражение для добавления значений в столбец. Например, чтобы объединить значения в столбцах Категория и Подкатегория , можно указать выражение: CONCAT(Category, Sub Category) .
Используйте средство выбора функций f(x) для построения выражения с использованием операторов, математических, строковых функций и функций преобразования.
Oracle Analytics добавляет шаг на панель Сценарий подготовки.
Если вы работаете с одним набором данных таблицы или набором данных на основе файлов, нажмите Применить сценарий .
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);
Разрешить можно только те ограничения, которые были установлены ранее, а в данный момент запрещены. Ограничение, процесс создания которого завершился неудачей, не будет существовать в запрещенном виде, ожидая своего разрешения после устранения ошибки. Как правило, владелец таблицы или тот, кому предоставлены соответствующие права, может удалить ограничение:
ALTER TABLE — изменение таблицы в SQL
Рассказываем о команде ALTER TABLE и учим вносить с ее помощью изменения в таблицы и столбцы.
Эта инструкция — часть курса «MySQL для новичков».
Смотреть весь курс
Введение
ALTER TABLE — один из самых незаменимых инструментов в работе с базами данных SQL. В этой статье мы рассмотрим SQL оператор ALTER TABLE и его применение. Узнаем, как добавить или удалить поля с помощью этого инструмента, и рассмотрим различные примеры его использования. В данной статье мы не будем рассматривать MS SQL и остановимся на синтаксисе наиболее популярной версии — MySQL.
Синтаксис оператора ALTER TABLE в SQL
Синтаксис оператора ALTER TABLE выглядит следующим образом:
ALTER TABLE название_таблицы [WITH CHECK | WITH NOCHECK]
Из этой записи мы видим, что сценариев применения данной команды достаточно много. Давайте рассмотрим их. В качестве примера мы будем использовать базу данных slcbookshelf, которую мы создавали в статье о первичных и внешних SQL ключах.
use slcbookshelf; Добавление столбца в таблицу (ADD COLUMN)
Сейчас наша таблица выглядит следующим образом:
mysql> DESC books; +---------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+-----+---------+-------+ | book_id | int | NO | | NULL | | | book_name | varchar(255) | NO | | NULL | | | book_category | varchar(255) | YES | | NULL | | +---------------+--------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) Давайте добавим в нашу таблицу новый столбец, в котором будет отображаться автор каждой книги:
ALTER TABLE books ADD author NVARCHAR(50) NOT NULL; Данным запросом мы создали в нашей таблице новый столбец authors с типом NVARCHAR и длиной в 50 символов, который не может принимать пустое значение. Если мы не знаем автора произведения, тогда наша команда будет иметь такой вид:
ALTER TABLE books ADD author NVARCHAR(50) NOT NULL DEFAULT 'Неизвестен'; Теперь для существующих данных, для которых не заполнен столбец author, значение по умолчанию будет «Неизвестен».
Переименование столбца и таблицы
Переименование столбца (RENAME)
С помощью ALTER TABLE можно переименовать существующий столбец. Для этого выполните команду:
ALTER TABLE books RENAME COLUMN author TO authors; Переименование таблицы (RENAME)
При помощи ALTER TABLE можно переименовать таблицу. Выполняем запрос:
ALTER TABLE books RENAME TO books_selectel; Удаление столбца (DROP)
Чтобы удалить столбец из таблицы с помощью ALTER TABLE, требуется выполнить следующий запрос:
ALTER TABLE books DROP COLUMN authors; Изменение столбца (ALTER COLUMN)
Иногда бывают случаи, когда необходимо изменить уже созданный ранее столбец. Это действие можно выполнить с помощью команды ALTER TABLE. Для изменения существующего столбца необходимо выполнить такой запрос:
ALTER TABLE books ALTER COLUMN book_category VARCHAR(200); В данном примере мы изменили максимальное количество символов, которое может использоваться в полях столбца book_category с 255 до 200.
Также с помощью ALTER TABLE можно сделать действие сразу с несколькими столбцами. Чтобы изменить сразу несколько столбцов, вам потребуется использовать эту команду:
ALTER TABLE books MODIFY book_category VARCHAR(200), MODIFY book_name VARCHAR(200), . ; Таким запросом мы изменили сразу два столбца: book_category и book_name.
Изменение типа столбца
При помощи ALTER TABLE можно изменить тип столбца в таблице SQL. Изменение типа существующего столбца осуществляется при помощи команды:
ALTER TABLE books ALTER COLUMN book_category NVARCHAR(200); Выполнив эту команду, мы изменили тип book_category на NVARCHAR(200).
Добавление первичного и внешнего ключей при помощи ALTER TABLE
Вы можете определить существующий столбец в таблице в качестве первичного ключа с помощью команды ALTER TABLE. Запрос, добавляющий в таблицу первичный ключ, будет выглядеть следующим образом:
ALTER TABLE books ADD PRIMARY KEY (book_id); Аналогично при помощи ALTER TABLE можно добавить внешний ключ таблицы. Чтобы создать внешний ключ для таблицы MySQL выполните команду:
ALTER TABLE books ADD FOREIGN KEY (author_id) REFERENCES authors(author_id); В результате выполнения этой команды поле author_id в таблице books будет внешним ключом для аналогичного поля в таблице authors.
Работа с ограничениями
Ограничения — специальные правила, которые применяются к таблице, чтобы ограничить типы данных в таблице. Ограничения очень важны, так как их правильное применение помогает обеспечить целостность данных в таблицах и наладить стабильную работу базы. Давайте рассмотрим одно из таких ограничений — ограничение CHECK. Применяя ограничения CHECK к столбцу таблицы, мы создаем правило, по которому при добавлении данных СУБД будет автоматически проверять их на соответствии заданным правилам.
Создание ограничения
Например, если нам необходимо, чтобы все клиенты в базе данных Customers имели возраст больше 21 года, мы можем установить следующее ограничение:
ALTER TABLE Customers ADD CHECK (Age > 21); При применении такого ограничения стоит учитывать, что если в столбце уже существуют данные, не соответствующие ограничению, то команда будет выполнена с ошибкой. Чтобы избежать подобного поведения, вы можете добавить ограничение со значением WITH NOCHECK. Таким образом, текущие значения столбца не вызовут ошибок при выполнении запроса при несоответствии ограничению:
ALTER TABLE Customers WITH NOCHECK ADD CHECK (Age > 21); Добавление ограничений с именами
Так как ограничений в таблицах может быть много, добавление имен к ограничениям может в значительной мере упростить будущую работу с таблицей. Создать имя для ограничения можно при помощи оператора CONSTRAINT:
ALTER TABLE Customers ADD CONSTRAINT Check_Age_Greater_Than_Twenty_One CHECK (Age > 21); Удаление ограничений
ALTER TABLE можно пользоваться и для удаления ограничений. Для удаления существующих ограничений необходимо выполнить следующую команду:
ALTER TABLE Customers DROP Check_Age_Greater_Than_Twenty_One; После выполнения этой команды ограничение перестанет применяться при добавлении новых данных в столбец.
Заключение
В данной статье мы с вами узнали что такое ALTER TABLE, рассмотрели работу с этой командой и научились вносить с ее помощью изменения в таблицы и столбцы, а также рассмотрели несколько примеров использования данной команды, которые сильно упростят будущую работу с базами данных.
Сброс пароля root в MySQL