ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table
One of our developers has problems with a select of different tables:
SELECT ROWID, TMP_TABLE_ALIAS.* FROM (select a.*, B.*, (SELECT MAX(TMP_CODEMP) FROM ATE_TABLE WHERE TMP_IDTRE = TRE_ID and TMP_FI is null ) AS TRE_CODEMP, (SELECT MAX(REV_DATA) FROM AR_TABLE WHERE REV_ID = TRE_ID ) AS TRE_DATREV FROM AT_TABLE a, AP_TABLE B WHERE PER_ID = TRE_ID (+) ) TMP_TABLE_ALIAS;
This statement works on the development environment, but in the production environment it fails with:
ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table 01445
The database versions is the same in both environments: Oracle 10g Release 2 (10.2.0.5).
After reviewing the tables in the production environment, I noticed that one of the tables has no primary key, and that caused the ORA-01445. Even with a unique index the select fails, it’s mandatory to declare primary keys for the tables when they are involved in joins.