Constraints (Part 2) - ORA-00001 and ORA-02431

Go to part 1

The example below was checked on Oracle 9, 10 and 11. First a table was created:

SQL> create table my_table

  2  (my_column number)

  3  /

Table created.

SQL>

The number of constraints in the database was checked:

SQL> select count(*) from dba_constraints

  2  /

  COUNT(*)

----------

      7658

SQL>

A unique index was added to ensure that no value appeared more than once in my_column:

SQL> create unique index my_uk

  2  on my_table (my_column)

  3  /

Index created.

SQL>

The number of constraints was checked again but it had not changed. This suggested that creating the unique index had not added an extra constraint to the database:

SQL> select count(*) from dba_constraints

  2  /

  COUNT(*)

----------

      7658

SQL>

An attempt was made to insert duplicate values into my_column:

SQL> insert into my_table values (1)

  2  /

1 row created.

SQL>

The attempt failed but the error message displayed suggested that creating the unique index had, in fact, added a constraint:

SQL> insert into my_table values (1)

  2  /

insert into my_table values (1)

*

ERROR at line 1:

ORA-00001: unique constraint (ANDREW.MY_UK) violated

SQL>

The table was queried to check that the unique index had worked as expected:

SQL> select * from my_table

  2  /

 MY_COLUMN

----------

         1

SQL>

If this happens and you decide that you genuinely need to allow duplicates in my_column, how would you do this? You cannot disable the constraint because it does not exist:

SQL> alter table my_table

  2  disable constraint my_uk

  3  /

alter table my_table

*

ERROR at line 1:

ORA-02431: cannot disable constraint (MY_UK) - no

such constraint

SQL>

Although this constraint does not appear in DBA_CONSTRAINTS, you can see what is enforcing it by looking for an index with that name in DBA_INDEXES. It will have UNIQUENESS set to UNIQUE:

SQL> select uniqueness from dba_indexes

  2  where index_name = 'MY_UK'

  3  /

UNIQUENES

---------

UNIQUE

SQL>

So, to allow duplicate values in my_column, you just need to drop the unique index (and replace it with an ordinary one if you find that an index is still required).

Go to part 3