RE: composite primary key versus unique index
i did exactly the first thing you named. <> --- Begin Message --- You mean you created an index A on the table with the descending column order, and then added a primary key constraint, which created another index on the same columns (except that all the columns are in ascending order). Or did you do something different? If you did something different I'd be curious to know exactly what you implemented. > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > > 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) > > > -Original Message- > From: Jacques Kilchoer > > 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? -- 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 ---
RE: composite primary key versus unique index
You mean you created an index A on the table with the descending column order, and then added a primary key constraint, which created another index on the same columns (except that all the columns are in ascending order). Or did you do something different? If you did something different I'd be curious to know exactly what you implemented. > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > > 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) > > > -Original Message- > From: Jacques Kilchoer > > 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? -- 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).
RE: composite primary key versus unique index
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) <> --- 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 ---
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).
RE: composite primary key versus unique index
The question is not very clear (at least to me). But the difference between primary key and unique key is that primary key does not allow null values, while unique - does. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- [EMAIL PROTECTED] Sent: Tuesday, July 08, 2003 3:54 AM To: Multiple recipients of list ORACLE-L hi all 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 ? thanks vr.gr. G Kor Sr. System Engineer I&DM Db RDW Holland -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Igor Neyman 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).
