Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Index as primary key?

Re: Index as primary key?

From: Joel Garry <joelga_at_pebble.org>
Date: Tue, 30 Jun 1998 21:55:42 GMT
Message-Id: <slrn6pinqb.m6s.joelga@pebble.org>


On Tue, 30 Jun 1998 10:13:15 GMT, rtgraf_at_sintec.de <rtgraf_at_sintec.de> wrote:
>In article <6n7sgc$mtf$1_at_nnrp1.dejanews.com>,
> rbook_at_my-dejanews.com wrote:
>>
>> Hi!
>>
>> For building the primary key in an dimension table i decided to
>> use an own generated index.
>> Can i use an index as primary key in ORACLE7?
>> How should i do?
>> It is better to generate an artifical primary key?
>>
>> Thank you!
>> Ralf
>Please be more explicit about what you actually want.
>Here are some guesses:
>1.) Technically, a UNIQUE INDEX on Columns with the
> NOT NULL Property is equivalent to a primary key.
> This means "go ahead, use your index" if you
> referred to Oracle Indices created with
> CREATE UNIQUE INDEX.
No, without the constraint you will get ORA-02270: no matching unique or primary key for this column-list when you try to add foreign key constraints referring to the unique key. Even though there _is_ a unique key - the error is a little misleading, in this case. The docs specify there has to be a primary key constraint. They are most definitely not technically equivalent.

Thomas Kyte's example:

>
>See the following example that shows a unique index doesn't work but the unique
>constraint (which will 'take over' the unique index -- it won't recreate it)
>does..
>
>SQL> create table p ( x int );
>Table created.
>
>SQL> create unique index p_idx on p(x);
>Index created.
>
>SQL> create table c ( y int references p(x) );
>create table c ( y int references p(x) )
> *
>ERROR at line 1:
>ORA-02270: no matching unique or primary key for this column-list
>
>
>SQL> alter table p add constraint p_unique unique (x);
>Table altered.
>
>SQL> create table c ( y int references p(x) );
>Table created.
>
>

--
These opinions are my own and not necessarily those of Information Quest or Pebble In The Sky http://www.informationquest.com mailto:jgarry@nospameiq.com http://ourworld.compuserve.com/homepages/joel_garry Remove nospam to reply. mailto:joel_garry_at_compuserve.nospam.com "See your DBA?" I AM the @#%*& DBA! Received on Tue Jun 30 1998 - 16:55:42 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US