Oracle约束知识笔记

Linux大全评论521 views阅读模式

管理数据完整性

一 学习目标

1.实现数据完整性约束

2.管理完整性约束

3.从数据字典中获取约束信息

二 保证数据完整性的方法

1.应用程序代码控制

2.触发器控制

3.声明完整性约束

三 约束的类型 (见图)

1.not null    (不能为空)

2.unique      (值必须唯一)

3.primary key (not null + unique)

4.foreign key (该表值必须在外键表中存在)

5.check       (自己加的条件)

6.ref         (不熟)

注：Constraints不但可以建立在Table上，也可以建立在View上。

四 约束状态

1.disable novalidate  既不会约束新增数据也不会验证已有数据，等同于disable

2.disable validate   约束新增数据但不会验证已有数据，启用后禁止DML

3.enable novalidate  约束新增数据但不会验证已有数据

  1. SQL> create table dept2 as select * from scott.dept;  
  2.    
  3. Table created  
  4.    
  5. SQL> select * from dept2 order by deptno;  
  6.    
  7. DEPTNO DNAME          LOC  
  8. ------ -------------- -------------   
  9.     10 ACCOUNTING     NEW YORK  
  10.     20 RESEARCH       DALLAS  
  11.     30 SALES          CHICAGO  
  12.     40 OPERATIONS     BOSTON  
  13.    
  14. SQL> alter table dept2 add constraint dept2_u1 unique(deptno);  
  15.    
  16. Table altered  
  17.    
  18. SQL> select index_name,table_name,uniqueness from dba_indexes where index_name = 'DEPT2_U1';  
  19.    
  20. INDEX_NAME                     TABLE_NAME                     UNIQUENESS  
  21. ------------------------------ ------------------------------ ----------   
  22. DEPT2_U1                       DEPT2                          UNIQUE  
  23.    
  24. SQL> select constraint_name,status,validated from dba_constraints where constraint_name = 'DEPT2_U1';  
  25.    
  26. CONSTRAINT_NAME                STATUS   VALIDATED  
  27. ------------------------------ -------- -------------   
  28. DEPT2_U1                       ENABLED  VALIDATED  
  29.    
  30. SQL> insert into dept2(deptno) values(10);  
  31.    
  32. insert into dept2(deptno) values(10)  
  33.    
  34. ORA-00001: unique constraint (SYSTEM.DEPT2_U1) violated  
  35.    
  36. SQL> select * from dept2 order by deptno;  
  37.    
  38. DEPTNO DNAME          LOC  
  39. ------ -------------- -------------   
  40.     10 ACCOUNTING     NEW YORK  
  41.     20 RESEARCH       DALLAS  
  42.     30 SALES          CHICAGO  
  43.     40 OPERATIONS     BOSTON  
  44.    
  45. SQL> alter table dept2 modify constraint dept2_u1 disable novalidate;  
  46.    
  47. Table altered  
  48.    
  49. SQL> select index_name,table_name,uniqueness from dba_indexes where index_name = 'DEPT2_U1';  
  50.    
  51. INDEX_NAME                     TABLE_NAME                     UNIQUENESS  
  52.   
  53. ------------------------------ ------------------------------ ----------   
  54.   
  55. （disable自动移除索引）  
  56.   
  57.    
  58. SQL> select constraint_name,status,validated from dba_constraints where constraint_name = 'DEPT2_U1';  
  59.    
  60. CONSTRAINT_NAME                STATUS   VALIDATED  
  61. ------------------------------ -------- -------------   
  62. DEPT2_U1                       DISABLED NOT VALIDATED  
  63.    
  64. SQL> insert into dept2(deptno) values(10);  
  65.    
  66. 1 row inserted  
  67.    
  68. SQL> select * from dept2 order by deptno;  
  69.    
  70. DEPTNO DNAME          LOC  
  71. ------ -------------- -------------   
  72.     10 ACCOUNTING     NEW YORK  
  73.     10                  
  74.     20 RESEARCH       DALLAS  
  75.     30 SALES          CHICAGO  
  76.     40 OPERATIONS     BOSTON  
  77.    
  78. SQL> alter table dept2 modify constraint dept2_u1 enable novalidate;  
  79.    
  80. alter table dept2 modify constraint dept2_u1 enable novalidate  
  81.    
  82. ORA-02299: cannot validate (SYSTEM.DEPT2_U1) - duplicate keys found  
  83.  （因为enable会去创建唯一性索引，而已有数据deptno存在重复数据10，所以这里不能enable）  
  84.   
  85. SQL> delete from dept2 where deptno=10 and dname is null;  
  86.    
  87. 1 row deleted  
  88.    
  89. SQL> alter table dept2 modify constraint dept2_u1 enable novalidate;  
  90.    
  91. Table altered  
  92.    
  93. SQL> select index_name,table_name,uniqueness from dba_indexes where index_name = 'DEPT2_U1';  
  94.    
  95. INDEX_NAME                     TABLE_NAME                     UNIQUENESS  
  96. ------------------------------ ------------------------------ ----------   
  97.   
  98. DEPT2_U1                       DEPT2                          UNIQUE  
  99.   
  100. （enable会自动创建唯一性索引）  
  101.   
  102.   
  103. SQL> select constraint_name,status,validated from dba_constraints where constraint_name = 'DEPT2_U1';  
  104.    
  105. CONSTRAINT_NAME                STATUS   VALIDATED  
  106. ------------------------------ -------- -------------   
  107. DEPT2_U1                       ENABLED  NOT VALIDATED  
  108.    
  109. SQL> insert into dept2(deptno) values(10);  
  110.    
  111. insert into dept2(deptno) values(10)  
  112.    
  113. ORA-00001: unique constraint (SYSTEM.DEPT2_U1) violated  
  114.    
  115. SQL> alter table dept2 modify constraint dept2_u1 disable validate;  
  116.    
  117. Table altered  
  118.    
  119. SQL> select index_name,table_name,uniqueness from dba_indexes where index_name = 'DEPT2_U1';  
  120.    
  121. INDEX_NAME                     TABLE_NAME                     UNIQUENESS  
  122. ------------------------------ ------------------------------ ----------   
  123.    
  124. SQL> select constraint_name,status,validated from dba_constraints where constraint_name = 'DEPT2_U1';  
  125.    
  126. CONSTRAINT_NAME                STATUS   VALIDATED  
  127. ------------------------------ -------- -------------   
  128. DEPT2_U1                       DISABLED VALIDATED  
  129.    
  130. SQL> insert into dept2(deptno) values(10);  
  131.    
  132. insert into dept2(deptno) values(10)  
  133.    
  134.   
  135. ORA-25128: No insert/update/delete on table with constraint (SYSTEM.DEPT2_U1) disabled and validated  
  136.   
  137. （disable validate后禁止DML）  
  138.   
  139.   
  140. SQL> select * from dept2 order by deptno;  
  141.    
  142. DEPTNO DNAME          LOC  
  143. ------ -------------- -------------   
  144.     10 ACCOUNTING     NEW YORK  
  145.     20 RESEARCH       DALLAS  
  146.     30 SALES          CHICAGO  
  147.     40 OPERATIONS     BOSTON  
  148.    
  149. SQL> alter table dept2 modify constraint dept2_u1 enable validate;  
  150.    
  151. Table altered  
  152.    
  153. SQL> select index_name,table_name,uniqueness from dba_indexes where index_name = 'DEPT2_U1';  
  154.    
  155. INDEX_NAME                     TABLE_NAME                     UNIQUENESS  
  156. ------------------------------ ------------------------------ ----------   
  157. DEPT2_U1                       DEPT2                          UNIQUE  
  158.    
  159. SQL> select constraint_name,status,validated from dba_constraints where constraint_name = 'DEPT2_U1';  
  160.    
  161. CONSTRAINT_NAME                STATUS   VALIDATED  
  162. ------------------------------ -------- -------------   
  163. DEPT2_U1                       ENABLED  VALIDATED  
  164.    
  165. SQL> insert into dept2(deptno) values(10);  
  166.    
  167. insert into dept2(deptno) values(10)  
  168.    
  169. ORA-00001: unique constraint (SYSTEM.DEPT2_U1) violated  
  170.    
  171. SQL> select * from dept2 order by deptno;  
  172.    
  173. DEPTNO DNAME          LOC  
  174. ------ -------------- -------------   
  175.     10 ACCOUNTING     NEW YORK  
  176.     20 RESEARCH       DALLAS  
  177.     30 SALES          CHICAGO  
  178.     40 OPERATIONS     BOSTON  

企鹅博客
  • 本文由 发表于 2020年7月22日 06:08:06
  • 转载请务必保留本文链接：https://www.qieseo.com/185927.html

发表评论