Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: ORA-00001: unique constraint violated

Re: ORA-00001: unique constraint violated

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Wed, 17 Nov 2004 07:06:24 +1100
Message-ID: <419a5dcc$0$24376$afc38c87@news.optusnet.com.au>


Michael wrote:
> Hello Howard,
>
> yes indeed I did not take the necessary time to write a round and
> understandable email ... sorry about .. just and that was what i wanted
> to say .. look at a table .. a primary and also a unique index is
> enforced with a contraint .... which you can disable or enable ... and
> yes the index will then be droped or recreated ...
> Clear enough?

Clear enough, I suppose, but wrong as it stands. "A primary and unique index is enforced with a constraint"... not true. A primary or unique CONSTRAINT is enforced with an index (unique or non-unique, either will work), but not the other way around.

If I 'create unique index on emp(empno)', that does NOT mean I suddenly have a unique or primary key constraint on the table. It means I have a unique index, that's all.

Logically, the *effect* of a unique index on the EMPNO column is practically the same as putting a unique or primary key constraint on the column (though the index won't spot attempts to insert NULL values, whereas a primary key constraint will). But that the outcome is logically the same doesn't mean you can legitimately equate the two mechanisms. You cannot, for example, disable an index. Or find out where a constraint is stored. Or use DBA_INDEXES to check which type of constraint has been created. They are different beasts entirely, and you must think of them as such.

This statement is also wrong: "a constraint...which you can disable or enable and the index will then be dropped or recreated". Not true since about version 8.0 and the introduction of deferrable constraints. If I create a primary key constraint which is 'DEFERRABLE INITIALLY IMMEDIATE', then Oracle will create a NON-UNIQUE index to help enforce it. And if I then disable or re-enable that constraint, the index is neither dropped nor re-created. The same goes for unique constraints.

And in 9i, I might add, you now have total control over whether or not an index is dropped when disabling constraints, since the syntax is now 'alter table X drop constraint Y keep index'. You can even ask to keep the unique index that might be being used to help enforce a unique or primary key constraint, though again it makes little logical sense to do so.

A little example to sum up:

SQL> connect scott/tiger
Connected.
SQL> create table e as select * from emp; Table created.

SQL> alter table e add constraint epk primary key(empno); Table altered.

[And because I didn't specify this constraint should be deferrable, it should cause a unique index to be created:]

SQL> select index_name, uniqueness from user_indexes;

INDEX_NAME                     UNIQUENES
------------------------------ ---------
EPK                            UNIQUE

SQL> alter table e disable constraint epk keep index; Table altered.

SQL> select index_name, uniqueness from user_indexes;

INDEX_NAME                     UNIQUENES
------------------------------ ---------
EPK                            UNIQUE

SQL> insert into e (empno) values (7934); insert into e (empno) values (7934)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.EPK) violated

[So, disabling the constraint but asking to keep the index is a silly thing to do, because logically it means uniqueness is still enforced]

SQL> alter table e enable constraint epk; Table altered.

SQL> insert into e (empno) values (7934); insert into e (empno) values (7934)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.EPK) violated

[The result, in fact, is identical to what happens when the constraint is explicitly enabled].

SQL> alter table e disable constraint epk drop index; Table altered.

SQL> insert into e (empno) values (7934); 1 row created.

[But here, I can disable the constraint AND drop the index -which would happen by default- and a violating row can be inserted. Now for a counter example:]

SQL> drop table e;
Table dropped.

SQL> create table e as select * from emp; Table created.
SQL> alter table e add constraint epk primary key(empno) deferrable; Table altered.

[Same table as before, but a DEFERRABLE -though not actually deferred- constraint. It makes a big difference...]

SQL> select index_name, uniqueness from user_indexes;

INDEX_NAME                     UNIQUENES
------------------------------ ---------
EPK                            NONUNIQUE

[...because you might notice that my primary key is now enforced with a non-unique index].

SQL> insert into e (empno) values (7934); insert into e (empno) values (7934)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.EPK) violated

[Yet the constraint is just as effective as it was with a unique index. The attempt to insert a duplicate value is still detected and prevented].

SQL> alter table e disable constraint epk; Table altered.

SQL> select index_name, uniqueness from user_indexes;

INDEX_NAME                     UNIQUENES
------------------------------ ---------
EPK                            NONUNIQUE

[And when I disable the constraint, the index is NOT dropped, because it doesn't need to be to make sense of the request, effectively, to permit violating rows into the table].

So you can't say: "... you can disable or enable ... and the index will then be droped or recreated", because it's not true.

Regards
HJR
> Greetings,
> Michael
>
Received on Tue Nov 16 2004 - 14:06:24 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US