I have a table with non-unique data in a column. However, moving forward, we would like to enter only unique data. Is it possible to build a unique key on this column with out doing anything to the existing non-unique data.
Thanks,
Ramesh
Printable View
I have a table with non-unique data in a column. However, moving forward, we would like to enter only unique data. Is it possible to build a unique key on this column with out doing anything to the existing non-unique data.
Thanks,
Ramesh
Code:SQL> select * from emp2;
EMPNO ENAME
---------- ----------
7999 CRAFT
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER
13 rows selected.
SQL> insert into emp2 values (7999, 'CRAFT');
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> alter table emp2
2 add (constraint u_empno
3 unique (empno));
add (constraint u_empno
*
ERROR at line 2:
ORA-02299: cannot validate (SCOTT.U_EMPNO) - duplicate keys found
To get around the problem, create an index first, then the constraint.
Code:SQL> create index idx_emp2_empno on emp2(empno);
Index created.
SQL> alter table emp2
2 add constraint u_empno unique(empno)
3 enable novalidate;
Table altered.
SQL> select * from emp2;
EMPNO ENAME
---------- ----------
7999 CRAFT
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER
7999 CRAFT
7999 CRAFT
7999 CRAFT
7999 CRAFT
17 rows selected.
SQL> insert into emp2 values (7999, 'CRAFT');
insert into emp2 values (7999, 'CRAFT')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.U_EMPNO) violated
Hi Stecal,
Thank you so much. It worked.
Ramesh