The problem is *NOT* the column names or the underscores in the table
names.

The problem is simply: one or more child records has a value in the
"housetype" column that does not exist in the "oil_housetype" table.

This query will identify the problem value(s) and show you the record(s)
that would violate this constraint:

select * from oil_customers oc
where not exists (select 1 from oil_housetype oh
where oh.oil_housetype = oc.houstype)

After you find them, you could do one (or more of these) actions:
1. Delete the "invalid" rows
2. Update the "invalid" values to valid values
3. Add the "invalid" values to the oil_housetype table
4. Create the constraint with "novalidate" to allow these records to be
grandfathered in, but prevent any future invalid values.


-----Original Message-----
From: Joel
[mailto:oracledba-ezmlmshield-x77760239.[Email address protected]
Sent: Wednesday, October 31, 2007 7:59 AM
To: LazyDBA Discussion
Subject: RE: Problem assigning a foreign key

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




---------------------------------------------------------------------
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



THE INFORMATION CONTAINED IN THIS E-MAIL MESSAGE AND ANY ATTACHMENTS SENT FROM GENTEX CORPORATION IS GENTEX CONFIDENTIAL INFORMATION INTENDED ONLY FOR THE PERSONAL USE OF THE INDIVIDUAL OR ENTITY NAMED ABOVE. If you are not the intended recipient, you are hereby notified that any review, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail, and delete this e-mail message and any attachments from your computer.



Oracle LazyDBA home page