Atypical problem with ORA-01445

Question

I have problem with following query Q1:

SELECT ADDRESS.STREET
     FROM  MYSALES.MYADDRESS ADDRESS 
 INNER JOIN MYSALES.MYCOUNTRY MCOUNTRY ON MCOUNTRY.MYCOUNTRY_ID = ADDRESS.MYCOUNTRY_ID 
 INNER JOIN MYGLOBAL.COUNTRY COUNTRY ON COUNTRY.COUNTRY_ID = MCOUNTRY.COUNTRY_ID 

When we translate this to good old notation, Q2:

SELECT ADDRESS.STREET
FROM MYSALES.MYADDRESS ADDRESS 
    ,MYSALES.MYCOUNTRY MCOUNTRY 
    ,MYGLOBAL.COUNTRY COUNTRY
WHERE 
    MCOUNTRY.MYCOUNTRY_ID = ADDRESS.MYCOUNTRY_ID 
    AND COUNTRY.COUNTRY_ID = MCOUNTRY.COUNTRY_ID 

The problems start when I wrap in view:

SELECT * FROM (Q1) or SELECT * FROM (Q2) results with error:

ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table 01445. 00000 - "cannot select ROWID from, or sample, a join view without a key-preserved table"

But for query Q3:

 SELECT * FROM  MYSALES.MYCOUNTRY MCOUNTRY
     INNER JOIN MYGLOBAL.COUNTRY COUNTRY ON COUNTRY.COUNTRY_ID = MCOUNTRY.COUNTRY_ID 

Result of SELECT * FROM (Q3) is correct.

So the problem is in Q4:

  SELECT * FROM MYSALES.MYADDRESS ADDRESS 
    INNER JOIN MYSALES.MYCOUNTRY MCOUNTRY ON MCOUNTRY.MYCOUNTRY_ID = ADDRESS.MYCOUNTRY_ID 

When I wrap the last query in a view, it fails, with the above error.

The columns that end with _ID, are should be primary or references key.

Could you please guide me a hint where should I look for solution ?

Answer 1

The solution of the problem is that table MCOUNTRY did not have the primary key. It was only configured with unique constraint that was called is the same way that primary keys. Even when the unique column was used in query Oracle could not provide expected result.

Something obvious but still lesson to learn, unique constraint is not the same as primary key where it comes to joins.

Have a nice day.