constraint enable，disable的問題解決

 

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