Create unique constraint for future data

[rkonda99]

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

[stecal]

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

[stecal]

To get around the problem, create an index first, then the constraint.

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

[rkonda99]

Hi Stecal,
Thank you so much. It worked.
Ramesh