Home » SQL & PL/SQL » SQL & PL/SQL » Preference: unique index or unique constraint?
Preference: unique index or unique constraint? [message #218316] Wed, 07 February 2007 13:17 Go to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Just curious if anyone had any compelling reason, with all other things being equal and for example on a brand new project, to use a unique index rather than a unique constraint (which of course creates a uniqueue index) to enforce uniqueness on one or more columns?

I generally prefer to use the constraint, because to me it is more "explicit". Also, it keeps all constraints showing up nicely in all_constraints.

But does anyone use indexes without constraints instead, and if so, why do you choose to do so?

MYDBA@orcl > create table test(a number, b number);

Table created.

MYDBA@orcl > create unique index test_idx on test(a,b);

Index created.

MYDBA@orcl > insert into test values (1,1);

1 row created.

MYDBA@orcl > insert into test values (1,1);
insert into test values (1,1)
*
ERROR at line 1:
ORA-00001: unique constraint (MYDBA.TEST_IDX) violated


MYDBA@orcl > select constraint_name from user_constraints where table_name = 'TEST';

no rows selected

MYDBA@orcl > select index_name from user_indexes where table_name = 'TEST';

INDEX_NAME
------------------------------
TEST_IDX

MYDBA@orcl > alter table test add constraint test_uc unique(a,b);

Table altered.

MYDBA@orcl > insert into test values (1,1);
insert into test values (1,1)
*
ERROR at line 1:
ORA-00001: unique constraint (MYDBA.TEST_UC) violated


MYDBA@orcl > select constraint_name from user_constraints where table_name = 'TEST';

CONSTRAINT_NAME
------------------------------
TEST_UC

MYDBA@orcl > select index_name from user_indexes where table_name = 'TEST';

INDEX_NAME
------------------------------
TEST_UC


Re: Preference: unique index or unique constraint? [message #218319 is a reply to message #218316] Wed, 07 February 2007 13:30 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> But does anyone use indexes without constraints
Not me Smile
I do not find any use for just unique indexes.
Constraints on the other hand are useful to create references and are deferrable.

[Updated on: Wed, 07 February 2007 13:31]

Report message to a moderator

Re: Preference: unique index or unique constraint? [message #218322 is a reply to message #218319] Wed, 07 February 2007 14:08 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Creation of unique constraints (or primary keys) can re-use any suitable existing indexes - they don't always create their own new ones. Disabling a unique constraint usually causes it's index to be dropped - but at least the constraint still remains and re-enabling withh re-create the index. Mysteriously missing indexes are something much harder to recover from...

SQL> create table ABC (col1 number, col2 varchar2(10));

Table created.

SQL> create index ABC_ix1 on ABC(col1);

Index created.

SQL> alter table ABC add constraint ABC_pk primary key (col1) using index;

Table altered.

SQL> select index_name, table_name, uniqueness
  2  from user_indexes where table_name = 'ABC';

INDEX_NAME                     TABLE_NAME                     UNIQUENES
------------------------------ ------------------------------ ---------
ABC_IX1                        ABC                            NONUNIQUE

SQL> -- 1st row succeeds
SQL> insert into ABC values (1, 'hello');

1 row created.

SQL> -- duplicate fails
SQL> insert into ABC values (1, 'world');
insert into ABC values (1, 'world')
*
ERROR at line 1:
ORA-00001: unique constraint (DEV.ABC_PK) violated

SQL> alter table ABC disable constraint ABC_pk;

Table altered.

SQL> -- OK - now insert works again!
SQL> insert into ABC values (1, 'world');

1 row created.

SQL> -- But no change in indexes present...
SQL> select index_name, table_name, uniqueness
  2  from user_indexes where table_name = 'ABC';

INDEX_NAME                     TABLE_NAME                     UNIQUENES
------------------------------ ------------------------------ ---------
ABC_IX1                        ABC                            NONUNIQUE


Of course if an index gets dropped due to disabling a constraint, you loose the storage information, so it needs to be included when re-ebabling the constraint...


Re: Preference: unique index or unique constraint? [message #218365 is a reply to message #218322] Wed, 07 February 2007 20:41 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
An ENABLED unique constraint must be enforced by an index, but the index does not have to be unique. If the constraint is deferrable, the index must be non-unique.

It is also possible to have additional suffix columns in the index that are not in the constraint.

Note in both of these cases that you may incur additional overheads in enforcing the constraint because it is not guaranteed by the index.

One last thing: you cannot create a unique constraint on a function-based index - a unique function-based index is as good as you can do.

Ross Leishman
Re: Preference: unique index or unique constraint? [message #218541 is a reply to message #218316] Thu, 08 February 2007 10:26 Go to previous message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Thanks for the feedback. I'm definitely not seeing anything that would cause me to change my mind (from using constraints). These unique indexes in this system I'm looking at will be changed in the next version.

Good to know on the function based index aspect of uniqueness. I'll have to keep that in mind in case it ever comes up.
Previous Topic: remove duplicates in a query
Next Topic: Truncate date string to mm/yyyy
Goto Forum:
  


Current Time: Mon Dec 05 06:42:50 CST 2016

Total time taken to generate the page: 0.04941 seconds