Re: Can Rowid be a foreign key? If so, would you recommend?

Tom is as usual right on the mark. I thought I'd just make one comment. "Unless you delete and reinsert the record" means not just using DML, but export/import too (most of you were probably already aware of that, but just to be sure!).

Pete

Thomas Kyte wrote:

> A copy of this was sent to "Ling" <circetsui_at_email.msn.com>
> (if that email address didn't require changing)
> On Sun, 23 Jan 2000 17:24:18 -0500, you wrote:
>
> >Hi all,
> >Just wonder if Oracle ROWID can be a foreign key in another table? And if
>
> technically -- yes.
>
> >so, would you recommend to do so.
>
> no, not at all, never.
>
> >Also is the rowid always be a constant to
> >a record?
> >
>
> In Oracle8.0 and before -- yes (unless you delete and reinsert the record in
> which case it is not the same record anymore).
>
> In Oracle8i release 8.1 and later there are at least 2 cases -- with paritioned
> tables with "Enable row movement" and IOT's -- that may cause a rowid to change
> for a row without having deleted it and reinserted it (an update can cause it to
> change).
>
> for example:
>
> 1* create table x ( x int primary key ) organization index
> ops$tkyte_at_8i> /
>
> Table created.
>
> ops$tkyte_at_8i> insert into x values ( 1 );
>
> 1 row created.
>
> ops$tkyte_at_8i> select rowid from x;
>
> ROWID
> -----------------------------------------
> *BADASQoCwQL+
>
> ops$tkyte_at_8i> update x set x = x + 100;
>
> 1 row updated.
>
> ops$tkyte_at_8i> select rowid from x;
>
> ROWID
> -----------------------------------------
> *BADASQoDwgIC/g
>
> >Thank you so much
> >
> >Ling
> >
>
> --
> See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
> Current article is "Part I of V, Autonomous Transactions" updated June 21'st
>
> Thomas Kyte tkyte_at_us.oracle.com
> Oracle Service Industries Reston, VA USA
>
> Opinions are mine and do not necessarily reflect those of Oracle Corporation

Received on Mon Jan 24 2000 - 11:44:33 CST