Chinaunix首页 | 论坛 | 博客
  • 博客访问: 396633
  • 博文数量: 88
  • 博客积分: 10
  • 博客等级: 民兵
  • 技术积分: 1054
  • 用 户 组: 普通用户
  • 注册时间: 2012-11-07 12:23
文章分类
文章存档

2014年(88)

我的朋友

分类: Oracle

2014-06-17 14:31:10

 

--disable的问题

 

ALTER TABLE EMPLOYEES DISABLE CONSTRAINT EMP_EMP_ID_PK
*
ERROR at line 1:
ORA-02297: cannot disable constraint (HR.EMP_EMP_ID_PK) - dependencies exist


SQL> !oerr ora 02297
02297, 00000,"cannot disable constraint (%s.%s) - dependencies exist"
// *Cause: an alter table disable constraint failed becuase the table has
//      foriegn keys that are dpendent on this constraint.
// *Action: Either disable the foreign key constraints or use disable cascade

SQL> show user     
USER is "HR"
SQL> alter table employees disable constraint EMP_EMP_ID_PK cascade;

Table altered.


SQL> select constraint_name,constraint_type ,status from user_constraints where table_name='EMPLOYEES';

CONSTRAINT_NAME                C STATUS
------------------------------ - --------
EMP_LAST_NAME_NN               C DISABLED
EMP_EMAIL_NN                   C DISABLED
EMP_HIRE_DATE_NN               C DISABLED
EMP_JOB_NN                     C DISABLED
EMP_SALARY_MIN                 C DISABLED
EMP_EMAIL_UK                   U DISABLED
EMP_EMP_ID_PK                  P DISABLED
EMP_MANAGER_FK                 R DISABLED
EMP_JOB_FK                     R DISABLED
EMP_DEPT_FK                    R DISABLED

10 rows selected.

 

--关于enable的问题

 

SQL>  select constraint_name,constraint_type ,status from user_constraints where table_name='EMPLOYEES';

CONSTRAINT_NAME                C STATUS
------------------------------ - --------
EMP_LAST_NAME_NN               C ENABLED
EMP_EMAIL_NN                   C ENABLED
EMP_HIRE_DATE_NN               C ENABLED
EMP_JOB_NN                     C ENABLED
EMP_SALARY_MIN                 C ENABLED
EMP_EMAIL_UK                   U DISABLED
EMP_EMP_ID_PK                  P DISABLED
EMP_MANAGER_FK                 R DISABLED
EMP_JOB_FK                     R ENABLED
EMP_DEPT_FK                    R ENABLED

10 rows selected.

 

 

SQL> ALTER TABLE EMPLOYEES ENABLE CONSTRAINT EMP_EMP_ID_PK ;
ALTER TABLE EMPLOYEES ENABLE CONSTRAINT EMP_EMP_ID_PK
*
ERROR at line 1:
ORA-14063: Unusable index exists on unique/primary constraint key


SQL> !oerr ora 14063
14063, 00000, "Unusable index exists on unique/primary constraint key"
// *Cause:  User attempted to add or enable a primary key/unique constraint
//          on column(s) of a table on which there exists an index marked
//          Index Unusable.
// *Action: Drop the existing index or rebuild it using ALTER INDEX REBUILD

SQL> select index_name from user_indexes where table_name='EMPLOYEES';

INDEX_NAME
------------------------------
EMP_EMAIL_UK
EMP_EMP_ID_PK
EMP_DEPARTMENT_IX
EMP_JOB_IX
EMP_MANAGER_IX
EMP_NAME_IX

6 rows selected.

 

SQL> ALTER INDEX EMP_EMP_ID_PK REBUILD;

Index altered.

SQL> ALTER TABLE EMPLOYEES ENABLE CONSTRAINT EMP_EMP_ID_PK;

Table altered.

阅读(1459) | 评论(0) | 转发(0) |
0

上一篇:ORA-02030的解决

下一篇:ORA-00439的解决

给主人留下些什么吧!~~