[分享]about modify unique constraint_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方-国内领先的云技术服务提供商
»  游客             当前位置:  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
1
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 2236 | 回复: 0   主题: [分享]about modify unique constraint        下一篇 
kyle
注册用户
等级:新兵
经验:31
发帖:58
精华:0
注册:2011-12-6
状态:离线
发送短消息息给kyle 加好友    发送短消息息给kyle 发消息
发表于: IP:您无权察看 2014-12-22 11:48:22 | [全部帖] [楼主帖] 楼主

开发的提一需求要把当前唯一约束从从三个列改为两列,发现实际改动还没那么容易记录一下。

1,首先没有alter constraint或alter table modify constraint修改列的语法,只能删掉重建

SQL> select constraint_name,table_name,column_name from user_cons_columns where constraint_name='UK_PROJECT_SCORE';
CONSTRAINT_NAME TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------ ------------------------------
UK_PROJECT_SCORE ICME_PROJECT_SCORE IC_CODE
UK_PROJECT_SCORE ICME_PROJECT_SCORE SCORE_ACTIVITY_DATE
UK_PROJECT_SCORE ICME_PROJECT_SCORE SUBJECT_ID
SQL> ALTER TABLE ICME.ICME_PROJECT_SCORE
2 DROP CONSTRAINT UK_PROJECT_SCORE;
Table altered.
SQL> ALTER TABLE ICME.ICME_PROJECT_SCORE ADD (
2 CONSTRAINT UK_PROJECT_SCORE
3 UNIQUE (IC_CODE, SUBJECT_ID)
4 ENABLE VALIDATE);
ALTER TABLE ICME.ICME_PROJECT_SCORE ADD (
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL> select object_name,object_type from user_objects where object_name='UK_PROJECT_SCORE';
OBJECT_NAME OBJECT_TYPE
----------------------------------------------------------------- -------------------
UK_PROJECT_SCORE INDEX
SQL> select index_name,UNIQUENESS,index_type,status from user_indexes where index_name='UK_PROJECT_SCORE';
INDEX_NAME UNIQUENES INDEX_TYPE STATUS
------------------------------ --------- --------------------------- --------
UK_PROJECT_SCORE UNIQUE NORMAL VALID
SQL> select table_name,column_name from user_ind_columns where index_name='UK_PROJECT_SCORE';
TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------
ICME_PROJECT_SCORE IC_CODE
ICME_PROJECT_SCORE SCORE_ACTIVITY_DATE
ICME_PROJECT_SCORE SUBJECT_ID
Tip:建唯一约束时,默认会自动在列上创建一个唯一索引,通常删除约束时会自动删除索引,但发现并不完全是这样,这次唯一索引就没有删除。
2,唯一索引问题一会儿再处理,先临时改名,创建新唯一约束
SQL> alter index UK_PROJECT_SCORE rename to IDX_ICME_PRO_SC_ICDATSUB;
Index altered.
SQL> ALTER TABLE ICME.ICME_PROJECT_SCORE ADD (
2 CONSTRAINT UK_PROJECT_SCORE
3 UNIQUE (IC_CODE, SUBJECT_ID)
4 ENABLE VALIDATE);
CONSTRAINT UK_PROJECT_SCORE
ERROR at line 2:
ORA-02299: cannot validate (ICME.UK_PROJECT_SCORE) - duplicate keys found
SQL> ALTER TABLE ICME.ICME_PROJECT_SCORE ADD (
2 CONSTRAINT UK_PROJECT_SCORE
3 UNIQUE (IC_CODE, SUBJECT_ID)
4 ENABLE NOVALIDATE);
CONSTRAINT UK_PROJECT_SCORE
ERROR at line 2:
ORA-02299: cannot validate (ICME.UK_PROJECT_SCORE) - duplicate keys found


Tip:无论是否对已存在数据验证这种方法创建时都会失败,就是因为默认唯一索引创建失败的问题,因为原来唯一建立在三个字段,两个字段确实存在不唯一的情况,也和开发的确认可以只对新数据验证
3,解决这个问题就是先创建一个非唯一索引,在创建唯一约束时指定索引并用ENABLE NOVALIDATE(相关可以看http://www.anbob.com/?p=646),加速索引创建用到了并行,nologing

SQL> alter session enable parallel ddl;
Session altered.
SQL> create index idx_project_score_uk on icme_project_score(ic_code,subject_id) online parallel 8 nologging;
Index created.
SQL> ALTER TABLE ICME.ICME_PROJECT_SCORE ADD (
2 CONSTRAINT UK_PROJECT_SCORE
3 UNIQUE (IC_CODE, SUBJECT_ID) using index idx_project_score_uk
4 ENABLE NOVALIDATE);
Table altered.
tip: 1 seconds
SQL> select index_name,logging,UNIQUENESS,degree,STATUS from user_indexes where index_name='IDX_PROJECT_SCORE_UK';
INDEX_NAME LOG UNIQUENES DEGREE STATUS
------------------------------ --- --------- ---------------------------------------- --------
IDX_PROJECT_SCORE_UK NO NONUNIQUE 8 VALID
SQL> alter index IDX_PROJECT_SCORE_UK logging;
Index altered.
SQL> alter index IDX_PROJECT_SCORE_UK noparallel;
Index altered.
SQL> select index_name,logging,UNIQUENESS,degree,STATUS,generated from user_indexes where index_name='IDX_PROJECT_SCORE_UK';
INDEX_NAME LOG UNIQUENES DEGREE STATUS G
------------------------------ --- --------- ---------------------------------------- -------- -
IDX_PROJECT_SCORE_UK YES NONUNIQUE 1 VALID N


4,再来解决上面唯一索引的问题,唯一索引也有唯一约束的作用,所以现在两个唯一显然冲突不符合业务.no-unique index convert unique index,no way!,只能重建或在线重定义

SQL> drop index IDX_ICME_PRO_SC_ICDATSUB;
Index dropped.


还是原来的方法创建一个普通索引,步骤不再重复

note:


并行创建的索引extents会更多,所以在一致读时IO就会相对多些

 Related Posts:
No related posts found!


--转自 北京联动北方科技有限公司




赞(0)    操作        顶端 
总帖数
1
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码: 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论