-
Create unique constraint for future data
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
-
Thank you so much
Hi Stecal,
Thank you so much. It worked.
Ramesh
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|