Re: Referencing SYS.DBA

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 23 Feb 1999 13:43:40 GMT
Message-ID: <36d8aeee.87806018@192.86.155.100>

A copy of this was sent to "news.logica.com" <GoorberghJ_at_Logica.com> (if that email address didn't require changing) On Tue, 23 Feb 1999 13:32:11 +0100, you wrote:

>Hi,
>
>The following script
>
>create table test_user_ref (name varchar2(30),
> userid_ref number,
> constraint fk_userid
> foreign key (userid_ref)
> references SYS.DBA_USERS
>(USER_ID));
>results in:
>
>ERROR at line 5:
>ORA-02444: Cannot resolve referenced object in referential constraints
>
>Does anybody have a workaround for this problem?
>

not really -- the problem is a couple fold:

sys objects are sys objects, they are special, you cannot alter them, should not reference them, cannot create triggers on them, and so on. For example, if we let you do what you tried above, you would be 'breaking' the DROP USER command -- most likely causing an internal error of some sort (we weren't expecting that)
dba_users is a view, you cannot 'reference' a view with declaritive integrity.
please don't even bother trying to do it with the base table referenced in the dba_users view.

You could put a trigger on the test_user_ref table to check upon insert or update if the record exists but if someone drops a user -- there will be no detection of that event. In Oracle8.1, you can put a trigger on database events such as create and drop so you will be able to do this in the future.

>Thanx,
>
>Jeroen
>

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Feb 23 1999 - 07:43:40 CST

Re: Referencing SYS.DBA_USERS ???