How to preserve key when joining to a preference table in Oracle I have a table of customers, and a table of customer preferences, where:

create table customer (
  customerId number,
  name varchar2(200),
  primary key (customerId)
);

create table pref (
  preftype number,
  customerId number,
  prefval varchar2(200),
  primary key (preftype, customerId)
);


So basically the pref table records the customer's preference based on each preference type. customerId is unique for each preftype.

Theoretically the following join should not lose the key-preserved property, but I am getting "ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table." Anyway I can rewrite the query to maintain the key-preservedness?

select rowid, vi.* from (
  select c.customerId, c.name, p.prefval
  from customer c, (select * from pref where preftype = 1) p
  where c.customerId = p.customerId (+)
) vi;




Restrictions / requirements:

In our case a query engine is building that outer query (the one with rowid) around the inner query, so we don't have as much control.

We'd like to have a query that can be wrapped around with something like:

select rowid, v.*
from
    ( 
    -- query
    ) v ;
I don't see the reason for having doubly nested subqueries when a simple LEFT JOIN can give the same result: 

select c.customerid, c.name, p.prefval   
       -- , c.rowid                        -- this errs
from customer c
    left join pref p
    on  c.customerid = p.customerid
    and p.preftype = 1 ;


Now, when we add c.rowid in the SELECT list, Oracle parser/optimizer is not able to identify that every row of customers is going to be joined with - at maximum - 1 row from pref. So it throws an error.

We can convert the LEFT JOIN to a subquery though - and no error is thrown:

-- query 2 --
select c.customerid, c.name, 
       ( select p.prefval
         from pref p
         where c.customerid = p.customerid
           and p.preftype = 1 
       ) as prefval,
       c.rowid                               -- but works here
from customer c ;


or putting the customers in a derived table and then left join to pref:

-- query 3 --
select c.customerid, c.name, p.prefval, c.rowid                       
from 
    (select c.*, c.rowid from customer c) c
    left join pref p
    on  c.customerid = p.customerid
    and p.preftype = 1 ;


Tested at rextester.com