oracle alter table详解

//建测试表

  1. create table dept(
  2. deptno number(3) primary key,
  3. dname varchar2(10),
  4. loc varchar2(13)
  5. );
  6. create table employee_info(
  7. empno number(3),
  8. deptno number(3),
  9. ename varchar2(10),
  10. sex char(1),
  11. phone number(11),
  12. address varchar2(50),
  13. introduce varchar2(100)
  14. );
  15. --
  16. //0.重命名
  17. //0.1 表:rename dept to dt;
  18. rename dt to dept;
  19. //0.2 列:alter table dept rename column loc to location;
  20. alter table dept rename column location to loc;
  21. //1.添加约束
  22. //1.1 primary key
  23. alter table employee_info add constraint pk_emp_info primary key(empno);
  24. //1.2 foreign key
  25. alter table employee_info add constraint fk_emp_info foreign key(deptno)
  26. references dept(deptno);
  27. //1.3 check
  28. alter table employee_info add constraint ck_emp_info check
  29. (sex in ('F','M'));
  30. //1.4 not null
  31. alter table employee_info modify phone constraint not_null_emp_info not null;
  32. //1.5 unique
  33. alter table employee_info add constraint uq_emp_info unique(phone);
  34. //1.6 default
  35. alter table employee_info modify sex char(2) default 'M';
  36. //2.添加列
  37. alter table employee_info add id varchar2(18);
  38. alter table employee_info add hiredate date default sysdate not null;
  39. //3.删除列
  40. alter table employee_info drop column introduce;
  41. //3.修改列
  42. //3.1 修改列的长度
  43. alter table dept modify loc varchar2(50);
  44. //3.2 修改列的精度
  45. alter table employee_info modify empno number(2);
  46. //3.3 修改列的数据类型
  47. alter table employee_info modify sex char(2);
  48. //3.4 修改默认值
  49. alter table employee_info modify hiredate default sysdate+1;
  50. //4.禁用约束
  51. alter table employee_info disable constraint uq_emp_info;
  52. //5.启用约束
  53. alter table employee_info enable constraint uq_emp_info;
  54. //6.延迟约束
  55. alter table employee_info drop constraint fk_emp_info;
  56. alter table employee_info add constraint fk_emp_info foreign key(deptno)
  57. references dept(deptno)
  58. deferrable initially deferred;
  59. //7.向表中添加注释
  60. comment on table employee_info is 'information of employees';
  61. //8.向列添加注释
  62. comment on column employee_info.ename is 'the name of employees';
  63. comment on column dept.dname is 'the name of department';
  64. //9.清除表中所有数据
  65. truncate table employee_info;
  66. //10.删除表
  67. drop table employee_info;
  68. --
  69. //下面来看看刚刚才我们对表dept和表employee_info所做的更改
  70. //user_constraints视图里面包含了刚刚才我们创建的所有约束,以及其他信息,
  71. //你可以用desc user_constraints命令查看其详细说明
  72. select constraint_name,constraint_type,status,deferrable,deferred
  73. from user_constraints
  74. where table_name='EMPLOYEE_INFO';
  75. --
  76. CONSTRAINT_NAME CONSTRAINT_TYPE STATUS DEFERRABLE DEFERRED
  77. ------------------------------ --------------- -------- -------------- ---------
  78. PK_EMP_INFO P ENABLED NOT DEFERRABLE IMMEDIATE
  79. FK_EMP_INFO R ENABLED DEFERRABLE DEFERRED
  80. NOT_NULL_EMP_INFO C ENABLED NOT DEFERRABLE IMMEDIATE
  81. SYS_C005373 C ENABLED NOT DEFERRABLE IMMEDIATE
  82. UQ_EMP_INFO U ENABLED NOT DEFERRABLE IMMEDIATE
  83. CK_EMP_INFO C ENABLED NOT DEFERRABLE IMMEDIATE
  84. //我们可以通过user_cons_columns视图查看有关列的约束信息;
  85. select owner,constraint_name,table_name,column_name
  86. from user_cons_columns
  87. where table_name='EMPLOYEE_INFO';
  88. --
  89. OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME
  90. ------------------------------ ------------------------------ ------------------------------ ---------------
  91. YEEXUN PK_EMP_INFO EMPLOYEE_INFO EMPNO
  92. YEEXUN CK_EMP_INFO EMPLOYEE_INFO SEX
  93. YEEXUN NOT_NULL_EMP_INFO EMPLOYEE_INFO PHONE
  94. YEEXUN SYS_C005373 EMPLOYEE_INFO HIREDATE
  95. YEEXUN UQ_EMP_INFO EMPLOYEE_INFO PHONE
  96. YEEXUN FK_EMP_INFO EMPLOYEE_INFO DEPTNO
  97. //我们将user_constraints视图与user_cons_columns视图连接起来
  98. //查看约束都指向哪些列
  99. column column_name format a15;
  100. select ucc.column_name,ucc.constraint_name,uc.constraint_type,uc.status
  101. from user_constraints uc,user_cons_columns ucc
  102. where uc.table_name=ucc.table_name and
  103. uc.constraint_name=ucc.constraint_name and
  104. ucc.table_name='EMPLOYEE_INFO';
  105. --
  106. COLUMN_NAME CONSTRAINT_NAME CONSTRAINT_TYPE STATUS
  107. --------------- ------------------------------ --------------- --------
  108. EMPNO PK_EMP_INFO P ENABLED
  109. DEPTNO FK_EMP_INFO R ENABLED
  110. PHONE NOT_NULL_EMP_INFO C ENABLED
  111. HIREDATE SYS_C005373 C ENABLED
  112. PHONE UQ_EMP_INFO U ENABLED
  113. SEX CK_EMP_INFO C ENABLED
  114. --
  115. //这里有个constraint_type,他具体指下面几种类型:
  116. //C:check,not null
  117. //P:primary key
  118. //R:foreign key
  119. //U:unique
  120. //V:check option
  121. //O:read only
  122. --
  123. //我们可以通过user_tab_comments视图获得对表的注释
  124. select * from user_tab_comments
  125. where table_name='EMPLOYEE_INFO';
  126. TABLE_NAME TABLE_TYPE COMMENTS
  127. ------------------------------ ----------- --------------------------
  128. EMPLOYEE_INFO TABLE information of employees
  129. --
  130. //我们还可以通过user_col_comments视图获得对表列的注释:
  131. select * from user_col_comments
  132. where table_name='EMPLOYEE_INFO';
  133. --
  134. TABLE_NAME COLUMN_NAME COMMENTS
  135. ------------------------------ ------------------------------ ---------------------------
  136. EMPLOYEE_INFO EMPNO
  137. EMPLOYEE_INFO DEPTNO
  138. EMPLOYEE_INFO ENAME the name of employees
  139. EMPLOYEE_INFO SEX
  140. EMPLOYEE_INFO PHONE
  141. EMPLOYEE_INFO ADDRESS
  142. EMPLOYEE_INFO ID
  143. EMPLOYEE_INFO HIREDATE
  144. --
  145. select * from user_col_comments
  146. where table_name='EMPLOYEE_INFO' and
  147. comments is not null;
  148. --
  149. TABLE_NAME COLUMN_NAME COMMENTS
  150. ------------------------------ ------------------------------ ------------------------
  151. EMPLOYEE_INFO ENAME the name of employees
  152. --
  153. //最后我们来查看一下修改后的表:
  154. desc employee_info;
  155. Name Type Nullable Default Comments
  156. -------- ------------ -------- --------- ---------------------
  157. EMPNO NUMBER(2)
  158. DEPTNO NUMBER(3) Y
  159. ENAME VARCHAR2(10) Y the name of employees
  160. SEX CHAR(2) Y 'M'
  161. PHONE NUMBER(11)
  162. ADDRESS VARCHAR2(50) Y
  163. ID VARCHAR2(18) Y
  164. HIREDATE DATE sysdate+1
  165. --
  166. desc dept;
  167. Name Type Nullable Default Comments
  168. ------ ------------ -------- ------- ----------------------
  169. DEPTNO NUMBER(3)
  170. DNAME VARCHAR2(10) Y the name of department
  171. LOC VARCHAR2(50) Y
  172. --