Re: foreign key between different users.

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 Received on Sat Apr 20 2002 - 19:18:32 CEST