Tuesday, October 22, 2013

Constraints (Part 4) - ORA-02299

Go to part 3

This was tested on an Oracle 11.2 database. As I have already said, you can add a constraint when a table is created or you can modify an existing table as shown below: 

SQL> create table tab1 as
  2  select trunc(sysdate) today from dual
  3  /
 
Table created.
 
SQL> alter table tab1 modify today unique
  2  /
 
Table altered.
 
SQL> select constraint_name from user_constraints
  2  where table_name = 'TAB1'
  3  /
 
CONSTRAINT_NAME
------------------------------
SYS_C00153494
 
SQL>

You can use a system generated name for the constraint as shown above or you can choose a name yourself as shown below:

SQL> create table tab2 as
  2  select trunc(sysdate) today from dual
  3  /
 
Table created.
 
SQL> alter table tab2
  2  modify today constraint con2 unique
  3  /
 
Table altered.
 
SQL> select constraint_name from user_constraints
  2  where table_name = 'TAB2'
  3  /
 
CONSTRAINT_NAME
------------------------------
CON2
 
SQL>

If you try to add a unique constraint to a column with duplicate values, you get an ORA-02299:

SQL> create table tab3 as
  2  select trunc(sysdate) today from dual
  3  /
 
Table created.
 
SQL> insert into tab3 select * from tab3
  2  /
 
1 row created.
 
SQL> alter table tab3
  2  modify today constraint con3 unique
  3  /
modify today constraint con3 unique
                        *
ERROR at line 2:
ORA-02299: cannot validate (ORACLE.CON3) - duplicate
keys found
 
SQL>

To get round this, you have to delete the duplicate values:

SQL> delete from tab3 a
  2  where rowid >
  3  (select min(rowid) from tab3
  4   where today = a.today)
  5  /
 
1 row deleted.
 
SQL>

Then you can add the constraint without Oracle giving you an error:

SQL> alter table tab3
  2  modify today constraint con3 unique
  3  /
 
Table altered.
 

No comments:

Post a Comment