Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Re[2]: What Sort of Privilege?
Jo,
I guess I should clarify.
If you made a consious decision to maintain multiple schema's within your database, then it is your choice.
I prefer one schema holding all of the database tables that make up the corporate-wide application.
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
From: jo_holvoet_at_amis.com [mailto:jo_holvoet_at_amis.com]
Sent: Tuesday, August 24, 2004 7:58 AM
To: oracle-l_at_freelists.org
Cc: 'oracle-l_at_freelists.org'; oracle-l-bounce_at_freelists.org
Subject: RE: Re[2]: What Sort of Privilege?
Not sure about that. We have for example an employee table that is referenced just about everywhere (e.g. which salesperson is responsible for this customer, which employee registered for this training course, which operator filed this incident report, ...). These are all different apps/schemas referencing this table so I don't see how we can avoid cross-schema FKs.
mvg/regards
Jo
"Mercadante, Thomas F" <thomas.mercadante_at_labor.state.ny.us>
Sent by: oracle-l-bounce_at_freelists.org
08/24/2004 13:49
Please respond to oracle-l
To: "'oracle-l_at_freelists.org'" <oracle-l_at_freelists.org> cc: Subject: RE: Re[2]: What Sort of Privilege?
Peter,
I totally agree with you. Allowing Fk references from outside of the
schema
is inviting confusion, duplication and disaster. There is really no good
reason for it.
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
From: Peter Robson [mailto:pgro_at_bgs.ac.uk]
Sent: Tuesday, August 24, 2004 4:57 AM
To: Jackie Brock
Cc: oracle-l_at_freelists.org
Subject: Re[2]: What Sort of Privilege?
Whoa there everybody!
While the answer is quite correct, of course (I read the FM years ago),
what
has not been raised is the danger of allowing reference to multiply
unhindered across numerous schemas.
Be very careful if you choose to do this, otherwise, without a little
care,
you may manage to lock your entire database solid with conflicting FKs!
We did, learned our lesson, and now reference is ONLY permitted within the one corporate schema.
peter
edinburgh
.............
JB> references
JB> All,
JB> what kind of privilege new for creating a FK constraint which JB> reference table belongs to another schema.
JB> ALTER TABLE PAM.table1 JB> ADD CONSTRAINT FKCONS1 JB> FOREIGN KEY (ID) JB> REFERENCES schema1.table2 (ID2);
JB> I have grant select,insert,update,delete for table2 BUT still I get JB> insufficient privilege. any idea?
JB> Hamid Alavi
--
mailto:pgro_at_bgs.ac.uk
BGS. . http://www.bgs.ac.uk *********************************************************************Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlput 'unsubscribe' in the subject line.
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlput 'unsubscribe' in the subject line.
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
![]() |
![]() |