Re: HELP. Strange problem creating foreign key constraint

From: anne gates <afgate_at_most.magec.com>
Date: 1995/07/13
Message-ID: <DBnvsD.5IJ_at_ss3.magec.com>


In article <DBCtoL.L7D_at_watserv1.uwaterloo.ca>, clarcher_at_mc4adm.uwaterloo.ca (Chris Archer) says:
>

System must explicitly be given references privileges against the table. A user cannot receive reference privileges through a role. Hope this helps.

We are having problems deleting records on parent tables involved in foreign key relationships. Have you been successful in doing deletes? Anne Gates,
afgate_at_most.magec.com

>
>We have a script, derived from CASE, to create a foreign key -
>
>PROMPT Creating constraint APPLTSK_TMPLOBJ_FK
>ALTER TABLE &1..APPLICATION_TASKS ADD (
> CONSTRAINT APPLTSK_TMPLOBJ_FK
> FOREIGN KEY (TSKTMPL_ID)
> REFERENCES &1..TEMPLATE_OBJECTS (
> ID)
>)
>
>The parameter &1 is 'dpauth' in this case. The problem is that whenever the DBA
>executes this script as user SYSTEM, the script fails at
>
>REFERENCES dpauth.TEMPLATE_OBJECTS (
> *
>** table or view does not exist!!
>
>However, user dpauth can run the same script without any problems. We've tried to
>eliminate all potential causes, and have come up with this: SYSTEM cannot seem to
>use a qualified (i.e. owner.table_name) object in the REFERENCES clause.
>THIS DOES NOT MAKE ANY SENSE!!
>(Naturally, the script fails if the table name is unqualified and SYSTEM does not own it)
>
>We've been trying to figure this one out for about 4 hours, with no success.
>Any help with resolving this would be GREATLY appreciated.
>
>-------------------------------------------------------------------------------
>Christopher L. Archer Data Processing, University of Waterloo
>clarcher_at_mc4adm.uwaterloo.ca (519) 885-1211 ext. 2292
>
>From: clarcher_at_mc4adm.uwaterloo.ca (Chris Archer)
>Path: mc4adm.uwaterloo.ca!clarcher
>Newsgroups: comp.databases.oracle
>Distribution: world
>Followup-To:
>Organization: University of Waterloo
>Subject: HELP!! Strange constraint definition error...
>Keywords: Constraints, SQL
>
>Hi, everyone.
>
>I have encountered a strange situation while trying to have a constraint set up
>using an SQL script. Our data processing operations procedure require that the
>DBA create all database objects, so here's the problem:
>
>We have a script, derived from CASE, to create a foreign key -
>
>PROMPT Creating constraint APPLTSK_TMPLOBJ_FK
>ALTER TABLE &1..APPLICATION_TASKS ADD (
> CONSTRAINT APPLTSK_TMPLOBJ_FK
> FOREIGN KEY (TSKTMPL_ID)
> REFERENCES &1..TEMPLATE_OBJECTS (
> ID)
>)
>
>The parameter &1 is 'dpauth' in this case. The problem is that whenever the DBA
>executes this script as user SYSTEM, the script fails at
>
>REFERENCES dpauth.TEMPLATE_OBJECTS (
> *
>** table or view does not exist!!
>
>However, user dpauth can run the same script without any problems. We've tried to
>eliminate all potential causes, and have come up with this: SYSTEM cannot seem to
>use a qualified (i.e. owner.table_name) object in the REFERENCES clause.
>THIS DOES NOT MAKE ANY SENSE!!
>(Naturally, the script fails if the table name is unqualified and SYSTEM does not own it)
>
>We've been trying to figure this one out for about 4 hours, with no success.
>Any help with resolving this would be GREATLY appreciated.
>
>-------------------------------------------------------------------------------
>Christopher L. Archer Data Processing, University of Waterloo
>clarcher_at_mc4adm.uwaterloo.ca (519) 885-1211 ext. 2292
>
>From: clarcher_at_mc4adm.uwaterloo.ca (Chris Archer)
>Path: mc4adm.uwaterloo.ca!clarcher
>Newsgroups: comp.databases.oracle
>Distribution: world
>Followup-To:
>Organization: University of Waterloo
>Subject: HELP!! Strange constraint definition error...
>Keywords: Constraints, SQL
>
>Hi, everyone.
>
>I have encountered a strange situation while trying to have a constraint set up
>using an SQL script. Our data processing operations procedure require that the
>DBA create all database objects, so here's the problem:
>
>We have a script, derived from CASE, to create a foreign key -
>
>PROMPT Creating constraint APPLTSK_TMPLOBJ_FK
>ALTER TABLE &1..APPLICATION_TASKS ADD (
> CONSTRAINT APPLTSK_TMPLOBJ_FK
> FOREIGN KEY (TSKTMPL_ID)
> REFERENCES &1..TEMPLATE_OBJECTS (
> ID)
>)
>
>The parameter &1 is 'dpauth' in this case. The problem is that whenever the DBA
>executes this script as user SYSTEM, the script fails at
>
>REFERENCES dpauth.TEMPLATE_OBJECTS (
> *
>** table or view does not exist!!
>
>However, user dpauth can run the same script without any problems. We've tried to
>eliminate all potential causes, and have come up with this: SYSTEM cannot seem to
>use a qualified (i.e. owner.table_name) object in the REFERENCES clause.
>THIS DOES NOT MAKE ANY SENSE!!
>(Naturally, the script fails if the table name is unqualified and SYSTEM does not own it)
>
>We've been trying to figure this one out for about 4 hours, with no success.
>Any help with resolving this would be GREATLY appreciated.
>
>-------------------------------------------------------------------------------
>Christopher L. Archer Data Processing, University of Waterloo
>clarcher_at_mc4adm.uwaterloo.ca (519) 885-1211 ext. 2292
>
>From: clarcher_at_mc4adm.uwaterloo.ca (Chris Archer)
>Path: mc4adm.uwaterloo.ca!clarcher
>Newsgroups: comp.databases.oracle
>Distribution: world
>Followup-To:
>Organization: University of Waterloo
>Subject: HELP!! Strange constraint definition error...
>Keywords: Constraints, SQL
>
>Hi, everyone.
>
>I have encountered a strange situation while trying to have a constraint set up
>using an SQL script. Our data processing operations procedure require that the
>DBA create all database objects, so here's the problem:
>
>We have a script, derived from CASE, to create a foreign key -
>
>PROMPT Creating constraint APPLTSK_TMPLOBJ_FK
>ALTER TABLE &1..APPLICATION_TASKS ADD (
> CONSTRAINT APPLTSK_TMPLOBJ_FK
> FOREIGN KEY (TSKTMPL_ID)
> REFERENCES &1..TEMPLATE_OBJECTS (
> ID)
>)
>
>The parameter &1 is 'dpauth' in this case. The problem is that whenever the DBA
>executes this script as user SYSTEM, the script fails at
>
>REFERENCES dpauth.TEMPLATE_OBJECTS (
> *
>** table or view does not exist!!
>
>However, user dpauth can run the same script without any problems. We've tried to
>eliminate all potential causes, and have come up with this: SYSTEM cannot seem to
>use a qualified (i.e. owner.table_name) object in the REFERENCES clause.
>THIS DOES NOT MAKE ANY SENSE!!
>(Naturally, the script fails if the table name is unqualified and SYSTEM does not own it)
>
>We've been trying to figure this one out for about 4 hours, with no success.
>Any help with resolving this would be GREATLY appreciated.
>
>-------------------------------------------------------------------------------
>Christopher L. Archer Data Processing, University of Waterloo
>clarcher_at_mc4adm.uwaterloo.ca (519) 885-1211 ext. 2292
>
>
Received on Thu Jul 13 1995 - 00:00:00 CEST

Original text of this message