Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?

share|improve this question
add comment

1 Answer

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.