Re: updating views - preserved keys - confusion

From: DanHW <danhw_at_aol.com>
Date: 2000/06/23
Message-ID: <20000623021607.20275.00000118_at_ng-fk1.aol.com>#1/1

>HiYa
>
>if I have 2 tables
>
>Table 1
>COL1 PRIMARY_KEY,
>COL2 PRIMARY_KEY,
>COL3,
>COL4
>
>Table 2
>COLA PRIMARY_KEY,
>COLB,
>COLC
>
>and declare a view
>
>View 1
>SELECT COL1, COL2, COLA
>from table1, table2
>where COL1 = COLA;
>
>
>are both tables preserved key tables??
>
>I want to write SQL to update rows in either table depending on the
>values that are changed
>
>thanks

First, you will need to determine yourself which table needs to be updated; Oracle will only do one. You will have to identify the table to update and do the appropriate SQL... but I have a question...

If you define a view
VIEW1 as SELECT T1.COL1,T1.COL2, T2.COLA, T2.COLB FROM TABLE1 T1, TABLE2 T2 WHERE
T2.COL2 = T2.COLA When a user changes COLB, how do you know if [1] they are attempting to change the value of COLB for that row in Table 2, or [2] change COL2 in Table 1 to point to a different row in TABLE2?

If they are doing [2], you have a lot of work to do because you need to find the new row in TABLE2, create one if it doesn't exist, then update TABLE1.COL2 to point to the newly created row. You will also need a UK on COL2 or you will have more problems.

If you want to do the update thrrough a view, you can only update one table. Which table will depend on the PK-FK relationships between the table, not the view definition or the data in the tables.

What you need is to define the tables like this: (You can only have 1 PK in a table; for this to make sense, you need a FK)

Table 1
COL1 PRIMARY_KEY,
COL2 FK
COL3,
COL4 Table 2
COLA PRIMARY_KEY,
COLB,
COLC Now create a FK from TABLE1.COL2 to TABLE2.COLA (COLA must be a PK or a UK, so this table definition is acceptable).

If you define a view
VIEW1 as SELECT T1.COL1,T1.COL2, T2.COLA, T2.COLB FROM TABLE1 T1, TABLE2 T2 WHERE
T2.COL2 = T2.COLA Table 1 is key preserved, so it can be updated through the view. To understand this, think of the table created by the view, and look at what columns in the view are still unique by virtue of the PK in the base tables...

COL1 - unique - (think of it as the PK in the view)
COL2 - not unique (several rows in COL1 can use the same FK value)
COLA - not unique (this is the tricky one - but because multiple rows in table

1 can have the same value of COL2, the entire row from TABLE2 is copied into the view, no row from table 2 is unique in the view) COLB - not unique

example:
TABLE1

  row 1:   col1=1 col2="7"
  row 2:   col1=2 col2="8"
  row 3:   col1=3 col2="8"

Table 2
 row 1: colA="7" colB="100"
 row 2: colB="8" colB="101"

Views rows are

1,7,7,100
2,8,8,101
3,8,8,101

Col1 in the view is still unique, so when you update the row, Oracle can still determine the row in table A to update. An update to any other column is ambigous as to what you are trying to do.

Thus, the only table involved in the view whose PK in the view is the same as the PK in the table is TABLE1. This is the only table that can be updated through the view.

Hope this helps...

Dan Hekimian-WIlliams Received on Fri Jun 23 2000 - 00:00:00 CEST