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):I assume that either the joined table (which is actually a system view) does not have a
UNIQUE
orPRIMARY 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.