I am trying to add unique key constraints on SCOTT.EMP.ENAME column and getting error:
ALTER TABLE EMP ADD
CONSTRAINT EMP_UK UNIQUE (ENAME) ENABLE NOVALIDATE;
Error starting at line 5 in command:
ALTER TABLE EMP ADD
CONSTRAINT EMP_UK UNIQUE (ENAME) NOVALIDATE
Error report:
SQL Error: ORA-02299: cannot validate (SCOTT.EMP_UK) - duplicate keys found
02299. 00000 - "cannot validate (%s.%s) - duplicate keys found"
*Cause: an alter table validating constraint failed because the table has
duplicate key values.
*Action: Obvious
This is understandable. I have duplicate values in ENAME columns and when I am saying to create a Unique constraint on this column, Oracle tries to create a Unique Index, and fails even we are saying Enable and No-validate existing values.
Solution 1 : The reason of failure is Oracle create Unique Index for a Unique constraints. If Oracle is unable to create such Index, We can try to support Uniqueness of values using Non-Unique Index by creating a Index on ENAME column.
Create index scott.ename_uk on scott.emp(ename) compute statistics;
SELECT index_name,uniqueness FROM USER_INDEXES WHERE table_NAME='EMP';
INDEX_NAME UNIQUENESS
------------------------------ ----------
PK_EMP UNIQUE
ENAME_UK NONUNIQUE
Now! A Non-Unique Index is present on ENAME column, so when we proceed with constraint creation statement, it is successful.
SELECT index_name,uniqueness FROM USER_INDEXES WHERE table_NAME='EMP';
ALTER TABLE EMP succeeded.
Inserting a duplicate values fails:
Insert into emp(empno,ename) values (1111,'WARD')
Error starting at line 4 in command:
Insert into emp(empno,ename) values (1111,'dd')
Error report:
SQL Error: ORA-00001: unique constraint (SCOTT.EMP_UK) violated
00001. 00000 - "unique constraint (%s.%s) violated"
*Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.
For Trusted Oracle configured in DBMS MAC mode, you may see
this message if a duplicate entry exists at a different level.
*Action: Either remove the unique restriction or do not insert the key.
Solution 2:
In solution 1, we create a Non-Unique index on ENAME column separately. There is another option, in which we have no need to create a index separately. In this we will mention USING INDEX clause in ADD CONSTRAINT statement.
Alter table emp add
Constraint emp_uk unique(ENAME) using index (create index emp_uk on emp(ename)) enable novalidate;
Unique Index and Non-Unique index creation.
Whenever, We create a table and add either Unique constraint or a Primary key constraint and we do not specify any Index in constraint addition statement (USING INDEX Clause) than along with constraint definition and an Index is created which is by default a Unique index.
E.g
SQL> Create table Scott.t3 as select level id1 from dual connect by level<=12;
Table created.
SQL> Alter Table scott.t3 add constraint t3_pk primary key(id1);
Table altered.
SQL> select index_name,uniqueness from user_indexes where index_name='T3_PK';
INDEX_NAME UNIQUENES
------------------------------ ---------
T3_PK UNIQUE
SQL> alter table t3 drop constraint T3_PK;
Table altered.
SQL> select index_name,uniqueness from user_indexes where index_name='T3_PK';
no rows selected
SQL> ALTER TABLE T3 ADD CONSTRAINT T3_PK PRIMARY KEY(ID1) USING INDEX(CREATE INDEX T3_PK ON T3(ID1));
Table altered.
SQL> select index_name,uniqueness from user_indexes where index_name='T3_PK';
INDEX_NAME UNIQUENES
------------------------------ ---------
T3_PK NONUNIQUE
In second , Alter table statement, We have guided Oracle to create a constraint using a Index. Now this time Oracle has not created Unique Index.
Any comment/Question is welcome....
