Oracle добавить столбец в таблицу
Оператор ALTER TABLE позволяет переминовывать существующую таблицу. Так же он может быть использован для добавления, модификации, или удаления столбцов существующей таблицы.
Переименование таблицы
Простейщий синтаксис переименования таблицы:
ALTER TABLE table_name
RENAME TO new_table_name;
ALTER TABLE suppliers
RENAME TO vendors;
Переименуем таблицу suppliers в таблицу vendors.
Добавление столбца (столбцов) в таблицу
Синтаксис оператора ALTER TABLE для добавления столбца в существующую таблицу:
ALTER TABLE table_name
ADD column_name column-definition;
ALTER TABLE supplier
ADD supplier_name varchar2(50);
Добавим столбец supplier_name в таблицу supplier.
Синтаксис оператора ALTER TABLE для добавления нескольких столбцов в существующую таблицу:
ALTER TABLE supplier ADD ( supplier_name varchar2(50), city varchar2(45) );
Добавим два столбца (supplier_name and city) в таблицу supplier.
Модификация столбца(-ов) в таблице
Синтаксис оператора ALTER TABLE для изменеия одного столбца в существующей таблице:
ALTER TABLE table_name
MODIFY column_name column_type;
ALTER TABLE supplier
MODIFY supplier_name varchar2(100) not null;
Синтаксис оператора ALTER TABLE для изменения нескольких столбцов в существующей таблице:
ALTER TABLE supplier MODIFY ( supplier_name varchar2(100) not null, city varchar2(75) );
Изменим столбцыsupplier_name и city.
Удаление столбца(-ов) в таблице
Синтаксис оператора ALTER TABLE для удаления одного столбца в существующей таблице:
ALTER TABLE table_name
DROP COLUMN column_name;
ALTER TABLE supplier
DROP COLUMN supplier_name;
Удалим столбец supplier_name в таблице supplier.
Переименование столбца(-ов) в таблице(НОВОЕ в Oracle 9i Release 2)
Начиная с Oracle 9i Release 2, вы можете переименовывать столбцы.
Синтаксис оператора ALTER TABLE для переименовывания одного столбца в существующей таблице::
ALTER TABLE table_name
RENAME COLUMN old_name to new_name;
ALTER TABLE supplier
RENAME COLUMN supplier_name to sname;
Переименуем столбец supplier_name в sname.
Переименовать таблицу departments, созданную ниже, в depts.
CREATE TABLE departments ( department_id number(10) not null, department_name varchar2(50) not null, CONSTRAINT departments_pk PRIMARY KEY (department_id) );
ALTER TABLE departments
RENAME TO depts;
Добавить в таблицу employees, созданную ниже, столбец salary с типом данных number(6).
CREATE TABLE employees ( employee_number number(10) not null, employee_name varchar2(50) not null, department_id number(10), CONSTRAINT employees_pk PRIMARY KEY (employee_number) );
ALTER TABLE employees
ADD salary number(6);
CREATE TABLE customers ( customer_id number(10) not null, customer_name varchar2(50) not null, address varchar2(50), city varchar2(50), state varchar2(25), zip_code varchar2(10), CONSTRAINT customers_pk PRIMARY KEY (customer_id) );
ALTER TABLE customers ADD ( contact_name varchar2(50), last_contacted date );
Поменять тип данных столбца employee_name на varchar2(75) в таблице employees.
CREATE TABLE employees ( employee_number number(10) not null, employee_name varchar2(50) not null, department_id number(10), CONSTRAINT employees_pk PRIMARY KEY (employee_number) );
ALTER TABLE employees
MODIFY employee_name varchar2(75);
В таблице customers запретить запись NULL-значений в столбец customer_name и поменять тип данных поля state на varchar2(2).
CREATE TABLE customers ( customer_id number(10) not null, customer_name varchar2(50), address varchar2(50), city varchar2(50), state varchar2(25), zip_code varchar2(10), CONSTRAINT customers_pk PRIMARY KEY (customer_id) );
ALTER TABLE customers MODIFY ( customer_name varchar2(50) not null, state varchar2(2) );
Удалить столбец salary в таблице employees.
CREATE TABLE employees ( employee_number number(10) not null, employee_name varchar2(50) not null, department_id number(10), salary number(6), CONSTRAINT employees_pk PRIMARY KEY (employee_number) );
ALTER TABLE employees
DROP COLUMN salary;
В таблице departments переименовать столбец department_name в dept_name.
CREATE TABLE departments ( department_id number(10) not null, department_name varchar2(50) not null, CONSTRAINT departments_pk PRIMARY KEY (department_id) );
ALTER TABLE departments
RENAME COLUMN department_name to dept_name;
Запись опубликована 10.12.2009 в 3:20 дп и размещена в рубрике Таблицы. Вы можете следить за обсуждением этой записи с помощью ленты RSS 2.0. Можно оставить комментарий или сделать обратную ссылку с вашего сайта.
Мы научились создавать таблицы в базе данных Oracle на предыдущем шаге. Таблицы и колонки таблиц, их названия, расположение, последовательность колонок, типы данных колонок называются структурой таблицы.
Структуру таблицы можно менять, то есть добавлять новые колонки в таблицу, удалять колонки из таблицы, менять типы данных у заданной колонки. Также, если таблица нам больше не нужна или просто надоела, существует возможность такую таблицу удалить.
Теория и практика
Существует несколько команд для изменения структуры таблицы, добавления, удаления или изменения типа данных колонки таблицы.
Все эти команды объединяет то, что они начинаются с ключевой команды ALTER TABLE .
Добавление колонки
Добавляем новую колонку к нашей таблице.
Синтаксис:
TABLE_NAME – наименование таблицы.
Column_NAME – наименование колонки.
Column_type – тип данных колонки ( VARCHAR (n) или NUMBER или DATE ).
Примеры:
Пусть у нас есть таблица GOODS, необходимо добавить колонку itemprice типа NUMBER , цена изделия.
Пусть у нас есть таблица MANS, необходимо добавить колонку DATEreg типа DATE , дата регистрации, и колонку patronymic – отчество VARCHAR2 (50) .
Удаление колонки
Также мы можем удалить колонку из заданной таблицы с помощью специальной SQL-команды DROP COLUMN .
Синтаксис:
Примеры:
Пусть у нас есть таблица GOODS, необходимо удалить колонку COLOR .
Пусть у нас есть таблица MANS, необходимо удалить колонку YEAROLD .
Меняем тип данных для колонки таблицы.
Синтаксис изменения типа колонки:
Сolumn_NAME – наименование колонки.
Data_type – тип данных колонки ( VARCHAR (n) или NUMBER или DATE ).
Примеры:
– заменить в таблице MANS тип поля NAME на VARCHAR2 (90) ;
– заменить в таблице GOODS тип поля INSERT_DATE на DATE;
Удаление таблицы из базы данных Oracle
Синтаксис команды SQL для удаления таблицы:
Здесь TABLE_NAME – наименование таблицы.
Примеры:
– удалить таблицу DOC ;
– удалить таблицу ITEMS ;
– удалить таблицу BILLING_PERIOD со связанными данными в таблице PERIODS.
Важные замечания
- При выполнении действий по изменению структуры таблицы следует быть особенно осторожным, следует тщательно взвешивать свои действия: восстановление таблицы в прежнем виде может быть затруднительно или невозможно.
- Если вы используете команды изменения типов данных и встречаетесь с ошибкой ORA-01439 , модифицируемый столбец при смене типа данных должен быть пуст. Сохраните данные в столбце и используйте специальные преобразования, о которых будет рассказано в следующих шагах.
- В некоторых случаях удаление таблицы или колонки таблицы будет запрещено, поскольку могут быть еще таблицы со связанными данными. Требуется сначала удалить данные в связанных таблицах, а уже затем удалять таблицу либо колонку. Или же воспользоваться специальной командой DROP CASCADE.
Возможные вопросы
Да, вполне, и для этого есть две команды:
Универсальный же синтаксис предполагает использование ALTER TABLE .
Примеры:
Переименуем таблицу с названием STAFF в EMP:
Переименуем таблицу с названием TRADES в TRADE:
Можно ли переименовать столбец в таблице?
Да, синтаксис команды:
Пример:
Переименовать колонку с наименованием NAME в таблице STAFF в колонку LASTNAME :
Стоит задача добавить столбец в таблицу oracle после определенного столба. Пробовал так: ALTER TABLE <имя таблицы> ADD <имя столца> VARCHAR(255) AFTER <имя столбца после которого необходимо добавить> ; Но данный способ не отрабатывает
в Oracle 12c: это пример
Порядок столбцов в таблице в нормальном случае не должен играть ни какой роли. То есть, если стоит такая задача, то ранее были допущены ошибки в дизайне БД или использующих её приложений.
Если бы порядок столбцов был бы важен и нужен, то оператор AFTER уже скорее всего был бы реализован в стандарте SQL.
Если всё-таки в редких случаях порядок столбцов желателен, например, для генераторов кода, то самое простое использовать представление вместо физической таблицы.
Изменение порядка столбцов в представлении не затрагивает структуры физической таблицы (indexes, constraints, triggers и пр.), то есть по-сути пройдёт "безболезнено":
И только в крайнем случае, можно воспользоваться многочисленными решениями, например, в этой ветке на enSO. Самый, по-моему мнению, правильный путь там отсутствует - выгрузить данные, пересоздать таблицу с "нуля", залить сохранённые данные.
В 12c появилась возможность изменить порядок столбцов изменением видимости уже существующих столбцов. Но основная цель введения невидимых столбцов - упростить миграцию старых, зависимых от порядка столбцов, приложений. Поэтому, этот способ добавить столбец в середину, иначе как "злоупотребленим" назвать нельзя - "от чего пытались уйти, туда и пришли". Подробнее в блоге Тома Кайта.
How can I add a column with a default value to an existing table in SQL Server 2000 / SQL Server 2005?
43 Answers 43
Syntax:
Example:
Notes:
Optional Constraint Name:
If you leave out CONSTRAINT D_SomeTable_SomeCol then SQL Server will autogenerate
a Default-Contraint with a funny Name like: DF__SomeTa__SomeC__4FB7FEF6
Optional With-Values Statement:
The WITH VALUES is only needed when your Column is Nullable
and you want the Default Value used for Existing Records.
If your Column is NOT NULL , then it will automatically use the Default Value
for all Existing Records, whether you specify WITH VALUES or not.
How Inserts work with a Default-Constraint:
If you insert a Record into SomeTable and do not Specify SomeCol 's value, then it will Default to 0 .
If you insert a Record and Specify SomeCol 's value as NULL (and your column allows nulls),
then the Default-Constraint will not be used and NULL will be inserted as the Value.
Notes were based on everyone's great feedback below.
Special Thanks to:
@Yatrix, @WalterStabosz, @YahooSerious, and @StackMan for their Comments.
The inclusion of the DEFAULT fills the column in existing rows with the default value, so the NOT NULL constraint is not violated.
When adding a nullable column, WITH VALUES will ensure that the specific DEFAULT value is applied to existing rows:
The most basic version with two lines only
2,835 1 1 gold badge 27 27 silver badges 25 25 bronze badgesBeware when the column you are adding has a NOT NULL constraint, yet does not have a DEFAULT constraint (value). The ALTER TABLE statement will fail in that case if the table has any rows in it. The solution is to either remove the NOT NULL constraint from the new column, or provide a DEFAULT constraint for it.
2,967 2 2 gold badges 19 19 silver badges 21 21 bronze badges
If you want to add multiple columns you can do it this way for example:
3,998 4 4 gold badges 24 24 silver badges 34 34 bronze badges
To add a column to an existing database table with a default value, we can use:
Here is another way to add a column to an existing database table with a default value.
A much more thorough SQL script to add a column with a default value is below including checking if the column exists before adding it also checkin the constraint and dropping it if there is one. This script also names the constraint so we can have a nice naming convention (I like DF_) and if not SQL will give us a constraint with a name which has a randomly generated number; so it's nice to be able to name the constraint too.
These are two ways to add a column to an existing database table with a default value.
5,903 2 2 gold badges 49 49 silver badges 52 52 bronze badgesYou can do the thing with T-SQL in the following way.
As well as you can use SQL Server Management Studio also by right clicking table in the Design menu, setting the default value to table.
And furthermore, if you want to add the same column (if it does not exists) to all tables in database, then use:
Bang up pops a little window with, you guessed it, the properly formatted guaranteed-to-work change script. Hit the easy button.
Alternatively, you can add a default without having to explicitly name the constraint:
If you have an issue with existing default constraints when creating this constraint then they can be removed by:
This can be done in the SSMS GUI as well. I show a default date below but the default value can be whatever, of course.
- Put your table in design view (Right click on the table in object explorer->Design)
- Add a column to the table (or click on the column you want to update if it already exists)
- In Column Properties below, enter (getdate()) or 'abc' or 0 or whatever value you want in Default Value or Binding field as pictured below:
The MSDN article ALTER TABLE (Transact-SQL) has all of the alter table syntax.
First create a table with name student:
Add one column to it:
The table is created and a column is added to an existing table with a default value.
This has a lot of answers, but I feel the need to add this extended method. This seems a lot longer, but it is extremely useful if you're adding a NOT NULL field to a table with millions of rows in an active database.
What this will do is add the column as a nullable field and with the default value, update all fields to the default value (or you can assign more meaningful values), and finally it will change the column to be NOT NULL.
The reason for this is if you update a large scale table and add a new not null field it has to write to every single row and hereby will lock out the entire table as it adds the column and then writes all the values.
This method will add the nullable column which operates a lot faster by itself, then fills the data before setting the not null status.
I've found that doing the entire thing in one statement will lock out one of our more active tables for 4-8 minutes and quite often I have killed the process. This method each part usually takes only a few seconds and causes minimal locking.
Additionally, if you have a table in the area of billions of rows it may be worth batching the update like so:
Читайте также: