|
Home > Archive > Oracle Server > August 2005 > Foreign Key to table in other schema?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Foreign Key to table in other schema?
|
|
| Martijn Tonies 2005-08-24, 9:23 am |
| Hi there,
I've checked the docs, but the only limitation I can find about
FKs is related to clustering.
From the SQL Syntax doc, I figured the below should be possible:
alter table scott.child
add constraint fk_child_child
foreign key (parentid) references northwind.parent (id)
I was logged in with "system" and I can select from "northwind.parent",
but the statement above kept on returning:
ORA-00942: table or view does not exist
Is this possible, or isn't it?
--
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www. databasedevelopmentf
orum.com
| |
| Daniel Fink 2005-08-24, 9:23 am |
| I think that you need to have the 'REFERENCES' privilege granted for
that object.
| |
| Mark D Powell 2005-08-24, 1:23 pm |
| Martijn, you can find information of the References privilege that
Daniel pointed out in the Concepts manual chapter on constraints and in
the SQL manual.
The References privilege allows user B to impose a restriction on the
table owner, A, and is required for B to be able to stop user A from
deleting rows from their own table. So user A must grant references to
user B.
HTH -- Mark D Powell --
| |
| Martijn Tonies 2005-08-24, 8:23 pm |
| Hello Mark,
> Martijn, you can find information of the References privilege that
> Daniel pointed out in the Concepts manual chapter on constraints and in
> the SQL manual.
>
> The References privilege allows user B to impose a restriction on the
> table owner, A, and is required for B to be able to stop user A from
> deleting rows from their own table. So user A must grant references to
> user B.
I'll have another look tomorrow...
I kinda figured that user SYSTEM would have privileges for everything,
by default :-)
I'll report later...
--
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www. databasedevelopmentf
orum.com
| |
| Daniel Fink 2005-08-24, 8:23 pm |
| I don't think that it is the SYSTEM user that you need to worry about.
it is the SCOTT user that needs the privilege.
| |
| Martijn Tonies 2005-08-25, 3:29 am |
| Hello Mark,
> Martijn, you can find information of the References privilege that
> Daniel pointed out in the Concepts manual chapter on constraints and in
> the SQL manual.
>
> The References privilege allows user B to impose a restriction on the
> table owner, A, and is required for B to be able to stop user A from
> deleting rows from their own table. So user A must grant references to
> user B.
Right... It indeed seems to be that when SYSTEM grants REFERENCES
to "scott" on "northwind.parent", it doesn't work, but when NORTHWIND
does, it works alright... Nasty, I didn't know that :-)
I figured: a privilege granted is a privilege granted... Got some reading to
do :-)
--
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www. databasedevelopmentf
orum.com
| |
| NetComrade 2005-08-25, 8:23 pm |
| On Wed, 24 Aug 2005 16:15:19 +0200, "Martijn Tonies"
<m.tonies@upscene-removethis.nospam.com> wrote:
>Hi there,
>
>I've checked the docs, but the only limitation I can find about
>FKs is related to clustering.
>
>From the SQL Syntax doc, I figured the below should be possible:
>
>alter table scott.child
>add constraint fk_child_child
>foreign key (parentid) references northwind.parent (id)
>
>
>I was logged in with "system" and I can select from "northwind.parent",
>but the statement above kept on returning:
>ORA-00942: table or view does not exist
>
>Is this possible, or isn't it?
>
grant references on owner_of_table_to_re
ference.table_name to
owner_of_table_refer
encing.
........
We use Oracle 8.1.7.4 and 9.2.0.6/7 on Solaris 2.7 and RH4
remove NSPAM to email
| |
|
|
|
|
|