Re: foreign key between different users.

postbus_at_sybrandb.demon.nl (Sybrand Bakker) wrote in message news:<a20d28ee.0204200918.2e21470a_at_posting.google.com>...
> gehart24_at_yahoo.com (george hart) wrote in message news:<c490a7ae.0204191823.3427f7a5_at_posting.google.com>...
> > Hello,
> >
> > Does anyone know if it is possible to have a foreign key for a column
> > between two different users in oracle 9i?
> >
> > basically i am doing something like this:
> >
> > create table foo(id int references otheruser.sometable(id));
> >
> > and getting a
> > ORA-00942: table or view does not exist
> >
> >
> > Any thoughts?
> >
> > Thank you,
> >
> > George
>
>
> Yes it is possible, and of course it is documented.
> Other than the usual privileges otheruser needs to grant you the
> references privilege.
> I strongly advise against it though, you will end up in a myriad of
> foreign keys pointing to different schemas, and this situation can
> also be described with a different word that starts with the letter m.
> I never grant references privilege to any user.
>
> Regards
>
> Sybrand Bakker
> Senior Oracle DBA

I have come across a case where this was useful, but it was clearly a temporary solution. Basically, I would agree with Sybrand. Foreign keys across schemas is not a good thing.

Ed Prochak

-- 
Edward J. Prochak   --- Magic Interface, Ltd.
Ofc: 440-498-3700
on the web at       --- http://www.magicinterface.com
email: ed.prochak_at_magicinterface.com