问:
最近有个项目提出新需求,其中一个业务表的主键字段需要修改;
麻烦的是,对于新的主键字段,表的已有数据中包含重复数据,
请问这个需求可以实现吗?具体该如何操作?
答:
首先这个需求肯定是可以实现的,在具体演示如何实现之前,我们先回顾一下针对Oracle约束的一组关键字 Ena ble/Disable/Validate/Novalidate。一、Enable/Disable/Validate/Novalidate
| Validate | Novalidate | ||
已有记录 | 新增/删除记录 | 已有记录 | 新增/删除记录 | |
Enable | Yes | Yes | No | Yes |
Disable | Yes | No | No | No |
Validate确保已有数据符合约束;
Novalidate不必考虑已有数据是否符合约束。
除非Novalidate被指定,Enable默认Validate;
除非Validate被指定,Disable默认Novalidate。
Enable Validate与Enable相同,检查已有记录和新增记录,确保都符合约束;
Enable Novalidate 允许已有记录不必满足约束条件,但新增/修改的记录必须满足;
Disable Validate禁用约束,删除约束上的索引,不允许修改任何被约束的记录;
Disable Novalidate与Disable相同,禁用约束,删除约束上的索引,且允许修改被约束的记录。
针对我们的问题,我们需要关注的是 Enable Novalidate,也就是说 允许已有记录不必满足约束条件,但新增 / 修改的记录必须满足。
二、创建测试表
点击(此处)折叠或打开
- SQL> select * from v$version;
-
- BANNER
- ----------------------------------------------------------------
- Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
- PL/SQL Release 10.2.0.4.0 - Production
- CORE 10.2.0.4.0 Production
-
- TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
- NLSRTL Version 10.2.0.4.0 - Production
-
- SQL> create table hoegh(x number,y number,z number,
- 2 constraint pk_hoegh primary key(x,y));
-
- Table created
-
- SQL> insert into hoegh values(1,1,1);
-
- 1 row inserted
-
- SQL> insert into hoegh values(1,2,1);
-
- 1 row inserted
-
- SQL> commit;
-
- Commit complete
-
- SQL>
- SQL> select * from hoegh;
-
- X Y Z
- ---------- ---------- ----------
- 1 1 1
- 1 2 1
三、针对非主键约束的Enable Novalidate
我们以check约束为例看一下如何使用 Enable Novalidate关键字。点击(此处)折叠或打开
- SQL> alter table hoegh add constraint check_z check(z>1) enable validate;
-
- alter table hoegh add constraint check_z check(z>1) enable validate
-
- ORA-02293: 无法验证 (HOEGH.CHECK_Z) - 违反检查约束条件
-
- SQL> alter table hoegh add constraint check_z check(z>1) enable novalidate;
-
- Table altered
-
- SQL> insert into hoegh values(2,1,1);
-
- insert into hoegh values(2,1,1)
-
- ORA-02290: 违反检查约束条件 (HOEGH.CHECK_Z)
-
- SQL>
同样的,外键约束也可以参考上述语句来添加或修改,那么主键约束可以吗?
四、针对主键约束的Enable Novalidate
1.查看当前约束和索引
点击(此处)折叠或打开
- SQL> select constraint_name,constraint_type FROM user_constraints where table_name='HOEGH';
-
- CONSTRAINT_NAME CONSTRAINT_TYPE
- ------------------------------ ---------------
- PK_HOEGH P
-
- SQL>
- SQL> select index_name,index_type,uniqueness from user_indexes where table_name='HOEGH';
-
- INDEX_NAME INDEX_TYPE UNIQUENESS
- ------------------------------ --------------------------- ----------
- PK_HOEGH NORMAL UNIQUE
-
- SQL>
2.删除当前主键约束和索引
点击(此处)折叠或打开
- SQL> alter table hoegh drop constraint PK_HOEGH cascade drop index;
-
- Table altered
-
- SQL> select constraint_name,constraint_type FROM user_constraints where table_name='HOEGH';
-
- CONSTRAINT_NAME CONSTRAINT_TYPE
- ------------------------------ ---------------
-
- SQL> select index_name,index_type,uniqueness from user_indexes where table_name='HOEGH';
-
- INDEX_NAME INDEX_TYPE UNIQUENESS
- ------------------------------ --------------------------- ----------
-
- SQL>
3.尝试添加主键约束并设置Enable Novalidate
点击( 此处 )折叠或打开- SQL>
- SQL> alter table hoegh add constraint PK_HOEGH primary key(x,z) enable novalidate;
-
- alter table hoegh add constraint PK_HOEGH primary key(x,z) enable novalidate
-
- ORA-02437: 无法验证 (HOEGH.PK_HOEGH) - 违反主键
-
- SQL>
- SQL> select * from hoegh;
-
- X Y Z
- ---------- ---------- ----------
- 1 1 1
- 1 2 1
-
- SQL>
虽然我们设置了 Novalidate ,但是和我们预想的结果不一致,数据库仍 然检验了已有数据。
我们知道主键约束=唯一约束+非空约束,那么这个是不是和唯一约束有关系呢?
接下来,我们尝试添加一个唯一约束,同时设置 Enable Nov alidate。
点击( 此处 )折叠或打开
- SQL> alter table hoegh add constraint unq_xz unique(x,z) enable novalidate;
-
- alter table hoegh add constraint unq_xz unique(x,z) enable novalidate
-
- ORA-02299: 无法验证 (HOEGH.UNQ_XZ) - 找到重复关键字
-
- SQL>
那么如何解决这个问题呢?接着往下看。
4.创建普通索引,然后添加主键约束并设置Enable Novalidate
我们 可以在创建主键前先给该字段建个普通 非唯一索引,这样建主键时系统就会自动利用这个普通索引了。点击( 此处 )折叠或打开
- SQL>
- SQL> create index idx_xz on hoegh(x,z);
-
- Index created
-
- SQL> alter table hoegh add constraint PK_HOEGH primary key(x,z) enable novalidate;
-
- Table altered
-
- SQL> insert into hoegh values(1,3,1);
-
- insert into hoegh values(1,3,1)
-
- ORA-00001: 违反唯一约束条件 (HOEGH.PK_HOEGH)
-
- SQL> select * from hoegh;
-
- X Y Z
- ---------- ---------- ----------
- 1 1 1
- 1 2 1
-
- SQL>
- SQL> insert into hoegh values(1,3,2);
-
- 1 row inserted
-
- SQL> commit;
-
- Commit complete
-
- SQL>
- SQL> select * from hoegh;
-
- X Y Z
- ---------- ---------- ----------
- 1 1 1
- 1 2 1
- 1 3 2
-
- SQL>
~~~~~~~ the end~~~~~~~~~
hoegh
2016.06.17
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30162081/viewspace-2120367/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30162081/viewspace-2120367/