管理Oracle约束与分区表_第1页
管理Oracle约束与分区表_第2页
管理Oracle约束与分区表_第3页
管理Oracle约束与分区表_第4页
管理Oracle约束与分区表_第5页
已阅读5页,还剩3页未读 继续免费阅读

下载本文档

版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领

文档简介

1、1约束作用:约束用于确保数据库数据的完整性,在oracle数据库中,可以使用约束,触发器和应用代码(过程,函数)3种方法实现数据完整性,这3种方法中,因为约束易于维护,并且具有最好的性能,所以实现数据完整性首选约束.分类:约束的种类有:not null,unique,primary key,foreign key,checkNot null确保字段值不能为空Unique:确保字段值唯一性Primary key,最常用的约束(主键约束),主键约束的列值不仅不能重复,也不能为NULL,注意一张表最多只能有一个主键约束,当定义主键约束后oracle自动建立一个以主键为关键字段的索引。Foreign

2、key:定义了主从表之间的关系,foreign要定义在从表上,但主表必须具有主键约束或唯一约束,当定义froeign key后外部键列的数据必须在主表的主键列中存在,或者为NULLCheck:用于强制表行数据必须满足的条件,如工资表,工人工资必须在2000-5000之间约束状态enable validate:是默认,新旧数据同时满足约束规则enable novalidate:旧数据可以不满足,检新插入的数据要满足约束disable validate:不允许在表上执行任何DML操作,主要用在分区表,对于主键和唯一约事,会删除相应的唯一索引,但约束状态任可用disable novalidate数据

3、可不满足约束规则,对于主键和唯一约事,会删除相应的唯一索引,约束常用语句1. create table t(i number,v mubmer not null)  2. create table t(i number,v mubmer unique)  3. create table t(i number constraint pk_i primary key,v number) &#

