Create unique constraint for future data
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Create unique constraint for future data

  1. #1
    Join Date
    Apr 2002
    Posts
    61

    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

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    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

  3. #3
    Join Date
    May 2002
    Posts
    2,645
    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

  4. #4
    Join Date
    Apr 2002
    Posts
    61

    Smile 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
  •  



Click Here to Expand Forum to Full Width