Oracle Integrity Constraint Question

From: Dave <dkendig_at_gcmd.nasa.gov>
Date: 17 Jan 2002 13:53:34 -0800
Message-ID: <dba457ce.0201171353.71957b39_at_posting.google.com>

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)

) tablespace md8_tables2;

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)

) tablespace md8_tables2;

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)

) tablespace serf_tables2;

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 - 22:53:34 CET