RE: composite primary key versus unique index

I don't understand how you can have those types of objects created.

Let's assume this situation:

create table T (a number, b number, c number) ; create unique index t_pk on t (a asc, b desc) ; alter table t add (constraint t_pk primary key (a, b)) ;

In 8.1 and later, the third statement (add constraint) will return an ORA-0955 error because Oracle is unable to build the index needed for the primary key constraint.

In 8.0 and earlier, the "desc" keyword will be ignored in the "create index" statement, so there will be no reason why you cannot create the primary key constraint and foreign key constraints referencing that primary key constraint.

Now, in 8.1 and later, if the index and the PK have different names, like so:

create table T (a number, b number, c number) ; create unique index t_idx1 on t (a asc, b desc) ; alter table t add (constraint t_pk primary key (a, b)) ;

then the third statement (add constraint) will create a second index on the table named t_pk, and again you will be able to create foreign key constraints referencing the primary key constraint.

What version of Oracle are you running, and could you describe the tables/indexes/constraints involved?

> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
>
> I have the following question for you :
> We have a mainframe database with tables which have composite
> primary keys
> with an ascending and a descending item.
> For example a table with license regisitrations with primary key items
> LICENSE in ascending and REGISTRATION_DATE in descending order.
> As far as i know this is not possible with Oracle, only a unique index
> should be a candidate to do this.
> I said should be, because if you need a foreign key relation to this
> specific composite index i get an ORA-2270 !.
> Anyone with an explanation why a create of a composite unique
> index with an
> ascending and descending order works and not with the primary
> key clause ?
> Is this triggered in oracle 9i or 10i ?

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jacques Kilchoer
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).