Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Oracle Integrity Constraint Question
Hi,
I have a personnel table that is referenced from two separate userspaces. The integrity constraints are enforced if user1 tries to delete a person but if user2 deletes a person, no constraints are checked. Why? Here is the example.
User1 creates and owns table 'md_person' and the relational table 'de_personnel'.
create table md_person (
id INT, first_name VARCHAR(80), middle_name VARCHAR(80), last_name VARCHAR(80), address VARCHAR(4000), PRIMARY KEY(id)
create table de_personnel (
id INT, rec_id INT, person_id INT, PRIMARY KEY(id), FOREIGN KEY(dif_id) REFERENCES MD_ENTRY(id), FOREIGN KEY(person_id) REFERENCES MD_PERSON(id)
User2 needs its own relational table:
create table re_personnel (
id INT, rec_id INT, person_id INT, PRIMARY KEY(id), FOREIGN KEY(rec_id) REFERENCES RECORD_ENTRY(id), FOREIGN KEY(person_id) REFERENCES mddba.MD_PERSON(id)
Now user2 grants:
grant delete, index, insert, update, references, select on
se_personnel to mddba;
and user1 grants:
grant delete, index, insert, update, references, select on md_person
to serfdba with grant option;
The integrity constraints work fine and are enforced if user1 deletes
a person that has a user1 record pointing to it under the same
userspace, but if user2 deletes a person in the user1 owned table, the
constraints are not checked.
I'd appreciate any suggestions or ideas why this may be.
Thanks in advance,
Dave Received on Thu Jan 17 2002 - 15:53:34 CST