104.Oracle数据库SQL开发之 表——修改表

104.Oracle数据库SQL开发之 表——修改表

欢迎转载，转载请标明出处：http://blog.csdn.net/notbaron/article/details/50043005

ALTER TABLE语句可以用于对表进行修改。可以执行：添加、修改或删除列；添加或删除约束；启用或禁用约束。

1.  添加列

例如，向order_status2表中添加一个名为modified_by的列

store@PDB1> alter table order_status2 addmodified_by integer;

 

Table altered.

查看order_status2表中的列：

store@PDB1> describe order_status2;

 Name                                                           Null?  Type

 ------------------------------------------------------------- ------------------------------------

 ID                                                        NOT NULL NUMBER(38)

 STATUS                                                                   VARCHAR2(10)

 LAST_MODIFIED                                                            DATE

 MODIFIED_BY                                                                NUMBER(38)

2.  添加虚拟列

11g中可以添加一个虚拟列，虚拟列只引用表中已有的其他列。

执行如下：

store@PDB1> alter table salary_grades add (average_salary as ((low_salary+high_salary)/2));

 

Table altered.

其中average_salary被设置为low_salary和high_salary的平均值

描述如下：

store@PDB1> describe salary_grades;

 Name                                                          Null?  Type

 ------------------------------------------------------------- ------------------------------------

 SALARY_GRADE_ID                                          NOT NULL NUMBER(38)

 LOW_SALARY                                                                 NUMBER(6)

 HIGH_SALARY                                                                NUMBER(6)

 AVERAGE_SALARY                                                       NUMBER

执行如下查询：

store@PDB1> select * from salary_grades;

 

SALARY_GRADE_ID LOW_SALARY HIGH_SALARYAVERAGE_SALARY

--------------- ---------- -------------------------

               1           1     250000         125000.5

               2    250001     500000         375000.5

               3    500001     750000         625000.5

               4    750001     999999           875000

3.  修改列

l  修改列的长度，条件是该列的数据类型的长度可以修改，例如CHAR和VARCHAR2

l  修改数字列的精度

l  修改列的数据类型

l  修改列的默认值

4.  修改列的长度

修改列的最大长度增加为15个字符：

store@PDB1> alter table order_status2 modifystatus varchar2(15);

 

Table altered.

只有在表中还没有任务行或所有列都为空值时才可以减少列的长度。

5.  修改数字列的精度

ALTER TABLE语句将id 列的精度修改为5，例如：

store@PDB1> alter table order_status2 modify idnumber(5);

 

Table altered.

6.  修改列的数据类型

修改status列的数据类型修改为CHAR

store@PDB1> alter table order_status2 modifystatus char(15);

 

Table altered.

如果一个表中还没有任何行或列为空值，就可以将列修改为任何一种数据类型。否则只能将列的数据类型修改为一种兼容的数据类型。例如，可以将VARCHAR2类型修改为CHAR，条件是没有缩短列的长度；但是不能将DATE修改为NUMBER。

7.  修改列的默认值

将ALTER TABLE语句将order_status2.last_modified 列的默认值修改为SYSDATE-1

store@PDB1> alter table order_status2 modifylast_modified default sysdate-1;

 

Table altered.

8.  删除列

删除列入下：

store@PDB1> alter table order_status2drop column initially_created;

9.  添加约束

约束以及其他类型的约束如下：

CHECK      指定一列或一组列的值必须满足某种条件

NOT NULL 指定一列不允许存储空值

PRIMARY KEY  指定表的主键

FOREIGN KEY   指定表的外键

UNIQUE   指定一列或一组列只能存储唯一的值

CHECK OPTION 通过视图对表行所做的改变必须先经过检查

READ ONLY      指定视图是只读的。

10.        添加CHECK约束

添加CHECK约束，如下：

store@PDB1> alter table order_status2 addconstraint order_status2_status_ck check (status in('PLACED','PENDING','SHIPPED'));

 

Table altered.

确保STATUS列的值始终设置为PLACED,PENDING或SHIPPED。

store@PDB1> ALTER TABLE order_status2 addconstraint order_status2_id_ck check (id>0);

 

Table altered.

确保id列就必须大于0.

可以通过指定ENABLE NOVALIDATE选项来让约束只使用于新添加的数据。

11.        添加NOT NULL约束

使用ALTER TABLE语句向列中添加一个NOT NULL约束：

store@PDB1> alter table order_status2 modifystatus constraint order_status2_status_nn not null;

 

Table altered.

没有指定约束的名称。数据库会自动为约束分配一个并不友好的名称。

12.        添加FOREIGN KEY约束

先删除modified_by列：

store@PDB1> alter table order_status2 drop columnmodified_by;

 

Table altered.

添加一个FOREIGN KEY约束：

store@PDB1> alter table order_status2 addconstraint order_status2_modified_by_fk modified_by references employees(employee_id);

 

Table altered.

使用一个带有FOREIGN KEY约束的ON DELETE CASCADE子句，可以指定在父表中删除一行记录时，子表中匹配的所有行也都将被删除。

使用ON DELETESET NULL子句，可以指定在附表中删除一行记录时，子表中匹配行的外键将被设置为空值。

13.        添加UNIQUE约束

使用ALTER TABLE语句向列添加一个UNIQUE约束

