Tuesday, October 15, 2013

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

No comments: