oracle alter table详解

广9567

　　//建测试表create table dept(deptno number(3) primary key,dname varchar2(10),loc varchar2(13) );create table employee_info(empno number(3),deptno number(3),ename varchar2(10),sex char(1),phone number(11),address varchar2(50),introduce varchar2(100));--//0.重命名//0.1 表:rename dept to dt;rename dt to dept;//0.2 列:alter table dept rename column loc to location;alter table dept rename column location to loc;//1.添加约束//1.1 primary keyalter table employee_info add constraint pk_emp_info primary key(empno);//1.2 foreign keyalter table employee_info add constraint fk_emp_info foreign key(deptno)references dept(deptno);//1.3 checkalter table employee_info add constraint ck_emp_info check(sex in ('F','M'));//1.4 not nullalter table employee_info modify phone constraint not_null_emp_info not null;//1.5 uniquealter table employee_info add constraint uq_emp_info unique(phone);//1.6 defaultalter table employee_info modify sex char(2) default 'M';//2.添加列alter table employee_info add id varchar2(18);alter table employee_info add hiredate date default sysdate not null;//3.删除列alter table employee_info drop column introduce;//3.修改列//3.1 修改列的长度alter table dept modify loc varchar2(50);//3.2 修改列的精度alter table employee_info modify empno number(2);//3.3 修改列的数据类型alter table employee_info modify sex char(2);//3.4 修改默认值alter table employee_info modify hiredate default sysdate+1;//4.禁用约束alter table employee_info disable constraint uq_emp_info;//5.启用约束alter table employee_info enable constraint uq_emp_info;//6.延迟约束alter table employee_info drop constraint fk_emp_info;alter table employee_info add constraint fk_emp_info foreign key(deptno)references dept(deptno)deferrable initially deferred;//7.向表中添加注释comment on table employee_info is 'information of employees';//8.向列添加注释comment on column employee_info.ename is 'the name of employees';comment on column dept.dname is 'the name of department';//9.清除表中所有数据truncate table employee_info;//10.删除表drop table employee_info;--//下面来看看刚刚才我们对表dept和表employee_info所做的更改//user_constraints视图里面包含了刚刚才我们创建的所有约束，以及其他信息，//你可以用desc user_constraints命令查看其详细说明select constraint_name,constraint_type,status,deferrable,deferredfrom user_constraintswhere table_name='EMPLOYEE_INFO';--CONSTRAINT_NAME                CONSTRAINT_TYPE STATUS   DEFERRABLE     DEFERRED------------------------------ --------------- -------- -------------- ---------PK_EMP_INFO                    P               ENABLED  NOT DEFERRABLE IMMEDIATEFK_EMP_INFO                    R               ENABLED  DEFERRABLE     DEFERREDNOT_NULL_EMP_INFO              C               ENABLED  NOT DEFERRABLE IMMEDIATESYS_C005373                    C               ENABLED  NOT DEFERRABLE IMMEDIATEUQ_EMP_INFO                    U               ENABLED  NOT DEFERRABLE IMMEDIATECK_EMP_INFO                    C               ENABLED  NOT DEFERRABLE IMMEDIATE//我们可以通过user_cons_columns视图查看有关列的约束信息；select owner,constraint_name,table_name,column_namefrom user_cons_columnswhere table_name='EMPLOYEE_INFO';--OWNER                          CONSTRAINT_NAME                TABLE_NAME                     COLUMN_NAME------------------------------ ------------------------------ ------------------------------ ---------------YEEXUN                         PK_EMP_INFO                    EMPLOYEE_INFO                  EMPNOYEEXUN                         CK_EMP_INFO                    EMPLOYEE_INFO                  SEXYEEXUN                         NOT_NULL_EMP_INFO              EMPLOYEE_INFO                  PHONEYEEXUN                         SYS_C005373                    EMPLOYEE_INFO                  HIREDATEYEEXUN                         UQ_EMP_INFO                    EMPLOYEE_INFO                  PHONEYEEXUN                         FK_EMP_INFO                    EMPLOYEE_INFO                  DEPTNO//我们将user_constraints视图与user_cons_columns视图连接起来//查看约束都指向哪些列column column_name format a15;select ucc.column_name,ucc.constraint_name,uc.constraint_type,uc.statusfrom user_constraints uc,user_cons_columns uccwhere uc.table_name=ucc.table_name anduc.constraint_name=ucc.constraint_name anducc.table_name='EMPLOYEE_INFO';--COLUMN_NAME     CONSTRAINT_NAME                CONSTRAINT_TYPE STATUS--------------- ------------------------------ --------------- --------EMPNO           PK_EMP_INFO                    P               ENABLEDDEPTNO          FK_EMP_INFO                    R               ENABLEDPHONE           NOT_NULL_EMP_INFO              C               ENABLEDHIREDATE        SYS_C005373                    C               ENABLEDPHONE           UQ_EMP_INFO                    U               ENABLEDSEX             CK_EMP_INFO                    C               ENABLED--//这里有个constraint_type，他具体指下面几种类型://C:check,not null//P:primary key//R:foreign key//U:unique//V:check option//O:read only--//我们可以通过user_tab_comments视图获得对表的注释select * from user_tab_commentswhere table_name='EMPLOYEE_INFO';TABLE_NAME                     TABLE_TYPE  COMMENTS------------------------------ ----------- --------------------------EMPLOYEE_INFO                  TABLE       information of employees--//我们还可以通过user_col_comments视图获得对表列的注释：select * from  user_col_commentswhere table_name='EMPLOYEE_INFO';--TABLE_NAME                     COLUMN_NAME                    COMMENTS------------------------------ ------------------------------ ---------------------------EMPLOYEE_INFO                  EMPNO                          EMPLOYEE_INFO                  DEPTNO                         EMPLOYEE_INFO                  ENAME                          the name of employeesEMPLOYEE_INFO                  SEX                            EMPLOYEE_INFO                  PHONE                          EMPLOYEE_INFO                  ADDRESS                        EMPLOYEE_INFO                  ID                             EMPLOYEE_INFO                  HIREDATE --select * from user_col_commentswhere table_name='EMPLOYEE_INFO' andcomments is not null;--TABLE_NAME                     COLUMN_NAME                    COMMENTS------------------------------ ------------------------------ ------------------------EMPLOYEE_INFO                  ENAME                          the name of employees--//最后我们来查看一下修改后的表:desc employee_info;Name     Type         Nullable Default   Comments              -------- ------------ -------- --------- --------------------- EMPNO    NUMBER(2)                                             DEPTNO   NUMBER(3)    Y                                        ENAME    VARCHAR2(10) Y                  the name of employees SEX      CHAR(2)      Y        'M'                             PHONE    NUMBER(11)                                            ADDRESS  VARCHAR2(50) Y                                        ID       VARCHAR2(18) Y                                        HIREDATE DATE                  sysdate+1--desc dept;Name   Type         Nullable Default Comments               ------ ------------ -------- ------- ---------------------- DEPTNO NUMBER(3)                                            DNAME  VARCHAR2(10) Y                the name of department LOC    VARCHAR2(50) Y--