Can a Key Preserved Table have a Composite Primary Key, on Oracle db

oracleplsqlprimary-keyupdateview

I'm trying to create an updatable view.

I understand that the db must be able to identify one single row that I'm trying to update.

I believe I satisfied this requirement. There are no aggregates. Each row in the view has a 1-to-1 relationship with rows in the source table which I'm trying to update (MY_CFG_TABLE below).

But my view is not updatable. Why not? Does it have something to do with the composite key?

CFG_VALUE is the column I want to update.

I want a join-view, rather than subquery, so that i can see, in the view, rows in ALL_TAB_COLUMNS for which there's no corresponding row in MY_CFG_TABLE (for that, it will have to be changed to outer join, but for now just trying to make the update work with an inner.)

In the end, I may resort to an INSTEAD OF trigger, but for now I would like to make an updatable join-view– or understand why I cannot.

Here's the update statement which fails:

update VBA_CFG_JOIN_V
SET CFG_VALUE = 'TRUE'
WHERE TABLE_NAME = 'APN_GR'
AND COLUMN_ID = 1
AND CFG_NAME = 'PART_ID';

Here's the error message:

cannot modify a column which maps to a non key-preserved table

How can I make my source table, MY_CFG_TABLE, a Key Preserved Table?

View:

CREATE VIEW VBA_CFG_JOIN_V
AS SELECT F.TABLE_NAME, T.COLUMN_NAME, F.COLUMN_ID, F.CFG_NAME, F.CFG_VALUE 
FROM ALL_TAB_COLUMNS T, MY_CFG_TABLE F 
WHERE F.TABLE_NAME = T.TABLE_NAME AND F.COLUMN_ID = T.COLUMN_ID;

Source tables:

ALL_TAB_COLUMNS is a built-in Oracle view. (Note, I'm not trying to update ALL_TAB_COLUMNS).
https://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2094.htm

MY_CFG_TABLE:

CREATE TABLE "GSM_OWNER"."MY_CFG_TABLE" 
   (    "TABLE_NAME" VARCHAR2(20 BYTE) NOT NULL ENABLE, 
    "COLUMN_ID" VARCHAR2(20 BYTE) NOT NULL ENABLE, 
    "CFG_NAME" VARCHAR2(20 BYTE) NOT NULL ENABLE, 
    "CFG_VALUE" VARCHAR2(20 BYTE) NOT NULL ENABLE, 
     CONSTRAINT "VBA_FIELDS_PK" PRIMARY KEY ("TABLE_NAME", "COLUMN_ID", "CFG_NAME")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "GSM_OWNER_TS"  ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "GSM_OWNER_TS" ;

Best Answer

The problem is that we know that the join will produce only 1 row (and no more) in the view for every row in MY_CFG_TABLE but Oracle doesn't. As described in OraFAQ: Updatable Join Views (emphasis mine):

Clearly the view cannot contain DISTINCT, GROUP BY, CONNECT BY, aggregate functions, UNION, INTERSECT, or MINUS clauses: simple joins are all that is allowed. There is one other restriction: the view must be key preserved. This means that the join must meet the following restrictions:

  • The join criteria must be equals (=) joins.
  • Tables other than the table that is to be updated must be joined on a primary or unique key.

If these conditions are violated, then Oracle cannot guarantee that the view will return one row only for each row in the base table. If two or more rows in the secondary table mapped to each row in the base table, then the update would be ambiguous. An attempt to update a non-key-preserved view will result in an Oracle error.

I assume that either the joined table (which is actually a system view) does not have a UNIQUE or PRIMARY KEY constraint or one of the underlying tables (when the whole view is expanded) is not joined on its unique/primary key. This might be causing the errors when we try to update the view.

The problem may be more complicated as we don't know the exact definition of the underlying ALL_TAB_COLUMNS view. It might have some non-equality joins or non-inner joins or unions that make the new view non-updatable.