store@PDB1> alter table order_status2 addconstraint order_status2_status_uq unique(status);

 

Table altered.

任何现有的行或新行始终必须有一个唯一的STATUS列值。

14.        删除约束

删除约束如下：

store@PDB1> alter table order_status2 dropconstraint order_status2_status_uq;

 

Table altered.

15.        禁用约束

约束在创建时是启用的。在创建约束时可以再CONSTRAINT子句的末尾添加DISABLE来禁用约束。

例如：

store@PDB1> alter table order_status2 addconstraint order_status2_status_uq unique (status) disable;

 

Table altered.

可以使用ALTER TABLE的DISABLE CONSTRAINT子句可以禁用现有的约束。

例如：

ALTER TABLE order_status2 disable constraintorder_status2_status_nn;

在DISABLE CONSTRAINT子句的末尾添加CASCADE可以禁用依赖于指定约束的任何约束。

在禁用主键或作为外键约束的一部分的唯一性约束时，必须使用CASCADE。

16.        启用约束

使用ALTER TABLE的ENABLE CONSTRAINT子句可以启用现有的约束。

store@PDB1> alter table order_status2 enableconstraint order_status2_status_uq;

 

Table altered.

可以通过ENABLE NOVALIDATE可以选择只对新数据应用某个约束，例如：

store@PDB1> alter table order_status2 enablenovalidate constraint order_status2_status_uq;

 

Table altered.

约束默认是ENABLE VALIDATE，意味现有的行必须经过约束检查。

17.        延迟约束

延迟约束（deferred constraint）是在事务被提交时强制执行的约束。约束一旦添加之后，就不能再修改为DEFERRABLE了，只能先删除，再重新创建这个约束。

         添加DEFERRABLE约束时，可以将其标识为INITIALLYIMMEDIATE或INITIALLY DEFERRED。

INITIALLY IMMEDIATE 每次向表中添加数据、修改表或数据或从表中删除数据时都要检查这个约束。 INITIALLY DEFERRED 的意思是只有在事务被提交时，才会检查这个约束。

store@PDB1> alter table order_status2 dropconstraint order_status2_status_uq;

 

Table altered.

添加约束，设置为DEFERRABLE INITIALLY DEFERRED：

store@PDB1> alter table order_status2 add constraint order_status2_status_uq unique(status) deferrable initially deferred;

 

Table altered.

只有在COMMIT命令时，才会进行检查。

18.        获得有关约束的信息

通过查询user_constraints 视图可以获得有关约束的信息。

例如：

store@PDB1> selectconstraint_name,constraint_type,status,deferrable,deferred fromuser_constraints where table_name='ORDER_STATUS2';

 

CONSTRAINT_NAME

----------------------------------------------------------------------------------------------------

C STATUS  DEFERRABLE         DEFERRED

- -------- -------------- ---------

ORDER_STATUS2_PK

P ENABLED NOT DEFERRABLE IMMEDIATE

 

ORDER_STATUS2_STATUS_UQ

U ENABLED DEFERRABLE       DEFERRED

 

ORDER_STATUS2_STATUS_CK

C ENABLED NOT DEFERRABLE IMMEDIATE

 

ORDER_STATUS2_ID_CK

C ENABLED NOT DEFERRABLE IMMEDIATE

 

ORDER_STATUS2_STATUS_NN

C ENABLED NOT DEFERRABLE IMMEDIATE

 

SYS_C0010249

R ENABLED NOT DEFERRABLE IMMEDIATE

 

 

6 rows selected.

19.        获得有关列的约束的信息

通过查询user_cons_columns视图可以获得有关列的约束的信息。

查询all_con_columns视图可以获得所有要访问的列的约束信息。

store@PDB1> column column_name format a15

store@PDB1> select constraint_name,column_namefrom user_cons_columns where table_name='ORDER_STATUS2' order byconstraint_name;

 

CONSTRAINT_NAME

----------------------------------------------------------------------------------------------------

COLUMN_NAME

---------------

ORDER_STATUS2_ID_CK

ID

 

ORDER_STATUS2_PK

ID

 

ORDER_STATUS2_STATUS_CK

STATUS

 

ORDER_STATUS2_STATUS_NN

STATUS

 

ORDER_STATUS2_STATUS_UQ

STATUS

 

SYS_C0010249

MODIFIED_BY

 

 

6 rows selected.

store@PDB1> selectucc.column_name,ucc.constraint_name,uc.constraint_type,uc.status fromuser_constraints uc,user_cons_columns ucc where

   uc.table_name=ucc.table_name and uc.constraint_name=ucc.constraint_nameand ucc.table_name='ORDER_STATUS2' order by ucc.constraint_name;

 

COLUMN_NAME

---------------

CONSTRAINT_NAME

----------------------------------------------------------------------------------------------------

C STATUS

- --------

ID

ORDER_STATUS2_ID_CK

C ENABLED

 

ID

ORDER_STATUS2_PK

P ENABLED

 

STATUS

ORDER_STATUS2_STATUS_CK

C ENABLED

 

STATUS

ORDER_STATUS2_STATUS_NN

C ENABLED

 

STATUS

ORDER_STATUS2_STATUS_UQ

U ENABLED

 

MODIFIED_BY

SYS_C0010249

R ENABLED

 

 

6 rows selected.