4、160;4. create table t2(c number,d number,constraint fk_d foreign key(c),references t1(v);  5. alter table t add constraint pk_i primary key (i)  6. alter table t modify i not null;

5、  7. alter table t add constraint t_i unique(i)(create index ind_name on t(i);  8. alter table t add constraint t_i check(i in (1,2,3,4,5);  9. alter table t disable novalidat

6、e constraint i  10. alter table t enable novalidate constraint check_i  11. alter table t drop constraint i;  12. alter table t drop primary key i; #常用的数据字典1. dba_constraints  2. d

7、ba_cons_columns  3. user_cons_columns  4. user_constraints  简单应用检验当为一个表建立主键索时后,这个字段是否满足约束非空,唯一性,而且自动建立一个索引,并查看当把约束状态关闭再次插入相同的记录,是否还能把把约束设为enable ividate状态。1. SQL> create table t(i number constraint pk_i primary key,v number);  

8、2. SQL> insert into t values(1,2);  3. SQL> insert into t values(3,4);  4. SQL> commit;  5. SQL> select * from t;  6.          I     

9、     V  7. - -  8.          1          2  9.          3          

10、4  现在表中有两条记录,然后给它插主键为空或相同的值1. SQL> insert into t values('',10);  2. ERROR at line 1:  3. ORA-01400: cannot insert NULL into ("Y"."T"."I")  4. SQL> insert into

11、60;t values(1,10);  5. ERROR at line 1:  6. ORA-00001: unique constraint (Y.PK_I) violated  可以看到全部报错,此时主键不能为空或重复查看是否建立索引1. SQL> select index_name from user_indexes;  2. INDEX_NAME  3. -  4. PK_I &#

12、160;把约束关闭再次做同样的操用1. SQL> alter table t disable novalidate constraint pk_i;  2. Table altered.  3. SQL> insert into t values('',10);  4. 1 row created.  5. SQL> insert into t 

13、;values(1,10);  6. 1 row created.  7. SQL> commit;  8. Commit complete.  9. SQL> select * from t;  10.          I          V  11.

14、 - -  12.          1          2  13.          3          4  14.     &#

15、160;              10  15.          1         10  16. SQL>  select index_name from user_indexes;  

16、17. no rows selected  可见当把约束关闭后就可以何意给表插数据了,而具索引也自动删除了。现在激活约束1. SQL> alter table t enable validate constraint pk_i;  2. alter table t enable validate constraint pk_i  3. ERROR at line 1: &

17、#160;4. ORA-02437: cannot validate (SYS.PK_I) - primary key violated  因为表中主键有相同的值所以不能恢复到enable validate状态了再次测试回复到enable novalidate1. SQL> alter table t enable novalidate constraint pk_i;  2. alter table 

18、;t enable validate constraint pk_i  3. ERROR at line 1:  4. ORA-02437: cannot validate (SYS.PK_I) - primary key violated  也失败了,因为表中主键有了空值和相同的值,所以恢复不到enable validate状态,但enable novalidate不检查旧数据所以应该还能恢复到enable nov

19、alidate.要想恢复到enable novalidate必须建立主键索引(关闭约束时自动删除的那个索引)如下:1. SQL> create index pk_i on t(i);  2. Index created.  然后恢复到enable disvalidate,以后再插数据不能为空,主键也不能重复了.1. SQL> alter table t enable novalidate constraint pk_i; &

20、#160;2. Table altered.  3. SQL> insert into t values(1,14);  4. insert into t values(1,14)  5. ERROR at line 1:  6. ORA-00001: unique constraint (SYS.PK_I) violated  2.修正约束数据当给一个表作主键约束时,因为已存数据

21、不满足约束规则,会提示错误信息,些时必须对数据进行修正要修正数据先找出不满足约束的数据如下表,有不满足约束的数据1. SQL> select * from t;  2.          I          V  3. - -  4.        &

22、#160; 1          2  5.          3          4  6.          15      

23、;   12  7.          15         10  如果一个表数据量多可通过如下方法查找1. SQL> alter table t drop constraint pk_i;    2. Table altered.  3.

24、SQL>conn y / 123  4. SQL> $ORACLE_HOME/rdbms/admin/utlexcpt.sql  5. Table created.  6. SQL> alter table t add constraint pk_i primary key (i) exceptions into exceptions;  7. select 

25、* from t where rowid in (select row_id from exceptions)  8.          I          V  9. - -  10.        

26、60; 15         12  11.          15         10  找到了重复的记录修正1. SQL>update t set i=10 where v=12;  2. SQL> s

27、elect * from t;  3.          I          V  4. - -  5.          1          2

28、  6.          3          4  7.          10         12  8.        

29、  15         10  再建主键约束1. alter table t add constraint pk_i primary key (i)  2. Table altered.  成功了!二:分区表管理作用:将在张大表的数据分布到多个表分区段,不同分区彼此独立,从而提高了表的可用性和性能种类:范围分区,散列分区(使用HASH算法,最常使

30、用),列表分区,范围/散列组合分区,范围/列表组合分区范围分区表创建范围分区表1. create table t(v number,b number)  2. partition by range(v) (  3. partition p1 values less than ('11') tablespace test1,  4. partition p2 values less&

31、#160;than ('21') tablespace test2);  增加与删除分区#增加分区1. alter table t add partition p3 values less than ('31') tablespace test3;  2. alter table t drop partition p3  一个

32、时间分区的例子1. alter session set nls_data_lanage=AMERICAN;  2. alter session set nls_data_format='DD-MON-YYYY' 3. create table t(v_date date,b number)  4. partition by range(v_date)(  5. partition p1 values&

33、#160;less than ('01-APR-2009') tablespace test1,  6. partition p2 values less than ('01-JUN-2009') tablespace test2);  2.散列分区表(最常用)创建1. create table t1(  2. v number,b number)  3. part

34、ition by hash(v)  4. (partition p1 tablespace test1,  5. partition p2 tablespace test2);  增加分区1. alter table t add partition p3 tablespace test3;  删除分区1. alter table t drop co

35、alesce partition;  3.列表分区建列表分区1. create table t(  2. v varchar2(10),  3. b number  4. )partition by list(v)  5. (partition p1 values('a','b') tablespace test1,  6. partition p2 values(

36、'c','d') tablespace test2);  #插入数据1. SQL> insert into t values('a',10);  2. SQL> insert into t values('d',20); #注意,插入数据时第一个字段只能为a,b,c,d1. SQL> insert into t values('

37、f',30);  2. ERROR at line 1:  3. ORA-14400: inserted partition key does not map to any partition  #查询1. select * from t;  2. select * from t partition(p1);  3. select 

38、;* from t partition(p2);  4. select * from t where v=XXX  增加分区1. alter table t add partition p3 values('31','32') tablespace test3;  删除分区1. alter table t drop 

39、partition p3  4.范围/散列组合分区建立散列组合分区1. create table t(  2. v number,b number)  3. partition by range(v)  4. subpartition by hash(b) subpartitions 2  5. store in (test1,test2)(  6. partition p1 

40、values less than ('11'),  7. partition p2 values less than ('21');  查询1. select * from t;  2. select * from t partition(p1);  3. select * from t where . &

41、#160;增加主分区和子分区1. alter table t add partition p3 values less than ('31') tablespace test3;  2. alter table t modify partition p3 add subpartition;  删除分区1. alter table t coal

42、esce partition;  2. alter table t modify partition p1 coalesce subpartition;  5.范围/列表组合分区创建1. create table t(  2. v number,b number)  3. partition by range(v)  4. subpartition by list(b) 

43、60;5. (partition p1 values less than ('11') tablespace test1(  6. subpartition p1_1 values('1','3'),  7. subpartition p1_2 values('5','6')  8. ),  9. partition p2 values 

44、less than ('21') tablespace test2(  10. subpartition p2_1 values('13','14'),  11. subpartition p2_2 values('15','16')  12. );  查询1. select * from t  2. select * from

45、 t partition(p1)  3. select * from t subpartition(p1_1)  4. select * from t where .  5. select segment_name,partition_name,tablespace_name  6. from user_segments where segment_name='T'  增

46、加分区和子分区1. alter table t add partition p3 values less than ('31') tablespace test3(  2. subpartition p3_1 values('25','26'),  3. subpartition p3_2 values('22','23');  4. alter table t modify partition r3  5. add subpartition r3_3&#

温馨提示

  • 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
  • 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
  • 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
  • 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
  • 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
  • 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
  • 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。

评论

0/150

提交评论