Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003

    Unanswered: updatability in comples view

    I got this from OCP exam book,
    'Only Columns from the key-preserved tables can be modified via the complex view. ...'

    I tested it on 8.1.7. The result doesn't agree with it.

    Test data:

    create table test2
    (id2 number(10),
    value number(10));

    create table test1
    (id1 number(10),
    id2 number(10),
    name varchar2(10));

    create or replace view test1_test2_view as
    (select id1, name, value
    from test1, test2
    where test1.id2 = test2.id2);

    alter table test2
    add constraint pk_test2
    primary key (id2);

    alter table test1
    add constraint fk_test1_id2
    foreign key (id2) references test2 (id2);

    SQL> desc test1
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    ID1 NUMBER(10)
    ID2 NUMBER(10)

    SQL> select column_name, updatable
    2 from user_updatable_columns
    3 where table_name = 'TEST1_TEST2_VIEW'

    ------------------------------ ---
    ID1 YES

    As you can see, test1 doesn't have a primary key. It isn't a key-preserved table according the definition.

    However, columns can be modified.

    Any idea? Or my book outdated?

  2. #2
    Join Date
    Sep 2002
    Provided Answers: 1

    Re: updatability in comples view

    The book isn't wrong about that, your understanding is wrong. The term "key preserved table" doesn't mean that table1 has a primary key, it means that the join from table1 to table2 cannot result in duplicated rows from table1. It is the primary key of table2 that ensures this, because it means that every row from table1 will join to exactly 1 row of table2, so each row of table1 can appear only once in the view. On the other hand, table2 is not key preserved, because the same table2 record can appear many times in the view, once for each table1 record it is referenced by.

    The term "key preserved" means that any unique key of table1 that appears in the view will also be a unique key for the view; table2 is not key preserved because its unique key is not a unique key for the view.

  3. #3
    Join Date
    Nov 2003

    My understanding is similar to your explanation, which doesn't agree exactly with the book. That's why I posted.

    The book reads as
    "A key-preserved table is a table in a complex view whose primary key column is present in the view and whose values are all unique and not NULL in the view".

    I think the clause "whose primary key column is present in the view" is misleading if not wrong.

    In the example I gave, table test1 should be a key-preserved table but it doesn't even have a primary key.

  4. #4
    Join Date
    Sep 2002
    Provided Answers: 1
    Yes I'd agree with you that the book is wrong about that, the author seems to have got confused by the term "key preserved"!

Posting Permissions

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