Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Location
    India
    Posts
    7

    Angry Unanswered: Key Preserved Table(s) In Oracle ???

    Hi,

    Can someone please elaborate the necessary and sufficient rules (if there are) for a table to be "key preserved" in select result ?

    As this is one of the necessary conditions for a join view to be modifiable(talking about data only, not definition, updatable wud be more precise) in Oracle.

    Hope I am able to make myself clear :-|

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Key Preserved Table(s) In Oracle ???

    For a table to be "key preserved" in a view requires that the view be guaranteed to return only 1 copy of each row from that table. This means that the tables it is joined with are joined by columns that form a primary or unique key for those tables - i.e. they are "lookups" for the "base" table of the view.

    The term "key preserved" tends to confuse because people think it means that the table concerned must have a primary key. Well, of course every table should have a primary key, but to be "key preserved" does not require one: it is all the other tables in the view that need to have primary or unique keys to prevent duplication of rows in the "key preserved" table. The term really means (as I see it) that the keys of the key preserved table (including the physical key ROWID) are also keys for the view, and so the view can "stand in" for the key preserved table in inserts, updates and deletes.

    I hope that helps a little.

  3. #3
    Join Date
    Dec 2003
    Location
    India
    Posts
    7

    Re: Key Preserved Table(s) In Oracle ???

    Hi Andrews,
    Thanks! I really didn't expect that someone will take the pain even to read my question?

    >For a table to be "key preserved" in a view requires that the view be guaranteed to return only 1 copy of each row from that table.

    I also agree with you.

    >The term "key preserved" tends to confuse because people think it means that the table concerned must have a primary key.

    This is regarding the implementation of *updatable views*. Here 'm developing a database server. I m clear what *key preserved* table is, (sorry I am sounding offensive) but not about all the suffcient conditions which can make a table *key preserved* in a *select* query.

    >all the other tables in the view that need to have primary or unique keys to prevent duplication of rows in the "key preserved" table.

    But this is the one condition, there may (or may not be, but are u sure?) several others.

    >The term really means (as I see it) that the keys of the key preserved table (including the physical key ROWID) are also keys for the view, and so the view can "stand in" for the key preserved table in inserts, updates and deletes.

    But *key preserved* concept seems genuine only for join queries, and "PHYSICAL ROWID" can't be selected involving joins.

    I will be thankful, if u throw a little more light on this concept.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Key Preserved Table(s) In Oracle ???

    >>all the other tables in the view that need to have primary or unique keys to prevent duplication of rows in the "key preserved" table.

    >But this is the one condition, there may (or may not be, but are u sure?) several others.

    I am sure. No other conditions are necessary to make a table key-preserved. Of course, there are other conditions necessary to make a view updateble (copied from docs):

    1) If a view is defined by a query that contains SET or DISTINCT operators, a GROUP BY clause, or a group function, then rows cannot be inserted into, updated in, or deleted from the base tables using the view.
    2) If a view is defined with WITH CHECK OPTION, then a row cannot be inserted into, or updated in, the base table (using the view), if the view cannot select the row from the base table.
    3) If a NOT NULL column that does not have a DEFAULT clause is omitted from the view, then a row cannot be inserted into the base table using the view.
    4) If the view was created by using an expression, such as DECODE(deptno, 10, "SALES", ...), then rows cannot be inserted into or updated in the base table using the view.

    >But *key preserved* concept seems genuine only for join queries, and "PHYSICAL ROWID" can't be selected involving joins.

    Well, it is trivially true for queries without joins! And a physical rowid can certainly be selected in a query involving joins:

    SQL> select emp.ename, emp.rowid, dept.dname, dept.rowid
    2 from emp, dept
    3 where emp.deptno = dept.deptno
    4 /

    ENAME ROWID DNAME ROWID
    ---------- ------------------ -------------- ------------------
    SMITH AAA6BFAAFAAAB5yAAA RESEARCH AAA6BDAAFAAABIPAAB
    ALLEN AAA6BFAAFAAAB5yAAB SALES AAA6BDAAFAAABIPAAC
    WARD AAA6BFAAFAAAB5yAAC SALES AAA6BDAAFAAABIPAAC
    JONES AAA6BFAAFAAAB5yAAD RESEARCH AAA6BDAAFAAABIPAAB
    MARTIN AAA6BFAAFAAAB5yAAE SALES AAA6BDAAFAAABIPAAC
    BLAKE AAA6BFAAFAAAB5yAAF SALES AAA6BDAAFAAABIPAAC
    CLARK AAA6BFAAFAAAB5yAAG ACCOUNTING AAA6BDAAFAAABIPAAA
    SCOTT AAA6BFAAFAAAB5yAAH RESEARCH AAA6BDAAFAAABIPAAB
    KING AAA6BFAAFAAAB5yAAI ACCOUNTING AAA6BDAAFAAABIPAAA
    TURNER AAA6BFAAFAAAB5yAAJ SALES AAA6BDAAFAAABIPAAC
    ADAMS AAA6BFAAFAAAB5yAAK RESEARCH AAA6BDAAFAAABIPAAB
    JAMES AAA6BFAAFAAAB5yAAL SALES AAA6BDAAFAAABIPAAC
    FORD AAA6BFAAFAAAB5yAAM RESEARCH AAA6BDAAFAAABIPAAB
    MILLER AAA6BFAAFAAAB5yAAN ACCOUNTING AAA6BDAAFAAABIPAAA

    These ROWIDs are accessible to Oracle when you query from a view, even though the ROWIDs values are not explicitly selected in the view. Oracle could not update the rows without knowing the ROWID.

    >I will be thankful, if u throw a little more light on this concept.

    I have probably exhausted my ability to do this here. Have you read all the Oracle documentation on the topic, e.g. Application Developer's Guide - Fundamentals, and Concepts?

  5. #5
    Join Date
    Dec 2003
    Location
    India
    Posts
    7

    Talking Re: Key Preserved Table(s) In Oracle ???

    Ohhh.... Thanks! Really it was quite apparent that u did a lot of work to clarify the doubt.

    Yes, I had read Oracle Docs and regarding "RowId" in join queries I meant, "RowId" without qualifier (really).

    Thanks again!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •