Oracle alter table add column example
来源:互联网 发布:线路板绘图软件 编辑:程序博客网 时间:2024/05/21 11:04
Oracle Tips by Burleson Consulting
We have "alter table" syntax from Oracle to add data columns in-place in this form:
alter table
table_name
add
(
column1_name column1_datatype column1_constraint,
column2_name column2_datatype column2_constraint,
column3_name column3_datatype column3_constraint
);
Here are some examples of Oracle "alter table" syntax to add data columns.
alter table
cust_table
add
cust_sex varchar2(1) NOT NULL;
Her is an example of Oracle "alter table" syntax to add multiple data columns.
ALTER TABLE
cust_table
ADD
(
cust_sex char(1) NOT NULL,
cust_credit_rating number
);
Sometimes, we find that a piece of data that we did not maintain becomes important, and we need to add a new table column to the database. We can add a table to hold the new data or add it to our current schema by adding a column to a current table.
For example, we discover that we need to keep a record of the last date that each author published and what they published. We need to add two columns to the author table,author_last_published (a date) and author_item_published (a varchar2(40)). To do this, we use the ALTER TABLE ADD command.
SQL> alter table author add (author_last_published date);
Table altered.
SQL> alter table author add (author_item_published varchar2(40));
Table altered.
SQL> desc author
Name Null? Type
------------------------------------- -------- ------------------AUTHOR_KEY VARCHAR2(11)
. . .
AUTHOR_ITEM_PUBLISHED VARCHAR2(40)
Notice that the new columns are at the end of the AUTHOR table. All current rows in the table now contain NULLs for the new columns.
I added each column separately, but you can add as many columns as needed in one command by separating them with commas.
alter table
author
add
(author_last_published date,
author_item_published varchar2(40));
If I define a default value for the new columns, all the current columns will have the default value. (I dropped and recreated the original author table.)
SQL> alter table author add (
2 author_last_published date default SYSDATE,
3 author_item_published varchar2(40)
4 default 'Magazine Article' not null
5 );
Table altered.
- Oracle alter table add column example
- Oracle alter table modify column Syntax example
- alter table *** add constraint
- oracle 的sql语句:alter table XX add XX,用法
- 在线生产系统执行 alter table add column type default x ;的分析
- alter table *** add constraint *** 用法
- alter table *** add constraint *** 用法
- alter table *** add constraint *** 用法
- Oracle alter table
- Oracle alter table
- Oracle alter table
- Oracle alter table
- oracle alter table详解
- Oracle :alter table
- oracle alter table详解
- oracle alter table详解
- Oracle alter table详解
- oracle alter table详解
- 授权文件比较工具完整版
- 内存以及和堆栈有关的问题
- c/c++常用算法(13) -- 基本查找算法
- Nginx限制ip访问
- [Leetcode] Letter Combinations of a Phone Number (Java)
- Oracle alter table add column example
- Java程序打包成jar包
- 0023 java中的各种数据类型在内存中存储的方式 【入门】
- useradd 命令
- 为什么项目估算偏差如此之大?
- ASN.1、BER、SMI、MIB、PDU的关系——转
- MySQL中的数据类型
- C++运算符编写CMyCString类
- 自动折行布局的实现