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

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sun, 23 Jan 2000 20:41:24 -0500
Message-ID: <l7bn8s4os6bo68pqovmlhkken0mduk3fru@4ax.com>

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 Sun Jan 23 2000 - 19:41:24 CST