Re: FK referencing column in another SCHEMA/TABLESPACE.

From: Stephan Born <stephan.born_at_beusen.de>
Date: Thu, 04 Nov 1999 15:46:07 +0100
Message-ID: <38219C2F.FB573DCA@beusen.de>

> Hi,
>
> Oracle 8.0.5 on Solaris and Linux.
> I have two separate apps in separate tablespaces.
> But there is a logical connection between them, I want a FK in one
> column to reference
> a table/column in the other apps SCHEMA/TABLESPACE.
> I've granted SELECT on that other table to the SCHEMA FK I want to
> refence it,
> but i still can't create the reffering table. I'm getting complaints
> about insufficient priviliges.
>
> Is this the wrong way to do it alltogether.
> Or have i just missed something to get it to work ?
>
> I'd be very gratefull for any coments.
>
> Best reagards.
>
> Björn-Åke Segrell / Pegit AB

I think you have to grant also the reference right ....

This is from the Oracle manual:

Example IV

To grant BLAKE the REFERENCES privilege on the EMPNO column and the UPDATE privilege on the EMPNO, SAL, and COMM columns of the EMP table in the schema SCOTT, issue the following statement:

GRANT REFERENCES (empno), UPDATE (empno, sal, comm) ON scott.emp
TO blake

BLAKE can subsequently update values of the EMPNO, SAL, and COMM columns. BLAKE can also define referential integrity constraints that refer to the EMPNO column. However, since the GRANT statement lists only these columns, BLAKE cannot perform operations on any of the other columns of the EMP table.

For example, BLAKE can create a table with a constraint:

CREATE TABLE dependent
(

    dependno NUMBER,
    dependname VARCHAR2(10),
    employee NUMBER CONSTRAINT in_emp REFERENCES scott.emp(empno) )

The constraint IN_EMP ensures that all dependents in the DEPENDENT table correspond to an employee in the EMP table in the schema SCOTT.

Hope that will help you, let me know.

Regards, Stephan

Dipl.-Inf. (FH) Stephan Born   | beusen Consulting GmbH
fon: +49 30 549932-0           | Landsberger Allee 392
fax: +49 30 549932-29          | 12681 Berlin
mailto:stephan.born_at_beusen.de  | Germany
---------------------------------------------------------------
       PGP-Key verfügbar       |      PGP-Key available
---------------------------------------------------------------

Received on Thu Nov 04 1999 - 08:46:07 CST