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: <3c5189c0$0$8506$ed9e5944@reading.news.pipex.net>

"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

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

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