RE: (Non)Unique Index Vs Unique Constraint

From: DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM>
Date: Sat, 17 Jan 2004 13:09:25 -0800
Message-ID: <F001.005DD211.20040117130925@fatcity.com>

Jay

That is a good one. The question is: "How is the uniqueness constraint being enforced when the index is nonunique?" Offhand I would have assumed your constraint would have been rejected since the index is nonunique -- nope. Then I would have guessed the index would have been converted to a unique index -- it isn't. Then I was skeptical whether the constraint was really being enforced, but it is. The next question is how the uniqueness is being enforced if the index is not unique.

Now, if you add some duplicate values to the table, then try to add the constraint, you receive:

alter table index_test add constraint index_test_uk1 UNIQUE(c1)

                                      *
ERROR at line 1:
ORA-02299: cannot validate (SILT.INDEX_TEST_UK1) - duplicate keys found

My guess, and this is only a guess, is that the nonunique index with a unique constraint executes the code for a unique index. I ran an insert on each version and the plan looks the same.

Of course, if you go around doing funny stuff like this, the person that eventually takes over your job duties will probably really bless your name each time they stumble onto something like a unique constraint on a nonumique index.

Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
Sent: Friday, January 16, 2004 9:14 PM
To: Multiple recipients of list ORACLE-L

All,

Please enlighten this Junior DBA.

Which method is more efficient? When should I go for option (1)?

1)NON-UNIQUE index Vs Unique Constraint
drop table index_test;
create table index_test(c1 number,c2 varchar2(20)); create index i1 on index_test(c1);
alter table index_test add constraint index_test_uk1 UNIQUE(c1);

2)UNIQUE index Vs Unique Constraint
drop table index_test;
create table index_test(c1 number,c2 varchar2(20)); create UNIQUE index i1 on index_test(c1); alter table index_test add constraint index_test_uk1 UNIQUE(c1);

Thanks in advance,
Jay
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Jay
INET: jaysingh1_at_optonline.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: DENNIS WILLIAMS
INET: DWILLIAMS_at_LIFETOUCH.COM

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------