ORA-01445: cannot select ROWID from a join view without a key-preserved table

25 pts.
Tags:
ORA
Oracle 11g
SQL
Hello, For the first time in my IT-life I've encountered following error message when I try to run query using ROWID on a view: ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table I found something about key-preserved tables, but I'm not sure that my comprehension is correct. If I understand it well, so every table has its own rowid and when I use join to build some view, this rowid is also transferred into view. Then, when I use rowid in select statement on the view, database cannot determinate which rowid I want to display and throws error. Thus in case I need rowid, one solution is to select rowid of a particular table as a new column in create view operation and then use in select statement on the view this column. Is it so?


Software/Hardware used:
Oracle database 11g
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

Since your view was created with a LEFT outer join, it means that every row in the view will have a component that comes from “t_first”, and thus, the ROWID from t_first is used as the rowid for the view. If you had a RIGHT join, then the rowid from t_second would be the one used in the view, so your understanding about that is somehow correct.

Now, regarding the error, the problem is that if you don’t use the whole primary key to join the tables, the resulting view could have more than one record for a given record in t_first, and thus, the rowid in the view, which in this case is being taken from t_first would be duplicated, and that is not allowed.

So, besides using the whole pk to join, yes, a solution could be to select the rowid from one or both of the tables to create the view, but you have to keep in mind that even though that column was originated from a rowid, in the view it could appear more than once, or in other words, it is no longer guaranteed to be unique.

Hope I was able to explain it in an understandable way.

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • ToddN2000
    Post your code and let us take a look. Is your ROWID the Identity key in the table?
    134,845 pointsBadges:
    report
  • LukyFox
    No, I don't use ROWID as primary key, but probably I found origin of my problem. I have two tables both with compounded primary keys and then a view which joins these tables with LEFT JOIN. In case I use for joining tables for view only one part of keys or another field, I get always the errror message above. I case I use for JOIN whole compounded key, selecting rowid from resulting view runs correctly. It means if I want to use rowdid in SELECT statement from a view, I always have to use (all) primary keys of relating tables to built a view. I only don't know what is the reason for such behavior. Let's see the code:
    CREATE TABLE T_FIRST
    (
      PKEY_P1              VARCHAR2(20 CHAR)        NOT NULL,
      PKEY_P2              VARCHAR2(20 CHAR)        NOT NULL,
      VALS                 VARCHAR2(20 CHAR)        NOT NULL
    );
    
    ALTER TABLE T_FIRST ADD (
      PRIMARY KEY
      (PKEY_P1, PKEY_P2)
      ENABLE VALIDATE);
      
    CREATE TABLE T_SECOND
    (
      PKEY_P1                         VARCHAR2(20 CHAR) NOT NULL,
      PKEY_P2                         VARCHAR2(20 CHAR) NOT NULL,
      VALS                            VARCHAR2(20 CHAR)
    );
    
    ALTER TABLE T_SECOND ADD (
      PRIMARY KEY
      (PKEY_P1, PKEY_P2)
      ENABLE VALIDATE);
      
    CREATE VIEW MYVIEW_V
    (
       a1,a2,a3
    )
    AS
       SELECT 
              t_first.pkey_p1,
              t_first.pkey_p2,
              t_second.vals
         FROM t_first
              LEFT OUTER JOIN
              t_second
                 ON     t_first.pkey_p1 = t_second.pkey_p1;
                /*
                if following line is uncommented, SELECT rowid from myview_v runns correctly
                */ 
                --    AND  t_first.pkey_p2 = t_second.pkey_p2
                    
    --and then starting of following SELECT cause error ORA-01445:
    SELECT rowid
    from MYVIEW_V;

      
    25 pointsBadges:
    report
  • LukyFox
    Oh, Todd, and thank you for your answer.
    25 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: