Oracle ：alter table

主要讲述Alter table语句的用法，对表进行修改，alter table 语句可以执行以下任务：

1、添加、修改或删除列

2、添加或删除约束

3、启用或禁用约束

一、添加列(alter table table_name add 列名 类型)

⑴、向表order_status2添加一个名为modified_by的列，类型为integer

SQL>alter table order_status2 add modified_by integer;

向表order_status2添加一个名为initially_created的列，类型为date，默认为sysdate

⑵、SQL>alter table order_status2 add initially_created date default sysdate not null;

二、修改列(alter table table_name modify 列名 ….)

1、修改列的长度，条件是该列的类型的长度可以修改，如：char或varchar2

2、修改数字列的精度

3、修改列的数据类型

4、修改列的默认值

以下将一一举例说明：

⑴修改列的长度

将表order_status2中status列的长度从10增加到20（类型为varchar2）

SQL>alter table order_status2 modify status varchar2(20);

注：只有在表中还没有任何行或所有列都为空值时才可以减小列的长度

⑵修改数字列的精度

将order_status2中id列的精度从40修改为20(类型为number)

SQL>alter table order_status2 modify id number(20);

注：只有在表中还没有任何行或所有列都为空值时才可以减小数字列的精度

⑶修改列的数据类型

将order_status2表中status列的数据类型从varchar2修改为char

SQL>alter table order_status2 modify status char(20);

⑷修改列的默认值

将order_status2表中last_modified列的默认值修改为sysdate-1

SQL>alter table order_status2 modify last_modified default sysdate-1;

⑸删除列

将order_status2表中的initially_creaded列删除

SQL>alter table order_status2 drop column initially_created;

三、添加约束(CHECK、NOT NULL、PRIMARY KEY、FOREIGN KEY、UNIQUE、CHECK OPTION、READ ONLY等)

⑴添加CHECK约束

向表order_status2中status列添加一个check约束

SQL>alter table order_status2 add constraint order_status2_status_chk check (status in (‘PLACED’,’PENDING’,’SHIPPED’));

添加一个ID约束，限制ID的值大于0；

SQL>alter table order_status2 add constraint order_status2_id_chk check (id>0);

⑵添加NOT NULL约束

向order_status2表中status列添加一个NOT NULL约束

SQL>alter table order_status2 modify status constraint order_status2_status_nn not null;

对modified_by 列添加一个NOT NULL约束

SQL>alter table order_status2 modify modified_by constraint order_status2_modified_by_nn not null;

SQL>alter table order_status2 modify last_modified not null;

⑶添加FOREIGN KEY约束

使用alter table首先从order_status2中删除modified_by列，然后添加一个引用employees.employee_id列的FOREIGN KEY约束；

SQL>alter table order_status2 drop column modified_by;

SQL>alter table order_status2 add constraint order_status2_modified_by_fk modified_by references employees(employee_id);

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

SQL>alter table order_status2 drop column modified_by;

SQL>alter table order_status2 add constraint order_status2_modified_by_fk modified_by references employee(employee_id) on delete cascade;

即当employee表中删除一行记录时，在order_status2表所有匹配的行也都将被删除

SQL>alter table order_status2 add constraint order_status2_modified_by_fk modified_by references employee(employee_id) on delete set null;

即当employee表中删除一行记录时，在order_status2表所有匹配的行也都将被设置为空值

⑷添加UNIQUE约束

向order_status2表的status列添加一个UNIQUE约束

SQL>alter table order_status2 add constraint order_status2_status_uq unique(status);

⑸删除约束

使用alter table的drop constraint子句可以删除约束

SQL>alter table order_status2 drop constraint order_status2_status_uq;

⑹禁用约束

以下是添加一个UNIQUE约束并禁用

SQL>alter table order_status2 add constraint order_status2_status_uq unique(status) disable;

禁用一个现有的约束

SQL>alter table order_status2 disable constraint order_status2_status_nn;

⑺启用约束

以下是启用order_status2_status_uq约束

SQL>alter table order_status2 enble constraint order_status2_status_uq;

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

SQL>alter table order_status2 enable novalidate constraint order_status2_status_uq;

⑻延迟约束

延迟约束(deferred constraint)是在事务被提交时强制执行的约束

INITIALLY IMMEDIATE：是每次向表中添加数据、修改表的数据或删除数据时都要检查这个约束(这与约束的默认行为相同)

INITIALLY DEFERRED：在事务提交(即执行commit命令)时才会检查约束

SQL>alter table order_status2

add constraint order_status2_status_uq unique(status)

deferrable initially deferred|immediate;

获得相关约束信息

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

使用all_constraints可以获得所有可以访问的约束的信息

SQL>select constraint_name,constraint_type,status,deferrable,deferred

 From user_constraints

 Where table_name=upper(‘order_status2’);

获得有关列的约束信息

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

使用all_con_columns可以获得所有可以访问的列的约束信息

SQL>column column_name format a15

SQL>select constraint_name,column_name

 From user_cons_columns

 Where table_name=upper(‘order_status2’);

下面是对user_constraints和user_cons_columns进行的联合查询

SQL>select ucc.column_name,ucc.constraint_name,uc.constraint_type,uc.status

 From user_constraints uc,user_cons_columns ucc

 Where uc.table_name=ucc.table_name

 And uc.constraint_name=ucc.constraint_name

 And ucc.table_name=upper(‘order_status2’);

⑼重命名表 

SQL>rename order_status2 to order_state;

⑽向表添加注释

以下是向表order_status2添加注释

SQL>comment on table order_status2 is ‘order_status2 stores the of an order’;

以下是向列order_status2.last_modified添加注释

SQL>comment on column order_status2.last_modified is ‘last_modified stores the date and time the order was modified last’;

使用user_tab_comments视图获取表的注释

SQL>select * from user_tab_comments where table_name=’ORDER_STATUS2’;

使用user_col_comments视图获取有关列的注释

SQL>select * from user_col_comments where table_name=’ORDER_STATUS2’;

⑾截断表

SQL>truncate table order_status2; (能降低高水位，回收空间，只删除表的数据，不删除此表)

SQL>drop table order_status2;