Re: foreign key

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/05/27
Message-ID: <338f3d98.3570644@newshost>#1/1

On 24 May 1997 13:06:05 GMT, "Aram Meguerian" <aram_at_unisys.com.br> wrote:

>
>
>> >The index, if required (and it usually is), must be created manually on
>> >the referring key.
>> >It is, of course, necessary that an index already exist on the
>> >referred-to primary (or other unique) key.
>
> I'm not so sure, but I think it is NOT necessary to have an index
> on the primary (or unique) key. In fact, it is not, since I do have
> some small reference tables (< 10K, < 20 rows), where I CACHE'd them
> and got ridden of the PK index (not the PK, however). Up to now (a
> month latter) I have had no problems.
>
>> >

I'd like to hear how you did that.

SQL> create table foo ( x int primary key );  

Table created.  

SQL> @dbls  

OBJECT_TYPE  OBJECT_NAME                    S TABLESPACE_NAME
------------ ------------------------------ - ------------------------------
INDEX        SYS_C0028434                     USERS
 
TABLE        FOO                              USERS
 
 

SQL> drop index SYS_C0028434;
drop index SYS_C0028434
*
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key    

You should not be able to drop the index for a unique/primary key.

>> >Chrysalis.
>> And, I'm told, if you don't create the index on the FK, you can have
>> awful locking problems.
>
> I haven't seem it hapenning and I have some FK that are no indexed
> also.
>

Refer the chapter 6 in the Application Developers Guide, section entitled "Concurrency Control, Indexes, and Foreign Keys" to see the implications of un-indexed foreign keys...

>> --
>> Paul Brewer
>>
>
>--
>
> Aram Meguerian
> aram_at_unisys.com.br
>
>-------------------------------------------------------------------
> TANSTAAFL - There ain't no such thing as a free lunch
> by Robert A. Heinlein
>-------------------------------------------------------------------
> I don't work at Unisys, it is just my Internet Provider,
> so don't blame it for anything I have just said.
>-------------------------------------------------------------------
>
>

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue May 27 1997 - 00:00:00 CDT