i didnt that try on Oracle, but our mainframe Rdms. We have that situation
there with the ascending en descending attributes in the primary key.
The workaround is to create a unique index (with and without desc) and
create afterwards a primary key constraint on it in oracle.
we have oracle 8.1.7.4.10. (including patches)
<<RE: composite primary key versus unique index>>
--- Begin Message ---
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).
--- End Message ---
