How to preserve key when joining to a preference table in Oracle

join;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 ;

Best Answer

No, it is not. It is not guaranteed (well, it is, with preftype=1, but that is in the subquery, and that is not good enough for the optimizer) that your query returns at most 1 row for each key value from the base table. You need a unique constraint on customerid to make this work:

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;

ERROR at line 3:
ORA-01445: cannot select ROWID from, or sample, a join view without a
key-preserved table

alter table pref add unique(customerid);

Table altered.

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;

no rows selected

This however may not be feasible for you, as a customer can have multiple different preftypes with your original design.

This works without the unique constraint (but not the same result):

alter table pref drop unique(customerid);

Table altered.

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

no rows selected

Or be more specific with the rowid:

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

no rows selected
Related Question