You can create an exceptions table and re-execute your statement adding
the exceptions into exceptions clause -- assuming your exceptions table
is named exceptions. You can then identify the orphaned records by
rowid.

The script is probably named something like ?/rdbms/admin/utlexcpt.sql
(close enough).

select * from exceptions;

SELECT * FROM &child_table_name WHERE ROWID IN (SELECT ROW_ID FROM
EXCEPTIONS);

Joel Patterson
Database Administrator
joel.[Email address protected]
x72546
904 727-2546

-----Original Message-----
From: Dave
[mailto:oracledba-ezmlmshield-x77369211.[Email address protected]
Sent: Tuesday, October 30, 2007 7:58 PM
To: LazyDBA Discussion
Subject: Problem assigning a foreign key

I'm trying to set a foriegn key in a table that has the same column name
as another table where that column name in the other table has already
been set as the primary key, but I got an error:

ALTER TABLE oil_customers
ADD CONSTRAINT housetype_oil_customers_fk FOREIGN KEY(housetype)
REFERENCES oil_housetype;

ERROR at line 2:
ORA-02298: cannot validate (SCOTT.HOUSETYPE_OIL_CUSTOMERS_FK) - parent
keys not found

Could this be because of the underscores? I know that the underscore
that comes right after 'housetype' is part of the code, and also the one
coming right before 'fk', but by co-incidence, I had already named the
table 'oil_customers' some time ago. Is that underscore in oil_customers
confusing SQL ?
If I rename all the tables to eliminate the underscore in their names,
wont that screw up other things?
Thanks.



---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



Oracle LazyDBA home page