Home -> Usenet -> comp.databases.oracle -> HELP. Strange problem creating foreign key constraint

HELP. Strange problem creating foreign key constraint

From: Chris Archer <clarcher_at_mc4adm.uwaterloo.ca>
Date: 1995/07/07
Message-ID: <DBCtoL.L7D_at_watserv1.uwaterloo.ca> 






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





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 Fri Jul 07 1995 - 00:00:00 CEST












Original text of this message