Home » Developer & Programmer » Designer » Schema issues
Schema issues [message #90830] Thu, 03 February 2005 23:43 Go to next message
Prasad
Messages: 104
Registered: October 2000
Senior Member
hi,

I have a doubt regarding Referential Integrity constraint in Oracle.

There is a scenario where we need to maintain different schemas.

DC entities refer to LOC entities and we need to give the referential integrity.

Assume that there are 2 users LOC and DC.
LOC contains a table called 'Group' with 'grp_id' as PK and DC contains a table GRP_CTRL.

Can i execute following commands in DC schema??

CREATE TABLE GRP_CTRL (
grp_id NUMBER(11) NOT NULL,
post_date DATE NOT NULL,
updated_by VARCHAR2(30) NULL,
update_module VARCHAR2(1024) NULL,
update_timestamp DATE NULL
);

ALTER TABLE GRP_CTRL
ADD ( FOREIGN KEY (grp_id ) REFERENCES LOC.GROUP) ;

Currently it is giving the error as user or table doesn't exist that seems to be logical because LOC tables are not visible to DC.

thanx
prasad
Re: Schema issues [message #90831 is a reply to message #90830] Fri, 04 February 2005 11:10 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
You can have RI constraints between 2 different schemas on the same database if you have granted sufficient privs from the Parent table owner to the Child table owner. If you grant ALL, you will grant SELECT, INSERT, UPDATE, DELETE, REFERENCES etc. REFERENCES is the main one required for the FK relationship.
Previous Topic: Database Design
Next Topic: Order of IVID altered after an export import workarea !
Goto Forum:
  


Current Time: Fri Mar 29 05:09:43 CDT 2024