| Schema issues [message #90830] |
Thu, 03 February 2005 23:43  |
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  |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
