REPOST: Re: 2 overlapping indexes

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Fri, 25 Jan 2002 16:37:17 -0000
Message-ID: <9$--$%%%%----$%__$@news.noc.cabal.int>

"miker" <mdent12_at_none.com> wrote in message news:l9v25ukv31dq4uooc17h3ln60b7imb1o6m_at_4ax.com...
> On Fri, 25 Jan 2002 09:39:39 -0000, Norman Dunbar
> <Norman.Dunbar_at_lfs.co.uk> wrote:
>
> >I must be missing the point here :
> >
> >id A,B,C are unique then surely A,B,C,D *must* also be unique if the
> >columns are in that order.
>
> Record 1 ABCD = X 1 Y 2
> Record 2 ABCD = X 1 Y 3
>
> ABCD is unique but ABC is not. If you put a unique index on ABCD then
> you do not enforce ABC uniqueness. I think the guy wants to enforce
> unique constrant AND speed up retrieval of ABCD record.

It does enforce the constraint.

SQL> select * from unique_demo;

COL1                       COL2 COL3             COL4
-------------------- ---------- ---------- ----------
x                             1 y                   2
x                             1 y                   3

SQL> create unique index idx on unique_demo(col1,col2,col3); create unique index idx on unique_demo(col1,col2,col3)

                           *

ERROR at line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

Like Norman I can't for the life of me see the point of adding col4 as the statement is that the first three columns are unique. Unless I have misread the OP of course.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

******************************************

========= WAS CANCELLED BY =======:
From: "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk>
Control: cancel <3c5189c0$0$8506$ed9e5944_at_reading.news.pipex.net>
Subject: cmsg cancel <3c5189c0$0$8506$ed9e5944_at_reading.news.pipex.net>
Date: Sun, 27 Jan 2002 23:20:52 GMT
Message-ID: <cancel.3c5189c0$0$8506$ed9e5944_at_reading.news.pipex.net>
X-No-Archive: yes
Newsgroups: microsoft.test,alt.flame.niggers,comp.databases.oracle.server
NNTP-Posting-Host: w088.z064003087.lax-ca.dsl.cnc.net 64.3.87.88
Lines: 1         
Path: news.uni-stuttgart.de!news.fh-hannover.de!news-han1.dfn.de!news-koe1.dfn.de!news-fra1.dfn.de!newsfeed.hanau.net!newsfeed01.sul.t-online.de!t-online.de!newsfeed.media.kyoto-u.ac.jp!out.nntp.be!propagator-SanJose!in.nntp.be!news-in-sanjose!sjc-feed.news.verio.net!sea-feed.news.verio.net!news.verio.net!msrnewsc1!cppssbbsa01.microsoft.com!tkmsftngp01!tkmsftngp04!u&n&a&c&anceller
Xref: news.uni-stuttgart.de control:40722861

This message was cancelled from within The Unacanceller's glorious new software, Lotus 1-2-3 For Rogue Cancellers.

Received on Fri Jan 25 2002 - 10:37:17 CST