--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.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23718752/viewspace-747180/，如需转载，请注明出处，否则将追究法律责任。