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.


Oracle LazyDBA home page