Oracle 中表的修改操作筆記

貝殼shell

Oracle 中表的修改筆記

                                                                   ---By 貝殼shell

主要講述Alter table語句的用法，對表進行修改，alter table 語句可以執(zhí)行以下任務(wù)：

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、修改數(shù)字列的精度

3、修改列的數(shù)據(jù)類型

4、修改列的默認值

以下將一一舉例說明：

⑴修改列的長度

將表order_status2中status列的長度從10增加到20（類型為varchar2）

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

注：只有在表中還沒有任何行或所有列都為空值時才可以減小列的長度

⑵修改數(shù)字列的精度

將order_status2中id列的精度從40修改為20(類型為number)

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

注：只有在表中還沒有任何行或所有列都為空值時才可以減小數(shù)字列的精度

⑶修改列的數(shù)據(jù)類型

將order_status2表中status列的數(shù)據(jù)類型從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表所有匹配的行也都將被設(shè)置為空值

⑷添加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;

禁用一個現(xiàn)有的約束

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，可以選擇只對新數(shù)據(jù)應(yīng)用某個約束

SQL>alter table order_status2 enable novalidate constraint order_status2_status_uq;

⑻延遲約束

延遲約束(deferred constraint)是在事務(wù)被提交時強制執(zhí)行的約束

INITIALLY  IMMEDIATE：是每次向表中添加數(shù)據(jù)、修改表的數(shù)據(jù)或刪除數(shù)據(jù)時都要檢查這個約束(這與約束的默認行為相同)

INITIALLY  DEFERRED：在事務(wù)提交(即執(zhí)行commit命令)時才會檢查約束

SQL>alter table order_status2

add constraint order_status2_status_uq unique(status)

  deferrable initially deferred|immediate;

 

獲得相關(guān)約束信息

通過查詢user_constraints可以獲得有關(guān)約束的信息

使用all_constraints可以獲得所有可以訪問的約束的信息

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

         From user_constraints

         Where table_name=upper(‘order_status2’);

獲得有關(guān)列的約束信息

通過查詢user_cons_columns可以獲得有關(guān)列的約束信息

使用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進行的聯(lián)合查詢

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視圖獲取有關(guān)列的注釋

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

⑾截斷表

SQL>truncate table order_status2; (能降低高水位，回收空間，只刪除表的數(shù)據(jù)，不刪除此表)

SQL>drop table order_status2;