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.

 

SQL>

Go to part 5